上周帮同事排查一条线上慢查询单表三千万行查询条件就两个字段联合索引也建了结果跑了 2.8 秒才返回。打开执行计划一看type是ALL索引根本没进去。这种情况我见过太多次了。很多人对 MySQL 索引优化的理解停留在给 where 条件加个索引但真正上手练习就会发现索引优化第一步不是加索引而是学会看懂一条 SQL 到底是怎么执行的以及优化器为什么做了这个选择。这篇文章我用自己的练习过程来拆解从造表、造数据到 explain 逐字段分析再到六个典型索引失效场景的复现与修复最后串一条完整的线上排查链路。内容偏实战适合已经会写基础 SQL、想系统提升索引优化能力的后端开发和 DBA 阅读。1. 先别急着建索引慢查询到底慢在哪1.1 一条慢查询的真实场景还原假设现在有个订单查询接口我接到的问题是按用户查最近订单很慢。表结构简化后长这样CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(32) NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB;业务 SQL 是这样的SELECT order_no, amount, status, create_time FROM order_info WHERE user_id 10234567 AND status IN (1, 2, 5) ORDER BY create_time DESC LIMIT 20;user_id上有索引idx_user_id但 explain 的结果里type ALLrows 估算三千万全表扫描。为什么有索引却不走核心原因在于MySQL 优化器会估算各条执行路径的代价当它认为走二级索引回表 排序的成本比直接全表扫更高时就会放弃索引。user_id 10234567能过滤掉大部分行但status IN条件其实过滤能力很弱加上ORDER BY create_time还需要回表后排序优化器最终选择了全表扫描。这里有个反直觉的点即使user_id的选择性很好优化器也可能不走索引。因为二级索引里只有user_id和主键而我们需要order_no、amount、status、create_time必须回聚簇索引取完整行回表一多成本就上去了。1.2 先排除索引之外的干扰因素在上手加索引之前我通常会按下面的顺序排查避免在错误方向上浪费时间打开slow_query_log确认这条 SQL 是不是稳定慢而不是偶发的网络抖动或锁等待。用SHOW ENGINE INNODB STATUS看看有没有长时间未提交的事务持有行锁否则慢的根源可能是锁而不是索引。确认统计信息是否过期ANALYZE TABLE order_info;之后再跑一次 explain很多时候只是统计信息不准导致优化器选错。最后才是分析执行计划、调整索引。提示第四步之前的三步经常被人跳过。我在实践中遇到过多次加索引没效果的假象最后发现是另一个会话长期占着锁索引压根没机会派上用场。先排除等待类问题再谈扫描类问题顺序不能反。1.3 索引不是越多越好先建立成本意识同一个表上每个索引都意味着额外的存储空间、写入时的维护成本以及优化器在选择路径时面临的复杂度。生产环境更常见的问题不是没索引而是索引太多优化器选错。我有一次接手过一张表上面挂了 11 个索引其中三个完全重复结果每次 Insert 都要同步维护一大堆二级索引写入性能被拖得很惨。删掉冗余索引之后写入延迟直接降了三分之一。所以在练习索引优化的时候每加一个索引之前都问自己三个问题这个索引覆盖了哪些高频查询是否已有索引能通过调整字段顺序达到同样效果写入压力和存储成本能不能承受带着这种成本意识再开始造表后面的练习才有意义。2. 造一张百万行测试表把索引优化变成可复现的练习2.1 表结构设计模拟真实业务字段练习用的表不能太简单字段最好覆盖常见的查询条件数字、字符串、状态值、日期。我用一张简化的会员订单表CREATE TABLE member_order ( id bigint NOT NULL AUTO_INCREMENT, member_id bigint NOT NULL, order_no varchar(32) NOT NULL, status tinyint NOT NULL DEFAULT 0, amount decimal(10,2) NOT NULL DEFAULT 0.00, channel varchar(16) DEFAULT NULL, remark varchar(255) DEFAULT NULL, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;字段设计有几个刻意安排order_no是唯一键用来演示等值查询和隐式类型转换。member_id是高频查询字段用来演示普通二级索引。status和channel是低选择性字段用来演示建了索引也可能不走的情况。create_time用来演示范围查询和排序。2.2 用存储过程批量造数据测试环境用 MySQL 8.0插入 100 万行数据只需要一个存储过程DROP PROCEDURE IF EXISTS insert_member_order; DELIMITER $$ CREATE PROCEDURE insert_member_order() BEGIN DECLARE i INT DEFAULT 1; SET AUTOCOMMIT 0; WHILE i 1000000 DO INSERT INTO member_order (member_id, order_no, status, amount, channel, create_time) VALUES ( FLOOR(RAND() * 10000) 1, CONCAT(NO, LPAD(i, 10, 0)), FLOOR(RAND() * 8), ROUND(RAND() * 1000, 2), ELT(FLOOR(RAND() * 3) 1, app, web, h5), NOW() - INTERVAL FLOOR(RAND() * 365) DAY ); IF i % 5000 0 THEN COMMIT; END IF; SET i i 1; END WHILE; COMMIT; END$$ DELIMITER ; CALL insert_member_order();存储过程里我刻意让member_id的分布只有 10000 个用户平均每个用户 100 条订单。这样模拟了真实系统的长尾分布。2.3 数据倾斜让练习更接近真实世界如果每个用户只有几条订单加索引效果会特别明显练起来不够有挑战。我建议在造数完成后单独制造几个大用户UPDATE member_order SET member_id 88888 WHERE id % 100 0;这样用户 88888 大约有 1 万条订单是普通用户的 100 倍。后面练习时用这个热点用户跑查询很容易复现索引在某些数据分布下失效的现象。索引优化练习里最容易出的问题就是用均匀分布的数据得出乐观结论真实业务的数据分布几乎一定倾斜。3. Explain输出逐字段拆解哪些数字是骗人的哪些才是关键3.1 type字段从ALL到const的代价阶梯执行EXPLAIN SELECT * FROM member_order WHERE member_id 123;返回结果的type字段决定了访问类型。我整理了常见取值按性能从差到好排type含义我的理解ALL全表扫描整张表从头扫到尾index全索引扫描扫的是索引树但也是全量range索引范围扫描通过索引锁定了某个区间ref非唯一索引等值匹配通过索引找到一批目标行eq_ref主键或唯一索引关联多表 JOIN 时一个值精确对应一行const主键或唯一索引等值最多一行优化器直接内联成常量练习时最需要警惕ALL和index。index虽然名字里带index但它是遍历了整棵索引树数据量一大一样慢。真正称得上用好了索引的至少要到range级别。3.2 rows、filtered 与真实扫描量rows是优化器估算需要读取的行数filtered是经过条件过滤后剩余行数的百分比。这两个数字经常骗人rows依赖统计信息不精确。统计信息过旧时误差可能到 10 倍以上。filtered在单表查询里意义有限在多表 JOIN 时更能反映优化器的判断。我会把这两个值相乘得到优化器眼里最终可能返回的行数再对比实际数据量。我在练习时有个习惯explain 看完之后用SELECT COUNT(*)实际跑一遍对比 explain 里的估算值。比如 explain 说 rows 3200实际 count 只有 45那这个执行计划不能全信先ANALYZE TABLE member_order;刷新统计信息再说。3.3 Extra列Using index到底意味着什么Extra列里出现频率最高的几个值我总结成三档Using index最优档查询所需列都在索引树上不需要回表。Using index condition用到索引下推了部分过滤在索引层完成但仍可能回表。Using where; Using filesort基本等于说索引没完全走通排序还得额外做。Using filesort并不代表一定在磁盘上排序内存排序也叫filesort。但它的出现意味着排序没有利用索引顺序需要额外付出排序成本。ORDER BY create_time DESC在大数据量下出现Using filesort基本就要考虑通过调整索引来消除。3.4 一个常见误区key不为空不代表索引用好了再看一种执行计划type: ref key: idx_member_status rows: 520000 Extra: Using index conditionkey不为空但 rows 显示 52 万行。原因往往是联合索引只用了最左列后面的字段没参与过滤。这种用了一半的索引比不用更麻烦它给了你优化的错觉。所以在练习时我每次看执行计划都是三个列一起看type、key、rows。只盯key是最容易自欺欺人的做法。理想状态应该是type尽量靠前、key对应正确索引、rows接近实际返回行数三者同时满足才算真正用好了。4. 索引失效现场六个典型场景的实测与修复这一章是全文最核心的部分我逐个场景给出复现 SQL、执行计划表现和修复方案。这些场景在实际工作中反复出现值得一个接一个亲手做一遍。4.1 对索引列使用函数常见写法SELECT * FROM member_order WHERE DATE(create_time) 2024-05-01;explain 结果type ALL索引idx_create_time没被使用。原因很直白优化器无法对DATE(create_time)做范围匹配因为函数改变了列在索引里的原始顺序。修复方案有两种-- 方案一范围写法对索引友好 SELECT * FROM member_order WHERE create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00; -- 方案二函数索引MySQL 8.0.13 可用 ALTER TABLE member_order ADD INDEX idx_create_date ((DATE(create_time)));我优先用方案一。函数索引虽然解决问题但会让优化器的选择空间变复杂版本兼容性也有限。能用原列范围写清楚的条件就不要引入额外机制。4.2 隐式类型转换表里order_no是 varchar如果查询写成数字SELECT * FROM member_order WHERE order_no 1234567890;MySQL 会把字符串列转换成数字再比较索引列上发生了隐式转换uk_order_no失效执行计划变成ALL。识别方法explain 里key是空的但Extra出现Using where说明条件是在回表之后过滤的。修复很粗暴SQL 里给值加上引号SELECT * FROM member_order WHERE order_no 1234567890;4.3 联合索引的最左前缀联合索引(member_id, status, create_time)下面三条 SQL 的索引用法完全不同-- 用得上member_id 是最左列 WHERE member_id 5 AND status 1; -- 用得上但只能用到 member_id 和 status WHERE member_id 5 AND status 1 AND create_time 2024-01-01; -- 用不上直接跳过了最左列 WHERE status 1 AND create_time 2024-01-01;第三条是练习里最常见的错误认知以为索引里有的字段都写上就完事。其实联合索引的字段顺序就是匹配顺序跳过了最左列整个索引都失效。修复思路是调整索引字段顺序最常作为等值条件的字段排前面范围字段放最后。比如上面的第三条查询如果业务上高频按status和create_time查就应该单独建(status, create_time)索引而不是指望(member_id, status, create_time)覆盖。4.4 LIKE前缀模糊查询SELECT * FROM member_order WHERE order_no LIKE %202405%;前缀模糊查询用不了普通 B 树索引的有序性uk_order_no直接失效。注意LIKE 2024%是可以走索引的只有%在前面的写法对索引不友好。业务上如果非要前缀模糊搜索我一般分两个方向处理改成前缀匹配LIKE 202405%配合order_no索引使用。如果场景是真正的全文检索建议引入专职的全文检索引擎而不是在 MySQL 里死磕 B 树。4.5 OR条件串联SELECT * FROM member_order WHERE member_id 5 OR order_no NO0000000001;MySQL 对 OR 的处理常常是两边条件都要访问如果其中一个条件没有有效索引就可能退化成全表扫描。虽然member_id有索引order_no也有唯一索引但优化器在 OR 场景下未必能同时利用两棵索引树。保险的做法是把 OR 改写为 UNIONSELECT * FROM member_order WHERE member_id 5 UNION SELECT * FROM member_order WHERE order_no NO0000000001;不过改写前先看执行计划因为 MySQL 8.0 对 OR 的优化能力比老版本强很多不是所有 OR 都必需改写。4.6 范围条件后面的字段失效联合索引(member_id, create_time, status)执行SELECT * FROM member_order WHERE member_id 5 AND create_time 2024-01-01 AND status 1;效果member_id和create_time用到了索引status只能回表后再过滤。因为 B 树索引在create_time上定位到一个范围区间这个区间内部的status是无序的无法继续匹配。修复方案是把等值条件提前范围条件放最后ALTER TABLE member_order ADD INDEX idx_cover (member_id, status, create_time);这个例子也呼应了最左前缀原则——字段顺序不是拍脑袋定的而是根据查询模式反复调出来的。4.7 六个失效场景的对照表我把上面的场景整理成一张速查表方便练习时对照失效场景典型写法核心原因修复方向函数处理列DATE(create_time) ...破坏了列序改范围写法或函数索引隐式转换order_no 123类型不匹配查询值加引号破坏最左前缀跳过联合索引首列匹配顺序不对调整索引字段顺序前缀模糊LIKE %abc无法利用有序性改前缀匹配或搜索引擎OR串联idx1 a OR idx2 b多路访问代价高UNION改写范围后字段范围条件后仍有等值字段区间内无序等值在前范围在后5. 覆盖索引、回表与排序少读一次数据页的收益有多大5.1 回表一次查询为什么要读两棵树InnoDB 的聚簇索引叶子节点存了整行数据二级索引叶子节点只存索引列和主键。通过二级索引查到主键后还要拿着主键回聚簇索引里找完整行这个动作叫回表。回表不是每次都很致命但数据量大、回表次数多的时候随机 IO 会成为瓶颈。我在 100 万行的表里做了简单测试查某个 member 的 5000 条订单普通回表方案耗时约 180ms覆盖索引方案约 60ms差距在 3 倍左右。对高频执行的小查询这个差距会被放大因为每个请求都在消耗额外 IO。5.2 覆盖索引的实战写法原 SQLSELECT order_no, amount, status FROM member_order WHERE member_id 123 AND status IN (1, 2) ORDER BY create_time DESC LIMIT 10;现有索引(member_id, status, create_time)执行计划里Extra是Using index condition说明走了索引下推但还需要回表取order_no和amount。这时建一个覆盖索引ALTER TABLE member_order ADD INDEX idx_member_status_create ( member_id, status, create_time, order_no, amount );覆盖索引的字段顺序要和查询条件的匹配顺序一致create_time放在status后面同时解决了排序问题order_no和amount放在最后是为了让 SELECT 列直接从索引树取不再回表。5.3 排序优化ORDER BY 和索引顺序的一致性ORDER BY create_time DESC如果索引顺序是(member_id, status, create_time)那么当member_id和status都等值确定后create_time在这个局部范围内是有序的MySQL 可以避免 filesort。反过来如果索引是(member_id, create_time, status)而 SQL 里 ORDER BY 是status, create_time排序就会走文件排序。判断方法还是看 Extra 有没有Using filesort。出现的时候检查三点WHERE 等值条件是否已经消耗掉了联合索引的最左列。ORDER BY 字段顺序是否和索引剩余字段顺序一致。升降序是否一致。MySQL 8.0 支持降序索引但默认升序索引对DESC排序帮助有限。5.4 前缀索引与索引选择性遇到remark这种长字符串字段要建索引时不要直接建全字段索引而是提取前 N 个字符做前缀索引ALTER TABLE member_order ADD INDEX idx_remark_prefix (remark(20));多长的前缀合适用选择性来计算SELECT COUNT(DISTINCT LEFT(remark, 10)) / COUNT(*) AS sel10, COUNT(DISTINCT LEFT(remark, 20)) / COUNT(*) AS sel20 FROM member_order;选择性越高越好一般建议达到 0.7 以上同时前缀长度不宜过长否则占空间大、写入也慢。前缀索引的缺点也要清楚不能再用于覆盖索引也不能用于 ORDER BY 和 GROUP BY。所以前缀索引适合的场景是等值查询 回表可接受的长字段检索。6. 从一条线上慢查询到索引重建完整排查链路复盘前面的练习是零散的场景复现这一节我把它们串起来复盘一条线上慢查询从发现到修复的完整过程。6.1 现象接口超时数据库 CPU 偏高某天订单查询接口 P99 延迟从 120ms 涨到 2.5s数据库 CPU 长时间在 70% 以上。我先打开slow_query_log捞出耗时最高的几条 SQL发现都是同一类查询SELECT id, order_no, amount, status, create_time, remark FROM member_order WHERE member_id ? AND create_time ? AND create_time ? ORDER BY create_time DESC LIMIT 20;参数不同但耗时普遍超过 1.5s。热点用户尤其明显。6.2 排查链路从慢日志到执行计划我按顺序执行了下面几步确认规律耗时和member_id的分布强相关热点用户明显更慢。SHOW INDEX FROM member_order;发现表上只有主键、uk_order_no和单列索引idx_member_id。对热点用户跑 explainEXPLAIN SELECT id, order_no, amount, status, create_time, remark FROM member_order WHERE member_id 88888 AND create_time 2024-01-01 AND create_time 2024-03-01 ORDER BY create_time DESC LIMIT 20;执行计划显示type refkey idx_member_idrows 13000Extra里有Using filesort。问题一下清楚了idx_member_id只用了member_id等值过滤查出 1.3 万行再回表读取完整行然后做文件排序取 20 条。热点用户订单多这个路径特别吃亏。6.3 根因定位索引设计没跟上查询模式这里有三个可优化点回表太多。二级索引叶子节点只有member_id和主键1.3 万行都要回表拿order_no、amount、create_time、remark等字段。文件排序。idx_member_id不包含create_time同一用户下的订单在索引里是无序的排序必须额外做。不能覆盖查询列。SELECT 里有remark即使加了create_time也覆盖不了所有列所以最终方案要考虑回表成本。最合理的修复是直接改成联合索引ALTER TABLE member_order DROP INDEX idx_member_id, ADD INDEX idx_member_time (member_id, create_time);注意我把旧的单列索引删了因为新索引的最左列就是member_id单列索引完全冗余。这也是前面强调过的索引不是越多越好的实战版本既解决当前问题又清理重复索引。6.4 验证与上线重建索引后再跑同样的 SQLtype: range key: idx_member_time rows: 420 Extra: Using index conditionrows 从 13000 降到 420实际查询耗时从 1.8s 降到 80ms 左右接口 P99 回落到 200ms 以内。上线前我额外做了件很多人忽略的事在测试环境用不同倾斜度的数据模拟热点用户反复跑 SQL确认新索引在最坏数据分布下不会退化。步骤不复杂但能避免本地正常、上线翻车。6.5 上线后的监控和反馈闭环索引改动上线后我持续观察三个指标slow_query_log里同类 SQL 是否消失。数据库 CPU 是否回落。索引使用情况通过performance_schema.table_io_waits_summary_by_index_usage或 sys 库的statement_analysis看新索引是否真的被高频使用。如果新索引建了一周都没被使用过那大概率是查询 SQL 和索引设计脱节了需要重新回到 explain 阶段排查。7. 练习之后的体会索引优化是取舍不是堆叠练习做到这里我发现索引优化真正的难点不是记住 B 树结构也不是背会 explain 每个字段的含义而是在具体业务场景里做取舍。idx_member_time解决了订单查询但也意味着每次插入、更新都要维护这个联合索引。如果这张表的写入量是 10 万 TPS索引项的增加会实打实反映在写入延迟上。所以我在设计索引时始终保留一个习惯列出这张表最核心的五到十条 SQL用它们来反推索引设计而不是看到 where 条件就加索引。另外一个体会是练习环境尽量贴近生产数据量要够大数据分布要倾斜SQL 要带真实的排序和分页。用一千行数据练索引优化得出的结论经常会误导人。我见过有人拿着 500 行测试表说加了索引就是快到了生产环境被打脸——差异就在于数据量和分布完全不同。如果你也想系统练一遍我建议按这个顺序走先把 explain 的type、rows、Extra三个列看熟然后在一张百万行表上把第四章的六个失效场景全部复现并修复最后再用第五章的覆盖索引思路优化几条慢查询。等这个过程跑完再去应对线上慢 SQL 和面试里的索引题会顺手很多。