做线上MySQL排查这些年跟索引打交道是最多也最扎心的一件事。前阵子我在某项目的订单库里连续处理了五起教科书式的索引事故——有的一天慢查询上千条有的直接让写接口锁等待超时还有一次凌晨两点被死锁告警叫醒。把这几段血泪经验整理出来尤其是每个坑背后的隐式类型转换、排序分页原理、锁顺序和索引冗余问题以及我最终用到的生产级解决思路希望能帮同样被MySQL索引折磨过的人少走几步弯路。每一条都不是理论推演全是线上真实翻车的复盘该给的排查命令和解决方案也都附在后面。1. 第一个坑等值查询被全表扫描罪魁是三年前随手写下的varchar1.1 线上事故现场一个“不该慢”的查询先说最典型的一个。某天订单详情接口的P99延迟从80ms直接飙到3.8秒监控大屏上慢查询数量像心跳图一样往上冲。我抓出耗时最高的SQL一看写法平平无奇SELECT order_id, user_id, status, amount FROM t_order WHERE phone 13812345678 ORDER BY create_time DESC LIMIT 20;phone字段上明明有索引而且主键、订单号、手机号、创建时间都建了索引怎么还会慢更诡异的是这个SQL在测试环境怎么执行都是毫秒级一上生产就全表扫描。当时我第一反应是“索引没生效统计信息坏了还是索引被删了”1.2 EXPLAIN拆解typeALL和keyNULL背后的隐式类型转换把SQL拉到生产从库上跑一次EXPLAIN结果让我愣了一下idselect_typetabletypekeykey_lenrowsExtra1SIMPLEt_orderALLNULLNULL1784321Using where; Using filesorttypeALLkeyNULLrows178万Extra里还有Using filesort。这基本宣告了这条查询在做全表扫描加文件排序不慢才怪。然后我做了几个对照实验。首先用字符串形式传参再跑EXPLAINEXPLAIN SELECT order_id, user_id, status, amount FROM t_order WHERE phone 13812345678 ORDER BY create_time DESC LIMIT 20;结果完全变了idselect_typetabletypekeykey_lenrowsExtra1SIMPLEt_orderrefidx_phone631Using index conditiontype从ALL变成refrows从178万降到1。问题就出在传参类型上接口层把手机号当成了数字传给SQL而表结构里phone是varchar(20)。MySQL优化器在比较不同类型时会先把字符串列的字段值隐式转换成数字再跟传入的数值比较。一旦对索引列做了函数/类型转换索引就失效了优化器只能退化成全表扫。1.3 为什么优化器宁可全表扫也不走索引很多人不理解隐式类型转换为什么能导致索引失效其实优化器在评估执行计划时看到的是CAST(phone AS SIGNED) 13812345678这种形式。索引列被包了一层函数/转换操作B树上的有序排列就被打破了——索引里面存的是原始字符串顺序不是数字顺序优化器无法利用它快速定位只能逐行扫描把每一行phone都转成数字再去比较。更隐蔽的一点是如果phone列上有大量非数字开头的字符串比如带“待复核”一类的备注转换过程中还会产生隐式转换报错或者NULL值参与过滤的额外开销。我当时实测过这类查询扫描178万行大约耗时2.1秒而正常走索引只需要2ms性能差距三个数量级都不止。1.4 生产级解决方案与应用层规范这个坑的修复其实不复杂但牵扯到代码和表结构两层应用层统一类型DAO层所有查询手机号、订单号这类业务字段时强制使用字符串类型拼接参数禁止JSON里把“手机号”解析成long再传SQL。表结构兜底把phone列统一为VARCHAR(20)同时写死字段类型规范不允许出现“主键是bigint但外键字段是varchar”的混搭设计。必要时对症改造如果历史遗留代码实在改不动可以在MySQL 8.0.13及以上版本使用函数索引CREATE INDEX idx_phone_cast ON t_order ((CAST(phone AS UNSIGNED)))但这只能救急不能作为长期方案因为函数索引在写入时也要额外维护且优化器是否选择它需要单独用EXPLAIN确认。提示排查这类问题有一个快筛动作直接看EXPLAIN的key列是否为NULL再看SQL里索引列是否被函数、运算、隐式类型转换包了一层。三条占全了八成就是同一个坑。2. 第二个坑深分页越翻越慢ORDER BY LIMIT让接口直接超时2.1 现象后台列表从第100页开始卡死第二个坑是后台管理系统的分页查询这个坑几乎每个做业务系统的人都会遇到。某次运营反馈订单管理列表从第100页开始“转圈”有一次页面直接504。后来一查慢查询卡住的全是这类SQLSELECT order_id, user_id, order_no, status, amount, create_time, remark FROM t_order ORDER BY create_time DESC LIMIT 100000, 20当时我第一反应是“给create_time加个索引”但仔细一看表上已经有idx_create_time了执行计划也确实走了这个索引可还是慢。2.2 原理offset不是跳过而是“扫描并丢弃”MySQL的LIMIT语法看起来像“跳过前面100000条取20条”实际执行方式是从B树索引上从头开始扫描把前100000条记录一条一条读出来全部丢弃再取接下来的20条返回。这意味着offset越大无效扫描越多。当时这条SQL的rows是179万Extra里还有一个看似不起眼的Using index condition但实际访问了约10万行索引项才取出20条结果。更要命的是ORDER BY create_time DESC虽然让记录按索引顺序返回但如果SELECT里带了大量非索引字段比如remark每个符合条件的行还要回表去主键索引取完整行数据扫描量就成倍放大。2.3 排查过程Using filesort与查询剖析第一次看EXPLAIN的时候我注意到两种极端情况如果只按create_time排序且查询字段都在索引里EXPLAIN显示Using index那是理想状态。如果排序字段没有索引或者索引顺序和排序方向不一致Extra里会出现Using filesort这是SQL慢的另一个大信号。为了确认时间花在哪我用SET profiling 1之后跑了一遍深分页SQL再查SHOW PROFILES和SHOW PROFILE FOR QUERY N结果几乎90%的时间都耗在Sorting result和Sending data上。也就是说真实瓶颈是“扫描并丢弃offset行”这个过程就算没有filesortoffset深分页本身也是灾难。2.4 生产级解决方案游标分页与延迟关联这个坑的解决方案我后来直接写进了团队的代码规范场景一列表页连续翻页时改成游标分页也叫keyset分页只保留最后一页的锚点值SELECT order_id, user_id, order_no, status, amount, create_time, remark FROM t_order WHERE create_time 2025-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20应用层记住上一次返回的最小create_time和订单ID下次查询把它作为过滤条件。这样每一页都只扫描目标范围内的20条记录不管翻到多深性能稳定。场景二业务无法避免随机跳页时至少要用“延迟关联”deferred join先只查主键再回原表捞完整数据减少回表次数SELECT t.* FROM ( SELECT id FROM t_order ORDER BY create_time DESC LIMIT 100000, 20 ) d JOIN t_order t ON t.id d.id ORDER BY t.create_time DESC场景三如果必须用传统分页可以给前端一个上限比如最多翻到第200页超出后强制走筛选条件。亲测在业务上这个限制通常是可以被接受的。关于分页还有个隐藏雷点如果需求里有LIMIT 1000000, 20这种写法无论怎么优化都救不了。生产环境要加慢查询阈值并对这类SQL做拦截LIMIT的offset值超过一定量级就报警。3. 第三个坑更新同一行也能死锁二级索引回表锁顺序看懂后头皮发麻3.1 事故现场死锁报告与凌晨2点的告警第三个坑最折磨人。某天凌晨两点某任务调度平台连续抛出死锁告警错误的概要信息是Deadlock found when trying to get lock; try restarting transaction LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)死锁的两条SQL看起来都人畜无害一个是根据order_no更新状态UPDATE t_order SET status 1 WHERE order_no SO202501150001;另一个是根据user_id更新状态UPDATE t_order SET status 2 WHERE user_id 10086;两个SQL更新的是同一行记录但由于走了不同的二级索引锁顺序完全相反直接撞成了环。3.2 锁机制拆解二级索引更新时到底先锁谁先说结论在InnoDB里走二级索引更新一条记录时加锁顺序通常是先对二级索引记录加锁再去聚簇索引主键索引回表定位真实数据行并加锁。为什么因为二级索引叶子节点只存储索引列和主键值要更新数据行必须通过主键值回到聚簇索引上找完整记录。热词里那条“mysql通过二级索引更新时先锁二级索引项再回表锁主键这个时间窗口容易形成交叉”说的就是这件事。两个事务如果各自先拿到了一个二级索引项的锁然后都想去回表锁对方已经锁住的主键记录就会互相等待。更复杂的是如果二级索引不是唯一索引InnoDB还会加next-key lock记录锁间隙锁。间隙锁的存在意味着锁的不只是一行而是“某个扫描区间”内的所有可能插入位置交叉等待的范围就被进一步放大了。3.3 死锁交叉的复现路径我用一个简化的时间线复现了当时的死锁时间事务A事务BT1通过idx_order_no锁定二级索引项“SO202501150001”通过idx_user_id锁定二级索引项“10086”T2回表请求主键id100的锁回表请求主键id100的锁T3等待B释放主键锁等待A释放二级索引锁实际上A和B各自都拿到了部分锁资源然后同时卡在对方持有的下一把锁上InnoDB死锁检测器介入选择回滚其中一个事务。虽然业务上有重试机制但报警量一多应用的错误率还是上来了。3.4 生产级解决方案事务顺序、短事务与重试兜底修复这个问题的思路不是“把所有二级索引都删了”而是从三个层面治理统一事务内的加锁顺序如果同一个事务里要用多个条件更新同一批数据尽量让SQL按照主键ID顺序处理。比如应用层先查出目标主键列表按ID排序后分批执行-- 先把订单号对应的主键查出来 SELECT id FROM t_order WHERE order_no IN (...) ORDER BY id FOR UPDATE; -- 再按主键ID逐个更新 UPDATE t_order SET status 1 WHERE id IN (...);这样无论外面传什么条件事务内部最终都是按主键顺序加锁能极大减少交叉等待概率。缩短事务时间把大批量UPDATE拆成小批量每批几百条事务内只做必要的检查快速提交。这样每把锁的持有时间都短其他事务等待窗口就小。降低隔离级别有条件时从REPEATABLE READ降到READ COMMITTED可以减少间隙锁的持有但这个要看业务是否能接受当前读的结果变化不能为了规避死锁盲目降级。重试兜底死锁无法100%消灭应用层必须写好死锁重试逻辑捕获Deadlock found when trying to get lock异常后进行1~3次重试。这不是纵容问题而是给极端并发场景上双保险。4. 第四个坑一张表建了8个索引查得还是慢冗余索引才是元凶4.1 场景索引建了一堆但每一条都在吃灰第四个坑不是线上告警而是一次例行巡检发现的。某项目的用户登录日志表t_login_log单表数据量不到300万却建了8个索引。按说索引多应该查询飞快但事实是全表几乎所有查询都在200ms以上写入还越来越卡。把索引列表拉出来一看就明白了idx_user_id (user_id)idx_create_time (create_time)idx_status (status)idx_user_create (user_id, create_time)idx_user_status (user_id, status)idx_status_create (status, create_time)idx_user_status_create (user_id, status, create_time)idx_platform (platform)这8个索引里idx_user_create完全覆盖了idx_user_id因为联合索引最左前缀原则user_id开头的联合索引本身就能作为单列user_id索引使用idx_user_status_create又覆盖了idx_user_create和idx_user_status。等于说后建的单个字段索引基本都在吃灰白白占用磁盘和内存。4.2 排查索引使用情况的实用SQL排查冗余索引不需要高端工具先看系统表就能定位大头-- 查看表上所有索引 SHOW INDEX FROM t_login_log; -- 查看未使用索引统计需要在performance_schema开启userstat后使用 SELECT * FROM sys.schema_unused_indexes;更直接的办法是把每个索引对应的查询跑一遍EXPLAIN看优化器到底选用了哪些索引。我当时统计下来真正起到作用的只有idx_user_status_create和idx_create_time其余6个索引的index_name根本没有出现在任何一条执行的SQL执行计划里。4.3 为什么独立索引叠加不出“最优解”很多人有个错觉查询条件有几个字段就建几个独立索引。MySQL的优化器确实没有强大到能随意把多个独立索引组合成最优的访问路径。虽然8.0里支持index merge但它需要额外的排序/合并操作而且对条件的选择性、返回行数非常敏感很多情况下优化器宁可全表扫描也不走index merge尤其是多条件同时存在的时候。更关键的是索引不是白来的。每个索引都是一棵独立的B树写入数据时要同步更新所有索引页。数据量越大索引越多写入放大越严重。当时那张表的INSERT虽然每秒只有200条但因为要同步维护8棵索引树平均写延迟是其他表的3倍还多。索引表空间也因此膨胀得很厉害备份恢复都要多花近一倍时间。4.4 生产级解决方案联合索引重设计删除冗余索引当时我重新推导了业务查询模式把高频查询归成三类按user_id查最近登录记录按statuscreate_time统计用户数按platform做渠道分布。基于这三类我把索引收敛成两个ALTER TABLE t_login_log DROP INDEX idx_user_id, DROP INDEX idx_create_time, DROP INDEX idx_status, DROP INDEX idx_user_status, DROP INDEX idx_status_create, DROP INDEX idx_platform, ADD INDEX idx_user_create (user_id, create_time), ADD INDEX idx_status_create (status, create_time);这里的取舍逻辑是联合索引(user_id, create_time)既能覆盖单列user_id的场景又能直接返回排序好的时间序列一箭双雕(status, create_time)则覆盖了状态时间的统计场景。注意任何索引删除都要先确认没有慢查询依赖它切忌一把梭。正确做法是先在测试环境用EXPLAIN/慢查询回放验证然后灰度一段时间后再删。如果担心遗漏可以用开源社区里常见的重复索引检查脚本扫描所有表把name完全相同或前缀完全一致的索引列出来人工判断。5. 第五个坑为了覆盖索引把所有字段塞进去结果更新变慢、读被锁等5.1 背景覆盖索引的诱惑与我的翻车经历第五个坑是我自己埋的。当时有一个核心报表查询每次要查单号、状态、金额、创建时间、备注、渠道、操作人等十几个字段回表开销大。我当时的思路很“教科书”既然覆盖索引能避免回表那就把所有要查的字段都塞进一个联合索引里让SELECT全部从索引树取数。于是建了这么一个大宽索引CREATE INDEX idx_report_covered ON t_order (create_time, status, amount, channel, remark, operator_id, ...);刚开始查询确实快EXPLAIN显示Using index回表次数归零。我一度觉得自己优化得很漂亮直到某次大批量数据修复任务上线。5.2 超宽索引带来的更新放大与锁等待那次修复任务要对某时间段内几十万条订单做状态修正SQL大概是UPDATE t_order SET status 4 WHERE create_time BETWEEN 2025-01-01 AND 2025-01-05;执行计划选择了idx_report_covered作为访问路径。问题是这个索引里塞了大量长字段比如备注和操作人ID单条索引记录非常宽整个索引树的页数量比正常索引大了好几倍。更新一条记录时不仅要改聚簇索引行还要在新旧的二级索引页上做标记和插入。几十万行更新下来二级索引页的写入量和redo日志直接爆量。更糟糕的是锁。因为走二级索引范围更新InnoDB会对扫描区间内的二级索引项加next-key lock同时回表锁定对应的主键行。大批量更新持续持有这些锁的时间长日常的读和写请求都在排队等待甚至出现了Lock wait timeout exceeded。那一刻我才意识到覆盖索引不是越多越好把索引做成“一张竖着的表”最终会反噬更新场景。5.3 排查锁等待与索引表空间膨胀的双重信号当时通过SHOW ENGINE INNODB STATUS查看锁等待段落能看到大量事务阻塞在同一个二级索引范围内。结合information_schema.innodb_tablespaces观察索引表空间大小发现idx_report_covered占用的页面数量比主键索引还多。索引树深度可能只差一两层但页面数量成倍增长后无论是在缓冲池里的缓存命中率还是大批量变更时的刷盘开销都会显著劣化。这个坑还带来一个连锁反应因为二级索引页过大binlog量也涨了主从复制的延迟从之前的几百ms涨到10分钟以上连带着从库上的报表查询也开始变慢。一条覆盖索引引发的“蝴蝶效应”算是给我上了很贵的一课。5.4 生产级解决方案索引列裁剪、前缀索引、分批更新后来我做了三个调整覆盖索引瘦身只保留查询中高频出现且长度短的字段。长字段如remark要么截断成短摘要要么不放进索引让它回表取数。覆盖索引的价值在于“够用”不是“全有”。长字符串用前缀索引如果必须把字符串字段放进索引用前缀长度而不是全字段。比如操作人标识列使用(operator_id(10))既能覆盖大部分等值查询又不会把索引页撑爆。大批量更新必须分批把几十万行拆成1000行一批每批之间sleep 1~2秒或者用主键范围做游标推进-- 第一批 UPDATE t_order SET status 4 WHERE create_time BETWEEN 2025-01-01 AND 2025-01-03 AND id 0 ORDER BY id LIMIT 1000;注意MySQL的UPDATE语句默认不支持ORDER BY LIMIT直接作用于多表但单表更新时可以先查主键在应用层循环拼接比如SELECT id FROM t_order WHERE create_time BETWEEN 2025-01-01 AND 2025-01-05 ORDER BY id LIMIT 1000; UPDATE t_order SET status 4 WHERE id IN (..., ..., ...);每次更新只锁1000行锁持有时间短普通读写请求完全不受影响。这个模式也在生产环境验证过40万行数据修复任务从原来的“把库拖垮”降到“静默完成”虽然总耗时变长了但业务方完全无感。6. 血泪总结索引不是建完就完事上线前请你自查这5件事6.1 一张表记住5个坑的根因和自查点复盘完这五个坑我把最容易复发的检查项整理成了一张自查表每次上线索引变更之前都会逐条过一遍坑位核心根因自查点优先修复动作1. 隐式类型转换索引列被CAST或函数包裹EXPLAIN是否keyNULL、typeALL统一数据类型必要时函数索引兜底2. 深分页/文件排序offset扫描并丢弃filesort分页是否超过百页、Extra是否有filesort游标分页/延迟关联3. 二级索引回表锁交叉二级索引和主键索引加锁顺序不一致死锁日志是否涉及不同二级索引统一事务加锁顺序、短事务、重试4. 冗余索引过多脑补式建索引导致写放大和膨胀sys.schema_unused_indexes、SHOW INDEX联合索引复用、删除未使用索引5. 覆盖索引过宽为了不回表把整行塞进索引索引列数量、索引页大小、UPDATE慢索引列裁剪、前缀索引、分批更新6.2 索引变更上线流程另外跟这5个坑配套的是我后来一直遵守的索引变更SOP先看慢查询日志确认瓶颈SQL的真实样本用EXPLAIN分析当前执行计划记录type、key、rows、Extra小流量验证新索引上线后观察至少24小时确认没有写入变慢、锁等待、主从延迟旧索引先标记不删除再观察一周如果慢查询日志里完全没有依赖它的SQL才真正下线索引变更和表结构变更一样要走变更审批凌晨低峰期执行。这套流程听上去繁琐但能挡住绝大多数“建完索引反而出问题”的尴尬。尤其是删除索引之前我见过太多人只看了索引名就下结论结果删掉之后第二天一个很少执行的批处理任务开始全表扫描直接炸了线上业务。6.3 个人体会踩过这五个坑之后我对MySQL索引的态度从“建越多越稳”变成了“能少建就少建能复用就复用”。索引本质上是用空间换时间的交易每一棵索引树的背后都有写放大、锁开销和存储成本。真正有效的优化从来不是拍脑袋加索引而是先把业务查询模式梳理清楚再用EXPLAIN验证每一步假设最后用监控数据来证明它确实有用。如果你也正在处理类似的慢查询或死锁问题建议把上面五条对照自己的系统过一遍很多“疑难杂症”其实根因都是同一个索引设计没有结合实际执行路径。先找到执行计划里的那一行再谈怎么优化这句心得比任何工具都管用。