做过几年 MySQL 排障之后我发现一个规律大部分线上慢查询真不是表数据量大到无解而是索引没建对。之前负责的一个项目订单表才三千多万行查询已经从秒开退化到转圈慢查询日志里刷屏的都是同一类 SQL。同事的第一反应是加缓存、上分库分表我没有急着动架构而是把 SQL 拉到 EXPLAIN 里走了一遍结果发现索引结构完全不合理改完两个索引同样的查询从 2.3 秒降到了 12 毫秒。MySQL 索引优化是排查这类问题性价比最高的一环。不需要改代码、不需要增加机器只要你把索引的底层结构和 SQL 的执行路径搞清楚一条组合索引就能解决一大片问题。这篇文章我不打算罗列教科书式的索引类型大全我想用实际排障和压测中的真实过程把理论上索引为什么快和实战中怎么建索引这两件事串起来。适合后端开发、DBA 入门以及所有正在为慢查询发愁的同行。你可以照着我给的 EXPLAIN 步骤去复现也可以直接套用文中的场景来解决自己库里遇到的问题。1. 索引快不是玄学先把 B 树读薄1.1 为什么 MySQL 选了 B 树而不是二叉树很多人知道索引是树但往深一问就卡住了二叉树结构简单为什么 MySQL 偏偏用 B 树我习惯用一个存折类比。想象一本银行存折每一页固定记录若干行流水页码按顺序钉在一起。要想找到某一天的某笔流水你不需要从头翻到尾而是通过页码索引直接定位到那一页。但如果这本书像二叉树一样每一页只分出两个分支那么在十万页的存折里找一笔记录至少要翻动 17 次。对于 MySQL 里常见的千万行表树的层级每多一层查询就多一次磁盘 IO而磁盘 IO 的耗时通常是内存操作的几十倍。平衡二叉树的理论结构很优雅但层高带来的 IO 次数在真实数据量下根本压不住。B 树的设计思路更像一本索引册 流水册的组合所有的真实数据都挂在最底层的叶子节点上中间的非叶子节点只存路由信息。这样做的好处有两个。第一同一块磁盘页能装下的路由条目非常多三层 B 树就能覆盖上千万行数据。InnoDB 默认页大小是 16KB主键用 bigint 占 8 字节加上指针等额外开销一个页大约能放一千多个条目三层树的承载量就是 1000×1000×1000上亿数据也不过四层。第二叶子节点之间通过链表相连做范围查询时只需要沿着链表顺序扫描不需要频繁回溯树结构。所以当你看到索引可以显著提升查询性能这种说法时底层逻辑其实是Mysql 用更少的磁盘访问次数换取了原本需要全表扫描才能拿到的数据。1.2 聚簇索引与二级索引为什么会有回表这回事InnoDB 里有一个概念很多人容易忽略表本身就是按主键组织成的一棵 B 树。主键索引的叶子节点上直接存储整行数据这叫聚簇索引。你在表上创建的其他索引统一叫二级索引也叫辅助索引它的叶子节点里存的不是完整数据而是主键值。这意味着什么如果你根据二级索引查数据过程通常是两步先通过二级索引 B 树找到主键值再拿主键值去聚簇索引 B 树里重新查找一次完整行记录。这第二步就是从业者常说的回表。回表并非每次都发生。如果二级索引的叶子节点上已经包含了查询需要的所有列MySQL 就能直接返回结果连聚簇索引那棵树的路径都省了。这就是后面要细讲的覆盖索引。理解聚簇索引和二级索引的区别是理解整个索引优化的大门。很多人优化半天没效果就是因为把索引当成了在列上建一个索引就万事大吉没有意识到查询请求最终还要回到主键索引上去走一趟。2. 建索引前的三条铁律基数、回表、最左前缀2.1 区分度太低索引就是鸡肋动手建索引之前先问一个问题这一列到底能不能有效缩小查询范围这要看基数。基数是某列去重后的取值个数。比如性别列只有两个值基数就是 2订单表的 order_id 基本每条都不同基数就接近行数。索引的价值在于通过索引扫描能够过滤掉绝大多数无关数据。如果一个列的基数太低比如性别、状态这类枚举值即使建了索引MySQL 优化器也会发现走索引之后还是要回表拿大量数据最终可能选择全表扫描。我见过有人在只有三个状态的字段上建索引结果执行计划里依然是 ALL 全表扫描。原因很简单状态值为已支付的记录可能占全表的 90%优化器算完账觉得逐条回表太亏干脆全表扫。真正适合建索引的列区分度应该足够高至少经过条件过滤后返回的行数占全表比例要明显下降。判断方法就是估算一个条件能过滤掉多少数据或者直接跑一下 COUNT 验证。2.2 组合索引的列顺序等值放前范围放后单列索引能解决一部分问题但真实业务里高频查询往往是多条件的。这个时候不建组合索引只靠多个单列索引MySQL 通常只能选择其中一个使用其他条件留给回表后再过滤。组合索引的列顺序直接影响索引的利用效率。我的经验口诀是等值条件列放最前面范围条件列放最后面。举一个实际例子。查询条件是 WHERE user_id 123456 AND status 1 ORDER BY create_time DESC那么一个 (user_id, status, create_time) 的组合索引就非常合适。user_id 和 status 都是等值匹配会定位到一个极其狭窄的区间然后 create_time 在这个区间内已经天然有序排序甚至可以直接从索引里取避免 filesort。如果把 create_time 放在前面变成 (create_time, user_id, status)虽然索引也能用上最左前缀但条件先把时间范围拉开再过滤用户扫描的数据量可能大得多。2.3 最左前缀组合索引的失效边界最左前缀原则是组合索引最容易踩坑的地方。它指的是 MySQL 在使用组合索引时必须从最左边的列开始匹配跳跃中间的列会导致后面的索引列失效。比如组合索引 (a, b, c)下面这些查询能用到索引WHERE a 1、WHERE a 1 AND b 2、WHERE a 1 AND b 2 AND c 3。而 WHERE b 1 或者 WHERE c 1 这种没带 a 的查询通常连整个索引都用不上只能全表扫描。如果把中间条件换成范围比如 WHERE a 1 AND b 100 AND c 3那 c 列就失效了因为 b 的范围条件之后索引无法继续精确定位。这些边界规则不是死记硬背的关键是理解 B 树的排序方式组合索引的每一层都是按前导列先排序前导列相同再按下一位排序。查询条件必须按同样的顺序逐层命中才能享受每一层的筛选能力。3. 用 EXPLAIN 给自己的 SQL 做一次体检3.1 先看 type 列从 ALL 到 const 的五个档位分析一条 SQL 是否走了正确的索引路线第一步永远是看 EXPLAIN 的输出。最值得关注的字段之一是 type它表示 MySQL 在表中找到所需行的访问方式。五个档位从差到好分别是ALL全表扫描没有用到索引通常是慢查询的头号嫌疑。index遍历了整棵索引树比 ALL 好一点但如果数据量大依然很伤。range索引范围扫描比如用在 BETWEEN、、、IN 等条件下。ref非唯一性索引等值匹配比如普通索引列 WHERE col 值。const / system通过主键或者唯一索引等值匹配最多只返回一行是理论上最快的级别。实际项目里我一般要求核心查询至少做到 range最好是 ref 或更高。如果看到 type 是 ALL先不要急着加索引要看看 WHERE 条件里的列是否真的被索引覆盖了或者是不是因为函数包裹、隐式类型转换导致索引失效。3.2 key_len 和 rows判断这条 SQL 真正要读多少数据EXPLAIN 里还有两个容易被忽略但非常关键的字段key_len 和 rows。key_len 表示 MySQL 实际使用到的索引长度单位是字节。它比 key 这个字段更有说服力如果组合索引 (user_id, status, create_time) 都建上了但 EXPLAIN 显示 key_len 只有 8 字节说明实际只用了 user_id 这一列后面两列没参与过滤。你可以根据 bigint 8 字节、int 4 字节、varchar 还要加变长内存分的字节数反推出索引被用到了第几层。rows 是 MySQL 预估需要扫描的行数。这个值越小越好但不代表最终返回的行数。我遇到过一些情况rows 显示几千行结果查询时间依然很高这时候要看 Extra 里是不是出现了文件排序或者临时表接下来要处理的问题可能是排序和分组的代价。3.3 Extra 列里的三个警告灯Extra 列是执行计划里信息量最大的部分我重点关注三个信号。第一个是 Using filesort代表 MySQL 无法利用索引直接排序需要在内存或磁盘上额外排序。看到它就要反思排序字段是否被包含在索引里顺序对不对第二个是 Using temporary代表查询需要建立临时表通常出现在 GROUP BY、DISTINCT 和某些子查询里。临时表压在内存或磁盘上数据量大了非常拖沓。第三个是 Using index这是好消息表示查询只需要从索引中获取数据不需要回表也就是覆盖索引生效。如果能把一条 SQL 从回表 额外排序优化成 Using index那性能会有一个质的飞跃。4. 实战案例订单表查询从 2.3 秒到 12 毫秒的完整过程4.1 慢查询的原始面貌先说背景。模拟项目里有一张订单表结构大致是这样CREATE TABLE t_order ( order_id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL COMMENT 用户ID, shop_id bigint NOT NULL COMMENT 店铺ID, status tinyint NOT NULL COMMENT 订单状态, amount decimal(10,2) NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (order_id) ) ENGINEInnoDB;实际数据量三千多万行最先卡顿的是这条查询SELECT order_id, user_id, amount, status FROM t_order WHERE user_id 123456 AND status 1 ORDER BY create_time DESC LIMIT 10;从业务上讲这是典型的用户查自己最近一笔成功订单。逻辑很简单但线上执行时间稳定在 2.3 秒左右接口频繁超时。4.2 第一轮尝试单列索引为什么没有效果我最开始犯过一个典型错误看到 WHERE 里有 user_id 和 status就顺手在这两个列上各自建了一个单列索引。ALTER TABLE t_order ADD INDEX idx_user_id (user_id); ALTER TABLE t_order ADD INDEX idx_status (status);加完索引再 EXPLAIN发现并没有开心的事情发生。执行计划里只用到 idx_user_idtype 是 refrows 预估还有几十万行Extra 里出现 Using filesort。这是因为 user_id 过滤后依然有大量历史订单MySQL 顺着 idx_user_id 拿到几十万个主键再一个个回表读完整行接着还要在内存里排序取前 10 条整个过程代价远超全表扫描。而 status 上的索引呢status 基数是 2优化器算一下觉得过滤效果太差直接忽略了。所以多个单列索引并不等于组合索引。如果查询里多个字段协同过滤MySQL 未必会主动把它们合并使用与其建一堆单列索引不如根据查询模式去设计组合索引。4.3 关键转折组合索引 覆盖索引同时到位我重新分析了一遍查询条件user_id 是等值筛选status 是等值筛选create_time 是排序字段。那么组合索引的最优设计是 (user_id, status, create_time)。ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time);加了这唯一一个组合索引之后执行计划立刻不一样了type 从 ref 变成 range因为 create_time 参与了排序范围扫描被合理利用。rows 从几十万降到几条。Extra 里不再有 Using filesort排序直接由索引树天然完成。但这里还是有一个回表现象查询需要返回 amount 字段而 amount 不在索引中MySQL 需要根据主键回表拿到 amount。虽然对于 LIMIT 10 来说回表只有 10 次代价已经很低但我又把查询字段改成了覆盖索引命中所有列进一步规避了回表。为此我把 amount 也包含进索引形成 (user_id, status, create_time, amount)。在这种情况下执行计划的 Extra 明确显示 Using index整个查询不需要碰聚簇索引的数据页。提示覆盖索引并不是索引列越多越好因为每一列都会增加写入和存储代价。覆盖索引只有在高频查询中收益大于成本时才推荐。当前案例里用户订单查询属于极高频率操作所以多包含几列表格是可以接受的。最终线上验证同样的 SQL 执行时间从 2.3 秒降到了 12 毫秒。这个过程里我没有动任何一行业务代码纯粹靠索引结构重构解决了问题。4.4 这个案例背后的思考方式优化这条 SQL 的关键难度不在于会不会写索引而在于分析查询的本质需求等值条件走索引定位排序条件走索引顺序返回字段尽量走覆盖索引。这个三步法可以顺手套用到很多场景上。如果你遇到的主查询已经建了单列索引还是慢可以按照这个顺序自查第一组合索引是否存在第二排序字段是否在索引里第三返回字段是否被索引覆盖第四EXPLAIN 的 Extra 列里还残留什么警告。5. 那些让我栽过跟头的索引陷阱5.1 在索引列上做函数运算最典型的写法是WHERE DATE(create_time) 2024-01-01。看起来很自然但实际上对索引列做了函数计算之后MySQL 没法直接在这列上走索引范围扫描因为你期望的等于某一天从索引树的角度看并不是一个连续的排序区间。正确写法是改成范围条件WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00这样 create_time 就能直接走索引范围扫描效率高得多。其他类似的场景还包括在列上做加减乘除、字符串拼接、CAST 转换都是索引失效的高发地。5.2 隐式类型转换字符串和数字的“错位”假如 user_id 列是 varchar 类型但查询写成WHERE user_id 123456MySQL 会试图把列类型转成数字去比较。一旦隐式类型转换发生索引同样失效。排查方法很简单看 EXPLAIN 的 type 列是否突然从 ref 变成了 ALL。还有一种典型情况是字符串列和数字列直接拼接比较比如WHERE str_col num_col。我建议所有查询参数都保持与表结构一致的类型必要时应用层强制传字符串数据库层面再确认一下字符集和排序规则是否一致。5.3 范围查询右边的列瞬间失效组合索引 (a, b, c) 里如果 b 列使用了范围条件c 列就无法通过索引继续过滤。这背后的逻辑是B 树在 b 列满足或条件时c 列在多个分支里不再是全局有序的所以索引无法继续精确定位 c。比如查询WHERE a 1 AND b 100 AND c 3实际只能用到 a 和 bc 条件要回表后再过滤。解决思路有两个一是把 c 的过滤条件想方设法挪到 b 之前前提是等值条件优先级更高二是拆分范围条件让每段走一次索引再用 IN 合并。具体选择要看数据分布不能一概而论。5.4 用 OR 连接不同条件时索引没法简单合并OR 是另一个常见的索引失效点。WHERE user_id 123 OR status 1这种查询MySQL 很可能会选择全表扫描因为优化器难以廉价地合并两个不同索引的搜索结果。如果两个条件分别有索引确实可以采用 UNION ALL 方式拆开再合并结果SELECT ... WHERE user_id 123 UNION ALL SELECT ... WHERE status 1 AND user_id 123;但更实际的方案是根本别让这种查询频繁出现在线上从业务上拆分成两个更明确的查询往往更高效。OR 逻辑通常意味着业务查询本身不够聚焦这也是调优过程中值得和产品同学讨论的一个点。6. 索引不是免费的午餐写入变慢、页分裂与维护成本6.1 索引的写放大效应很多人在优化查询时只盯着读速度忘记了索引是要付出写代价的。每增加一个索引相当于在写入数据时额外维护一棵 B 树。插入一行数据InnoDB 不仅要把行写入聚簇索引还要同步更新所有二级索引。更新的列如果恰好被多个索引覆盖每一个索引都要重建相关节点。这带来的直观结果就是索引越多写入越慢。一个只读场景的表你甚至可以建五六个索引来覆盖各种查询但一个写密集的表加索引必须非常谨慎。我通常的做法是先统计线上真实的读写比例再决定索引数量。对于写入频繁的核心表索引数量控制在三个以内是常见的经验值。6.2 随机插入与页分裂B 树索引是顺序组织的插入时如果主键不是单调递增可能会频繁触发页分裂导致大量随机 IO。这也是为什么要强调主键设计尽量使用自增或者雪花算法生成的趋势递增 ID而不是随机 UUID。页分裂不仅影响写入性能还会让索引数据散乱增加扫描成本。长时间频繁删除和更新数据索引的碎片率也会升高表现为明明行数不多但查询扫描的页数远超预期。6.3 统计信息更新与碎片整理建议索引能否被有效使用依赖于优化器的统计信息。如果表的基数统计严重过期优化器会做出错误的选择比如明明有索引却选了全表扫描。遇到这种诡异情况先执行一下 ANALYZE TABLE 更新统计信息很多时候比盲目加索引有用。碎片整理方面对于删除比较频繁的表我一般会定期执行 OPTIMIZE TABLE 或者用在线 DDL 工具来做表重建。但要注意这类操作在数据量大时会对线上产生一定压力最好放在业务低峰期并且先做备份验证。不要动不动就 OPTIMIZE表不大、删除不多的时候完全没必要。聊到这儿我再分享一点个人体会。MySQL 索引优化不是什么高深魔法核心永远是三件事理解 B 树的排序结构分析查询条件的等值与范围匹配顺序然后用 EXPLAIN 验证执行计划是否真正受益。我在项目里优化过很多慢查询每一轮改索引之前都会先反问自己这个索引到底减少了几次磁盘 IO它有没有引入额外的排序或回表成本如果回答不上来就不要急着加索引。另外一个实操小习惯是所有索引变更都先在测试库跑一轮全量慢查询对比前后执行计划再决定是否上生产。数据库调优最大的风险不是改坏了而是你以为改好了实际上业务高峰期才会暴露出新的访问路径问题。希望大家碰到慢查询时第一反应不是怪数据量大而是带着 EXPLAIN 去审视自己的索引这会是性价比最高的起点。