上一篇文章聊完索引的基础结构后后台私信最多的一个问题explain看明白了索引也按网上教程建了为什么线上mysql慢SQL还是压不下来这篇文章作为mysql索引优化实战系列的第二篇不重复B树原理直接讲我近期在几个项目里做优化时的真实案例。核心会围绕复合索引顺序、索引失效现场、覆盖索引与回表成本、order by排序优化最后给一套从慢日志到索引落地验证的完整流程。内容偏实战适合已经会用EXPLAIN、想继续提升SQL优化能力的同学。1. 复合索引怎么建等值、范围、排序三要素的取舍1.1 为什么不能按SQL里条件的出现顺序建索引先讲一个高频问题where条件里有a和b应该怎么建索引很多人直接建(a,b)理由是SQL里a写在前面。这个说法非常片面。复合索引的顺序本质上由条件的约束类型、字段区分度、排序需求共同决定。我接下来说清楚。一个复合索引对查询的贡献可以理解为三层能力等值匹配a1 AND b2索引能精确锁定范围匹配a1 AND b2索引从a1的第一条b2记录开始扫排序与覆盖前面等值列固定后索引自身顺序可以让order by、group by免于filesort。为什么等值列要放到前面因为范围条件会“截断”后续字段的排序价值。索引(a,b,c)里a1 AND b5能用到a和b做定位但c字段在b5这段范围内是无序的无法再作为等值或排序条件利用索引。把等值条件放前面才能一层层ref定位下去把扫描范围压到最小。1.2 一个订单查询的完整设计过程假设有一张订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT NOT NULL, status TINYINT NOT NULL, pay_type TINYINT NOT NULL, create_time DATETIME NOT NULL, KEY idx_user_status_time (user_id, status, create_time) ) ENGINEInnoDB;业务查询SELECT id, user_id, status, create_time FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC LIMIT 10;这个场景里user_id和status是等值条件create_time是排序字段。按“等值在前、排序最后”的原则idx_user_status_time的顺序是合理的。explain里应该能看到ref或rangeExtra里没有filesort因为create_time在索引中天然有序反向扫描即可。再看带范围的版本WHERE user_id 123 AND create_time 2025-01-01 ORDER BY user_id, create_time;user_id已经被等值固定再排它没有意义。真正能省掉filesort的机会是让排序字段在索引中本来就是有序的。如果排序字段不是等值列后面的列基本走filesort。遇到这种SQL我的建议是不要急着堆索引先看业务能不能改成游标分页或者把排序字段纳入一个更合理的复合索引。决策优先级整理成一张表方便你贴手边条件类型在索引中的位置原因等值条件最前面快速定位到ref后续列保持有序范围条件中间范围扫描后的段内后续列顺序不可用排序字段最后前面等值列固定后索引顺序才能直接支撑排序再补充一个关于区分度的经验区分度高的列通常放前面比如user_id比status更值得放前面因为status只有几个取值扫出来可能几十万行。但区分度不是唯一标准如果某个字段在查询里出现的频率极低也不用为了区分度硬塞在前面。2. 常见的索引失效现场七个场景与根因下面这些不是网上教程的目录而是我从线上慢日志里一个一个捞出来的真实问题。2.1 隐式类型转换最经典的是手机号。表字段是phone varchar(20)查询写WHERE phone 13800138000。表面上没毛病实际MySQL会把字符串列转成DOUBLE与数字比较等于在索引列上悄悄做了一次函数运算索引自然用不上。explain看到typeALLrows巨大。改成WHERE phone 13800138000立刻能走ref。写SQL的习惯决定索引生死参数类型与字段类型保持严格一致。同样的坑还有身份证号、订单号这类看起来像数字的字符串字段。2.2 函数包裹索引列WHERE DATE(create_time) CURDATE()这种写法在报表查询里非常多。索引里存的是完整DATETIME值套上DATE函数后B树的有序性被打破只能全表扫描。改写为WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY;这样既命中范围索引又不需要上层过滤函数。MySQL 8.0支持函数索引但函数索引会在写入时增加计算成本我一般优先改写SQL函数索引留给实在改不动的场景。2.3 LIKE前置通配符WHERE title LIKE %优化%百分号在左边B树前缀匹配直接失效。数据量小时可以建覆盖索引减少回表数据量大或对相关性有排序要求时普通索引解决不了得换全文索引或搜索引擎。这里先记住结论前置通配符基本告别普通B树索引。2.4 OR连接不同字段WHERE status 1 OR pay_type 3如果两个字段各有单列索引MySQL有时会做index_mergeExtra里能看到Using union一旦预估结果集太大还是会选全表扫描。更稳的做法是把OR拆成两个结果集用UNION合并。注意UNION默认去重业务允许时用UNION ALL性能更好。2.5 范围条件切断后续字段前文提过索引(a,b,c)查询a1 AND b10 AND c2优化器只会用到a和bc的等值条件只能在存储引擎层通过ICP索引条件下推做过滤无法再参与定位。如果b的范围很宽c这个条件等于白建。正确姿势是适当调整索引顺序或者把c单独建索引别硬撑一个看似完美的三列索引。2.6 负向查询与NULL判断很多人说、NOT IN一定不用索引这是错的。优化器按成本估算只要预估行数占比足够小负向条件照样走索引范围扫描。我一般用覆盖索引兜底统计类SQL只查主键和几个高频字段就让这些字段都进索引即使条件负向扫描的也是索引树而不是整张聚簇索引。还有一点IS NULL并没有“一定失效”的问题但IS NOT NULL在字段大量为NULL时基数很低优化器常常放弃。2.7 一次慢SQL的完整排查链路拿一张user_action表举例user_id字段是VARCHARSQL写成了WHERE user_id 998 AND create_time BETWEEN ...慢日志显示执行2.3秒。explain结果typeALLrows约120万possible_keys为空。表上明明有(user_id, create_time)索引为什么没用上问题就出在数字998与varchar字段比较发生了隐式转换。我把查询改成user_id 998后type变成rangerows从120万降到4万执行时间降到60ms。这套链路没什么高深的魔法慢日志捞SQLexplain看type和rows顺藤摸瓜找到失效原因修正后再用explain对比。任何一步跳过都可能把问题带偏。3. 用Extra列做精细化优化覆盖索引与回表成本3.1 回表为什么慢InnoDB主键索引的叶子节点存整行数据二级索引的叶子节点存“索引列主键值”。用二级索引查到主键后如果要拿索引列以外的字段还得去主键索引做随机IO取行这就是回表。一次回表不可怕可怕的是LIMIT 20返回20行可能要回表20次。磁盘随机读和顺序读的延迟差距可以达到一到两个数量级即使换SSD随机IO的队列延迟也比顺序IO明显。很多同学只看type是否ref忽略了Extra有没有Using index。它们不是一回事Using index二级索引覆盖了查询需要的所有列不需要回表。Using index condition存储引擎用索引下推做了部分过滤但仍可能要回表取完整行。Using where取回数据后在Server层过滤不代表没用索引定位。3.2 覆盖索引的改造实例之前的查询SELECT id, author_id, status, title FROM article WHERE author_id 123 ORDER BY create_time DESC LIMIT 20;原索引只有author_id单列执行计划typeref但Extra里有Using filesort而且title不在索引里每条都要回表。改造后加索引ALTER TABLE article ADD KEY idx_author_time_cover (author_id, create_time, status, title), ALGORITHMINPLACE, LOCKNONE;这里不需要把id显式写进索引InnoDB二级索引叶子会自动带上主键id所以查询里的id、author_id、status、title都能从索引取出。改造后Extra变成Using index排序也免了。我实测这个查询从30ms左右降到2ms左右效果明显。但注意这个索引已经比较宽。如果title或status换成超大文本就不该塞进索引。索引本质是一棵排序树每次插入和更新都要同步维护索引列越多写入放大越大。覆盖索引的目标是让高频查询足够爽不是让所有查询都爽。3.3 什么时候该忍住不建覆盖索引我见过有同事为了让一个日报查询达到Using index把一个40个字符的description也放进索引结果写入性能掉得厉害磁盘占用也明显上涨。判断标准其实很简单查询的调用频率和返回行数。每天跑一次、返回几百行的报表回表一次真无所谓每秒调用上千次的核心接口省一次回表都值得。索引不是免费的每一列都是成本要按性价比来算。还有一个容易被忽略的点既然二级索引自动包含主键只查COUNT(*)或MAX(id)这类语句时二级索引完全可以扛住不必每次都纠结要不要加主键索引。做索引设计时这个“隐藏字段”也要算进覆盖能力里。4. 排序和分组优化filesort不是洪水猛兽但深分页要命4.1 filesort的真实成本与深分页问题当order by无法使用索引顺序时MySQL会把结果集放到sort_buffer里排序数据量超过阈值还会写临时文件explain的Extra里显示为Using filesort。filesort本身不一定慢结果集只有几十行时完全无感。真正麻烦的是深分页比如SELECT * FROM t WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20;即使status和create_time有复合索引LIMIT 100000也会让MySQL先取出100020条符合条件的记录排序后再丢掉前100000条大量工作都浪费在“取出来又扔掉”上。我遇到这种场景会先怀疑业务层翻页方式而不是马上加索引。4.2 延迟关联深分页的经典解法核心思路是先在覆盖索引上完成排序和分页只取主键和时间字段再回到原表拿完整行。看例子SELECT t.* FROM t INNER JOIN ( SELECT id, create_time FROM t WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id ORDER BY tmp.create_time DESC;内层子查询只读二级索引id和create_time都在索引里排序和分页都在索引上完成不会把整行无关列拖进sort_buffer。外层再用主键回表只取20行完整数据。这个方案在深分页场景里常见受益从秒级降到几十毫秒并不稀奇。如果连内层排序都嫌慢还可以改成游标分页WHERE status 1 AND create_time ? ORDER BY create_time DESC LIMIT 20;每次把上一页最后一条的create_time作为下一页起点彻底去掉OFFSET。代价是业务层要做无状态改造适合数据量真的很大的列表页。4.3 判断order by能不能走索引的快速规则where里有等值条件order by字段正好是这些等值列之后的索引列顺序与索引一致基本能免掉filesort。where里有范围条件排序字段的索引顺序大概率已经被打断优化器倾向于filesort。DESC和ASC混排时MySQL 8.0之前难以利用单列索引的反向顺序8.0可以创建KEY idx_a_b (a ASC, b DESC)这类降序索引但要注意版本和查询计划。group by和order by同理索引能让分组字段相邻从而减少Using temporary和filesort。遇到分组慢先看Extra是不是Using temporary再决定要不要调整索引。优化排序还有一个容易被低估的点只要结果集足够小filesort完全可以接受。为了一个偶尔触发的排序接口去维护宽索引不如先用LIMIT缩小数据范围或者让应用层多承担一点。排序优化的本质是减少参与排序的数据量而不是消灭filesort这个标记。小型实测对比我记录过一个表格简单参考场景查询方式Extra实测耗时索引直接排序LIMIT 20等值前导Using index20msfilesort小结果集返回50行无等值前导Using filesort30ms深分页OFFSET 100000Using filesort1.8s5. 慢SQL优化闭环从慢日志到索引落地的一整套打法前面的案例都是单点问题。要在团队里持续做索引优化我一般按下面这个闭环走。5.1 先把看不见的SQL捞出来开启慢日志是第一件要做的事slow_query_log ON long_query_time 1 log_queries_not_using_indexes ON想要动态生效也可以SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;慢日志文件可以用mysqldumpslow汇总但更快的方式是直接查performance_schemaSELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, SUM_ROWS_EXAMINED / COUNT_STAR AS avg_rows_examined FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 20;也可以直接看sys库的statements_with_full_table_scans专门把全表扫描的语句列出来。注意有些实例没开performance_schema先开启再观察不要为了查一条SQL重启数据库。5.2 用EXPLAIN ANALYZE验证而不是靠猜MySQL 8.0.18之后EXPLAIN ANALYZE能真实执行SQL并输出实际耗时和rows。比如EXPLAIN ANALYZE SELECT user_id, action, create_time FROM user_action WHERE user_id 998 AND create_time BETWEEN 2025-01-01 AND 2025-02-01 ORDER BY id DESC;输出会带actual time、actual rows这些真实数据。它比传统EXPLAIN可靠因为能看到优化器的估算到底偏了多少。但EXPLAIN ANALYZE会真实执行语句生产环境务必在低峰期或从库上跑重型SQL不要直接在主库试。传统EXPLAIN依赖优化器统计信息如果表很久没有ANALYZEestimate rows会失真这时候你看到possible_keys有索引、type却是ALL不一定是索引配置有问题而是统计信息告诉优化器“索引没什么用”。遇到这种情况先ANALYZE TABLE t;刷新统计信息再重新看explain。我之前踩过这个坑上线半年的一张表数据量翻了几十倍索引一直没变却突然从range退化成ALL刷新统计信息后立刻恢复正常。优化前后的对比建议按表格留档指标优化前优化后typeALLrangerows120000040000实际耗时2.3s60ms这种数据留档很有价值。很多团队优化完就结束过两三个月SQL又慢下来回头看当时的对比记录能快速判断是统计信息过期、数据量增长还是索引失效而不是从头再查一遍。5.3 索引落地与冗余清理新索引上线前先确认不会和已有索引重复。比如表里已经有idx_status_time(status, create_time)又建了一个idx_status(status)后者就是绝对冗余可以直接清理。判断方法很简单SHOW INDEX FROM 表名 看一下每个索引的列序列如果某个索引的前缀列和另一个索引完全相同而多出来的列又不是高频查询优先删除较短的旧索引。大批量表加索引时优先用online DDLALTER TABLE t ADD KEY idx_cover (a, b), ALGORITHMINPLACE, LOCKNONE;不过online DDL并不代表没有任何代价它仍然会触发大量索引页写入会占用buffer pool和IO。我的经验是大表加索引不要业务高峰直接执行低峰期或者用gh-ost这类工具更稳妥。最后分享一个我给自己定的规矩任何索引变更上线后一周内回看这张表的写入延迟和慢SQL数量。如果慢SQL少了但写入涨了说明索引到了收益边界如果慢SQL没少该删的索引立刻删不要因为“已经建好了”就舍不得。索引优化是一道长期维护题建索引不是终点保持监控闭环才是。
MySQL索引优化实战：复合索引顺序、覆盖索引与慢SQL排查方法
上一篇文章聊完索引的基础结构后后台私信最多的一个问题explain看明白了索引也按网上教程建了为什么线上mysql慢SQL还是压不下来这篇文章作为mysql索引优化实战系列的第二篇不重复B树原理直接讲我近期在几个项目里做优化时的真实案例。核心会围绕复合索引顺序、索引失效现场、覆盖索引与回表成本、order by排序优化最后给一套从慢日志到索引落地验证的完整流程。内容偏实战适合已经会用EXPLAIN、想继续提升SQL优化能力的同学。1. 复合索引怎么建等值、范围、排序三要素的取舍1.1 为什么不能按SQL里条件的出现顺序建索引先讲一个高频问题where条件里有a和b应该怎么建索引很多人直接建(a,b)理由是SQL里a写在前面。这个说法非常片面。复合索引的顺序本质上由条件的约束类型、字段区分度、排序需求共同决定。我接下来说清楚。一个复合索引对查询的贡献可以理解为三层能力等值匹配a1 AND b2索引能精确锁定范围匹配a1 AND b2索引从a1的第一条b2记录开始扫排序与覆盖前面等值列固定后索引自身顺序可以让order by、group by免于filesort。为什么等值列要放到前面因为范围条件会“截断”后续字段的排序价值。索引(a,b,c)里a1 AND b5能用到a和b做定位但c字段在b5这段范围内是无序的无法再作为等值或排序条件利用索引。把等值条件放前面才能一层层ref定位下去把扫描范围压到最小。1.2 一个订单查询的完整设计过程假设有一张订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT NOT NULL, status TINYINT NOT NULL, pay_type TINYINT NOT NULL, create_time DATETIME NOT NULL, KEY idx_user_status_time (user_id, status, create_time) ) ENGINEInnoDB;业务查询SELECT id, user_id, status, create_time FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC LIMIT 10;这个场景里user_id和status是等值条件create_time是排序字段。按“等值在前、排序最后”的原则idx_user_status_time的顺序是合理的。explain里应该能看到ref或rangeExtra里没有filesort因为create_time在索引中天然有序反向扫描即可。再看带范围的版本WHERE user_id 123 AND create_time 2025-01-01 ORDER BY user_id, create_time;user_id已经被等值固定再排它没有意义。真正能省掉filesort的机会是让排序字段在索引中本来就是有序的。如果排序字段不是等值列后面的列基本走filesort。遇到这种SQL我的建议是不要急着堆索引先看业务能不能改成游标分页或者把排序字段纳入一个更合理的复合索引。决策优先级整理成一张表方便你贴手边条件类型在索引中的位置原因等值条件最前面快速定位到ref后续列保持有序范围条件中间范围扫描后的段内后续列顺序不可用排序字段最后前面等值列固定后索引顺序才能直接支撑排序再补充一个关于区分度的经验区分度高的列通常放前面比如user_id比status更值得放前面因为status只有几个取值扫出来可能几十万行。但区分度不是唯一标准如果某个字段在查询里出现的频率极低也不用为了区分度硬塞在前面。2. 常见的索引失效现场七个场景与根因下面这些不是网上教程的目录而是我从线上慢日志里一个一个捞出来的真实问题。2.1 隐式类型转换最经典的是手机号。表字段是phone varchar(20)查询写WHERE phone 13800138000。表面上没毛病实际MySQL会把字符串列转成DOUBLE与数字比较等于在索引列上悄悄做了一次函数运算索引自然用不上。explain看到typeALLrows巨大。改成WHERE phone 13800138000立刻能走ref。写SQL的习惯决定索引生死参数类型与字段类型保持严格一致。同样的坑还有身份证号、订单号这类看起来像数字的字符串字段。2.2 函数包裹索引列WHERE DATE(create_time) CURDATE()这种写法在报表查询里非常多。索引里存的是完整DATETIME值套上DATE函数后B树的有序性被打破只能全表扫描。改写为WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY;这样既命中范围索引又不需要上层过滤函数。MySQL 8.0支持函数索引但函数索引会在写入时增加计算成本我一般优先改写SQL函数索引留给实在改不动的场景。2.3 LIKE前置通配符WHERE title LIKE %优化%百分号在左边B树前缀匹配直接失效。数据量小时可以建覆盖索引减少回表数据量大或对相关性有排序要求时普通索引解决不了得换全文索引或搜索引擎。这里先记住结论前置通配符基本告别普通B树索引。2.4 OR连接不同字段WHERE status 1 OR pay_type 3如果两个字段各有单列索引MySQL有时会做index_mergeExtra里能看到Using union一旦预估结果集太大还是会选全表扫描。更稳的做法是把OR拆成两个结果集用UNION合并。注意UNION默认去重业务允许时用UNION ALL性能更好。2.5 范围条件切断后续字段前文提过索引(a,b,c)查询a1 AND b10 AND c2优化器只会用到a和bc的等值条件只能在存储引擎层通过ICP索引条件下推做过滤无法再参与定位。如果b的范围很宽c这个条件等于白建。正确姿势是适当调整索引顺序或者把c单独建索引别硬撑一个看似完美的三列索引。2.6 负向查询与NULL判断很多人说、NOT IN一定不用索引这是错的。优化器按成本估算只要预估行数占比足够小负向条件照样走索引范围扫描。我一般用覆盖索引兜底统计类SQL只查主键和几个高频字段就让这些字段都进索引即使条件负向扫描的也是索引树而不是整张聚簇索引。还有一点IS NULL并没有“一定失效”的问题但IS NOT NULL在字段大量为NULL时基数很低优化器常常放弃。2.7 一次慢SQL的完整排查链路拿一张user_action表举例user_id字段是VARCHARSQL写成了WHERE user_id 998 AND create_time BETWEEN ...慢日志显示执行2.3秒。explain结果typeALLrows约120万possible_keys为空。表上明明有(user_id, create_time)索引为什么没用上问题就出在数字998与varchar字段比较发生了隐式转换。我把查询改成user_id 998后type变成rangerows从120万降到4万执行时间降到60ms。这套链路没什么高深的魔法慢日志捞SQLexplain看type和rows顺藤摸瓜找到失效原因修正后再用explain对比。任何一步跳过都可能把问题带偏。3. 用Extra列做精细化优化覆盖索引与回表成本3.1 回表为什么慢InnoDB主键索引的叶子节点存整行数据二级索引的叶子节点存“索引列主键值”。用二级索引查到主键后如果要拿索引列以外的字段还得去主键索引做随机IO取行这就是回表。一次回表不可怕可怕的是LIMIT 20返回20行可能要回表20次。磁盘随机读和顺序读的延迟差距可以达到一到两个数量级即使换SSD随机IO的队列延迟也比顺序IO明显。很多同学只看type是否ref忽略了Extra有没有Using index。它们不是一回事Using index二级索引覆盖了查询需要的所有列不需要回表。Using index condition存储引擎用索引下推做了部分过滤但仍可能要回表取完整行。Using where取回数据后在Server层过滤不代表没用索引定位。3.2 覆盖索引的改造实例之前的查询SELECT id, author_id, status, title FROM article WHERE author_id 123 ORDER BY create_time DESC LIMIT 20;原索引只有author_id单列执行计划typeref但Extra里有Using filesort而且title不在索引里每条都要回表。改造后加索引ALTER TABLE article ADD KEY idx_author_time_cover (author_id, create_time, status, title), ALGORITHMINPLACE, LOCKNONE;这里不需要把id显式写进索引InnoDB二级索引叶子会自动带上主键id所以查询里的id、author_id、status、title都能从索引取出。改造后Extra变成Using index排序也免了。我实测这个查询从30ms左右降到2ms左右效果明显。但注意这个索引已经比较宽。如果title或status换成超大文本就不该塞进索引。索引本质是一棵排序树每次插入和更新都要同步维护索引列越多写入放大越大。覆盖索引的目标是让高频查询足够爽不是让所有查询都爽。3.3 什么时候该忍住不建覆盖索引我见过有同事为了让一个日报查询达到Using index把一个40个字符的description也放进索引结果写入性能掉得厉害磁盘占用也明显上涨。判断标准其实很简单查询的调用频率和返回行数。每天跑一次、返回几百行的报表回表一次真无所谓每秒调用上千次的核心接口省一次回表都值得。索引不是免费的每一列都是成本要按性价比来算。还有一个容易被忽略的点既然二级索引自动包含主键只查COUNT(*)或MAX(id)这类语句时二级索引完全可以扛住不必每次都纠结要不要加主键索引。做索引设计时这个“隐藏字段”也要算进覆盖能力里。4. 排序和分组优化filesort不是洪水猛兽但深分页要命4.1 filesort的真实成本与深分页问题当order by无法使用索引顺序时MySQL会把结果集放到sort_buffer里排序数据量超过阈值还会写临时文件explain的Extra里显示为Using filesort。filesort本身不一定慢结果集只有几十行时完全无感。真正麻烦的是深分页比如SELECT * FROM t WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20;即使status和create_time有复合索引LIMIT 100000也会让MySQL先取出100020条符合条件的记录排序后再丢掉前100000条大量工作都浪费在“取出来又扔掉”上。我遇到这种场景会先怀疑业务层翻页方式而不是马上加索引。4.2 延迟关联深分页的经典解法核心思路是先在覆盖索引上完成排序和分页只取主键和时间字段再回到原表拿完整行。看例子SELECT t.* FROM t INNER JOIN ( SELECT id, create_time FROM t WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id ORDER BY tmp.create_time DESC;内层子查询只读二级索引id和create_time都在索引里排序和分页都在索引上完成不会把整行无关列拖进sort_buffer。外层再用主键回表只取20行完整数据。这个方案在深分页场景里常见受益从秒级降到几十毫秒并不稀奇。如果连内层排序都嫌慢还可以改成游标分页WHERE status 1 AND create_time ? ORDER BY create_time DESC LIMIT 20;每次把上一页最后一条的create_time作为下一页起点彻底去掉OFFSET。代价是业务层要做无状态改造适合数据量真的很大的列表页。4.3 判断order by能不能走索引的快速规则where里有等值条件order by字段正好是这些等值列之后的索引列顺序与索引一致基本能免掉filesort。where里有范围条件排序字段的索引顺序大概率已经被打断优化器倾向于filesort。DESC和ASC混排时MySQL 8.0之前难以利用单列索引的反向顺序8.0可以创建KEY idx_a_b (a ASC, b DESC)这类降序索引但要注意版本和查询计划。group by和order by同理索引能让分组字段相邻从而减少Using temporary和filesort。遇到分组慢先看Extra是不是Using temporary再决定要不要调整索引。优化排序还有一个容易被低估的点只要结果集足够小filesort完全可以接受。为了一个偶尔触发的排序接口去维护宽索引不如先用LIMIT缩小数据范围或者让应用层多承担一点。排序优化的本质是减少参与排序的数据量而不是消灭filesort这个标记。小型实测对比我记录过一个表格简单参考场景查询方式Extra实测耗时索引直接排序LIMIT 20等值前导Using index20msfilesort小结果集返回50行无等值前导Using filesort30ms深分页OFFSET 100000Using filesort1.8s5. 慢SQL优化闭环从慢日志到索引落地的一整套打法前面的案例都是单点问题。要在团队里持续做索引优化我一般按下面这个闭环走。5.1 先把看不见的SQL捞出来开启慢日志是第一件要做的事slow_query_log ON long_query_time 1 log_queries_not_using_indexes ON想要动态生效也可以SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;慢日志文件可以用mysqldumpslow汇总但更快的方式是直接查performance_schemaSELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, SUM_ROWS_EXAMINED / COUNT_STAR AS avg_rows_examined FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 20;也可以直接看sys库的statements_with_full_table_scans专门把全表扫描的语句列出来。注意有些实例没开performance_schema先开启再观察不要为了查一条SQL重启数据库。5.2 用EXPLAIN ANALYZE验证而不是靠猜MySQL 8.0.18之后EXPLAIN ANALYZE能真实执行SQL并输出实际耗时和rows。比如EXPLAIN ANALYZE SELECT user_id, action, create_time FROM user_action WHERE user_id 998 AND create_time BETWEEN 2025-01-01 AND 2025-02-01 ORDER BY id DESC;输出会带actual time、actual rows这些真实数据。它比传统EXPLAIN可靠因为能看到优化器的估算到底偏了多少。但EXPLAIN ANALYZE会真实执行语句生产环境务必在低峰期或从库上跑重型SQL不要直接在主库试。传统EXPLAIN依赖优化器统计信息如果表很久没有ANALYZEestimate rows会失真这时候你看到possible_keys有索引、type却是ALL不一定是索引配置有问题而是统计信息告诉优化器“索引没什么用”。遇到这种情况先ANALYZE TABLE t;刷新统计信息再重新看explain。我之前踩过这个坑上线半年的一张表数据量翻了几十倍索引一直没变却突然从range退化成ALL刷新统计信息后立刻恢复正常。优化前后的对比建议按表格留档指标优化前优化后typeALLrangerows120000040000实际耗时2.3s60ms这种数据留档很有价值。很多团队优化完就结束过两三个月SQL又慢下来回头看当时的对比记录能快速判断是统计信息过期、数据量增长还是索引失效而不是从头再查一遍。5.3 索引落地与冗余清理新索引上线前先确认不会和已有索引重复。比如表里已经有idx_status_time(status, create_time)又建了一个idx_status(status)后者就是绝对冗余可以直接清理。判断方法很简单SHOW INDEX FROM 表名 看一下每个索引的列序列如果某个索引的前缀列和另一个索引完全相同而多出来的列又不是高频查询优先删除较短的旧索引。大批量表加索引时优先用online DDLALTER TABLE t ADD KEY idx_cover (a, b), ALGORITHMINPLACE, LOCKNONE;不过online DDL并不代表没有任何代价它仍然会触发大量索引页写入会占用buffer pool和IO。我的经验是大表加索引不要业务高峰直接执行低峰期或者用gh-ost这类工具更稳妥。最后分享一个我给自己定的规矩任何索引变更上线后一周内回看这张表的写入延迟和慢SQL数量。如果慢SQL少了但写入涨了说明索引到了收益边界如果慢SQL没少该删的索引立刻删不要因为“已经建好了”就舍不得。索引优化是一道长期维护题建索引不是终点保持监控闭环才是。
