MySQL索引进阶：联合索引设计、失效排查与慢查询调优实战-开发者社区

索引这个问题，我见过太多开发同学栽跟头了。用了一两年MySQL，CREATE INDEX没少写，EXPLAIN也没少看，可真到线上慢查询榜单拉出来，发现有索引没走上、走了没用对、甚至索引本身把写入拖垮的，比比皆是。今天不聊安装配置，也不讲建索引的基础语法，就聊进阶使用里最容易让人困惑的几个点：联合索引到底怎么设计、WHERE后同时出现a和b时索引怎么建、哪些写法会让索引静默失效、排序和回表怎么权衡、以及线上索引怎么维护。如果你写SQL已经有一定基础，但总觉得索引这块“能用但说不透”，那可以从头看到尾。

1. 先搞清楚索引的底层逻辑，再谈优化

1.1 InnoDB为什么死磕B+树

MySQL InnoDB的索引底层是B+树，这点大家都知道。但很多人并没有真正理解：B+树到底解决了什么，为什么不是哈希表，不是红黑树，也不是B-树。只有把这个问题想清楚，后面所有建索引的决策才有依据。

先看数据页。InnoDB默认一个数据页16KB，磁盘读写的最小单位就是页，不是单行。所有索引结构的设计，本质上都是在最小化“读几个页”这个指标，因为一次随机IO的代价动辄几毫秒，而顺序读取因为磁盘预读和内存Page Cache的存在，会便宜非常多。哈希表能做到单点O(1)查找，但没法做范围查询，也没法维持有序性。红黑树是内存结构，树高和节点分裂在磁盘场景下并不划算。B-树的非叶子节点也存数据，导致同样容量下存的孩子数量更少，树更高，IO次数更多。B+树把所有数据都集中在叶子节点，非叶子节点纯粹当“目录”用：

非叶子节点只保存索引键值和指向子节点的指针，空间利用率高，扇出（fanout）大。
叶子节点按索引键值有序排列，并且通过双向链表互相连接。
从根到叶子每一层的节点数量呈几何级增长，层数天然很低。

算一笔账。假设主键是bigint，8字节，再加6字节的行指针，一个非叶子节点大约能存 16KB / 14 ≈ 1170 个键值。两层非叶子节点能覆盖 1170×1170 ≈ 137万个叶子节点，如果每个叶子节点按16KB装载数据，覆盖上亿行数据最多也就3到4层。换句话说，一张千万级甚至亿级的表，从根节点出发只要3次左右IO就能定位到叶子页。

顺带回应一个大家经常搜的“双向索引”问题。严格说MySQL里没有所谓双向索引，B+树的叶子节点之间是双向链表，这保证了：范围查询可以从第一个满足条件的叶子页开始，顺着链表顺序读取；order by在走索引时，不需要额外排序，直接按链表顺序返回；反向扫描时也能从链表尾部向前遍历。

理解了B+树的这层设计，你就能理解为什么“离散随机主键”是索引的大敌。插入UUID字符串主键时，数据会随机在各个位置发生页分裂，索引页碎片化严重，查询IO次数暴涨，这个问题后面还会提到。

1.2 回表、覆盖索引与索引下推，三者的关系

索引为什么能加速查询，本质上就是“先用小表查目录，再翻正文”。InnoDB里数据默认是聚簇索引组织：主键索引的叶子节点保存整行数据；而二级索引（普通索引、联合索引）的叶子节点只保存索引键和主键值。用二级索引查询时，第一步先在二级索引树里定位，拿到主键值，第二步再到主键索引树里取整行数据，第二步就叫回表（table lookup）。

回表是一次额外的随机IO，这才是二级索引慢的根因。举一个直观的对比：假设表有500万行，二级索引命中2000行，如果全部回表，就是2000次随机IO，即使每次都命中Page Cache，也明显慢于覆盖查询。怎么减少回表？三个手段：

覆盖索引（Covering Index）：如果SQL里select的列全部落在某个索引的字段集合里，InnoDB根本不需要回表，直接在索引树的叶子节点把数据取完。执行计划的Extra会显示 Using index。
索引下推（ICP，Index Condition Pushdown）：MySQL 5.6引入。以前存储引擎从二级索引读出记录后，直接把整行回表，再由Server层过滤WHERE。ICP允许把WHERE里能用索引字段判断的条件优先放到存储引擎层执行，先把不满足的行过滤掉再回表，减少了回表次数。执行计划的Extra显示 Using index condition。
覆盖索引无法覆盖时，尽量让where条件把范围收窄，缩小回表的行数。

这里容易混淆的是覆盖索引与索引下推的区别。覆盖索引是“压根不回表”，ICP是“少回表”。很多新手看到Extra里出现 Using index condition，以为索引已经很理想了，其实它只说明存储引擎帮你过滤了一部分，最终大概率还是回了表。

用查字典来类比最直观：拼音索引是二级索引，页码是主键。普通查询就是先查拼音索引拿页码，再翻到正文把整行字读完；覆盖索引相当于这个字典后面附了“笔画-拼音对照速查表”，你要的信息在里面直接有，不用翻正文；索引下推则是你先在拼音索引区域把明显不是目标字的一批页码划掉，再挑着翻正文。同样是查字典，翻正文的次数完全不同。

2. 联合索引设计与最左前缀原则

2.1 where a and b 到底怎么建索引

很多同学都在纠结一个问题：SQL里同时出现where a = ? and b = ?，到底应该怎么建索引？第一反应往往是给a建一个索引，再给b建一个索引，然后看优化器心情二选一。大概率会翻车。先给结论：如果a、b都是等值条件，优先建 (a, b) 联合索引，而不是两个单列索引。

为什么？联合索引的定义本身就是按字段顺序排序的：第一列a全局有序，a相同的情况下b有序。查询时可以先定位a，再在a的范围内精确定位b，两个等值条件都能命中索引的精确定位，避免回表后还要做额外过滤。而两个单列索引，优化器最多只能使用其中一个做定位，另一个条件只能回表取整行后过滤。即使MySQL支持Index Merge（索引合并，把两个索引结果做交并集），对等值场景也往往不如联合索引高效，还会产生额外多路IO。所以除非两个字段各自承担高频独立查询，否则不要轻易拆成单列索引。

再进一步，联合索引内部顺序怎么排？有两个维度要考虑：

区分度（Cardinality）：区分度高的字段放前面。道理很简单，索引先按第一列排序，第一列的区分度越高，定位越精确，后面扫描范围越小。比如部门id和员工状态，用部门id做首列的联合索引，比用状态做首列通常要稳。
范围条件放最后，因为联合索引在“遇到范围条件”之后，右边的字段就无法再利用索引的精确排序和定位了。

第二个维度值得展开。假设SQL是where a > 100 and b = 1，如果建 (a, b)，索引会先按a做range扫描，b的作用会大打折扣；如果建 (b, a)，b的等值条件先把范围精确锁死，a > 100就在b=1这个小区间内做range，效率高很多。所以“范围条件放最后”完全可以理解为：谁的能力强谁先上，等值字段优先于范围字段。

具体到“where a and b”这个最常见场景，最优先级排序口诀可以浓缩成三条：等值字段优先于范围字段；区分度大的优先于区分度小的；联合索引不是越多越好，要根据实际查询集合来找公共前缀。

2.2 order by排序场景下的索引设计

排序慢是另一个高频痛点，“MySQL排序”这个话题的搜索量一直不低。MySQL排序有两条路：一是索引天然有序，直接顺着B+树叶子链表读就行，不需要额外排序；二是无法利用索引时走filesort，把结果集先取出来再排序，数据量大时可能落到磁盘临时文件，性能断崖式下降。

要让order by走索引，设计联合索引时得把排序字段考虑进去。比如这条SQL：

SELECT id, emp_no, name FROM employee WHERE dept_id = 100 AND status = 1 ORDER BY create_time DESC LIMIT 20;

最理想的联合索引是(dept_id, status, create_time)。查询先用dept_id和status定位到目标行范围，而这个范围内的记录天然就是按create_time排好序的，所以order by create_time直接复用索引顺序，Extra里不会出现Using filesort。这里能排序成功的关键，是排序字段必须满足联合索引的“排序版最左前缀原则”：前面的等值条件把字段固定住，后面的字段才能保持全局有序。

同样场景，如果你写where dept_id = 100 order by status, create_time，status是等值条件，那联合索引 (dept_id, status, create_time) 也能排序通过。但如果写where dept_id > 100 order by create_time，dept_id用范围后，create_time在索引里的顺序就无法保证全局有序了，大概率还是要filesort。

值得一提的是，filesort不等于灾难。如果结果集很小且能全部装入内存sort buffer，排序很快；但如果出现 Using filesort 且 rows巨大、Extra里还带 Using temporary，那就要警惕了。排查思路很简单：先看where字段是否用了索引，再看order by字段是否满足最左前缀的排序条件。

2.3 主键索引与唯一索引的取舍

“主键索引和唯一索引的区别”也经常被问到。这俩用处在业务上相似，很多同学以为可以互相替代，其实差别很大，核心区别有三点：

唯一性约束强度。主键要求非空且唯一，一张表只能有一个；唯一索引允许NULL值，而且MySQL允许同一列存在多个NULL（NULL与NULL互不相等）。
索引类型。InnoDB的主键索引是聚簇索引，叶子节点保存整行数据，它决定了表数据的物理存储顺序；唯一索引只是二级索引，叶子节点保存索引键+主键值，查询通常还要回表。
存储与维护代价。主键索引树的规模基本等于表数据本身，是所有数据访问的入口；唯一索引多一棵独立的树，插入和更新都要多做一次唯一性校验，高并发写入下开销更明显。

基于这些特性，实操建议很明确。主键不要用业务敏感字段，比如身份证号这种，业务字段一旦变更，聚簇索引的数据物理位置要跟着动，代价极高；主键建议用自增整数或雪花算法这类单调递增值，避免UUID随机字符串导致的页分裂和碎片；唯一索引尽量建在业务真正需要唯一约束的字段上，比如订单号、手机号，不是为了查询快才建；如果只是想加速一个高频等值查询，且没有唯一性要求，普通二级索引就够了，不必上唯一索引。

这里多说一句，“逻辑主键”和“业务唯一键”可以同时存在。表的主键用id自增，业务上让phone或order_no建唯一索引，这是最稳妥的组合，兼顾了聚簇索引的存储性能和业务唯一性校验。

3. 索引失效的典型场景与避坑清单

3.1 手写SQL时最容易踩的八个坑

索引设计了半天，结果SQL一写就把索引弄失效了，这种场面在代码评审里太常见了。我总结了一份高频失效清单，基本覆盖日常手写SQL的所有坑：

隐式类型转换。索引列是varchar，条件却传数字，比如phone = 13800138000，MySQL会把字符串转成数字再比较，索引直接失效。反过来更隐蔽：int列用字符串条件时，字符串会先被转成数字，索引可以走，但性能也不保险。最稳的处理就是字段和参数类型严格一致。
对索引列使用函数或表达式。where DATE(create_time) = '2025-01-01'、where YEAR(create_time) = 2025、where price * 2 > 100，这些写法都会让优化器放弃索引。正确做法是把函数挪到等号右边，比如create_time >= '2025-01-01' AND create_time < '2025-01-02'。
前导模糊查询。like '%keyword'无法走索引，因为索引按前缀排序，无从定位；like 'keyword%'则可以走range。
违反最左前缀。联合索引 (a, b, c)，只写b或只写c条件，是无法直接利用索引精确定位的；MySQL对这类查询会有一些优化手段，但效率通常不如完整的联合索引命中好。
or连接导致失效。where a = 1 or b = 2，如果b上没有索引，或者a、b不是同一个索引覆盖，可能会让整个查询退化。
负向查询。not in、!=、not like这些往往会让优化器放弃索引，但弃不弃有时候取决于数据分布和统计信息。比如一个字段99%的值都是1，查!= 1时优化器觉得索引还不如全表扫，就会自己放弃。
对索引列做字符串拼接或cast，等价于函数操作。
优化器自己“反水”。即使SQL写法没问题，如果回表代价太大，也就是命中行数在总行数中占比太高，优化器会放弃二级索引而选择全表扫描，因为全表顺序读比几千次随机回表IO更便宜。

关于第8条多说一句：这往往是数据分布变化的信号。一张500万行的表，某条件统计数据在1000行内，走索引很香；某天数据变成100万行命中，优化器评估后可能就直接ALL了。这时候该做的不是强制索引，而是想办法缩小扫描范围，或者考虑把高频字段改造成覆盖索引。

3.2 用Explain的Extra辨别“半失效”状态

很多同学以为执行计划里用到了索引就是万事大吉，其实索引是否用“到位”区别很大。我在处理一个慢查询时，发现Extra里显示 Using index condition，以为索引用得很充分，结果深入一看，命中了几万行，回表压力全在存储引擎层，整体还是慢。

这里列一下最常见的四种Extra状态和实际含义：

Extra状态	含义	是否理想
Using index	覆盖索引，SELECT的列全在索引里，不用回表	最优
Using index condition	触发ICP，部分WHERE条件下推到存储引擎过滤，但仍可能回表	较好，需关注rows
Using where	索引定位后，Server层再做一次行过滤，常见于索引无法覆盖全部过滤条件	尚可
Using filesort	结果集需要额外排序	需要警惕
Using temporary	使用了临时表，常见于复杂group by/union，很影响性能	很差

实操里我建议多关注 rows 和 key_len。rows是预估扫描行数，数字越大说明索引筛选能力越弱；key_len表示索引用到了几个字段，比如联合索引 (dept_id, status, create_time)，dept_id是int，非空时key_len是4，如果执行计划里key_len只显示4，说明后面的status和create_time都没用上，这时候就要回到SQL写法或索引顺序上找原因。key_len的计算逻辑并不复杂，但真正排查时不需要手算，对比一下同一个索引在不同SQL里的key_len就能看出端倪。

4. 索引维护与性能调优实战

4.1 索引表空间与碎片问题

有人把“索引表空间”当成一个很高深的概念来搜，其实没那么玄。InnoDB的表默认是一张表一个表空间，数据和二级索引都放在同一个.ibd文件里。表空间里既有B+树的页面，也有段（Segment）、区（Extent）、页（Page）的管理结构。所以索引越多，表空间文件就越大，这是物理层面的必然。

实际运维中大家感受最深的往往是“删除大量数据后，表空间大小不变”。因为默认配置下，被标记删除的页不会立刻归还给操作系统，而是在表空间内部留存复用。这会造成两个后果：碎片增多，很多页的利用率不高，扫描效率下降；文件系统层面文件大小撑在那里，磁盘看起来永远不见少。处理办法是定期做碎片整理：

OPTIMIZE TABLE employee;

OPTIMIZE TABLE会重建表并整理索引页面，效果是表空间显著缩小、查询扫描页数下降。但注意两个坑：一是大表OPTIMIZE期间会有锁，尽量在业务低峰期执行；二是MySQL 8.0之后可以用ALTER TABLE employee ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;来实现在线整理，减少锁表时间。

还有一个容易被忽略的点：给大表新增索引，不要直接执行ALTER TABLE ... ADD INDEX，在8.0以前这会长时间阻塞写入。更平滑的方案是借助pt-online-schema-change这类工具，通过触发器把增量变更同步到新表，完成后原子切换。如果用的是MySQL 8.0+，部分DDL已经支持INSTANT / INPLACE算法，但依然建议把大表的ALTER操作放到低峰期，同时对锁等待做好准备。

4.2 一例完整的联合索引优化复盘

光讲理论容易飘，拿一条实际慢SQL走一遍完整流程更有说服力。假设有一张员工表，结构大致如下：

CREATE TABLE employee ( id BIGINT PRIMARY KEY AUTO_INCREMENT, emp_no VARCHAR(32), dept_id INT NOT NULL, status TINYINT NOT NULL, name VARCHAR(64), create_time DATETIME, KEY idx_dept_id (dept_id) );

业务方反馈这个查询很慢，要求优化：

SELECT id, emp_no, name FROM employee WHERE dept_id = 100 AND status = 1 ORDER BY create_time DESC LIMIT 20;

第一步，先执行EXPLAIN看现状。最开始时表上只有idx_dept_id单列索引，执行计划大概是 type=ref、rows=几万、Extra=Using where + Using filesort。明明用了索引为什么还慢？因为dept_id=100这个条件命中行数太多，status过滤在Server层做，order by还要额外排序，最后回表取整行，每一步都拖累性能。

第二步，改造成联合索引(dept_id, status, create_time)。执行计划从 type=ref、rows几万变成 rows几千甚至几百，Extra变成Using index condition，filesort消失。这个改造的收益来自三处：

dept_id和status两个条件都在索引中完成精确定位，rows大幅缩小。
create_time复用索引顺序，彻底消除文件排序。
ICP把status的过滤提前到存储引擎，回表行数变少。

第三步，如果SQL里select的列能尽可能收敛到索引字段，覆盖索引还能更香。比如业务只需要统计某个部门某状态下的create_time分布，把select改成dept_id, status, create_time，Extra就会变成Using index，连回表都省了。

这里有个很实用的经验：线上优化不要一次加一堆索引，先explain定位最大的瓶颈点，是rows太大、filesort太重、还是回表太频繁，改完一个再看效果，逐步逼近最优查询计划。

4.3 索引不是越多越好

说完怎么加，必须说怎么砍。很多人一遇到慢查询，第一反应就是给WHERE字段补索引，结果一年后表上挂了十几个索引，更新变慢、表空间膨胀、优化器选择困难。每个二级索引都是一棵独立的B+树，写操作需要同步维护每一棵树，这就是写放大（Write Amplification）。一张写入频繁的表，索引数量从5个涨到10个，写入耗时可能翻倍都不止。

一个可行的索引治理流程：

开启慢查询日志，把执行频率高、扫描行数大的SQL捞出来。
对业务SQL做归一化，去掉具体参数值，统计同构SQL的执行频次。
找出高频SQL里的WHERE、ORDER BY、GROUP BY字段，提炼一组能覆盖大多数场景的公共前缀联合索引。
借助sys.schema_unused_indexes或performance_schema找出一段时间内从未被使用的索引，评估后删除。
删除索引后观察一周，重点关注慢查询数量和写入性能变化。

我在实际项目中见过最典型的情况：一张订单表上同时有 idx_user_id、idx_status、idx_create_time 三个单列索引，业务高频查询是WHERE user_id = ? AND status = ? ORDER BY create_time DESC，三个索引一个都用不到位。改成(user_id, status, create_time)联合索引后，表上索引从三个缩成一个，查询和写入同时变快。这就是索引治理的价值。

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

5.1 三个高频问题排查实录

问题一：加了索引但explain还是ALL。这类问题建议按这个顺序排查：先看是否对索引列用了函数或隐式转换；再看SQL里有没有or、like前导通配、负向查询；然后看联合索引是否满足最左前缀；最后看rows占比，如果命中行数超过表行数的20%~30%，优化器大概率会主动放弃索引。前四步都排除了还不行，可以用FORCE INDEX做一次对照实验，看强制走索引是不是真的更慢。如果是，说明数据分布已经变了，该从SQL和索引设计上重新想方案。

问题二：联合索引和多个单列索引到底选哪个。判断标准是：看这张表实际的高频查询，是“总是同时用a和b”还是“a、b各自独立查询”。如果高频场景就是where a and b，果断用联合索引；如果a和b分别有不同的高频独立查询，那可以保留两个单列索引，再评估是否还需要一个联合索引兜底。同时要留意，单列索引和联合索引可以共存，MySQL优化器会自己选，但索引总数要控制。

问题三：排序字段无论如何都出现filesort。多半是违反最左前缀的排序要求。记住这个判断方法：联合索引 (a, b, c)，where里a=1、order by b、c有序，这是对的；where里a范围、order by b有序，这通常不可能直接用索引排序；order by b、c而where里没有a的等值条件，也不会复用索引顺序。如果排序结果集很小，filesort也不是大问题，真正要防的是filesort + temporary组合出现。

5.2 给新手的几条实操心得

这几年配合开发团队优化SQL，我自己沉淀了几条不怎么写进文档里的经验，分享出来供参考：

写SQL时尽量保持“索引列裸奔”。不要在索引列上套函数、做运算、拼字符串，所有加工尽量放到条件右侧，这是性价比最高的习惯。
不要迷信“必须覆盖SELECT所有字段”。覆盖索引虽好，但为了一次查询把所有字段都塞进索引，往往会带来巨大的写入成本和表空间膨胀，多数场景下保证ref + 少量回表，比强行覆盖更合理。
统计信息是会过期的。批量导入、大量删除后，记得跑一次ANALYZE TABLE，让优化器重新评估基数，否则它会拿着过期的统计做出反直觉的计划。
线上影响面大的ALTER操作，永远先看SHOW PROCESSLIST，评估写入压力，再用工具平滑执行，宁慢勿快。
索引命名别偷懒。主键叫PK，唯一索引叫uk_表名_字段，普通索引叫idx_表名_字段。三个月后回来看代码，你会感谢当初的自己。

最后再分享一个我在排查慢查询时反复验证过的方法：不要一上来就看索引有没有“命中”，先看rows这个数字。同样一条SQL，从一个走索引但rows十几万的计划，变成一个全表扫描但rows几千的计划，后者反而更快。索引只是工具，最终目标是让扫描行数最小、IO次数最少。这个意识建立起来，你在索引进阶这条路上就真的入门了。