很多人看我写MySQL优化文章总觉得我好像天生就会调索引。其实不是我最早接手生产库的时候光一个订单查询就把我搞到凌晨三点——加了索引慢查询还是慢。后来查EXPLAIN才发现那条索引被当成“废铁”了压根没走。更别提后来在另一个项目里因为一条UPDATE语句的二级索引锁交叉两个核心表直接死锁业务告警足足响了四十分钟。今天把这些年踩过的坑整理成5个最典型的每一个都附上生产级解决方案你如果能避开这几条绝对能少走半年弯路。这话不敢说满但至少覆盖了索引失效、冗余索引、排序、锁竞争和统计信息这几类最高频的问题。新手最需要看前两个坑老手建议重点看第五个锁相关的案例那才是真正埋在生产库里的雷。1. 索引失效的隐形杀手隐式类型转换与函数运算1.1 一条VARCHAR字段上的“数字条件”是怎么毁掉索引的我印象最深的一个案例是某用户表里有一个手机号字段建表时定义成VARCHAR(20)也加了普通索引。业务方发来一条慢查询SELECT * FROM users WHERE user_phone 13800138000;单独看这条SQL很多人觉得没毛病。但问题恰恰出在条件右边的“13800138000”是数字类型而字段user_phone是字符串类型。MySQL在比较时会把user_phone先隐式转换成数字再和右边的数字比较。一旦对索引列做了隐式转换索引基本就废了。我当时用EXPLAIN看了一下执行计划type从预期中的ref直接掉到ALL全表扫描十万行。你想象一下如果这个表有一千万行那这条看起来很正常的等值查询带来的压力就是毁灭性的。解决办法最干净的一种是在应用层把参数统一转成字符串再传进去SELECT * FROM users WHERE user_phone 13800138000;只要类型匹配索引就能正常走。除此之外生产环境还有一种常用方案就是针对这种“无论如何都可能传错类型”的场景建一个生成列来兜底ALTER TABLE users ADD COLUMN user_phone_num BIGINT UNSIGNED GENERATED ALWAYS AS (CAST(user_phone AS UNSIGNED)) STORED, ADD KEY idx_phone_num (user_phone_num);然后查询写成WHERE user_phone_num 13800138000既能匹配数字类型参数又能用上索引。这个方案的原理其实很朴素把“隐式转换”变成“显式存储”提前算好、存好查询时直接走索引。1.2 在索引列上做函数运算神仙也救不了相比隐式类型转换函数运算更常见。比如统计某天注册用户数SELECT COUNT(*) FROM users WHERE DATE(created_at) 2024-06-01;created_at上就算有索引DATE()函数把每一行的值都“加工”了一遍MySQL只能老老实实全表扫完再去过滤。这类问题的修复套路也比较固定把函数从索引列上挪走改用范围条件。SELECT COUNT(*) FROM users WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;这种写法有几个好处一是索引列保持原样能正常用于搜索二是范围条件本身是索引友好的三是性能上限比函数写法高出几个数量级。如果你的表是MySQL 8.0.13以上官方也支持函数索引但底层实现其实还是隐藏的生成列本质思路和我上面说的一致只是不用自己手动加列了。注意不只是DATE()包括YEAR()、MONTH()、SUBSTRING()甚至是简单的column 1 9这种算术运算都可能让索引失效。原则只有一个查询条件里的索引列别套任何函数或表达式。2. 索引建得多不如建得准冗余索引的代价远超想象2.1 复合索引下藏着的大量“重复建设”很多团队建索引的思路是“业务提什么就加什么”结果索引越建越多却不知道建的是冗余索引。举个例子你为了支持user_phone查询建了idx_phone后来又因为user_phone status的组合查询建了idx_phone_status。KEY idx_phone (user_phone), KEY idx_phone_status (user_phone, status)这时候idx_phone就是冗余的因为idx_phone_status的左侧前缀已经覆盖了user_phone单独查询的场景。冗余索引最坑的地方不在于它出现在表里而在于它带来的副作用每次INSERT、UPDATE、DELETE都要额外维护一份索引数据占空间是小事写放大带来的性能跌幅才是大事。我在生产环境清理过一次类似的重复索引表数据量大约三千万删除三个冗余索引后写入耗时直接下降了约15%。对高频写入的核心表来说这已经不是“优化”层面的收益了而是“减负”级别的改进。2.2 用系统库找出冗余索引别靠肉眼排查MySQL的sys库已经提供了现成的诊断视图不需要自己写复杂脚本SELECT * FROM sys.schema_redundant_indexes;它会直接列出哪些索引是冗余的、冗余在哪张表哪个列上还会告诉你重复的部分占用多大空间。这个视图的原理是解析所有索引的列前缀结构凡是能由另一个复合索引完全覆盖的单列索引或前缀索引都会被识别出来。清理的时候别急着一次全删生产库上的任何DDL都建议分批做。我的习惯是先挑业务低峰期在测试环境跑一遍SELECT统计确认删除影响面最小然后分两到三次删除每次删除后观察慢查询数量和锁等待情况。如果后续发现某条查询因为删除索引而退化成全表扫描再针对具体SQL重新评估加一个更轻量的解决方案。还有一个更隐蔽的情况两个复合索引前缀相同但顺序不同比如(a,b)和(b,a)它们不是冗余的因为查询条件里b单独出现的场景可以走第二个索引a,b同时出现的场景走第一个更合适。这类“看起来像重复但不是重复”的设计要结合真实查询模式来判断不能机械地套用“前缀覆盖”规则去删。3. ORDER BY引发的性能灾难排序设计不只是加个索引那么简单3.1 为什么带排序的查询加了索引还是filesort有一段时间我们订单列表页特别慢核心SQL长这样SELECT order_id, user_id, amount, status FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 20;当时已经在status上建了索引理论上过滤到2000行再进行排序也不算太傻。但问题是在status 1的等值条件下优化器可以先用status索引快速定位得到的结果集中包含大量created_at无序的数据于是不得不做一次内部排序即典型的filesort。数据量小的时候感觉不明显一旦status 1的数据膨胀到几十万行排序就是实打实的CPU和内存消耗。这个场景更合理的方案是建复合索引让过滤和排序共用同一条索引路径ALTER TABLE orders ADD KEY idx_status_created (status, created_at);查询执行时MySQL先定位到status 1的第一行然后沿着created_at的有序性直接向下扫描20行全程不需要排序。这也是复合索引“一石二鸟”的核心思想。3.2 深分页LIMIT 100000, 20才是真正的无底洞排序问题还没完另一个常见坑是深分页。同样的ORDER BY created_at DESC LIMIT 100000, 20即使索引完美MySQL也得先按索引顺序扫描到第100020行再把前100000行扔掉。这么做的时间复杂度随页码增长而增长越翻越慢。生产上我常用的两种方案一种是“延迟关联”SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;内层只查主键id不取整行数据走的还是索引扫描成本低很多外层再根据20个id回表取完整数据性能提升非常明显。另一种方案是适合“滚动加载”场景的游标分页上一页最后一条记录的created_at作为下一页的起点SELECT * FROM orders WHERE status 1 AND created_at 2024-06-01 12:00:00 ORDER BY created_at DESC LIMIT 20;这种方式完全规避了偏移量计算无论翻到多深扫描行数都固定在一个小范围内。需要注意的是如果排序字段存在重复值游标分页会漏数据所以建议排序字段带上主键作为二级排序方式比如ORDER BY created_at DESC, id DESC同时游标条件也要带上id。4. 二级索引更新时的锁交叉一个容易被忽视的生产级死锁现场4.1 更新一条记录InnoDB到底加了哪些锁这个坑我最想详细说因为它不像前面的问题那样看EXPLAIN就能发现。有一次核心账户表死锁我SHOW ENGINE INNODB STATUS看到两个事务在相互等待一个是二级索引项上的锁一个是主键记录上的锁互相咬死。背后的机制是这样的InnoDB更习惯用主键定位记录。当你用二级索引定位并更新某条记录时InnoDB会先对二级索引项加锁然后再回表对主键索引对应的记录加锁。对于一条记录来说这个“先锁二级索引再锁主键”的过程是固定顺序但如果两个事务分别通过不同的二级索引访问同一条主键记录锁的获取顺序就可能出现交叉假设表里有索引idx_status和idx_phone事务A执行UPDATE users SET ... WHERE status 1通过idx_status定位记录先拿idx_status上的锁再拿主键锁。事务B执行UPDATE users SET ... WHERE user_phone 13800138000通过idx_phone定位同一行记录先拿idx_phone锁再去抢同一行主键锁。如果A已经拿着主键锁B等着拿主键锁同时A的下一步又想拿idx_phone上的锁而这个锁恰好被B持有就形成了交叉等待死锁产生。4.2 生产级解决思路统一访问路径消除锁顺序冲突这类死锁最直接的办法是让所有更新操作尽量通过同一条索引路径去定位记录。如果一个事务用status更新另一个事务用phone更新两者握手时风险就高。我在业务侧做的第一件事就是统一写路径核心更新一律用主键id操作而不是让用户通过各种二级索引条件直接改行数据。比如业务流程先SELECT id FROM users WHERE user_phone ...拿到主键后再执行UPDATE users SET ... WHERE id 12345;这样所有写操作都走主键锁顺序统一交叉现场自然消失。主键定位还有一个额外好处主键索引是聚簇索引不需要回表锁的持有时间更短。另外MySQL 8.0里可以通过打开死锁日志来捕捉这类问题的详细现场SET GLOBAL innodb_print_all_deadlocks ON;开启后每次死锁都会写入错误日志包含事务执行的SQL、持有锁和等待锁的具体索引项。定位到是哪些索引路径互相交叉后下一步就可以从索引设计上做文章减少同一条记录上的多路二级索引访问或者把业务里真正必须走二级索引的写操作梳理成一批固定模板。4.3 隔离级别与锁类型也要纳入考虑还有一个容易踩的细节在REPEATABLE READ隔离级别下InnoDB为了处理幻读会在范围查询时加gap lock或next-key lock。这意味着锁的范围不只是满足条件的行还可能是索引段区间。两级索引叠加区间锁死锁概率更高。如果业务允许把隔离级别调整成READ COMMITTED能显著减少间隙锁带来的额外锁定范围从而降低锁竞争和死锁概率。当然这不是拍脑袋就能改的决定需要和业务团队确认读取场景是否接受“不可重复读”带来的影响。至少在做索引设计时要意识到大概率的锁竞争不只是“锁了哪些行”更是“锁了哪个索引区间”。5. 统计信息“过期”导致优化器选错索引性能跳水的隐形原因5.1 同一个SQL测试环境秒回生产环境卡死这是很典型的一个坑SQL完全一样索引完全一样测试环境执行计划用的是正解生产环境却像一个“偏执狂”死活不走该走的索引。有次我排查了半天最后发现是表上的统计信息没有及时更新。InnoDB的优化器在决定走哪个索引时依赖的是表的统计信息包括行数、索引基数、采样页分布等。生产环境的数据经过高频增删改统计信息很容易变得不精确。优化器一看统计信息里某个索引的“区分度”很低就选了全表扫描或者换了一条次要索引性能自然就崩了。解决办法首先是手动触发一次统计信息更新ANALYZE TABLE orders;这是最轻量的操作不会重建表也不会长时间锁表。对于一般场景这条SQL跑完执行计划往往就能恢复正常。但要注意它不是根治方案数据还在持续变化统计信息还会再次失真。5.2 从“救火”到“防火”统计信息维护策略更稳定的做法是在业务低峰期设置定期维护任务比如每周对核心大表执行一次ANALYZE TABLE。MySQL本身有innodb_stats_auto_recalc参数控制自动重算默认开启但它的触发逻辑是针对单表超过约10%数据变更时才会重算。对于部分高频更新但整体体量又很大的表来说10%的阈值不容易触发需要主动兜底。还可以调大统计信息采样页数让优化器拿到更准确的分桶数据SET GLOBAL innodb_stats_persistent_sample_pages 32;这个参数决定了ANALYZE TABLE时扫描多少页来做基数估计设置偏小会让统计结果出现偏差。一般32到64之间的值在大多数场景下是合理的太大了会增加统计分析和DDL的耗时没必要盲目提高。对于特别复杂的查询如果临时无法优化统计信息FORCE INDEX可以作为应急手段SELECT * FROM orders FORCE INDEX (idx_status_created) WHERE status 1 AND created_at 2024-06-01 ORDER BY created_at DESC LIMIT 20;但我不建议把FORCE INDEX当成长期方案因为它会冻结执行计划一旦后续数据分布变化强制指定索引可能变成新的瓶颈。它更合理的定位是“救火工具”真正的长期方案还是把统计信息维护和SQL写法都规范化。5.3 一张排查速查表解决80%的索引问题这些年我把踩过的坑整理成了一张自查清单分享出来排查线上慢查询时顺序对照就行问题分类典型表现快速检查方式常用解决方式隐式类型转换等值查询却走了全表扫描EXPLAIN看type ALLkey为空参数类型和字段类型统一函数/表达式运算索引列被包住EXPLAIN看Extra无Using index condition改写为范围条件或生成列冗余索引写入慢、占空间sys.schema_redundant_indexes分批删除冗余索引排序filesort查询中带ORDER BY且速度快不了EXPLAIN看Extra有Using filesort建(过滤字段,排序字段)复合索引深分页偏移翻页越深越慢慢日志中SQL固定且耗时递增延迟关联或游标分页锁交叉死锁频繁出现死锁报错SHOW ENGINE INNODB STATUS统一主键更新路径减少间隙锁统计信息过期同样SQL执行计划不同对比慢日志与实际行数ANALYZE TABLE 定期维护任务6. 线上索引变更的完整落地步骤从设计到验证一次做对6.1 上线前先做“索引健康检查”变更索引之前先跑一轮当前库的健康检查是不可省略的准备工作。我自己的习惯是执行下面三个SQL-- 查看所有索引大小与占用 SELECT TABLE_NAME, INDEX_NAME, stat_value * innodb_page_size / 1024 / 1024 AS index_size_mb FROM mysql.innodb_index_stats WHERE database_name your_db AND stat_name size; -- 查看各索引的使用情况8.0用performance_schema SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db AND INDEX_NAME IS NOT NULL ORDER BY COUNT_STAR ASC;先看清哪些索引几乎没被使用过哪些索引虽然使用但效果不佳再结合业务查询模式来判断删除或新增。不要只看慢日志索引使用频率数据会更客观。6.2 变更流程中的三个关键节点索引变更虽然不像改表结构那么重但在生产库上也必须按流程走。第一步在测试环境用完整数据量的副本做一次EXPLAIN对比记录新增或删除索引前后的执行计划变化。第二步在主库低峰期执行DDL建议使用pt-online-schema-change或者MySQL 8.0原生的ALGORITHMINPLACE选项避免长时间阻塞读写ALTER TABLE orders ADD KEY idx_status_created (status, created_at), ALGORITHMINPLACE, LOCKNONE;第三步变更完成后立刻开启一段观察窗口监控慢查询、锁等待、CPU/IO指标至少要覆盖一个完整的业务高峰周期。如果发现异常第一时间回滚到备份版本不要在现场“调试”太久。6.3 从执行计划到真实扫描行数的双重验证很多新人只看EXPLAIN里的key字段看到索引名就放心了。这是最大的误区。EXPLAIN给出的rows是估算值不一定代表真实扫描量。更靠谱的方式是在MySQL 8.0里用EXPLAIN ANALYZE直接看实际执行路径和时间EXPLAIN ANALYZE SELECT order_id, user_id, amount, status FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 20;它会给出每个阶段的实际行数、实际耗时和循环次数能非常直观地发现“走了索引但还是扫了太多行”的情况。我的经验是任何索引优化在落地前都要用EXPLAIN ANALYZE确认最终扫描行数和预期一致再谈上线。7. 最后想说的几句话把这几套方案完整理完之后我自己最大的一个体会是索引优化的关键不在于“加了多少索引”而在于“每一步执行都符合预期”。你不需要把MySQL的锁机制背得滚瓜烂熟但至少要能在遇到问题时用对排查工具、看懂执行计划、理解锁等待的方向。另一个实际经验是索引变更最能暴露压测做不到的意外每个方案上线前我都建议准备一份“回滚预案”——删掉的索引先备份创建语句切换的查询先保留旧版本统计信息改动前记录默认参数。宁可准备用不上也别在故障发生时干瞪眼。如果非要我提炼一条最管用的建议做任何MySQL查询优化第一件事永远是EXPLAIN第二件事是确认实际扫描行数第三件事才是讨论索引怎么改。把顺序反了很多功夫都会白费。这套方法论经过多次实战验证希望你也能少踩几个坑。