作为一个天天跟慢查询打交道的人我其实不太喜欢把索引优化讲得太玄乎。mysql索引优化实战2这个标题说白了就是接着上一轮实战继续聊那些真正影响线上性能的细节。我今天不会上来就给你讲B树原理而是直接从一条慢SQL的排查现场出发把执行计划怎么看、索引为什么会失效、联合索引到底怎么设计这些事用我实际踩过的坑串起来。这轮内容更适合已经有半年以上MySQL使用经验、被线上慢查询折磨过的同学如果你是刚接触索引的新手建议先把EXPLAIN的各个字段含义搞清楚再来看这篇不然会有点吃力。我自己维护的业务库大概是千万级数据量日常查询压力不算小这轮优化过程中的每个案例都是我实际调整过的给出的参数和结论也都经过了线上环境验证你可以放心参考。1. 优化前的整体思路1.1 先从慢查询日志说起接手任何一个索引优化任务我的第一步从来都是同一个动作打开慢查询日志。很多人上来就对着业务代码猜这个查询哪里写得不合理、那个表是不是该加索引这种靠感觉的做法在数据量小的时候能糊弄过去一旦数据上了千万猜错的代价就是线上大面积超时。你可以在MySQL里这样开启和确认慢查询日志# 确认当前慢查询日志状态 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; # 如果没开启可以这样打开 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的SQL都会被记录下来生产环境我一般把long_query_time设置为1秒有些核心交易系统甚至要求0.5秒。设置好之后跑个半小时看一遍日志基本就能找到你最需要优化的那批SQL。慢查询日志的意义不只是告诉你哪些SQL慢更重要的是让你形成“按数据说话”的习惯而不是被直觉带偏。1.2 优化目标到底是什么拿到一条慢SQL先别急着加索引先问自己三个问题这条SQL的执行频率有多高单次执行时间多少它对整体系统资源的消耗占比有多大这三个问题的答案直接决定了你要投入多少精力去优化。比如一条每天只跑一次的后台统计SQL跑了8秒你花五小时去优化它收益其实很有限但如果是一条用户每次点击都要执行的查询从300毫秒优化到50毫秒这个收益就非常可观。我通常会给SQL分类高频低耗型执行次数很多但单次不慢这种往往要关注的是是否走了不必要的回表低频高耗型执行次数少但单次很慢比如月底报表类的聚合查询优化目标是减少扫描量高频高耗型这种是最需要优先处理的常见于核心列表页和订单查询区分清楚之后你才知道精力应该花在哪里。我们这次讲的就是第二种和第三种场景下的索引设计。2. 让执行计划开口说话2.1 EXPLAIN不只是看type列很多文章喜欢给你一个结论type列达到ref或者const就算好出现ALL或者index就是坏。这个说法大方向没错但如果只盯着type你会在实际优化中踩很多坑。我见过太多人一看到type为ALL就疯狂加索引结果加了索引之后还是ALL因为问题根本不在索引上而在查询语句的写法上。看执行计划我习惯了按下边这个顺序逐个分析id多个表的连接顺序id相同从上往下执行id不同大的先执行select_type是不是子查询、是不是union查询table哪张表type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALLpossible_keys理论上可能用到的索引key实际用到的索引key_len用到的索引长度这个长度越长说明用到的索引列越多rowsMySQL预估需要扫描的行数filtered经过WHERE条件过滤后剩余的比例Extra非常关键后面单独说这里我想重点提一下key_len。很多人忽略这个字段但它能直观地告诉你联合索引到底用到了几列。比如我们有个idx_a_b_c(a, b, c)索引如果key_len只等于a列的长度说明这次查询只用了联合索引的a列后面的b和c都被跳过了。这个时候你的联合索引设计就存在浪费要么调整索引列顺序要么改写SQL让更多列用到索引。2.2 Extra里藏着的回表信号Extra字段是执行计划里信息量最大的部分这里列几个我平时最关注的Using where表示存储引擎返回数据后又进行了过滤这种通常还能优化Using index覆盖索引扫描不回表这是最理想的状态Using index condition索引条件下推部分过滤在存储引擎层完成比单纯Using where好Using filesort需要排序而且排序没有走索引这个非常关键Using temporary用了临时表常见于GROUP BY和DISTINCT的联合使用如果一条SELECT里出现Using filesort我会高度警惕。排序可以直接走索引完成只要ORDER BY的字段和索引的顺序能匹配上。举个例子如果查询条件是WHERE category_id ?排序条件是ORDER BY create_time那你给(category_id, create_time)建联合索引排序就能直接用索引完成不会再产生filesort。这里有一个特别容易忽略的细节索引列的顺序对排序的影响。假设你已经有了(category_id, create_time)这个联合索引那么以下两条SQL的排序执行方式是完全不同的-- 查询条件对category_id做等值匹配order by的列正好是下一个索引列排序走索引 SELECT * FROM article WHERE category_id 12 ORDER BY create_time DESC LIMIT 20; -- 虽然也是按category_id范围查询但create_time没法沿用索引完成排序 SELECT * FROM article WHERE category_id 12 ORDER BY create_time DESC LIMIT 20;第二条SQL因为category_id是范围查询索引对后面的create_time列就失去了排序作用这一步很容易被忽略但恰恰是线上很多filesort的根源。3. 索引失效的六个真实场景3.1 函数操作和隐式转换这是索引失效案例里最经典也是我排查时最先检查的两个点。先说函数只要索引列参与了函数运算MySQL就会放弃走索引。这里的“函数运算”范围很广包括DATE_FORMAT(create_time, %Y-%m-%d)、LEFT(name, 3)、YEAR(create_time)等等。我举一个实际碰到过的例子。业务方想统计某天创建的订单第一版SQL写的是SELECT COUNT(*) FROM order_info WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-05-20;这条SQL的执行计划里type是ALL因为create_time被DATE_FORMAT包了一层索引直接失效。但只需要改写一下把函数从索引列上挪走用范围查询代替SELECT COUNT(*) FROM order_info WHERE create_time 2024-05-20 00:00:00 AND create_time 2024-05-21 00:00:00;改写之后走了索引在百万级数据表上这条SQL的执行时间从1080毫秒直接降到了62毫秒。这就是很典型的索引优化收益。再说隐式转换。MySQL对字符串列和数字的等值判断会自动把字符串列转成数字进行比较一旦发生类型转换索引一样失效。比如user_id是varchar类型你写WHERE user_id 1001这个SQL大概率不会走索引。排查这类问题有个简单办法拿到执行计划后看一下type和key_len如果发现varchar字段的key_len比应该的长度短很多往往就是发生了隐式转换。3.2 联合索引最左前缀的边界在哪里最左前缀原则大家多少都知道一些但实际设计联合索引的时候还是会有人栽在这里。我复盘一个真实的设计失误。有一张订单明细表业务上经常按order_id和product_id组合查询SELECT * FROM order_detail WHERE order_id 20240001 AND product_id P20331;当时同事给这张表建了一个联合索引(product_id, order_id)理由是product_id区分度更高。这个考虑本身没问题问题出在另一个追加需求上运营团队后来经常按order_id单独去查明细。这下就发现product_id, order_id的索引组合完全帮不上忙因为最左前缀被卡住了order_id作为第二列没法单独使用索引。最后我们调整成了(order_id, product_id)虽然product_id单独查询的场景变弱了但订单维度的查询以及订单加商品的组合查询都稳住了整体收益更大。你做联合索引设计的时候一定要先想清楚哪些列会单独作为查询条件出现而不是单纯追求单列区分度。4. 实战一条3000万级订单查询的优化全程4.1 定位问题SQL和执行计划我们线上有一张订单主表trade_order数据量在3000万行左右字段包含order_no、user_id、status、pay_time、total_amount等。业务方报了一个体感很明显的慢查询用户中心打开订单列表接口非常慢最夸张的时候达到5秒以上。抓到的SQL如下SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id U1000233 AND status 3 ORDER BY create_time DESC LIMIT 10;先看一眼执行计划EXPLAIN SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id U1000233 AND status 3 ORDER BY create_time DESC LIMIT 10;结果大概是这样的idselect_typetabletypepossible_keyskeykey_lenrowsfilteredExtra1SIMPLEtrade_orderrefidx_user_ididx_user_id6613288010.00Using where; Using filesort看到idx_user_id生效了type是ref扫描行数估算13万行问题看起来不算离谱。但注意Extra里有Using filesort也就是说ORDER BY create_time没有走索引MySQL要把13万行数据找出来之后再做排序再取前10条。这个排序成本在3000万行的表里被放大了接口慢也就不奇怪了。4.2 第一次优化覆盖索引加联合索引我决定建一个联合索引把user_id、status和create_time都放进去同时把要查询的列尽量覆盖在索引里减少回表ALTER TABLE trade_order ADD INDEX idx_user_status_time (user_id, status, create_time, order_no, total_amount);这里有一个细节可以多说几句create_time放在status后面是为了保证WHERE里的等值条件status 3都能命中最左前缀同时让ORDER BY create_time也能顺带走索引。加完索引之后再看执行计划idselect_typetabletypepossible_keyskeykey_lenrowsfilteredExtra1SIMPLEtrade_orderrefidx_user_status_timeidx_user_status_time7052010.00Using where; Using index扫描行数从13万降到了520行Using filesort也消失了执行时间从原来的5秒多降到了30毫秒左右。这次优化的核心收益来自两个方面一个是create_time进了索引排序被索引解决了另一个是覆盖索引让查询不用回到聚簇索引里取数据整体IO开销大幅下降。这里我想特别说明我把order_no和total_amount也放进了索引是为了让索引覆盖这条SQL的所有查询列避免回表。如果只放(user_id, status, create_time)那查order_no和total_amount的时候MySQL还是要回表拿数据性能会打个折扣。4.3 后续调整考虑更多业务路径索引上线之后我并没有收工因为我知道user_id status这个组合虽然覆盖了用户订单列表的常见场景但运营后台还有“按订单号精确查单”和“按支付时间区间查已付款订单”两个核心查询路径。经过第二轮分析我们额外保留了两个单列索引idx_order_no和idx_status_pay_time。这里有个取舍问题。订单号的查询必须精确匹配单列索引足够支付时间的查询要结合状态条件所以建了(status, pay_time)的联合索引。我们刻意没有把索引建得过多每增加一个索引写入和更新时的维护成本都会上升。线上系统写入压力也不小索引不是越多越好。4.4 再战分页线程上的深坑索引建好之后我和同事都觉得这回应该稳了。结果第二天收到告警后台管理页的分页查询又开始超时。定位到的SQL长这样SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id U1000233 AND status 3 ORDER BY create_time DESC LIMIT 100000, 20;执行计划走了新索引扫描行数也不多但MySQL需要先把前100000行数据全部找出来再丢掉只返回最后20条。这个“取出再丢弃”的过程在主键排序下尤其痛苦因为每一行都要回表拿数据累计损耗非常可观。解决思路不复杂把分页条件尽量转换成基于主键的定位查询。比如先用上一页拿到的最小主键ID作为下一页的起点SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id U1000233 AND status 3 AND id 100860 ORDER BY create_time DESC LIMIT 20;配合上一页最后一条记录的create_time和id可以做到稳定的键值分页不再需要深分页扫描。这种改法对用户端接口效果明显但后台管理页如果要支持任意跳页就还得结合时间范围做二次过滤不能无脑用同一个方案。5. 排序、分组和DISTINCT的索引写法5.1 ORDER BY的索引命中规则前面提过排序走索引的一些场景这里把规则说完整。只要ORDER BY的字段顺序和索引列顺序完全一致同时排序方向一致就能直接走索引完成排序。比如索引(category_id, create_time)ORDER BY category_id, create_time没问题但ORDER BY create_time就不行因为category_id没出现在条件里最左前缀就不成立了。还有一个细节经常被忽视ORDER BY和GROUP BY的字段顺序如果和索引顺序不一致MySQL不光要用临时表还可能引入排序叠加这种SQL在数据量大的时候几乎必挂。5.2 用覆盖索引优化高并发计数业务上“统计某个状态下订单数量”这类需求很常见SELECT COUNT(*) FROM trade_order WHERE status 3;这条SQL看着简单但如果status的区分度不高MySQL可能选择全表扫描。用覆盖索引可以避免回表让统计直接在索引里完成ALTER TABLE trade_order ADD INDEX idx_status (status, id);执行计划里会出现Using index扫描行数会大幅下降。不过要注意如果你的status分布极其不均匀比如99%的订单都是状态3那即便走了索引扫描量依然很大这时候要考虑的是业务层缓存或者其他架构手段单靠索引解决不了。5.3 聚合查询的临时表陷阱除了排序GROUP BY也是临时表的重灾区。比如统计每个用户的订单数SELECT user_id, COUNT(*) FROM trade_order GROUP BY user_id;如果user_id上有索引MySQL可以沿着索引顺序扫描分组避免Using temporary。但如果分组列不在任何索引上就必须建临时表。很多开发者面对百万级数据做分组统计时没感觉一旦到了千万级就瞬间卡死这就是临时表的代价。优化方式我总结为两种一种是把GROUP BY改成DISTINCT能覆盖的写法另一种是考虑加索引让分组走索引顺序。但更常见的业务场景其实是在分组条件里加上WHERE时间范围限制尽量减少参与分组的数据量。6. 常用索引优化SQL脚本6.1 帮你快速定位可疑索引的查询下面这个SQL会列出当前数据库中所有未使用过的索引注意这里的“未使用”指的是统计信息层面不一定100%准确但能给你一个排查方向SELECT s.INDEX_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s.CARDINALITY, t.TABLE_ROWS FROM information_schema.STATISTICS s LEFT JOIN information_schema.TABLES t ON s.TABLE_SCHEMA t.TABLE_SCHEMA AND s.TABLE_NAME t.TABLE_NAME WHERE s.INDEX_SCHEMA your_database AND s.INDEX_NAME PRIMARY ORDER BY t.TABLE_ROWS DESC;CARDINALITY是基数估算如果这个值明显偏小说明索引区分度不高命中它实际过滤掉的行数有限。6.2 一键生成批量删除冗余索引的语句查出疑似冗余索引后可以用下面的SQL拼接出删除语句但先不要直接执行建议每条都人工确认一遍SELECT CONCAT(ALTER TABLE , TABLE_SCHEMA, ., TABLE_NAME, DROP INDEX , INDEX_NAME, ;) AS drop_statement FROM information_schema.STATISTICS WHERE TABLE_SCHEMA your_database AND INDEX_NAME PRIMARY GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME HAVING COUNT(*) 0;这里HAVING COUNT(*) 0其实是个恒真条件我真实用的脚本会更复杂会去重同一个索引名在多个字段上的情况防止对联合索引生成重复的删除语句。经过这段排查我们在线上去掉了两个长期未被使用的冗余索引写入性能的提升虽然不算大但更新时的索引维护开销肉眼可见地降下来了。6.3 查看当前所有连接正在执行的查询索引优化最终要落到线上性能而线上性能又不只取决于索引。很多时候SQL慢是因为锁等待或者连接堆积所以我还习惯用下面这个SQL看实时状态SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS query_preview FROM information_schema.PROCESSLIST WHERE command Sleep ORDER BY time DESC;发现有大量进程卡在Sending data或者statistics状态基本就能断定是某条大查询在作祟。结合前面说的慢查询日志可以快速定位到具体SQL并回查执行计划。7. 索引失效排查问题速查表我整理一下排查索引问题时常用到的对照表可以当成工作笔记用。注意这不是全部情况但覆盖了线上最常见的案例。场景典型SQL写法问题本质推荐处理方式索引列使用函数WHERE DATE_FORMAT(create_time,%Y-%m-%d)2024-05-20函数破坏索引有序性改写为范围查询隐式类型转换WHERE user_id 1001user_id是varchar字符串列被转成数字参数加引号保持类型一致LIKE前置通配符WHERE name LIKE %关键词%无法利用B树顺序查找改全文索引或ESOR条件两边有非索引列WHERE user_id1 OR nick_nameabc优化器可能放弃合并索引改写为UNION或补索引联合索引跳过前导列WHERE product_idP1 AND create_time...索引为(order_id, product_id)违反了最左前缀调整索引列顺序NOT IN/等否定条件WHERE status 3优化器认为全表扫描成本更低改写范围查询或加新索引这个表只能当排查线索不能当定理。优化器最终怎么选还取决于表的数据分布和统计信息实际优化时务必用EXPLAIN逐个验证。8. 我最后的一些体会索引优化做到后面你会发现真正决定成败的不是会不会建索引而是能不能准确判断查询的瓶颈。执行计划、扫描行数、回表次数、排序方式、临时表使用情况这些组合在一起才构成完整的问题画像。我自己的习惯是每条慢SQL都保留一组优化前后的执行计划截图定期复盘这样慢慢就能形成直觉。还有一点索引优化永远不是一锤子买卖。业务在变数据分布也在变三个月前的最优索引设计三个月后可能就变成冗余索引。我们线上每个季度都会做一轮索引体检结合慢查询日志和实际执行频率把不再使用的索引删掉把新的查询模式用新索引覆盖。这轮实战讲到这儿其实已经覆盖了从执行计划分析到索引失效排查再到分页深坑的完整链路最后再补一句我在实际运维里用得很顺手的小技巧每次发布索引变更前先在测试库复制一份线上数据的最新统计信息跑一遍所有核心查询的EXPLAIN确认type和Extra都符合预期再上线。别直接在生产环境试探那种试探的代价通常都是挂一次。
MySQL索引优化实战：从慢查询日志到执行计划的排查指南
作为一个天天跟慢查询打交道的人我其实不太喜欢把索引优化讲得太玄乎。mysql索引优化实战2这个标题说白了就是接着上一轮实战继续聊那些真正影响线上性能的细节。我今天不会上来就给你讲B树原理而是直接从一条慢SQL的排查现场出发把执行计划怎么看、索引为什么会失效、联合索引到底怎么设计这些事用我实际踩过的坑串起来。这轮内容更适合已经有半年以上MySQL使用经验、被线上慢查询折磨过的同学如果你是刚接触索引的新手建议先把EXPLAIN的各个字段含义搞清楚再来看这篇不然会有点吃力。我自己维护的业务库大概是千万级数据量日常查询压力不算小这轮优化过程中的每个案例都是我实际调整过的给出的参数和结论也都经过了线上环境验证你可以放心参考。1. 优化前的整体思路1.1 先从慢查询日志说起接手任何一个索引优化任务我的第一步从来都是同一个动作打开慢查询日志。很多人上来就对着业务代码猜这个查询哪里写得不合理、那个表是不是该加索引这种靠感觉的做法在数据量小的时候能糊弄过去一旦数据上了千万猜错的代价就是线上大面积超时。你可以在MySQL里这样开启和确认慢查询日志# 确认当前慢查询日志状态 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; # 如果没开启可以这样打开 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的SQL都会被记录下来生产环境我一般把long_query_time设置为1秒有些核心交易系统甚至要求0.5秒。设置好之后跑个半小时看一遍日志基本就能找到你最需要优化的那批SQL。慢查询日志的意义不只是告诉你哪些SQL慢更重要的是让你形成“按数据说话”的习惯而不是被直觉带偏。1.2 优化目标到底是什么拿到一条慢SQL先别急着加索引先问自己三个问题这条SQL的执行频率有多高单次执行时间多少它对整体系统资源的消耗占比有多大这三个问题的答案直接决定了你要投入多少精力去优化。比如一条每天只跑一次的后台统计SQL跑了8秒你花五小时去优化它收益其实很有限但如果是一条用户每次点击都要执行的查询从300毫秒优化到50毫秒这个收益就非常可观。我通常会给SQL分类高频低耗型执行次数很多但单次不慢这种往往要关注的是是否走了不必要的回表低频高耗型执行次数少但单次很慢比如月底报表类的聚合查询优化目标是减少扫描量高频高耗型这种是最需要优先处理的常见于核心列表页和订单查询区分清楚之后你才知道精力应该花在哪里。我们这次讲的就是第二种和第三种场景下的索引设计。2. 让执行计划开口说话2.1 EXPLAIN不只是看type列很多文章喜欢给你一个结论type列达到ref或者const就算好出现ALL或者index就是坏。这个说法大方向没错但如果只盯着type你会在实际优化中踩很多坑。我见过太多人一看到type为ALL就疯狂加索引结果加了索引之后还是ALL因为问题根本不在索引上而在查询语句的写法上。看执行计划我习惯了按下边这个顺序逐个分析id多个表的连接顺序id相同从上往下执行id不同大的先执行select_type是不是子查询、是不是union查询table哪张表type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALLpossible_keys理论上可能用到的索引key实际用到的索引key_len用到的索引长度这个长度越长说明用到的索引列越多rowsMySQL预估需要扫描的行数filtered经过WHERE条件过滤后剩余的比例Extra非常关键后面单独说这里我想重点提一下key_len。很多人忽略这个字段但它能直观地告诉你联合索引到底用到了几列。比如我们有个idx_a_b_c(a, b, c)索引如果key_len只等于a列的长度说明这次查询只用了联合索引的a列后面的b和c都被跳过了。这个时候你的联合索引设计就存在浪费要么调整索引列顺序要么改写SQL让更多列用到索引。2.2 Extra里藏着的回表信号Extra字段是执行计划里信息量最大的部分这里列几个我平时最关注的Using where表示存储引擎返回数据后又进行了过滤这种通常还能优化Using index覆盖索引扫描不回表这是最理想的状态Using index condition索引条件下推部分过滤在存储引擎层完成比单纯Using where好Using filesort需要排序而且排序没有走索引这个非常关键Using temporary用了临时表常见于GROUP BY和DISTINCT的联合使用如果一条SELECT里出现Using filesort我会高度警惕。排序可以直接走索引完成只要ORDER BY的字段和索引的顺序能匹配上。举个例子如果查询条件是WHERE category_id ?排序条件是ORDER BY create_time那你给(category_id, create_time)建联合索引排序就能直接用索引完成不会再产生filesort。这里有一个特别容易忽略的细节索引列的顺序对排序的影响。假设你已经有了(category_id, create_time)这个联合索引那么以下两条SQL的排序执行方式是完全不同的-- 查询条件对category_id做等值匹配order by的列正好是下一个索引列排序走索引 SELECT * FROM article WHERE category_id 12 ORDER BY create_time DESC LIMIT 20; -- 虽然也是按category_id范围查询但create_time没法沿用索引完成排序 SELECT * FROM article WHERE category_id 12 ORDER BY create_time DESC LIMIT 20;第二条SQL因为category_id是范围查询索引对后面的create_time列就失去了排序作用这一步很容易被忽略但恰恰是线上很多filesort的根源。3. 索引失效的六个真实场景3.1 函数操作和隐式转换这是索引失效案例里最经典也是我排查时最先检查的两个点。先说函数只要索引列参与了函数运算MySQL就会放弃走索引。这里的“函数运算”范围很广包括DATE_FORMAT(create_time, %Y-%m-%d)、LEFT(name, 3)、YEAR(create_time)等等。我举一个实际碰到过的例子。业务方想统计某天创建的订单第一版SQL写的是SELECT COUNT(*) FROM order_info WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-05-20;这条SQL的执行计划里type是ALL因为create_time被DATE_FORMAT包了一层索引直接失效。但只需要改写一下把函数从索引列上挪走用范围查询代替SELECT COUNT(*) FROM order_info WHERE create_time 2024-05-20 00:00:00 AND create_time 2024-05-21 00:00:00;改写之后走了索引在百万级数据表上这条SQL的执行时间从1080毫秒直接降到了62毫秒。这就是很典型的索引优化收益。再说隐式转换。MySQL对字符串列和数字的等值判断会自动把字符串列转成数字进行比较一旦发生类型转换索引一样失效。比如user_id是varchar类型你写WHERE user_id 1001这个SQL大概率不会走索引。排查这类问题有个简单办法拿到执行计划后看一下type和key_len如果发现varchar字段的key_len比应该的长度短很多往往就是发生了隐式转换。3.2 联合索引最左前缀的边界在哪里最左前缀原则大家多少都知道一些但实际设计联合索引的时候还是会有人栽在这里。我复盘一个真实的设计失误。有一张订单明细表业务上经常按order_id和product_id组合查询SELECT * FROM order_detail WHERE order_id 20240001 AND product_id P20331;当时同事给这张表建了一个联合索引(product_id, order_id)理由是product_id区分度更高。这个考虑本身没问题问题出在另一个追加需求上运营团队后来经常按order_id单独去查明细。这下就发现product_id, order_id的索引组合完全帮不上忙因为最左前缀被卡住了order_id作为第二列没法单独使用索引。最后我们调整成了(order_id, product_id)虽然product_id单独查询的场景变弱了但订单维度的查询以及订单加商品的组合查询都稳住了整体收益更大。你做联合索引设计的时候一定要先想清楚哪些列会单独作为查询条件出现而不是单纯追求单列区分度。4. 实战一条3000万级订单查询的优化全程4.1 定位问题SQL和执行计划我们线上有一张订单主表trade_order数据量在3000万行左右字段包含order_no、user_id、status、pay_time、total_amount等。业务方报了一个体感很明显的慢查询用户中心打开订单列表接口非常慢最夸张的时候达到5秒以上。抓到的SQL如下SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id U1000233 AND status 3 ORDER BY create_time DESC LIMIT 10;先看一眼执行计划EXPLAIN SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id U1000233 AND status 3 ORDER BY create_time DESC LIMIT 10;结果大概是这样的idselect_typetabletypepossible_keyskeykey_lenrowsfilteredExtra1SIMPLEtrade_orderrefidx_user_ididx_user_id6613288010.00Using where; Using filesort看到idx_user_id生效了type是ref扫描行数估算13万行问题看起来不算离谱。但注意Extra里有Using filesort也就是说ORDER BY create_time没有走索引MySQL要把13万行数据找出来之后再做排序再取前10条。这个排序成本在3000万行的表里被放大了接口慢也就不奇怪了。4.2 第一次优化覆盖索引加联合索引我决定建一个联合索引把user_id、status和create_time都放进去同时把要查询的列尽量覆盖在索引里减少回表ALTER TABLE trade_order ADD INDEX idx_user_status_time (user_id, status, create_time, order_no, total_amount);这里有一个细节可以多说几句create_time放在status后面是为了保证WHERE里的等值条件status 3都能命中最左前缀同时让ORDER BY create_time也能顺带走索引。加完索引之后再看执行计划idselect_typetabletypepossible_keyskeykey_lenrowsfilteredExtra1SIMPLEtrade_orderrefidx_user_status_timeidx_user_status_time7052010.00Using where; Using index扫描行数从13万降到了520行Using filesort也消失了执行时间从原来的5秒多降到了30毫秒左右。这次优化的核心收益来自两个方面一个是create_time进了索引排序被索引解决了另一个是覆盖索引让查询不用回到聚簇索引里取数据整体IO开销大幅下降。这里我想特别说明我把order_no和total_amount也放进了索引是为了让索引覆盖这条SQL的所有查询列避免回表。如果只放(user_id, status, create_time)那查order_no和total_amount的时候MySQL还是要回表拿数据性能会打个折扣。4.3 后续调整考虑更多业务路径索引上线之后我并没有收工因为我知道user_id status这个组合虽然覆盖了用户订单列表的常见场景但运营后台还有“按订单号精确查单”和“按支付时间区间查已付款订单”两个核心查询路径。经过第二轮分析我们额外保留了两个单列索引idx_order_no和idx_status_pay_time。这里有个取舍问题。订单号的查询必须精确匹配单列索引足够支付时间的查询要结合状态条件所以建了(status, pay_time)的联合索引。我们刻意没有把索引建得过多每增加一个索引写入和更新时的维护成本都会上升。线上系统写入压力也不小索引不是越多越好。4.4 再战分页线程上的深坑索引建好之后我和同事都觉得这回应该稳了。结果第二天收到告警后台管理页的分页查询又开始超时。定位到的SQL长这样SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id U1000233 AND status 3 ORDER BY create_time DESC LIMIT 100000, 20;执行计划走了新索引扫描行数也不多但MySQL需要先把前100000行数据全部找出来再丢掉只返回最后20条。这个“取出再丢弃”的过程在主键排序下尤其痛苦因为每一行都要回表拿数据累计损耗非常可观。解决思路不复杂把分页条件尽量转换成基于主键的定位查询。比如先用上一页拿到的最小主键ID作为下一页的起点SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id U1000233 AND status 3 AND id 100860 ORDER BY create_time DESC LIMIT 20;配合上一页最后一条记录的create_time和id可以做到稳定的键值分页不再需要深分页扫描。这种改法对用户端接口效果明显但后台管理页如果要支持任意跳页就还得结合时间范围做二次过滤不能无脑用同一个方案。5. 排序、分组和DISTINCT的索引写法5.1 ORDER BY的索引命中规则前面提过排序走索引的一些场景这里把规则说完整。只要ORDER BY的字段顺序和索引列顺序完全一致同时排序方向一致就能直接走索引完成排序。比如索引(category_id, create_time)ORDER BY category_id, create_time没问题但ORDER BY create_time就不行因为category_id没出现在条件里最左前缀就不成立了。还有一个细节经常被忽视ORDER BY和GROUP BY的字段顺序如果和索引顺序不一致MySQL不光要用临时表还可能引入排序叠加这种SQL在数据量大的时候几乎必挂。5.2 用覆盖索引优化高并发计数业务上“统计某个状态下订单数量”这类需求很常见SELECT COUNT(*) FROM trade_order WHERE status 3;这条SQL看着简单但如果status的区分度不高MySQL可能选择全表扫描。用覆盖索引可以避免回表让统计直接在索引里完成ALTER TABLE trade_order ADD INDEX idx_status (status, id);执行计划里会出现Using index扫描行数会大幅下降。不过要注意如果你的status分布极其不均匀比如99%的订单都是状态3那即便走了索引扫描量依然很大这时候要考虑的是业务层缓存或者其他架构手段单靠索引解决不了。5.3 聚合查询的临时表陷阱除了排序GROUP BY也是临时表的重灾区。比如统计每个用户的订单数SELECT user_id, COUNT(*) FROM trade_order GROUP BY user_id;如果user_id上有索引MySQL可以沿着索引顺序扫描分组避免Using temporary。但如果分组列不在任何索引上就必须建临时表。很多开发者面对百万级数据做分组统计时没感觉一旦到了千万级就瞬间卡死这就是临时表的代价。优化方式我总结为两种一种是把GROUP BY改成DISTINCT能覆盖的写法另一种是考虑加索引让分组走索引顺序。但更常见的业务场景其实是在分组条件里加上WHERE时间范围限制尽量减少参与分组的数据量。6. 常用索引优化SQL脚本6.1 帮你快速定位可疑索引的查询下面这个SQL会列出当前数据库中所有未使用过的索引注意这里的“未使用”指的是统计信息层面不一定100%准确但能给你一个排查方向SELECT s.INDEX_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s.CARDINALITY, t.TABLE_ROWS FROM information_schema.STATISTICS s LEFT JOIN information_schema.TABLES t ON s.TABLE_SCHEMA t.TABLE_SCHEMA AND s.TABLE_NAME t.TABLE_NAME WHERE s.INDEX_SCHEMA your_database AND s.INDEX_NAME PRIMARY ORDER BY t.TABLE_ROWS DESC;CARDINALITY是基数估算如果这个值明显偏小说明索引区分度不高命中它实际过滤掉的行数有限。6.2 一键生成批量删除冗余索引的语句查出疑似冗余索引后可以用下面的SQL拼接出删除语句但先不要直接执行建议每条都人工确认一遍SELECT CONCAT(ALTER TABLE , TABLE_SCHEMA, ., TABLE_NAME, DROP INDEX , INDEX_NAME, ;) AS drop_statement FROM information_schema.STATISTICS WHERE TABLE_SCHEMA your_database AND INDEX_NAME PRIMARY GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME HAVING COUNT(*) 0;这里HAVING COUNT(*) 0其实是个恒真条件我真实用的脚本会更复杂会去重同一个索引名在多个字段上的情况防止对联合索引生成重复的删除语句。经过这段排查我们在线上去掉了两个长期未被使用的冗余索引写入性能的提升虽然不算大但更新时的索引维护开销肉眼可见地降下来了。6.3 查看当前所有连接正在执行的查询索引优化最终要落到线上性能而线上性能又不只取决于索引。很多时候SQL慢是因为锁等待或者连接堆积所以我还习惯用下面这个SQL看实时状态SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS query_preview FROM information_schema.PROCESSLIST WHERE command Sleep ORDER BY time DESC;发现有大量进程卡在Sending data或者statistics状态基本就能断定是某条大查询在作祟。结合前面说的慢查询日志可以快速定位到具体SQL并回查执行计划。7. 索引失效排查问题速查表我整理一下排查索引问题时常用到的对照表可以当成工作笔记用。注意这不是全部情况但覆盖了线上最常见的案例。场景典型SQL写法问题本质推荐处理方式索引列使用函数WHERE DATE_FORMAT(create_time,%Y-%m-%d)2024-05-20函数破坏索引有序性改写为范围查询隐式类型转换WHERE user_id 1001user_id是varchar字符串列被转成数字参数加引号保持类型一致LIKE前置通配符WHERE name LIKE %关键词%无法利用B树顺序查找改全文索引或ESOR条件两边有非索引列WHERE user_id1 OR nick_nameabc优化器可能放弃合并索引改写为UNION或补索引联合索引跳过前导列WHERE product_idP1 AND create_time...索引为(order_id, product_id)违反了最左前缀调整索引列顺序NOT IN/等否定条件WHERE status 3优化器认为全表扫描成本更低改写范围查询或加新索引这个表只能当排查线索不能当定理。优化器最终怎么选还取决于表的数据分布和统计信息实际优化时务必用EXPLAIN逐个验证。8. 我最后的一些体会索引优化做到后面你会发现真正决定成败的不是会不会建索引而是能不能准确判断查询的瓶颈。执行计划、扫描行数、回表次数、排序方式、临时表使用情况这些组合在一起才构成完整的问题画像。我自己的习惯是每条慢SQL都保留一组优化前后的执行计划截图定期复盘这样慢慢就能形成直觉。还有一点索引优化永远不是一锤子买卖。业务在变数据分布也在变三个月前的最优索引设计三个月后可能就变成冗余索引。我们线上每个季度都会做一轮索引体检结合慢查询日志和实际执行频率把不再使用的索引删掉把新的查询模式用新索引覆盖。这轮实战讲到这儿其实已经覆盖了从执行计划分析到索引失效排查再到分页深坑的完整链路最后再补一句我在实际运维里用得很顺手的小技巧每次发布索引变更前先在测试库复制一份线上数据的最新统计信息跑一遍所有核心查询的EXPLAIN确认type和Extra都符合预期再上线。别直接在生产环境试探那种试探的代价通常都是挂一次。
