1. 排查前先搞清楚MySQL索引为什么“没被用上”1.1 你以为建好的索引在优化器眼里可能一文不值先说一个我几乎每周都能遇到的场景某开发者跑过来跟我说“我这表明明建了索引查询还是很慢是不是MySQL出问题了”我让他把这表的所有索引列出来发现确实建了有模有样的单列索引加联合索引都有。结果EXPLAIN一出来type直接是ALLkey是NULL好几百万行数据全表扫描。问题出在哪很多人对索引的理解停留在“建了就生效”但MySQL的优化器压根不这么想。索引本质是一棵B树它能把数据按顺序组织起来让查找从“一张张翻页”变成“按目录查”。但优化器决定用一个索引之前会先估算成本——走这个索引要扫多少行、每扫一行回表要多少次随机IO、最后要不要排序这些加起来如果比全表扫描还贵它就果断放弃索引。这里有个经典类比你有一本一千页的词典想找一个大概在第一页就能看到的生僻字你还会先去翻目录吗大概率直接从头翻两页就找到了。这就是优化器的逻辑——它不是看你有没有索引而是看走索引划不划算。所以建索引之前至少要想明白三件事这个列的区分度高不高查出来之后要回表多少次能不能通过索引直接覆盖查询需要的全部字段。这三个问题没想清楚索引建了也是白建。1.2 三分钟定位慢查询EXPLAIN该看哪几列拿到一条慢SQL我建议按下面这套流程来不用瞎猜每一步都有明确目的。第一步开启慢查询日志。MySQL里可以临时设置不用重启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;这样超过1秒的查询就会落日志方便你去捞真正该优化的SQL。生产环境建议长期开着对性能影响很小。第二步对目标SQL执行EXPLAIN。EXPLAIN的输出列很多但你先重点盯四列列名含义你需要关注的点type访问类型从好到差依次是system const eq_ref ref range index ALL看到ALL基本等于全表扫了key实际用到的索引NULL说明没用上任何索引跟你建没建没关系rows预估扫描行数这个数字远远大于你预期就说明优化器估算出了问题Extra附加信息出现Using filesort、Using temporary、Using where通常意味着有额外成本第三步如果EXPLAIN还不够用优化器跟踪。MySQL 8.0可以通过optimizer trace把优化器每一步的成本计算过程完整导出来SET optimizer_trace enabledon; SELECT * FROM order_record WHERE uid U123456 ORDER BY create_time DESC LIMIT 20; SELECT * FROM information_schema.OPTIMIZER_TRACE;这个结果会告诉你优化器为什么放弃了某个索引或者为什么选择了另一个执行计划。我排查到后面会详细展示怎么用这个工具。整个定位过程不需要什么黑科技EXPLAIN一把梭就能解决90%的问题。真正难的是读懂EXPLAIN背后反映出来的索引设计问题。2. 索引失效的常见套路我自己踩过的几个典型场景2.1 对索引列做函数操作索引直接“报废”这是最经典的坑也是最容易踩的。比如你要查某一天创建的订单很顺手就写成SELECT * FROM order_record WHERE DATE(create_time) 2024-11-01;看起来没毛病对吧但这个写法等于告诉MySQL“先把create_time每一行都套一层DATE函数再拿结果去和字符串比较。”索引里存储的是原始时间值经过函数之后顺序完全被打乱B树的有序性就失效了优化器只能放弃索引。正确写法是范围查询SELECT * FROM order_record WHERE create_time 2024-11-01 00:00:00 AND create_time 2024-11-02 00:00:00;这个写法能直接利用B树的范围查找能力。我实测过同样一张三百万行的表函数写法跑出来要两秒多改成范围查询后基本在几十毫秒内返回。差别就是这么明显。还有个常被忽视的类似场景用SUBSTR截取字符串去匹配。比如对订单号字段执行SUBSTR(order_no, 1, 8)去查询效果和DATE函数一样索引同样失效。记住一个原则对索引列做任何运算、函数、隐式转换都要格外小心能改写法就改写法。2.2 隐式类型转换和字符集不一致索引“假死”的隐形杀手这个坑比函数操作隐蔽得多因为它不会报错只会悄悄让索引失效。我排查过一个案例某系统的订单号order_no字段定义为VARCHAR类型但代码里查询时用的是数字而不是字符串SELECT * FROM order_record WHERE order_no 123456789012;MySQL看到字段是VARCHAR而比较值是数字会把字段值先转成数字再比较。转换过程中索引再次失去作用。这条SQL执行计划直接是全表扫描。把数字改成字符串后SELECT * FROM order_record WHERE order_no 123456789012;执行计划立刻变ref性能从秒级降到毫秒级。字符集不一致更坑。两表关联时如果一张表字符集是utf8mb4另一张是utf8mb3MySQL会强制将一侧做字符集转换索引同样发挥不了作用。我用过一次惨痛的教训订单表和用户表关联查询慢到爆排查半天把两表字符集统一后查询时间直接砍半。建议在建表时就统一全库字符集为utf8mb4并且在写SQL前确认字段类型尤其是主键、外键、关联键的类型和字符集必须完全一致。2.3 前缀模糊匹配和OR连接词让索引无用武之地LIKE查询是很多业务系统离不开的但LIKE %关键词%这种写法索引大概率帮不上忙。原因在于B树只能处理“某个连续前缀”的查找你要的字符串前面有个通配符MySQL没法用索引的有序性去定位范围只能逐个扫描。如果业务确实需要全文级模糊搜索与其硬扛不如考虑全文索引或者搜索引擎组件。OR的情况也有点类似。我曾经处理过一个查询SELECT * FROM order_record WHERE status 1 OR pay_type 2;status和pay_type都分别建了索引但MySQL并没有同时用两个索引然后合并结果。很多时候它依然会选择全表扫描。因为两个条件任一匹配都算命中逐一判断比分别查索引再合并更直接。改写思路有两种一是看能不能拆成两条查询用UNION合并二是重新设计联合索引让OR的两个条件都在同一个索引内。不过在实际业务中我更建议直接检查这个查询是否真的需要两个条件很多情况是业务逻辑本身可以拆分。2.4 联合索引没遵守最左前缀一建就废联合索引是另一个常见灾难点。很多开发者的习惯是“哪个查询慢了就给哪个字段加索引”结果一张表攒了五六个单列索引性能还是上不去。我举一个真实案例。某订单表有多个查询场景按uid查、按status查、按create_time查。开发者给这三个字段分别建了单列索引结果每个场景的查询速度都不太理想。原因很简单单列索引只能在单个字段上提效一旦查询条件组合在一起比如WHERE uid ? AND status ?单列索引的利用率会大打折扣。正确做法是用一个联合索引同时覆盖多个条件。但联合索引有个核心规则最左前缀原则。索引(a, b, c)相当于给了你三种查找能力(a)、(a, b)、(a, b, c)但不能直接用于只有b或者只有c的查询。所以设计索引时要把查询频率最高、区分度最好的字段放最左边。MySQL 8.0推出了索引跳跃扫描在某些场景下可以跳过最左前缀直接查后面的列但限制比较多实际收益不稳定。我的建议是别指望跳跃扫描老老实实按照查询模式去设计联合索引。2.5 区分度太低优化器直接放弃这类坑最让人哭笑不得。一个字段明明建了索引EXPLAIN显示没用上你觉得服务器出问题了其实是因为这个字段的取值太“扁”。举个例子订单表里is_deleted字段只有0和1两个值你给is_deleted建索引想象一下100万行数据里95万行是0。优化器一算走这个索引95万行都要通过主键回表取完整数据一次随机IO一次命中的成本比全表顺序扫描高得多直接放弃走全表。我见过有人给sex、status这种枚举值很少的字段单独建索引毫无意义。这类低区分度字段不是不能进索引而是必须和别的字段组合成一个联合索引让它作为辅助过滤条件才有价值。比如(uid, status)联合索引中status放在第二位既不影响uid查找又能在uid锁定后多一层过滤这才是低区分度字段的正确用法。3. 排查实录一个真实慢查询的全过程3.1 现象与第一轮排查走了索引为什么还是慢我处理过一个比较典型的线上问题发生在某个电商系统的订单模块。订单表order_record大概三百万行结构简化后包含这几个核心字段id主键、uid、order_no、status、create_time、pay_type。线上反馈说订单列表页的接口越来越慢尤其是翻页到后面的时候一个接口要两秒多。我先抓到了其中一条慢SQLSELECT id, order_no, status, create_time FROM order_record WHERE uid U123456 ORDER BY create_time DESC LIMIT 1000000, 20;第一眼看到这条SQL我的判断是索引肯定没走全不然不会这么慢。结果EXPLAIN一跑让我有点意外type: refkey: idx_uidrows: 326Extra: Using index condition; Using filesort注意这里索引其实用上了idx_uidrows也不算大就只有几百行。真正的问题是Extra里的Using filesort。这个表的索引只有idx_uid单列索引ORDER BY create_time需要MySQL把uidU123456的所有记录全部捞出来再另起炉灶做一次排序。当这个用户订单量很大的时候排序成本不可小觑。但这只是第一层。真正致命的是LIMIT 1000000, 20这个分页偏移量太大了。3.2 深挖下去深分页和回表到底多可怕我们来算一笔账。这条SQL执行过程中MySQL做了这么几件事第一通过二级索引idx_uid定位到uidU123456的所有主键ID。第二用这些ID回表把每一行的完整数据读出来目的是取到create_time字段用于排序。第三在内存里对所有结果按create_time降序做filesort。第四排完序之后从头数到第100万条再把第100万到第100万零20条返回给你。你会发现LIMIT 1000000, 20这个写法意味着前面99万条数据都已经完整走了一遍“回表排序”流程最后全部被丢弃。真正有用的只有最后20条。这种浪费简直离谱。这也是很多人最容易忽略的点哪怕索引用对了深分页照样能把MySQL压垮。你可以做个实验LIMIT 0,20和LIMIT 1000000,20的耗时差距往往不是线性增长而是几十倍、上百倍的差距。原因就在于回表次数和排序数据量翻了太多倍。另外Using filesort这里还有个隐患如果排序的数据量超过sort_buffer_sizeMySQL会把中间结果写到磁盘临时文件里多次归并排序。磁盘IO一上场性能直接雪崩。当时我看了这个用户的数据订单总数大概三千条排序本身还好但结合深分页问题就被无限放大了。3.3 顺藤摸瓜统计信息陈旧和参数配置排查到这一步我顺手又检查了两件事统计信息是否准确、关键缓存参数是否合理。我执行了EXPLAIN之后发现rows326但实际EXPLAIN ANALYZE出来的执行行数和预期差别不小。这说明优化器用的统计信息可能偏旧。MySQL在大量增删改之后统计信息如果不及时更新优化器就会用一套过时的“经验”去估算成本。后来我执行了一遍ANALYZE TABLE order_record;统计信息刷新后执行计划确实有了变化。另外在MySQL 8.0里还可以给非均匀分布的字段建直方图ANALYZE TABLE order_record UPDATE HISTOGRAM ON create_time WITH 2048 BUCKETS;这能让优化器更精准地估算某个值范围的行数。参数层面两个值值得关注。第一个是innodb_buffer_pool_sizeInnoDB的缓冲池如果太小回表时大量数据要从磁盘加载性能会很惨。第二个是sort_buffer_size排序缓冲区太小会导致filesort落盘这个问题我在另外一次排查中遇到过那次是几千行数据排序跑了十几秒查了半天才发现是缓冲区落盘了。但这里提醒一句别把sort_buffer_size盲目调得过大它是每个连接独立分配的连接多了内存很快就爆。4. 优化方案与落地测试从0.2s降到0.03s4.1 重新设计联合索引让过滤和排序一起走索引问题定位清楚了方案也就清晰了。第一步是动索引。既然查询模式是“按uid过滤再按create_time排序”就该把这两个字段放进同一个联合索引里而不是各建各的。我当时的改造方案是ALTER TABLE order_record DROP INDEX idx_uid, ADD INDEX idx_uid_create_time (uid, create_time DESC);这条索引的核心思路是uid字段负责等值过滤create_time字段放在联合索引第二位负责排序。因为联合索引在B树中是按(uid, create_time)顺序存储的当你用uidU123456定位到之后这部分数据天然就是按create_time排好序的MySQL连排序都不用做了直接顺着索引往后读就行。注意我故意用了DESC关键字MySQL 8.0支持倒序索引能让降序查询彻底避免filesort。如果你们用的是5.7及以下版本可以建ASC索引然后SQL里ORDER BY create_time DESCMySQL也会反向扫描索引效果差不多但部分场景下效率略低。索引改完之后我再跑EXPLAINtype: refkey: idx_uid_create_timeExtra: Using index conditionUsing filesort消失了这就是很大进步。但这还不够深分页的问题还在。4.2 游标分页改造彻底甩掉大偏移量对于订单列表翻页这种场景我的建议是别用LIMIT offset, pageSize这种深分页方式改成基于游标的分页。改造前的写法SELECT id, order_no, status, create_time FROM order_record WHERE uid U123456 ORDER BY create_time DESC LIMIT 1000000, 20;改造后SELECT id, order_no, status, create_time FROM order_record WHERE uid U123456 AND create_time 2024-11-01 12:00:00 ORDER BY create_time DESC LIMIT 20;后端每次返回最后一条记录的create_time作为下一页的游标。下一次查询时只要带上“create_time小于这个值”的条件数据库就能顺着索引快速定位到游标位置直接往后读20条数据。这中间完全不需要跳过99万条无关数据。这个方案有两个注意点。第一如果业务允许输入的时间戳精度不够可能会有重复数据或漏数据。稳妥做法是游标不止带时间还带上最后一条记录的id让条件变成create_time ? OR (create_time ? AND id ?)确保排序完全稳定。第二游标分页牺牲了“任意跳页”的能力你没法直接飞到第100万条。但从产品角度看绝大多数场景下用户不会真的去翻第100万页与其为了这个低频需求付出巨大数据库代价不如接受游标分页。4.3 覆盖索引的额外加成查询列全进索引第三层优化是覆盖索引。原本查询要返回fields包括order_no、status。这些字段不在索引里每查一条记录就必须回表一次。虽然几百万行的表回表20次可以接受但如果你想做到极致可以把查询需要用到的字段都塞进联合索引里ALTER TABLE order_record ADD INDEX idx_uid_ct_fields (uid, create_time DESC, order_no, status);改造后MySQL在二级索引树里就能把id、order_no、status、create_time全拿到连回表都省了。EXPLAIN里会出现Using index意思是“这是一个覆盖索引扫描”。不过别高兴得太早覆盖索引不是越宽越好。每个索引都有存储成本索引列越多写入时要更新的索引数据就越大插入速度会受影响。我个人的习惯是覆盖列只加查询频率极高、数据量不大的字段如果某个字段是很长的VARCHAR或者业务上几乎不会出现在查询列表里那就不强行塞进去。三层优化落地后我又做了一轮压测。同样的查询改造前平均耗时约0.2秒改造后基本稳定在0.03秒左右。更重要的是翻页越深性能差距越夸张——旧方案翻到第100万条可能要两秒以上新方案翻到任意后面页面都能保持在几十毫秒级别。5. 常见问题排查清单与防御性建索引原则5.1 一张速查表索引问题快速定位与解法为了以后排查方便我整理了一张踩坑速查表基本覆盖了最常见的场景从表象直接定位到根因。慢查询特征EXPLAIN表现根因解决方案查询条件里对索引列用了函数或计算typeALLkeyNULL函数破坏了B树有序性改写为范围查询避免对列套函数字段是VARCHAR但传值带数字key可能为NULL或明显低效隐式类型转换导致索引失效SQL里传字符串字段类型保持统一两表关联查询慢驱动表出现全表扫描关联字段字符集或类型不一致统一表字符集为utf8mb4关联键类型一致查询用了LIKE %xxx%typeALL前缀通配符无法利用索引顺序改用覆盖索引或全文索引方案查询条件多个字段但各建单列索引可能看到index_merge或ALL单列索引组合使用效率低按查询模式设计联合索引区分度低的字段单独建了索引typeALL优化器估算回表成本过高低区分度字段并入联合索引分页很深但扫描量不大rows小但有filesort深分页导致大量无意义排序和回表改游标分页加覆盖索引明明有索引rows预估严重偏大rows远超实际统计信息过期定期ANALYZE TABLE必要时建直方图这张表我打印出来贴在工位上很久。每次遇到“索引建了还慢”的问题先对着表确认是哪一类再动手不要上来就瞎加索引。5.2 建索引前先问自己四个问题踩坑多了之后我现在建索引之前都会先过四道关卡。第一这个字段的区分度够不够取值太少就不值得单独建索引要考虑和其他查询字段合并成联合索引。第二索引能否减少回表如果每个查询都要回表拿到大量列那索引带来的收益会被回表的随机IO抵消。这时候就要考虑覆盖索引或者改写查询字段。第三这个索引能不能同时服务过滤和排序很多场景下ORDER BY字段如果能和WHERE条件字段放进同一个联合索引就能直接消掉filesort这是性价比非常高的一步。第四能不能用一个联合索引替代多个单列索引多个单列索引听着很多实际上可能互相打架执行计划选择哪个都不完美。联合索引一次解决多个查询模式才是良方。这四关都过了索引才值得建。我见过有人一张表建了二十几个索引不仅性能没提升写入速度反而变慢了。因为每次INSERT或者UPDATEMySQL都要同步维护每一个索引。索引是典型的空间换时间产品多一个索引就多一份写入负担必须克制。5.3 线上巡检与防御性运维建议最后聊一下运维层面的防御措施。即使当时排查完把问题解决了如果后续没有一套巡检机制类似问题很快会卷土重来。我的日常做法有四个。第一慢查询日志常态化开启收集周期内所有超过阈值1秒的SQL定期归档分析。第二每周检查一次sys库里的无用索引视图看有没有大量冗余索引需要清理SELECT * FROM sys.schema_unused_indexes;第三对数据量大、写入频繁的表定期执行ANALYZE TABLE让统计信息保持新鲜。第四索引变更不直接在生产环境一把梭。先在一套数据量接近生产的测试库上验证执行计划再在低峰期执行在线DDL。大表加索引本身就可能锁表或给主从复制带来延迟千万不能业务高峰时硬来。另外我建议把索引变更纳入代码评审流程。每次新功能涉及新查询先让开发者把SQL和EXPLAIN结果贴出来确认走了预期索引再合入。这不是流程繁琐是真的能提前拦住一大批从源头就能避免的性能问题。这些年折腾下来我的体会是MySQL的问题十有八九不是引擎不行而是人把索引用错了。EXPLAIN不是万能的但连EXPLAIN都不看就想优化SQL那基本就是靠玄学。每次写完SQL养成先看一眼执行计划的习惯比事后被线上报警急得满头大汗要舒服得多。