1. 为什么我们需要关注MySQL索引优化？
我至今还记得第一次在生产环境遇到慢查询时的场景——那是一个用户订单列表页面，随着数据量增长，加载时间从毫秒级逐渐恶化到十几秒。当我打开EXPLAIN分析执行计划时，发现全表扫描的type列赫然显示着"ALL"，而possible_keys列却空空如也。这就是典型的索引缺失案例。
索引之于数据库，就像书籍的目录之于读者。没有索引的查询就像在图书馆里逐页翻找特定内容，而合理的索引能让数据库引擎直接"翻到正确的那一页"。根据我的经验，在千万级数据表中，一个缺失的索引可能导致查询性能相差1000倍以上。
注意：索引不是越多越好。我曾经接手过一个系统，开发者为每个查询条件都建立了索引，结果INSERT操作比原来慢了8倍。每增加一个索引，写入时就需要额外维护一棵B+树。
2. MySQL索引的工作原理与类型选择
2.1 B+树：MySQL索引的基石
MySQL的InnoDB引擎默认使用B+树作为索引结构，这与大多数教科书上介绍的二叉树有很大不同。B+树有三大特点：
- 所有数据都存储在叶子节点，非叶子节点只存键值（类似书的目录只存章节标题和页码）
- 叶子节点通过指针连接形成链表（方便范围查询）
- 通常保持3-4层的树高（千万级数据也只需3次IO）
我常用一个类比向团队解释：假设你在找一本400页的书里的某个知识点：
- 全表扫描：逐页翻完整本书
- 二叉树索引：先看第200页，再看第100或300页...
- B+树索引：先看目录的章节目录（根节点），再看详细目录（非叶子节点），最后直接翻到目标页（叶子节点）
2.2 常见索引类型实战选型
主键索引（PRIMARY KEY）：
CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, PRIMARY KEY (id), UNIQUE KEY (order_no) ) ENGINE=InnoDB;- 必须包含唯一、非空的列
- InnoDB的表数据本身就是主键索引组织表（ clustered index）
- 自增ID vs UUID：在分布式系统中我倾向使用雪花ID（Snowflake）
普通索引（INDEX）：
ALTER TABLE users ADD INDEX idx_email (email);- 适合高选择性的列（如邮箱、手机号）
- 可以包含重复值
- 建议命名规范：idx_字段名
联合索引（Composite Index）：
CREATE INDEX idx_name_phone ON customers(last_name, first_name, phone);- 最左前缀原则：查询必须使用索引的第一列
- 我在电商项目中常用的组合：
(category_id, status, create_time) - 字段顺序公式：区分度高的列在前 + 常用排序字段在后
覆盖索引（Covering Index）： 当索引包含所有查询字段时，引擎无需回表：
-- 假设有索引 (user_id, status) SELECT user_id, status FROM orders WHERE user_id = 1001; -- 比以下查询快3-5倍 SELECT * FROM orders WHERE user_id = 1001;3. 索引优化实战：从EXPLAIN开始
3.1 读懂EXPLAIN的输出
上周我帮团队优化了一个执行时间8秒的查询，关键就是分析EXPLAIN：
EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 100 ORDER BY sales DESC LIMIT 10;输出结果中需要特别关注的列：
| 列名 | 值 | 诊断建议 |
|---|---|---|
| type | range | 理想情况是ref或eq_ref，range表示范围扫描 |
| key | idx_category | 实际使用的索引名称 |
| rows | 18426 | 预估扫描行数，值越大性能越差 |
| Extra | Using filesort | 需要额外排序，考虑添加(sales)到索引 |
3.2 索引失效的六大陷阱
在我经历的优化案例中，90%的性能问题源于这些陷阱：
- 隐式类型转换：
-- phone是varchar类型 SELECT * FROM users WHERE phone = 13800138000; -- 索引失效 SELECT * FROM users WHERE phone = '13800138000'; -- 使用索引- 函数操作：
-- 错误示范 SELECT * FROM orders WHERE DATE(create_time) = '2023-01-01'; -- 优化方案 SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';- 前导通配符：
-- 无法使用索引 SELECT * FROM products WHERE name LIKE '%手机%'; -- 可以使用索引 SELECT * FROM products WHERE name LIKE '苹果%';- OR条件不当：
-- 索引失效 SELECT * FROM logs WHERE action = 'login' OR user_id = 100; -- 优化方案 SELECT * FROM logs WHERE action = 'login' UNION ALL SELECT * FROM logs WHERE user_id = 100 AND action != 'login';- 联合索引顺序错误：
-- 索引 (a,b,c) SELECT * FROM table WHERE b = 2 AND c = 3; -- 无法使用索引- 索引列参与计算：
-- 错误示范 SELECT * FROM products WHERE price + 100 > 200; -- 优化方案 SELECT * FROM products WHERE price > 100;4. 高级优化策略与实战案例
4.1 索引跳跃扫描（Index Skip Scan）
MySQL 8.0的新特性，当联合索引前导列区分度低时可以优化：
-- 索引 (gender, name) SELECT * FROM employees WHERE name = '张三'; -- 8.0前：全表扫描 -- 8.0后：先枚举gender值('M','F')，再查name4.2 索引下推（ICP）
存储引擎在取数据前先过滤，减少回表次数：
-- 索引 (age, salary) SELECT * FROM staff WHERE age > 30 AND salary < 10000; -- 无ICP：先查age>30的所有行，再回表过滤salary -- 有ICP：在索引层就过滤age>30 AND salary<100004.3 电商平台真实案例
去年优化过一个商品搜索接口，原始查询：
SELECT * FROM products WHERE category_id IN (1,5,9) AND status = 1 AND price BETWEEN 100 AND 500 ORDER BY sales_volume DESC LIMIT 20 OFFSET 0;优化步骤：
- 创建联合索引：
(category_id, status, price, sales_volume) - 改写分页逻辑：使用
WHERE id > last_id替代LIMIT offset - 引入结果缓存：高频查询结果缓存5分钟
最终效果：响应时间从1200ms降至80ms，QPS从50提升到300+。
5. 索引监控与维护
5.1 识别无用索引
我每月会运行这个查询找出使用率低的索引：
SELECT object_schema, object_name, index_name, COUNT_READ, COUNT_FETCH FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL ORDER BY COUNT_READ + COUNT_FETCH ASC;5.2 索引统计信息更新
当发现执行计划不准确时：
ANALYZE TABLE orders; -- 更新统计信息5.3 在线DDL最佳实践
大表添加索引的正确姿势：
-- 原生方式（锁表） ALTER TABLE big_table ADD INDEX idx_new_column(new_column); -- 使用pt-online-schema-change pt-online-schema-change --alter "ADD INDEX idx_new_column(new_column)" D=database,t=big_table6. 特殊场景的索引策略
6.1 JSON字段索引
处理产品属性等半结构化数据：
ALTER TABLE products ADD INDEX idx_color ((CAST(properties->'$.color' AS CHAR(20)))); SELECT * FROM products WHERE JSON_EXTRACT(properties, '$.color') = 'red';6.2 全文索引（FULLTEXT）
适用于商品搜索：
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, body); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);6.3 空间数据索引（SPATIAL）
地理位置查询：
ALTER TABLE stores ADD SPATIAL INDEX(position); SELECT * FROM stores WHERE ST_Distance_Sphere(position, POINT(116.404, 39.915)) < 1000;在多年的MySQL优化实践中，我发现最有效的学习方式就是不断分析真实查询的EXPLAIN输出，配合慢查询日志定位问题。每次优化后，建议用sysbench或实际业务流量进行基准测试，避免优化引入新的性能瓶颈。记住，索引优化是持续过程，随着数据增长和查询模式变化，需要定期review和调整。
