1. 覆盖索引先从一次慢查询讲起1.1 一次让人头疼的回表经历先说个我自己的真实案例。之前帮一个电商团队优化后台订单查询表里数据量大概几百万行SQL长这样SELECT order_id, user_id, status FROM orders WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;业务方反馈说这个页面转圈要好半天我一看执行计划Extra那一栏明晃晃写着Using filesort再看key字段虽然用上了索引但rows预估扫描了几万行。几万行本身不算多问题在于每一行都得靠主键去聚簇索引里再捞一次完整记录这个动作就叫回表。回表一次两次无所谓回表几万次哪怕每次都是主键查找累加起来也足够让接口响应时间冲到几百毫秒甚至一秒以上。很多人对回表的理解停留在“需要回表”这个结论上但对于回表到底为什么慢、慢在哪个环节其实没太想清楚。InnoDB 的索引结构是 B 树二级索引叶子节点存的是索引列 主键聚簇索引叶子节点存的是完整行记录。你用二级索引去过滤的时候查出来的其实是“主键值列表”再用这些主键值去聚簇索引里面捞整行。这中间隔着一次随机 I/O。机械硬盘时代这是致命伤SSD 时代虽然好一些但一次回表至少也是一次 B 树搜索几万行就是几万次搜索累积起来非常可观。1.2 覆盖索引的原理就是“免回表”覆盖索引解决的就是这个问题。所谓覆盖索引指的是一个二级索引包含了查询所需要的全部列。这个“全部列”包括三部分WHERE 子句用到的过滤列、SELECT 子句要返回的列、ORDER BY 或 GROUP BY 涉及的排序列。当这所有列都落在同一个索引里的时候MySQL 直接从二级索引的叶子节点取数返回就行了完事不需要再回聚簇索引。为什么能这样因为二级索引的叶子节点上本身就有完整的索引列值和主键值。你要的数据全在这些值里面那引擎层直接返回即可。我之前遇到的那个慢查询优化方式就是建一个联合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time, order_id);这个索引把 WHERE 过滤条件涉及的user_id、status排序涉及的create_timeSELECT 返回的order_id全包进去了。建完之后再看执行计划Extra 栏出现了Using index这个词在 MySQL 里就是覆盖索引的标志后面还跟着一个Using filesort消失了因为create_time已经在索引里有序排列直接顺序读就行。效果很明显原来 800ms 的查询优化完差不多 10ms 以内。一个索引同时解决了回表和 filesort 两个问题。1.3 覆盖索引的验证方法和使用边界怎么确认你的 SQL 真的走了覆盖索引核心就看EXPLAIN的 Extra 列Using index代表查询命中覆盖索引不需要回表如果同时出现Using where说明虽然索引覆盖了但还有部分条件是在索引扫描结果上再做过滤这种仍然算覆盖索引如果出现Using index condition那是索引下推跟覆盖索引是两个不同的机制后面细说什么都没写那大概率回表了这里要强调一个很容易踩的坑覆盖索引不是万能的不能为了覆盖所有查询就拼命往索引里塞列。索引列越多B 树越宽每个叶子节点能存放的条目越少树的高度就会变高同时写入时要更新的索引页也更多插入性能会明显下降。我见过有团队为了覆盖一个多条件查询建了一个包含 8 列的索引结果是写接口慢了 30%这就是典型的得不偿失。合理做法是优先覆盖高频查询控制索引列数在 3 到 4 列以内如果个别列比如大的TEXT字段不能进索引就退而求其次先保证过滤和排序列在索引里SELECT 列实在覆盖不了的就让它回表但把回表行数压到最低。2. 索引下推索引未覆盖时的一层额外保护2.1 从一次 LIKE 查询的困惑说起再来分享一个优化经历。有一次排查一个用户搜索接口表结构是CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50), KEY idx_name_age (name, age) ) ENGINEInnoDB;搜索 SQL 长这样SELECT * FROM users WHERE name LIKE 张% AND age BETWEEN 18 AND 25;第一反应是联合索引idx_name_age(name, age)肯定能用上但问题在于 LIKE 的%通配符放在后面意味着索引只能用到name这一列的前缀部分age这个条件怎么处理如果是在 MySQL 5.5 及更早版本流程是这样的存储引擎根据name LIKE 张%这个条件在索引里找到所有姓张的用户的 ID然后回表把完整记录捞出来再到 Server 层去判断age BETWEEN 18 AND 25。问题来了如果姓张的用户有 10 万行但年龄在范围内只有 1 万行意味着你白白回表了 9 万次做了 9 万次毫无意义的主键查找。2.2 索引下推到底“推”了什么MySQL 5.6 引入了索引下推Index Condition PushdownICP就是为了解决上面这个场景。它的核心逻辑是把 WHERE 条件中能用索引列判断的部分从 Server 层“推”到存储引擎层去执行。还是刚才那个查询。有了 ICP 之后存储引擎在扫描二级索引时先用name LIKE 张%定位到候选范围然后在索引内部继续用age BETWEEN 18 AND 25这个条件去过滤过滤完了再回表。回表次数从 10 万次直接降到 1 万次。简单总结一下没有 ICP存储引擎扫描二级索引 → 得到主键列表 → 回表 → Server 层过滤剩余条件有 ICP存储引擎扫描二级索引 → 在索引内过滤掉不满足条件的数据 → 得到主键列表 → 回表 → Server 层拿到结果回表依然存在但回表次数被大幅压缩了。这也是 ICP 和覆盖索引最大的区别覆盖索引是从结果侧消灭回表ICP 是从过程侧减少回表。2.3 EXPLAIN 怎么看索引下推EXPLAIN出来Extra 列如果显示Using index condition就代表这条 SQL 命中了索引下推优化。注意这里的关键词是condition而不是Using index两个千万不要搞混。实操中还有一种常见组合Extra 同时出现Using index condition; Using where这个组合的含义是有一部分条件在存储引擎层通过索引过滤掉了Using index condition还有一部分条件必须回到 Server 层才能过滤Using where。比如WHERE name LIKE 张% AND age BETWEEN 18 AND 25 AND city 杭州联合索引是idx_name_agecity不在索引里那city条件就只能等回表之后在 Server 层过滤。在索引里能把name和age先筛掉已经帮了大忙。我在 MySQL 8.0 里执行时还发现一个细节WHERE条件里如果包含无法用索引判断的函数运算比如WHERE age 1 BETWEEN 18 AND 25这种优化器就没法把age条件推下去因为存储引擎在索引里只能做等值和范围判断不能对索引列做表达式计算。所以想让 ICP 发挥效果SQL 书写时尽量保持索引列独立别包在函数或表达式里。2.4 索引下推的适用边界和生效条件ICP 不是对所有查询都生效的我在实际使用中总结了几个关键限制第一版本限制。ICP 是 MySQL 5.6 引入的特性5.6 之前的版本连功能开关都没有。虽然现在生产环境已经很少见 5.5 了但如果你还在维护老系统要注意这个问题。第二只能在二级索引上生效聚簇索引没有意义。聚簇索引本身叶子节点就是完整行记录根本没有回表动作自然不存在“减少回表”的优化空间。第三存储引擎要支持。InnoDB 和 MyISAM 都支持 ICP如果你用的是其他存储引擎需要自己确认一下文档说明。第四条件类型有限制。下推判断只适用于REF、EQ_REF、RANGE等访问类型能处理的场景。如果是LIKE %关键字这种前导通配符索引本身都没法走自然谈不上把条件下推。第五系统开关也可能被关掉。可以通过optimizer_switch来检查SELECT optimizer_switch;输出内容里找到index_condition_pushdownon如果是off可以手动开启SET optimizer_switch index_condition_pushdownon;不过绝大多数默认安装是开启状态这个操作更多用在对查询行为做验证时的开关对比。3. 联合索引计划覆盖索引和索引下推的协同优化3.1 一个实际业务场景的完整拆解前面分别讲了两个技术点实际生产中更常见的情况是一条 SQL 同时需要覆盖索引和索引下推来配合。我来还原一个完整的优化过程。业务背景是一个内容管理系统的文章列表页支持按作者、状态、发布时间筛选SQL 大致如下SELECT article_id, title, status, publish_time FROM articles WHERE author_id 2088 AND status 1 AND publish_time 2024-01-01 ORDER BY publish_time DESC LIMIT 20;当前的索引情况是author_id上有单列索引status和publish_time都没有独立索引。执行计划显示 key 是idx_author_idExtra 是Using filesortrows预估 5000 行左右。现在的核心问题有两个author_id单列索引过滤完之后要回表拿title、status、publish_time等所有 SELECT 列回表完还需要在内存里做一次排序因为idx_author_id里只有author_id没有publish_time的排序信息3.2 索引设计的具体推演过程这里就要倒推一下怎么设计索引才能同时满足 WHERE、ORDER BY、SELECT 三方面需求。首先是 WHERE。author_id 2088是等值条件优先级最高放组合索引最左边。status 1是第二个等值条件跟在后面。publish_time 2024-01-01是范围条件只能放在等值条件之后否则会导致后面列无法走索引。其次是 ORDER BY。ORDER BY publish_time DESC需要publish_time在索引里而且它前面必须全是等值条件才能保证索引顺序和排序顺序一致。目前author_id和status都是等值条件把publish_time放在第三个位置排序就能自动走索引。最后是 SELECT。article_id是主键二级索引自动带上title必须显式加进索引才能实现覆盖。综合下来最终索引设计是ALTER TABLE articles ADD INDEX idx_author_status_time (author_id, status, publish_time, title);这个索引同时干了三件事完成了 WHERE 条件的快速过滤、消除了Using filesort、实现了覆盖索引避免回表。执行计划里 Extra 变成了干净的Using indexrows降到了几十行响应时间从 200ms 降到了 5ms 以内效果好得肉眼可见。3.3 当覆盖索引做不到时ICP 如何兜底但现实往往不会这么理想。有时候 SELECT 列包含大字段比如content这种 TEXT 类型没法加进索引。这时候覆盖索引计划就得放弃但也不意味着眼睁睁看着上万次回表发生。拿一个具体场景来说文章表里有content大字段查询要求按category_id和publish_time过滤返回文章标题和摘要同时要排除掉删除状态的文章。SELECT article_id, title, summary FROM articles WHERE category_id 10 AND publish_time BETWEEN 2024-01-01 AND 2024-06-01 AND status 1;组合索引可以设计成idx_category_time(category_id, publish_time, status)。三个字段都在索引里WHERE 条件的所有过滤动作都可以在索引内部完成虽然最终依然要回表捞title、summary但关键是status 1这个条件在存储引擎层就被过滤掉了回表的行数只剩下那些真正符合所有条件的记录这比先把所有category_id 10的用户回表捞出来再判断status要高效得多。这两种思路在实际业务里经常交替出现。我个人的判断标准很简单SELECT 列能不能全部放进索引能就奔着覆盖索引去不能就退而求其次把 WHERE 过滤条件尽量设计进索引利用 ICP 把回表规模压到最小。两者一组合慢查询基本能消灭一大半。4. 常见问题与排查技巧实录4.1 一张排查速查表实际操作中不管是覆盖索引还是索引下推出问题的场景其实高度集中在下面几个情况里。我把这些年积累的排查结论整理成了一个速查表方便遇到问题时直接对照疑问可能原因排查方法建立了覆盖索引但 EXPLAIN 显示回表SELECT 列超出索引范围LIKE 左模糊导致条件失效OR 条件破坏索引核对索引列和 SELECT 列是否完全匹配检查 WHERE 条件写法没有显示 Using index condition版本低于 5.6optimizer_switch 被关闭条件无法在引擎层判断检查 version查询optimizer_switch确认条件是索引列上的等值或范围索引下推没起效果索引列顺序不对例如范围条件放在等值条件前面条件涉及函数或类型转换调整索引列顺序改写 SQL 让索引列独立索引建了不少但查询还是慢优化器没选中预期索引索引统计信息过期ANALYZE TABLE更新统计信息用FORCE INDEX临时验证索引覆盖了但是写入变慢索引列太多B 树变大评估查询频率删除不常用的冗余索引4.2 两个深坑深分页和类型隐式转换第一个深坑是深分页。很多开发会用LIMIT 10000, 20这种写法做分页从 MySQL 角度来说它会先扫到前 10020 条符合条件的记录再把前 10000 条丢弃只返回最后 20 条。如果配合覆盖索引前 10020 条都不用回表性能还勉强撑得住但一旦覆盖不了就得回表 10020 次再丢弃 10000 条简直就是灾难。这里我推荐用延迟关联或者游标分页来改写。简单说先用覆盖索引查出符合条件的id再用id去关联原表捞完整数据类似下面这样SELECT a.* FROM articles a INNER JOIN ( SELECT id FROM articles WHERE author_id 2088 AND status 1 ORDER BY publish_time DESC LIMIT 10000, 20 ) t ON a.id t.id;内层子查询因为只需要id、author_id、status、publish_time完全可以通过覆盖索引扫描回表次数被压缩到只有最后 20 次。第二个深坑是隐式类型转换。比如user_id是字符串类型传参时后端代码传了数字MySQL 在比较时就会做类型转换这会导致索引列被函数包裹覆盖索引和 ICP 直接失效动作全变成全表扫描。这种问题最隐蔽EXPLAIN 看不出来端倪只有用SHOW WARNINGS才能看到底层 SQL 被改写成了CAST(user_id AS INT)。排查时遇到索引突然失效优先检查字段类型和传入参数类型是否一致。4.3 利用 MySQL 8.0 的 EXPLAIN ANALYZE 做验证MySQL 8.0.18 开始引入了EXPLAIN ANALYZE这是一个我非常推荐的实际调试工具。跟传统EXPLAIN不同它会真实执行这条 SQL然后输出每一步的实际耗时、实际行数、扫描行数、循环次数。EXPLAIN ANALYZE SELECT article_id, title, status, publish_time FROM articles WHERE author_id 2088 AND status 1 AND publish_time 2024-01-01 ORDER BY publish_time DESC LIMIT 20;输出的结果里可以清晰看到actual time和actual rows比如- Limit: 20 row(s) (actual time2.345..2.348 rows20 loops1) - Sort: articles.publish_time DESC, limit input to 20 row(s) (actual time2.344..2.347 rows20 loops1) - Index range scan on articles using idx_author_status_time (actual time0.108..2.178 rows37 loops1)对比传统EXPLAIN只能看到预估行数EXPLAIN ANALYZE能直接看到每个环节的实际耗时分布。哪个环节扫的行数多、哪个环节排序耗时高一目了然。做覆盖索引优化前和优化后用这个工具各跑一次效果对比非常直观。最后分享一个小技巧如果EXPLAIN显示行数跟实际差异很大很多时候不一定是 SQL 问题而是表统计信息太久没更新了。运行一下ANALYZE TABLE table_name;再重新看执行计划可能结果完全不同。这一点在索引设计完成后做验证时特别重要别让过期的统计信息误导你做出错误的索引判断。 覆盖索引与索引下推：MySQL慢查询优化实战
1. 覆盖索引先从一次慢查询讲起1.1 一次让人头疼的回表经历先说个我自己的真实案例。之前帮一个电商团队优化后台订单查询表里数据量大概几百万行SQL长这样SELECT order_id, user_id, status FROM orders WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;业务方反馈说这个页面转圈要好半天我一看执行计划Extra那一栏明晃晃写着Using filesort再看key字段虽然用上了索引但rows预估扫描了几万行。几万行本身不算多问题在于每一行都得靠主键去聚簇索引里再捞一次完整记录这个动作就叫回表。回表一次两次无所谓回表几万次哪怕每次都是主键查找累加起来也足够让接口响应时间冲到几百毫秒甚至一秒以上。很多人对回表的理解停留在“需要回表”这个结论上但对于回表到底为什么慢、慢在哪个环节其实没太想清楚。InnoDB 的索引结构是 B 树二级索引叶子节点存的是索引列 主键聚簇索引叶子节点存的是完整行记录。你用二级索引去过滤的时候查出来的其实是“主键值列表”再用这些主键值去聚簇索引里面捞整行。这中间隔着一次随机 I/O。机械硬盘时代这是致命伤SSD 时代虽然好一些但一次回表至少也是一次 B 树搜索几万行就是几万次搜索累积起来非常可观。1.2 覆盖索引的原理就是“免回表”覆盖索引解决的就是这个问题。所谓覆盖索引指的是一个二级索引包含了查询所需要的全部列。这个“全部列”包括三部分WHERE 子句用到的过滤列、SELECT 子句要返回的列、ORDER BY 或 GROUP BY 涉及的排序列。当这所有列都落在同一个索引里的时候MySQL 直接从二级索引的叶子节点取数返回就行了完事不需要再回聚簇索引。为什么能这样因为二级索引的叶子节点上本身就有完整的索引列值和主键值。你要的数据全在这些值里面那引擎层直接返回即可。我之前遇到的那个慢查询优化方式就是建一个联合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time, order_id);这个索引把 WHERE 过滤条件涉及的user_id、status排序涉及的create_timeSELECT 返回的order_id全包进去了。建完之后再看执行计划Extra 栏出现了Using index这个词在 MySQL 里就是覆盖索引的标志后面还跟着一个Using filesort消失了因为create_time已经在索引里有序排列直接顺序读就行。效果很明显原来 800ms 的查询优化完差不多 10ms 以内。一个索引同时解决了回表和 filesort 两个问题。1.3 覆盖索引的验证方法和使用边界怎么确认你的 SQL 真的走了覆盖索引核心就看EXPLAIN的 Extra 列Using index代表查询命中覆盖索引不需要回表如果同时出现Using where说明虽然索引覆盖了但还有部分条件是在索引扫描结果上再做过滤这种仍然算覆盖索引如果出现Using index condition那是索引下推跟覆盖索引是两个不同的机制后面细说什么都没写那大概率回表了这里要强调一个很容易踩的坑覆盖索引不是万能的不能为了覆盖所有查询就拼命往索引里塞列。索引列越多B 树越宽每个叶子节点能存放的条目越少树的高度就会变高同时写入时要更新的索引页也更多插入性能会明显下降。我见过有团队为了覆盖一个多条件查询建了一个包含 8 列的索引结果是写接口慢了 30%这就是典型的得不偿失。合理做法是优先覆盖高频查询控制索引列数在 3 到 4 列以内如果个别列比如大的TEXT字段不能进索引就退而求其次先保证过滤和排序列在索引里SELECT 列实在覆盖不了的就让它回表但把回表行数压到最低。2. 索引下推索引未覆盖时的一层额外保护2.1 从一次 LIKE 查询的困惑说起再来分享一个优化经历。有一次排查一个用户搜索接口表结构是CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50), KEY idx_name_age (name, age) ) ENGINEInnoDB;搜索 SQL 长这样SELECT * FROM users WHERE name LIKE 张% AND age BETWEEN 18 AND 25;第一反应是联合索引idx_name_age(name, age)肯定能用上但问题在于 LIKE 的%通配符放在后面意味着索引只能用到name这一列的前缀部分age这个条件怎么处理如果是在 MySQL 5.5 及更早版本流程是这样的存储引擎根据name LIKE 张%这个条件在索引里找到所有姓张的用户的 ID然后回表把完整记录捞出来再到 Server 层去判断age BETWEEN 18 AND 25。问题来了如果姓张的用户有 10 万行但年龄在范围内只有 1 万行意味着你白白回表了 9 万次做了 9 万次毫无意义的主键查找。2.2 索引下推到底“推”了什么MySQL 5.6 引入了索引下推Index Condition PushdownICP就是为了解决上面这个场景。它的核心逻辑是把 WHERE 条件中能用索引列判断的部分从 Server 层“推”到存储引擎层去执行。还是刚才那个查询。有了 ICP 之后存储引擎在扫描二级索引时先用name LIKE 张%定位到候选范围然后在索引内部继续用age BETWEEN 18 AND 25这个条件去过滤过滤完了再回表。回表次数从 10 万次直接降到 1 万次。简单总结一下没有 ICP存储引擎扫描二级索引 → 得到主键列表 → 回表 → Server 层过滤剩余条件有 ICP存储引擎扫描二级索引 → 在索引内过滤掉不满足条件的数据 → 得到主键列表 → 回表 → Server 层拿到结果回表依然存在但回表次数被大幅压缩了。这也是 ICP 和覆盖索引最大的区别覆盖索引是从结果侧消灭回表ICP 是从过程侧减少回表。2.3 EXPLAIN 怎么看索引下推EXPLAIN出来Extra 列如果显示Using index condition就代表这条 SQL 命中了索引下推优化。注意这里的关键词是condition而不是Using index两个千万不要搞混。实操中还有一种常见组合Extra 同时出现Using index condition; Using where这个组合的含义是有一部分条件在存储引擎层通过索引过滤掉了Using index condition还有一部分条件必须回到 Server 层才能过滤Using where。比如WHERE name LIKE 张% AND age BETWEEN 18 AND 25 AND city 杭州联合索引是idx_name_agecity不在索引里那city条件就只能等回表之后在 Server 层过滤。在索引里能把name和age先筛掉已经帮了大忙。我在 MySQL 8.0 里执行时还发现一个细节WHERE条件里如果包含无法用索引判断的函数运算比如WHERE age 1 BETWEEN 18 AND 25这种优化器就没法把age条件推下去因为存储引擎在索引里只能做等值和范围判断不能对索引列做表达式计算。所以想让 ICP 发挥效果SQL 书写时尽量保持索引列独立别包在函数或表达式里。2.4 索引下推的适用边界和生效条件ICP 不是对所有查询都生效的我在实际使用中总结了几个关键限制第一版本限制。ICP 是 MySQL 5.6 引入的特性5.6 之前的版本连功能开关都没有。虽然现在生产环境已经很少见 5.5 了但如果你还在维护老系统要注意这个问题。第二只能在二级索引上生效聚簇索引没有意义。聚簇索引本身叶子节点就是完整行记录根本没有回表动作自然不存在“减少回表”的优化空间。第三存储引擎要支持。InnoDB 和 MyISAM 都支持 ICP如果你用的是其他存储引擎需要自己确认一下文档说明。第四条件类型有限制。下推判断只适用于REF、EQ_REF、RANGE等访问类型能处理的场景。如果是LIKE %关键字这种前导通配符索引本身都没法走自然谈不上把条件下推。第五系统开关也可能被关掉。可以通过optimizer_switch来检查SELECT optimizer_switch;输出内容里找到index_condition_pushdownon如果是off可以手动开启SET optimizer_switch index_condition_pushdownon;不过绝大多数默认安装是开启状态这个操作更多用在对查询行为做验证时的开关对比。3. 联合索引计划覆盖索引和索引下推的协同优化3.1 一个实际业务场景的完整拆解前面分别讲了两个技术点实际生产中更常见的情况是一条 SQL 同时需要覆盖索引和索引下推来配合。我来还原一个完整的优化过程。业务背景是一个内容管理系统的文章列表页支持按作者、状态、发布时间筛选SQL 大致如下SELECT article_id, title, status, publish_time FROM articles WHERE author_id 2088 AND status 1 AND publish_time 2024-01-01 ORDER BY publish_time DESC LIMIT 20;当前的索引情况是author_id上有单列索引status和publish_time都没有独立索引。执行计划显示 key 是idx_author_idExtra 是Using filesortrows预估 5000 行左右。现在的核心问题有两个author_id单列索引过滤完之后要回表拿title、status、publish_time等所有 SELECT 列回表完还需要在内存里做一次排序因为idx_author_id里只有author_id没有publish_time的排序信息3.2 索引设计的具体推演过程这里就要倒推一下怎么设计索引才能同时满足 WHERE、ORDER BY、SELECT 三方面需求。首先是 WHERE。author_id 2088是等值条件优先级最高放组合索引最左边。status 1是第二个等值条件跟在后面。publish_time 2024-01-01是范围条件只能放在等值条件之后否则会导致后面列无法走索引。其次是 ORDER BY。ORDER BY publish_time DESC需要publish_time在索引里而且它前面必须全是等值条件才能保证索引顺序和排序顺序一致。目前author_id和status都是等值条件把publish_time放在第三个位置排序就能自动走索引。最后是 SELECT。article_id是主键二级索引自动带上title必须显式加进索引才能实现覆盖。综合下来最终索引设计是ALTER TABLE articles ADD INDEX idx_author_status_time (author_id, status, publish_time, title);这个索引同时干了三件事完成了 WHERE 条件的快速过滤、消除了Using filesort、实现了覆盖索引避免回表。执行计划里 Extra 变成了干净的Using indexrows降到了几十行响应时间从 200ms 降到了 5ms 以内效果好得肉眼可见。3.3 当覆盖索引做不到时ICP 如何兜底但现实往往不会这么理想。有时候 SELECT 列包含大字段比如content这种 TEXT 类型没法加进索引。这时候覆盖索引计划就得放弃但也不意味着眼睁睁看着上万次回表发生。拿一个具体场景来说文章表里有content大字段查询要求按category_id和publish_time过滤返回文章标题和摘要同时要排除掉删除状态的文章。SELECT article_id, title, summary FROM articles WHERE category_id 10 AND publish_time BETWEEN 2024-01-01 AND 2024-06-01 AND status 1;组合索引可以设计成idx_category_time(category_id, publish_time, status)。三个字段都在索引里WHERE 条件的所有过滤动作都可以在索引内部完成虽然最终依然要回表捞title、summary但关键是status 1这个条件在存储引擎层就被过滤掉了回表的行数只剩下那些真正符合所有条件的记录这比先把所有category_id 10的用户回表捞出来再判断status要高效得多。这两种思路在实际业务里经常交替出现。我个人的判断标准很简单SELECT 列能不能全部放进索引能就奔着覆盖索引去不能就退而求其次把 WHERE 过滤条件尽量设计进索引利用 ICP 把回表规模压到最小。两者一组合慢查询基本能消灭一大半。4. 常见问题与排查技巧实录4.1 一张排查速查表实际操作中不管是覆盖索引还是索引下推出问题的场景其实高度集中在下面几个情况里。我把这些年积累的排查结论整理成了一个速查表方便遇到问题时直接对照疑问可能原因排查方法建立了覆盖索引但 EXPLAIN 显示回表SELECT 列超出索引范围LIKE 左模糊导致条件失效OR 条件破坏索引核对索引列和 SELECT 列是否完全匹配检查 WHERE 条件写法没有显示 Using index condition版本低于 5.6optimizer_switch 被关闭条件无法在引擎层判断检查 version查询optimizer_switch确认条件是索引列上的等值或范围索引下推没起效果索引列顺序不对例如范围条件放在等值条件前面条件涉及函数或类型转换调整索引列顺序改写 SQL 让索引列独立索引建了不少但查询还是慢优化器没选中预期索引索引统计信息过期ANALYZE TABLE更新统计信息用FORCE INDEX临时验证索引覆盖了但是写入变慢索引列太多B 树变大评估查询频率删除不常用的冗余索引4.2 两个深坑深分页和类型隐式转换第一个深坑是深分页。很多开发会用LIMIT 10000, 20这种写法做分页从 MySQL 角度来说它会先扫到前 10020 条符合条件的记录再把前 10000 条丢弃只返回最后 20 条。如果配合覆盖索引前 10020 条都不用回表性能还勉强撑得住但一旦覆盖不了就得回表 10020 次再丢弃 10000 条简直就是灾难。这里我推荐用延迟关联或者游标分页来改写。简单说先用覆盖索引查出符合条件的id再用id去关联原表捞完整数据类似下面这样SELECT a.* FROM articles a INNER JOIN ( SELECT id FROM articles WHERE author_id 2088 AND status 1 ORDER BY publish_time DESC LIMIT 10000, 20 ) t ON a.id t.id;内层子查询因为只需要id、author_id、status、publish_time完全可以通过覆盖索引扫描回表次数被压缩到只有最后 20 次。第二个深坑是隐式类型转换。比如user_id是字符串类型传参时后端代码传了数字MySQL 在比较时就会做类型转换这会导致索引列被函数包裹覆盖索引和 ICP 直接失效动作全变成全表扫描。这种问题最隐蔽EXPLAIN 看不出来端倪只有用SHOW WARNINGS才能看到底层 SQL 被改写成了CAST(user_id AS INT)。排查时遇到索引突然失效优先检查字段类型和传入参数类型是否一致。4.3 利用 MySQL 8.0 的 EXPLAIN ANALYZE 做验证MySQL 8.0.18 开始引入了EXPLAIN ANALYZE这是一个我非常推荐的实际调试工具。跟传统EXPLAIN不同它会真实执行这条 SQL然后输出每一步的实际耗时、实际行数、扫描行数、循环次数。EXPLAIN ANALYZE SELECT article_id, title, status, publish_time FROM articles WHERE author_id 2088 AND status 1 AND publish_time 2024-01-01 ORDER BY publish_time DESC LIMIT 20;输出的结果里可以清晰看到actual time和actual rows比如- Limit: 20 row(s) (actual time2.345..2.348 rows20 loops1) - Sort: articles.publish_time DESC, limit input to 20 row(s) (actual time2.344..2.347 rows20 loops1) - Index range scan on articles using idx_author_status_time (actual time0.108..2.178 rows37 loops1)对比传统EXPLAIN只能看到预估行数EXPLAIN ANALYZE能直接看到每个环节的实际耗时分布。哪个环节扫的行数多、哪个环节排序耗时高一目了然。做覆盖索引优化前和优化后用这个工具各跑一次效果对比非常直观。最后分享一个小技巧如果EXPLAIN显示行数跟实际差异很大很多时候不一定是 SQL 问题而是表统计信息太久没更新了。运行一下ANALYZE TABLE table_name;再重新看执行计划可能结果完全不同。这一点在索引设计完成后做验证时特别重要别让过期的统计信息误导你做出错误的索引判断。