MySQL索引优化实战：从B+树原理到加索引避坑指南

1. 动手之前，先把这几个问题想清楚

做 MySQL 索引优化有个很常见的现象：一听到查询慢，第一反应就是“加索引”，加完之后发现要么没效果，要么反而把写入拖垮了。我在线上环境踩过太多次这种坑，所以这篇博文先不讲语法，而是把加索引前必须想明白的几件事说清楚。真正决定索引是否有效的，不是你有没有加，而是加之前有没有把数据分布、查询模式和写入压力理清楚。

1.1 索引不是越多越好，先看业务怎么查

索引本质上是拿空间换时间，是给查询服务的一种“预排序”结构。你问“这张表要不要加索引”，应该先回答几个问题：这条 SQL 是低频统计还是高频核心路径？过滤条件区分度高不高？查询是不是要回表拿整行？更新频率高不高？

我见过最典型的反面案例：把表上所有查询涉及的字段全建了单列索引，一张 10 个字段的表建了 8 个索引。结果是查询计划确实能选到索引，但每个 INSERT 都要同步维护 8 棵 B+ 树，写入延迟直接从 5ms 涨到 20ms，磁盘空间也翻了快一倍。索引数量不是越多越好，而是“够用就好”：核心查询路径上，能用联合索引覆盖尽量用联合索引；低频报表查询，宁可全表扫，也别让它拖累在线业务。

判断有没有必要加索引，有一个粗略的估算方法：在数据量百万级以上的表，如果 WHERE 过滤后命中的行数占全表比例超过 20%，优化器大概率会放弃索引、选择全表扫描，因为回表成本太高。所以先跑一条验证 SQL，比如统计命中行数，再决定加不加。

我一般用这个思路做索引规划：

• 先收集慢查询日志，按执行频率和扫描行数排序；
• 对每一条高频 SQL，人工分析 WHERE、ORDER BY、JOIN 条件，列出候选字段；
• 用候选字段做区分度验证，COUNT(DISTINCT col) / COUNT(*)越接近 1，索引越值得建；
• 把多个候选字段组合成联合索引，而不是各建各的。

这个流程走下来，一般能把索引数量控制在 5 个以内，而且每条都是被实际查询计划用到的。

1.2 一张表该建几个索引？从数据量聊起

数据量少的时候，全表扫描根本不慢。比如一张只有几百行的配置表，你给它建索引，理论上没毛病，实际收益接近于零，反而白白占内存和磁盘。我习惯把 10 万行作为一个大致分界线：低于这个量，先优化 SQL 本身，比如少用 SELECT *、减少不必要的大字段读取；高于这个量，查询走全表扫描开始明显变慢，这时候索引才有真正的用武之地。

但光看行数不够，还得看行长。一张表如果单行特别宽，比如塞了好几个 TEXT 或 JSON，即使只有几万行，全表扫描的 IO 成本也可能高得离谱。这种情况下，即使行数不大，也值得给常用查询字段建索引，因为索引能大幅减少扫描的数据页数量。

还有一个容易被忽略的点：索引和业务写入比例的关系。假设一张表每分钟写入几千行，又是核心交易数据，那每多一个索引，写入路径上就要多维护一棵 B+ 树，还涉及页分裂和 redo log 的额外开销。这种场景下，我宁可让某些查询慢一点，也不轻易加第二个索引。反过来，如果是一张读多写少的报表表，多建几个索引完全没问题，因为查询性能收益盖过了写入成本。

2. 搞清楚 MySQL 索引到底是怎么工作的

给 MySQL 加索引，写 SQL 只是最后一步，真正拉开水平差距的是对索引底层机制的理解。InnoDB 引擎用的 B+ 树结构，决定了你建的索引是“快”还是“没被用上”。这一节我会把 B+ 树、回表、覆盖索引、索引下推和二级索引更新的锁行为都过一遍。理解了这些东西，你就明白为什么明明建了索引，EXPLAIN 里却显示type=ALL。

2.1 B+ 树与回表：影响索引选择的底层逻辑

InnoDB 的索引结构是 B+ 树，非叶子节点存索引键值和指向下一层节点的指针，叶子节点存完整数据（主键索引）或索引键 + 主键（二级索引）。因为是按序排列的，B+ 树天然支持范围查询和排序，不需要额外的 filesort。但这里有一个关键概念：回表。

拿一条最简单的 SQL 举例：

SELECT * FROM user WHERE name = '张三';

假设name上有二级索引，查询过程是：先在二级索引 B+ 树里找到name = '张三'的叶子节点，拿到主键值；再拿主键去主键索引 B+ 树里查一整行数据，这个二次查找就是回表。如果命中了 100 行，就可能产生 100 次随机 IO，性能自然受影响。所以索引列的选择性很重要：如果一个字段只有几个枚举值，比如status只有 0 和 1 两种值，索引选择性差，优化器大概率直接忽略它，因为回表成本比全表扫还高。

后来我调优时养成一个习惯：建索引之前先算COUNT(DISTINCT 列) / COUNT(*)的比值，比值低于 20% 的字段，除非是联合索引的前缀，否则基本不考虑单列索引。另一个办法是把回表次数压下来，也就是把查询字段塞进索引里，让索引直接“覆盖”查询，这就引出覆盖索引。

2.2 主键索引、普通索引、唯一索引、联合索引怎么选

MySQL 里的索引类型看着多，其实按用途就分成几类：

• 主键索引：聚簇索引，叶子节点直接存整行数据，一张表只能有一个。建议用自增整数或雪花 ID，不要用 UUID，因为 B+ 树是顺序组织，UUID 随机性太强会产生大量页分裂，写性能会很差。
• 普通索引：二级索引，叶子节点存索引键 + 主键值，主要加速查询，但没法直接代替主键。
• 唯一索引：在普通索引基础上加唯一约束，适合业务上需要保证唯一性的场景，比如手机号、订单号。注意唯一索引不只影响查询，还影响写入，因为每次插入都要做唯一性检查。
• 联合索引：多个字段组成的索引，核心是“最左前缀原则”。比如(a, b, c)联合索引，能生效的查询组合包括a、a,b、a,b,c，但b,c或c单独查是用不上这个索引的。

在设计联合索引时，字段顺序通常按两个原则排：选择性高的放前面，或者等值查询的字段放前面。“选择性高的放前面”适合大多数场景，因为索引树能更快收敛；“等值查询优先”适合多个字段都是等值匹配、且存在范围条件的情况，这时候要把范围条件的字段放在最后，避免范围条件后面的字段失效。

举个例子，有一张订单表，查询模式是WHERE user_id = ? AND status = ? ORDER BY create_time DESC，那我猜你应该建(user_id, status, create_time)这个联合索引，既能过滤前两个条件，又能直接利用 B+ 树的有序性完成排序，避免 filesort。

2.3 覆盖索引与索引下推：两个容易忽略的加速点

覆盖索引的意思是：查询需要的所有列都包含在索引树里，不需要回表。最直白的优化案例是：

SELECT id, name FROM user WHERE name = '张三';

如果(name, id)建了联合索引，那么查询直接遍历二级索引就能拿到id和name，完全不碰主键索引。这种优化对高并发接口特别重要，因为减少一次随机 IO，响应时间可能就从 10ms 降到 1ms 级别。但覆盖索引不是免费的，它会让索引占用更多空间，所以一般只针对高频查询路径做覆盖设计。

索引下推（Index Condition Pushdown，ICP）是另一层优化。MySQL 5.6 以后默认开启，它允许存储引擎在二级索引扫描过程中，直接对索引包含的字段做 WHERE 过滤，把无法匹配的记录提前丢弃，减少回表次数。比如联合索引(city, age)，查询条件是city = '北京' AND age > 30，如果没有 ICP，引擎要把所有city='北京'的二级索引项都回表，再去判断age；有了 ICP，age > 30的过滤在索引扫描时就执行了，回表数量大幅减少。

这两个机制都是索引“内生”的优化，不需要额外配置，但前提是你得把查询字段设计进索引里。实战中我经常用一条EXPLAIN输出里的Extra字段来判断：看到Using index说明走了覆盖索引，看到Using index condition说明走了 ICP。如果两者都没有，就要认真反思索引设计是不是不合理。

还有一个在线业务场景很容易踩坑：通过二级索引更新时，先锁二级索引项，再回表锁主键。典型 SQL 是UPDATE t SET status = 1 WHERE phone = '138xxxx'，假设phone上只有二级索引，InnoDB 的处理流程是先锁二级索引对应的项，再回表锁主键记录。两个事务如果分别用不同二级索引更新同一行，锁的顺序可能交叉，形成死锁或长时间锁等待。解决思路是让更新尽量走主键，或者利用索引合并减少多棵索引树的锁交互。这个细节常规文档不会写，但线上出了问题排查起来非常费劲。

3. 添加索引的完整实操流程

思路理清楚了，接下来就是动手。这一节我会列出最常用的索引管理 SQL、可视化工具操作、EXPLAIN 验证方法，以及大表加索引时为什么推荐用ALGORITHM=INPLACE而不是直接对表加锁。内容很基础，但都是生产环境里真正能直接抄作业的。

3.1 常用 SQL 语句与可视化工具操作

创建索引常用的只有三条 SQL：

-- 普通索引 ALTER TABLE `user` ADD INDEX idx_name (`name`); -- 唯一索引 ALTER TABLE `user` ADD UNIQUE KEY uk_phone (`phone`); -- 联合索引 ALTER TABLE `user` ADD INDEX idx_name_status (`name`, `status`);

也可以用CREATE INDEX达到同样的效果：

CREATE INDEX idx_name ON `user` (`name`);

删除索引对应的是：

ALTER TABLE `user` DROP INDEX idx_name;

查看表上已有索引：

SHOW INDEX FROM `user`;

如果是用 Navicat 或 DataGrip 这类可视化工具，其实就是右键表 -> 设计表 -> 索引/键，填索引名、字段、索引类型。但我建议生产环境还是用命令行，因为可以留下操作记录，也方便回滚清理。这里说一个区分度很高的技巧：建索引时给名字命名，统一用idx_字段名或uk_字段名，千万别让索引名含日期后缀。我在维护老项目时见过idx_20240101这种名字，根本猜不到对应字段，只能靠查SHOW INDEX去反推，维护成本极高。

还有一个字段长度问题需要单独拿出来讲。如果字段是 VARCHAR，并且值很长，MySQL 默认对整列建索引可能导致索引页利用率太低。这时候可以用前缀索引，比如CREATE INDEX idx_title ON article(title(20))。代价是精确匹配可能失效，但 LIKE 模糊匹配和分组排序的场景下很实用。

3.2 用 EXPLAIN 验证索引是否真正生效

加完索引别急着上线，先跑一遍执行计划，确认优化器真的走索引了。我每次加索引都会执行：

EXPLAIN SELECT id, name, status FROM user WHERE name = '张三' AND status = 1;

重点看几个字段：

这里有个容易误导新手的地方：possible_keys有索引不代表会用，key才是最终结果。如果key是NULL或者type=ALL，就要回头查是不是写法破坏了索引条件。我在 3.3 部分会详细讲失效场景。

实测过程中我还习惯加一个关键字ANALYZE（MySQL 8.0.32 之后可以用）。普通EXPLAIN显示的是估算值，EXPLAIN ANALYZE会真实执行 SQL，输出每一步实际耗时和行数，对定位慢查询特别有用。不过生产环境调用线上 SQL 时要小心，尤其是 UPDATE、DELETE 这类操作，别真的把数据改了。一般建议用只读 SQL 做EXPLAIN ANALYZE。

3.3 大表加索引的在线 DDL 注意事项

给一张几百万行甚至上千万行的表加索引，不能直接拿ALTER TABLE ADD INDEX一把梭。MySQL 8.0 虽然支持ALGORITHM=INPLACE，很多操作不会锁全表，但大表索引构建期间仍有明显的主从延迟和磁盘 IO 压力。

实际操作建议：

• 尽量在业务低峰期执行，比如凌晨窗口；
• 确认 MySQL 版本是 5.6 以上，这样多数索引操作支持在线 DDL；
• 加索引前查看当前从库延迟，如果延迟过高就先暂停；
• 使用ALGORITHM=INPLACE, LOCK=NONE显式声明：

ALTER TABLE `big_table` ADD INDEX idx_user_id (`user_id`), ALGORITHM=INPLACE, LOCK=NONE;

如果表实在太大，比如上亿行，直接ALTER可能阻塞太久。这种情况我一般会考虑用额外工具做在线变更，虽然不是这篇文章的重点，但核心思想是：先建一张结构一样的新表，在新表上加好索引，然后通过增量同步把数据追平，最后原子切换表名。用数据库代运维平台也可以，但一定要理解底层原理，别盲目依赖。

很多人忽略的一点是，加索引过程中主库的写入并不是完全不受影响的。INPLACE虽然不阻塞 DML，但构建索引需要扫描整表，会消耗大量 CPU 和 IO；如果磁盘性能差，照样拖慢正常业务。所以加索引前看一眼监控，确认峰值 IO 和 CPU 还有余量，再动手。

4. 常见问题与排查技巧实录

加索引看似简单，实际工作中 80% 的时间都花在“为什么加了索引没效果”和“索引带来的顽固副作用”上。这一节把我遇到最多的几类问题整理成速查，希望能帮大家少走弯路。

4.1 索引确实建了，但查询计划就是不选它

最经典的原因有两个：索引列上用了函数，或者前导模糊查询。看这两条 SQL：

SELECT * FROM user WHERE DATE(create_time) = '2024-06-01'; SELECT * FROM user WHERE name LIKE '%张%';

第一条里create_time被DATE()包住，索引树里存的是原始时间值，MySQL 没法直接比较函数结果，只能放弃索引；第二条%张%是前导模糊，B+ 树的有序性彻底失效，只能扫全表。解决办法分别是：改成范围查询create_time >= '2024-06-01' AND create_time < '2024-06-02'；或者考虑用全文索引，但对前缀匹配场景，至少把%移到后面，写成name LIKE '张%'才可能用到索引。

另外还有一个被问烂但总有人踩的坑：字段类型不一致导致隐式转换。比如user_id是 VARCHAR，但查询条件写的是user_id = 10086这种数字，MySQL 会把字符串列转成数字做比较，于是索引列被函数处理，索引直接失效。排查方法很简单，看 EXPLAIN 的type和rows，如果明明建了索引却走了全表扫，先把字段类型和参数类型统一。

还有一种情况容易被误判：优化器“觉得”全表扫比走索引更快。比如一张只有 1000 行的表，WHERE 条件命中 80% 的行，优化器算完回表成本后决定直接全表读。这不是索引没用，而是数据分布决定的。解决思路不是强行加索引，而是用覆盖索引把回表成本降下去，让优化器重新计算成本后选中索引。

4.2 加了索引之后 UPDATE 变慢、死锁增多、表还变大了

索引不是免费的午餐，这个前面反复强调过，这里讲两个具体表现。

第一，更新变慢。每次 UPDATE 一行，InnoDB 除了改主键索引的数据页，还要同步修改所有二级索引。如果一个二级索引正好命中，就会多出一次索引页写入。更麻烦的是，如果更新导致索引键值变化，B+ 树可能要发生页分裂或合并，进而产生额外的 redo log。这是正常机制，但如果你发现某张表 UPDATE 延迟很高，先查一下是不是索引建太多、太宽。我做过一次优化：把一张表 7 个索引减到 3 个，写入延迟直接降了 40%。

第二，死锁。回到前面提过的锁顺序问题：更新走二级索引时，加锁顺序是“二级索引项 -> 主键记录”。两个事务分别用不同二级索引条件更新同一行时，可能出现互相持有对方需要的二级索引锁、同时等待主键锁的局面，这就是经典交叉死锁。这类问题在 error log 里能看到异常日志，处理手段包括：避免一个事务里做多条不同索引条件的更新；尽量用主键做更新条件；或者把二级索引合并成联合索引，减少锁面交叉。

第三，表空间膨胀。索引数据会持久化到磁盘，宽字段的索引尤其明显。比如 VARCHAR(255) 建了普通索引，一张千万行表可能额外占用几个 GB。这种膨胀不影响正确性，但会让 buffer pool 命中率下降、备份变慢。建议定期清理低频或重复索引，可以用类似sys.schema_redundant_indexes的视图查出多余索引。

4.3 我总结的几条实操笔记，送给正在调索引的人

最后把经验浓缩成几条笔记，当作一段个人总结：

• 加索引前先确认执行计划，加完后再跑一次EXPLAIN，对比rows和type是否有改善；
• 联合索引字段顺序不要拍脑袋，用区分度 + 等值/范围判断，范围条件放最后，否则后面字段容易失效；
• 谨慎使用函数包裹索引列，任何对索引列的函数操作都可能导致索引失效；
• 更新核心业务的表时，优先考虑主键条件或减少二级索引参与修改；
• 如果发现 MySQL 走错了执行计划，少数场景可以用FORCE INDEX强制索引，但这只是临时方案，根本解法还是优化索引设计。

另外一个小技巧：MySQL 8.0 里可以查performance_schema或sys库看索引使用统计，比如sys.schema_unused_indexes能找出从来没被用过的索引。我每次做季度清理，都会先跑这个视图，把长期不用的索引删掉，效果很直接。删索引的风险比加索引小得多，但同样建议在低峰期执行，别在业务高峰期动任何表结构。

在实际操作里，我还有一个习惯：把每个索引的“设计理由”写在注释或表说明里。比如改成idx_user_status对应的是“首页用户列表的高频查询路径”。刚开始觉得多此一举，后来接手过别人的表，看到索引名根本猜不到业务意图，才发现这步有多省事。数据库表结构不是只给一个人看的，留清楚上下文，后面维护的人会少骂你好几句。