MySQL索引避坑指南：5个生产事故级血泪教训与优化方案

如果不是那天凌晨两点被值班电话吵醒我可能到现在还觉得“建了索引”和“用对索引”是一回事。我负责的核心流水表已经过了两亿行平时读写还算稳。但那天晚上一条极其简单的查询直接把数据库CPU打到接近100%连接数瞬间拉满。值班同学把SQL发到群里我用EXPLAIN一看就发现了一个让人非常憋屈的事实表上明明有索引可优化器就是不走。从那之后我陆续在生产环境又踩了四次类似的坑每次都能写出一篇复盘文档。这篇就把这5个MySQL索引相关的血泪教训一次性说透每个坑都附带上线五分钟就能用上的生产级解决方案希望你能绕开这些弯路。1. 第一个坑最左前缀原则的误用联合索引到底是怎么被“跳过”的1.1 一条“看起来没毛病”的SQL先还原当时的现场。有一张核心流水表order_record大约4000万行表上有一个联合索引定义大概长这样ALTER TABLE order_record ADD INDEX idx_uid_otype_ctime (user_id, order_type, create_time);当时业务方反馈一个列表页打开很慢我拿到的慢SQL简化后是这样的SELECT * FROM order_record WHERE order_type 2 AND create_time 2024-06-01 00:00:00 AND create_time 2024-07-01 00:00:00 ORDER BY create_time DESC LIMIT 50;第一眼看上去我的判断是“索引里面明明有 order_type也有 create_time肯定能走”。结果EXPLAIN一打脸rows 高达数百万type 还是可怕的ALL也就是说优化器最终选择了全表扫描。问题出在联合索引的列顺序上。索引idx_uid_otype_ctime是按(user_id, order_type, create_time)排序的最左侧优先匹配user_id。可这条查询里根本没有user_id等于直接从第二列开始用条件。B树的有序性是“从左到右”构建的没有最左前列的等值锚点优化器根本没法在索引树上做快速定位只能退化成全索引扫描甚至全表扫描。这就是典型的“数据列都在索引里但索引用不上”的场景。它比“没建索引”还隐蔽因为你会下意识认为索引已经覆盖了所有查询字段。提示判断联合索引是否命中先别管字段在不在索引里先看“最左前缀列是否在WHERE里以等值或范围条件出现”。没满足最左匹配后面所有列都白搭。1.2 如何重新设计列顺序让这个查询跑进30毫秒我把线上所有高频查询模板整理出来按条件类型分成三类等值条件WHERE user_id ?、WHERE order_type ?范围条件WHERE create_time ?、WHERE create_time ?排序条件ORDER BY create_time DESC联合索引设计有一个非常实用的顺序口诀等值列放最前范围列次之排序列再次之。等值列可以让优化器直接在索引树上精准定位到一段连续区间范围列利用B树有序性做区间扫描排序列则可以消除Using filesort让数据按索引顺序直接输出。于是针对这个高频查询我单独补了一个更适合业务场景的索引ALTER TABLE order_record ADD INDEX idx_otype_ctime (order_type, create_time);改完后EXPLAIN的结果变成type: rangerows 从数百万降到几千查询耗时从800毫秒左右降到20毫秒上下。这里还有一个细节容易忽略ORDER BY create_time DESC。索引(order_type, create_time)内部是正序存储的MySQL 可以反向扫描索引来实现 DESC 排序所以 Extra 里不再出现Using filesort。如果你建的索引列顺序是对的但Extra仍然有 filesort多半是排序列没进索引或者排序方向和索引前向匹配出了问题。1.3 复盘索引设计不能“猜”要按查询模板来这个坑我复盘时想明白了一个道理索引不是给“字段”建的而是给“查询模板”建的。你收藏了某个字段经常被查询不等于你得建一个包含这个字段的索引你要做的是把线上所有慢查询和核心查询收集起来提炼成模板看每个模板里条件的类型和顺序再决定联合索引怎么排列。一个比较实用的检查标准是上线前对每个核心查询模板跑EXPLAIN要求type至少达到ref或range如果出现ALL或index必须说明原因并给出优化方案。不满足这个标准就别让这段SQL上生产。我后来把这条规则写成了发布检查项堵住了很多潜在风险。2. 第二个坑在大表上直接加索引DDL锁带来的线上事故2.1 低峰期执行ALTE从库延迟还是把我打懵了第二次事故比第一次严重得多。当时一张7000多万行的订单流水表需要加一个联合索引我也知道大表DDL有风险特意选择了工作日的凌晨两点执行。命令很简单ALTER TABLE order_record ADD INDEX idx_otype_ctime (order_type, create_time);结果这条在测试环境跑得飞快的语句在7000万行的大表上整整跑了18分钟。InnoDB虽然对新增二级索引支持ALGORITHMINPLACE理论上可以允许并发DML但整个操作仍然要扫描全表数据、构建新的索引结构CPU和IO必然被大量占用操作结束前还需要短暂获取元数据锁来完成切换。更致命的是从库延迟。DDL在主库执行完接下来要把整个执行过程通过binlog重放到从库。由于从库本身还有大量读流量重放时要在从库也完成同样的索引构建IO一下子就顶不住了。我当时看到主从延迟从0.2秒一路飙到10分钟以上整个人是懵的。读流量因为有延迟保护开始自动切回主库主库的读写压力瞬间叠加又把连接数顶到接近上限。本来只想加个索引结果搞成了小范围故障。2.2 生产级方案用在线无锁表变更工具那次之后我再也没有在核心大表上直接执行ALTER TABLE ADD INDEX。如果要加索引默认优先用基于在线无锁变更思路的工具。这里我重点说两个gh-ost和pt-online-schema-change。gh-ost的思路是创建一个影子表用 binlog 持续同步原表变化等影子表数据追平后通过一次轻量切换完成表替换。整个过程对原表不持有长时间锁对主从延迟也有非常严格的控制。我常用的命令大致如下gh-ost \ --userops_user --passwordchange_me \ --host10.0.0.10 --port3306 \ --databasepay --tableorder_record \ --alterADD INDEX idx_otype_ctime(order_type, create_time) \ --chunk-size1000 \ --max-lag-millis1500 \ --execute几个参数说明--chunk-size1000每次拷贝1000行避免单次IO过大。--max-lag-millis1500从库延迟超过1500毫秒就自动暂停拷贝直到延迟回落再继续。这套工具能把大表加索引从“一次性全部重建”变成“可控速率的持续拷贝”从库延迟被牢牢按在安全阈值内。因为加了--max-lag-millis工具本质上是“看从库脸色”工作生产环境非常稳。pt-online-schema-change则是另一种思路它依赖触发器和外键约束做数据同步虽然也有效但对原表的写并发有一定影响。我更倾向在触发条件不合适或团队更熟悉Percona工具链时选用它。pt-online-schema-change \ --alter ADD INDEX idx_otype_ctime(order_type, create_time) \ Dpay,torder_record \ --max-load Threads_running30 \ --chunk-size10002.3 没有工具时的兜底操作如果你所在环境不允许引入外部工具又必须对大表加索引我的建议顺序是先确认MySQL版本支持ALGORITHMINPLACE不要选择COPY方式。评估业务可接受的从库延迟窗口选一个真正的业务最低谷期。准备好回滚预案如果主从延迟超过阈值允许手动终止DDL。尽量把从库的读流量提前切走一部分给从库留出IO余量。另外一个容易被忽略的问题是加索引的DDL在某些条件下仍需要短暂持有元数据锁。如果此时线上有长事务没提交DDL会卡在等锁状态后面所有新请求都会堆在等待队列里。所以执行前务必先查一遍performance_schema里是否有长时间未提交的事务避免雪上加霜。我后来养成了一个习惯核心大表的所有结构变更先写成变更单标清楚工具、速率、阈值、回滚方案再由团队复核一遍才执行。这个流程看起来“重”但它真的能拦下大部分故障。3. 第三个坑为读性能盲目加索引写入放大带来的连锁雪崩3.1 加了9个索引写入RT从15毫秒恶化到58毫秒第三个坑发生在一张写多读少的库存流水表inventory_log上。这张表高峰每秒大约4000次写入业务方为了各种实时查询方便陆陆续续在单列上加了差不多9个索引订单号、状态、类型、商品ID、时间、操作人等等恨不得每个字段都建一个。结果某天高峰主库写入平均RT从15毫秒涨到58毫秒主从延迟从0.2秒涨到几十秒部分写入还开始出现锁等待超时。原因其实不复杂。InnoDB每次插入一行不但要写聚簇索引还要同步维护该表上的每一个二级索引。插入一行数据逻辑上等于往9个不同的B树里各写一条记录删除一行要从9棵树里各删除一遍。索引是数据的“冗余副本”副本越多写放大越明显。尤其是当表数据量超过buffer pool容量后二级索引的叶子节点会大量走随机IO写入性能会断崖式下跌。以为“每个索引都能加速查询”结果全是拖累写入的负担。注意索引不是免费的。每个索引都要占用磁盘空间每次INSERT/UPDATE/DELETE都要付出维护成本。加索引之前先问一句这个索引真的有查询在用它吗3.2 用Performance Schema找出“吃空饷”的索引光靠猜没用我直接用performance_schema里现成的索引统计表来排查SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA inventory AND OBJECT_NAME inventory_log ORDER BY COUNT_READ ASC;这个查询能告诉我每个索引被读取了多少次、被写入维护了多少次。当时结果非常直观有两三个索引COUNT_READ 0也就是上线以来压根儿没被任何查询当作定位键用过但COUNT_WRITE却非常大因为每次DML都在同步维护它们。我按照这个数据把零读取的索引一个个DROP INDEX掉再把几个低区分度单列索引合并成一个联合索引最终索引数量从9个降到5个。之后同峰值写入RT回到19毫秒左右主从延迟也恢复了正常。这里要提醒一下performance_schema的统计是累计值如果库里长期跑着全表扫描读取计数可能不准确。建议至少观察7天再结合慢查询日志交叉验证避免误删真正在用的索引。3.3 写密集表的索引数量我从一个原则开始的关于一张表到底建多少个索引并没有绝对的数字但有一个经验原则写入越密集索引越要克制。我会把表分成三类读多写少可以适当多建索引哪怕5到8个只要查询收益大于写入成本。读写均衡控制在4到6个尽量用联合索引覆盖多个查询模板。写多读少优先保证写入稳定索引能少就少3个以内最好是核心查询的复合索引。另外我还会周期性检查“索引前缀重复”的问题。比如已经有(order_id, type)又建了一个(order_id, status)两个索引的重复度很高。如果两者都能被合并成(order_id, type, status)且不影响查询命中的话合并往往更划算。这个坑给我的最大教训是建索引要看投入产出比不能只看“查询有没有变快”还要看“写入付出了多少代价”。4. 第四个坑隐式类型转换和函数操作让索引悄悄失效4.1 mobile字段查全表只因为少写了一对引号这个坑特别容易踩而且特别坑后端同学。某张用户表user_info上mobile字段是varchar(20)索引idx_mobile也是建好的。某天业务接口突然慢查询我把SQL拿出来一看SELECT * FROM user_info WHERE mobile 13800001111;注意mobile是字符串类型但这里等号右边写成了整数。MySQL在处理“字符串列和整数比较”时会把字符串列隐式转换成数值再比较。对列做隐式类型转换等同于给索引列套了一层转换函数索引自然就失效了。EXPLAIN出来是type: ALLrows直接是全表。我一度以为是索引坏了后来改成带引号的写法SELECT * FROM user_info WHERE mobile 13800001111;EXPLAIN立刻变成type: ref查询时间从秒级回到毫秒级。这类问题在ORM场景尤其常见。比如某些框架把字段映射成了数值类型参数拼接时不带引号或者中间层做参数处理时把字符串转成了数字。排查慢SQL时只要发现对比列是字符串、对比值是数字第一反应就应该查一下类型是否一致。经验凡是看到索引列和字段类型对不上号的比较先别怀疑优化器先怀疑类型转换。字符集不同、排序规则不同也都会导致类似问题。4.2DATE(create_time) 2024-01-01是索引杀手另一个高频写法是日期函数包裹索引列。比如很多人喜欢这么查当天数据SELECT * FROM order_record WHERE DATE(create_time) 2024-01-01;看着很自然但优化器没法直接利用idx_otype_ctime里的create_time因为B树里存的是完整的datetime值而查询条件要求先对每个索引值做一次DATE()函数运算才能跟常量比较。索引树的有序性按原始值排列被函数加工后顺序就乱了索引自然用不上。正确的写法是用范围条件代替函数SELECT * FROM order_record WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;这样优化器就可以在索引树上做标准的范围扫描。同样的套路还适用于按小时、按月统计有人特别喜欢写MONTH(create_time) 6其实都可以改成区间的形式。如果业务上确实无法避免按自然日查询而且时间范围很频繁可以考虑在表里冗余一个create_day字段并为它建索引查询条件直接写create_day 2024-01-01。虽然多一个字段需要维护但换来的是稳定走索引。是否值得要看查询频度。4.3 表关联时字符集不一致索引也会失效还有一种情况比较隐蔽两张表做JOIN关联字段都有索引但执行计划却不好好走。原因往往是两个字段的字符集或排序规则不同。比如一个表是utf8mb4_general_ci另一个是utf8mb4_unicode_ci在关联比较时MySQL需要对其中一侧做隐式转换导致关联字段上的索引无法被充分利用。遇到大数据关联查询变慢时别只顾着看单个表的索引还要检查SHOW FULL COLUMNS里的Collation。统一字符集和排序规则是避免这类隐式转换的根本手段。这个问题在分库分表的老系统里特别常见表是不同时期建的字符集五花八门一联合就出事。这个坑给我的直接教训是凡是在SQL里对索引列做了“加工”——不管是函数、计算还是类型转换都要在心里拉响警报。索引是有序结构任何加工都可能破坏有序性进而让索引失效。5. 第五个坑低区分度索引被优化器冷落强扭的瓜也不甜5.1 status1明明建了索引优化器为什么不用有一张优惠券批次表coupon_batch总量2000万行status字段只有4种取值其中status 1的数据大约占45%。开发同学在status上建了单列索引idx_status查询需求是SELECT * FROM coupon_batch WHERE status 1 AND valid_time 2024-06-01 00:00:00 ORDER BY id DESC LIMIT 50;EXPLAIN一出type: ALLidx_status完全没进执行计划。开发很委屈明明建了索引为什么不用优化器不笨。status 1这条件能让45%的行被命中用索引的话每找到一行满足条件的数据都要回表一次去聚簇索引里拿其他字段。回表是随机IO命中率又高磁盘上跳来跳去非常慢。反过来直接全表扫描跟着聚簇索引顺序读IO成本反而更低。所以优化器宁可全表扫也不愿意走这个低区分度索引。只有当选择率低到一个阈值我个人的经验值大约10%以内索引回表成本才会显著低于全表扫描优化器才会主动选它。像status这种低区分度字段建单列索引很多时候就是白花钱。5.2 不要用FORCE INDEX硬着头皮走索引有些人看到这儿会说那就用FORCE INDEX强制它走呗。确实有些场景强制之后短期变快了但我不建议把FORCE INDEX当成常规方案。因为优化器是根据统计信息和成本模型做选择的。你今天FORCE INDEX成功明天数据分布一变比如status 1的占比从5%涨到40%强制索引就变成逼着数据库做大量回表性能反而更差。而且FORCE INDEX是写在SQL里的业务代码越多牵一发而动全身后面想调优都难。正确做法是提升查询本身的选择性比如说把高区分度的列加进联合索引让索引真正能筛选掉大部分数据。如果业务里经常用status valid_time两个条件可以试试(status, valid_time)联合索引看valid_time能不能进一步压缩扫描范围。如果查询只需要少数几个字段考虑覆盖索引让查询完全不需要回表。如果status1的数据量实在太大任何普通索引都救不了那不如把热数据放到缓存层让数据库承受的查询压力降下来。5.3 统计信息过期也会让优化器“犯糊涂”还有一种情况是索引本身选择率并不差但优化器拿到的统计信息太久没更新导致成本评估失真。表现就是“昨天还走索引今天突然全表扫”。MySQL的优化器依赖statistics来估计选择率和行数。如果表频繁增删改统计信息滞后评估就会失真。解决办法也很简单对核心大表定期执行ANALYZE TABLE让它重新采样生成最新统计信息。ANALYZE TABLE coupon_batch;另外也要学会用SHOW INDEX FROM coupon_batch查看索引的Cardinality列。Cardinality是索引的区分度估算值如果它远小于实际行数说明这个索引的选择性可能很差。结合它来评估是否该保留索引比拍脑袋靠谱得多。6. 生产级索引管理我总结的五个操作闭环6.1 新索引上线前先做“三道检查”在经历这些事故之后我把索引变更当成一次正式发布来管控没有捷径可走。新索引上线前我要求必须过三道检查核心查询跑一遍EXPLAIN确认type至少是ref或range不允许出现ALL或index。在预发环境用生产数据快照验证不能只在几十万行的小表测试。确认执行方式核心大表优先用在线无锁工具禁止裸跑ALTER TABLE。这三条看起来都是常规检查但真正坚持下来能挡掉一大半问题。6.2 索引的生命周期管理要“数据驱动”以前我们上线索引就完事了没人关心它后面是否真的被用到。现在我的做法是把索引当成需要持续健康检查的对象每周用performance_schema.table_io_waits_summary_by_index_usage扫描一次核心表索引使用情况。对COUNT_READ 0的索引打标记连续观察两个周期仍为零提交删除评审。每次大版本业务上线重新梳理核心查询模板检查有没有新索引需求或旧索引冗余。大表在大量增删后及时ANALYZE TABLE避免统计信息过期影响优化器判断。这套流程让索引的增删从“经验判断”变成了“数据判断”。哪些索引该留哪些该删都有数字作为依据。6.3 索引不是越多越好而是越准越好最后分享一个我自己形成的认知索引优化的本质不是“加索引”而是“让优化器在最小代价下找到正确数据”。对读多写少的表索引可以慷慨一点对写多读少的表就必须精打细算。每次加索引前想想它要为多少次DML买单再决定值不值。还有一个务实小技巧线上执行任何索引变更前先把变更语句保存好同时把回滚语句也写进变更单。万一变更后出现性能回退回滚语句就在手边不用临时查语法、临时想方案。别小看这个习惯真正出故障的时候多花10秒准备就能少熬半个小时的夜。