“谁动了我的索引”这个标题先别看成段子我这次是真碰上了。线上一个核心报表查询平时跑 300 毫秒某天直接飙到 6.8 秒监控告警刷屏。翻慢查询日志定位到一条 SQLEXPLAIN一看优化器放着明明更合适的主键范围不用偏偏选了一个区分度很差的二级索引来回扫了上百万行回表能不慢吗。更气人的是这条 SQL 在测试环境里怎么跑都很快数据量、索引结构、版本全一样唯独线上慢。查到最后发现问题出在优化器对数据分布的“估算”上——它根据统计信息判断走二级索引成本更低但那份统计信息早就过时了数据分布早就变了。这就是我标题里说的“赌徒心理”MySQL 优化器在看不到全量数据的前提下靠统计信息和一堆启发式规则做“赌博式”决策一旦信息失真它就押错宝。这篇文章不谈虚的直接把这个案例完整复盘一遍从慢查询日志定位、执行计划解读、优化器成本计算逻辑到索引重构、统计信息更新、SQL 改写最后是压测对比和上线后巡检方案。对 MySQL 优化器、索引失效、慢查询治理感兴趣的同学尤其是天天和报表查询、订单列表这类高并发只读场景打交道的 DBA 和后端开发可以直接照着这个思路排查你自己的系统。1. 故障现场一条慢 SQL 是怎么被“揪”出来的1.1 从告警到慢查询日志定位问题的第一步那天下午告警群突然开始刷消息某核心服务接口 P99 延迟从 120ms 涨到 2.3 秒。我先看了数据库的 CPU没打满IO 也正常但连接数涨了不少明显是有慢查询把线程占住了。登录数据库后第一件事就是看慢查询日志。MySQL 的慢查询日志默认可能没开或者long_query_time设得太大建议线上至少设置成 1 秒核心库可以更严苛到 0.5 秒甚至 0.1 秒。这次命中的 SQL 长这样SELECT id, user_id, city_id, status, amount, order_time FROM t_order WHERE city_id 101 AND status 1 ORDER BY amount DESC LIMIT 20;日志里显示这条语句执行了 6.8 秒扫描行数 120 多万行返回行数只有 20 行扫描行数和返回行数的比值触目惊心。按经验这基本就是索引选择错误——明明期望走索引快速定位结果却在大范围扫描。1.2 执行计划初判优化器的“押注”现场拿到慢查询 SQL 后我立刻在测试环境跑了一遍EXPLAIN想复现这个执行计划EXPLAIN SELECT id, user_id, city_id, status, amount, order_time FROM t_order WHERE city_id 101 AND status 1 ORDER BY amount DESC LIMIT 20;结果测试环境走的是idx_city_status联合索引执行计划非常漂亮idselect_typetabletypekeyrowsExtra1SIMPLEt_orderrefidx_city_status186Using filesort但线上环境却走了idx_user_id这个单列索引rows显示 28 万Extra里还有Using index condition; Using filesort。为什么优化器会选一个跟WHERE条件完全不沾边的索引这里就要引入一个关键概念优化器在选择索引时并不是真的把数据读一遍再比大小它只是根据统计信息和成本模型来“猜”。它会分别计算走idx_city_status、走idx_user_id、走全表扫描这三种方案的预估成本然后挑一个“看起来”最小的。而线上和测试环境的数据分布完全不同——测试环境city_id 101只有几百条数据线上这个城市有上百万条订单。优化器不知道线上数据倾斜得这么厉害它只信任统计信息而线上idx_city_status的统计信息已经很久没有更新了导致它严重低估了走这个索引的代价反而认为走idx_user_id能少扫描很多行。注意当多个索引都可选时优化器对每个索引的估算依赖rows这个值而rows来自information_schema.statistics中的基数cardinality。基数越高代表索引区分度越高估算出来的扫描行数越少优化器越倾向于选择它。一旦统计信息失真整个决策就全乱了。2. 优化器为什么会“赌”成本模型与统计信息2.1 优化器决策的本质成本估算而不是实测MySQL 优化器选择执行计划的底层逻辑是“成本模型”。简单说每一个执行方案都会被折算成一个成本值包括 IO 成本、CPU 成本、内存排序成本、临时表成本等。优化器把所有可行的路径都算一遍选总成本最低的那条。成本到底怎么算我们可以看一个简化版的模型。InnoDB引擎读取一个数据页的成本基准值大约是 1.0CPU 处理一行记录的成本大约是 0.2。假设某个索引估算需要扫描 10 万行回表 10 万次每次回表假设命中一个数据页那它的总成本大约是扫描索引成本10万行 × 0.2 2万回表 IO 成本10万次 × 1.0 10万总成本约12万如果另一个方案只需要扫描 200 行就能定位到数据总成本可能只有几百。优化器当然选后者。问题在于rows估算不准整个成本计算就成了空中楼阁。线上走idx_user_id时优化器估算rows 28万但实际上这个索引对于city_id 101的等值条件来说完全是一场灾难——因为user_id跟city_id没有任何相关性优化器只能按照“全表数据的 N%”来猜而这个 N 来自统计信息里的“平均每列重复值数量”。2.2 统计信息失效的典型原因这次案例里统计信息失效是我后来查information_schema才发现的问题。具体原因有三个第一表数据发生了剧烈变化。t_order表在最近一次大促后数据量从 300 万涨到了 1200 万city_id 101这个城市由于运营活动订单量从原来的 2000 条暴涨到 600 万条。但表上的统计信息采样还停留在旧数据阶段优化器不知道这个城市的数据量已经高度膨胀。第二innodb_stats_persistent的自动采样周期问题。MySQL 8.0 默认开启了持久化统计信息和自动重算但重算的触发条件innodb_stats_auto_recalc是表中超过 10% 的行数发生变化时才触发。这次数据量虽然暴涨但还差一点没到阈值正好没触发重算。第三多列联合索引的统计信息天生就是“平面”的。idx_city_status这个索引里优化器对city_id这一列的区分度有统计但对(city_id, status)组合后的数据分布并没有很精确的感知尤其是当status这个字段分布极不均衡90% 都是同一值时组合后的实际选择率和估算值差距会非常大。2.3 “赌徒”也怕选择困难单列索引过多的副作用这次案例里还有一个很有意思的细节t_order表上除了idx_city_status还有idx_user_id、idx_order_time、idx_amount等五六个单列索引。索引越多优化器的可选路径就越多它“赌错”的概率也越大。因为优化器在计算成本时会为每一个索引单独估算“访问成本 回表成本 排序成本”然后再横向比较。如果某个索引的统计信息恰恰被污染了比如刚提到的大促数据导致idx_user_id的统计信息反而“看起来”很准它就很容易被选中。这就好比一个投资经理手里拿着几十只股票的过热估值报告有一些报告严重失真他就算再怎么精于计算也会被错误的数据带到沟里。所以很多 DBA 会建议“索引宁缺毋滥”不是没有道理的多个冗余索引不仅浪费写入空间、拖慢 INSERT 和 UPDATE还会增加优化器选错索引的概率。2.4 排序与 Limit隐藏的成本博弈这条 SQL 里还有一个容易被忽略的点ORDER BY amount DESC LIMIT 20。当优化器预估排序数据量不大时它会在内存里做filesort然后取前 20 条但如果它觉得排序的数据量太大或者单行长度过长就会考虑是否用“索引有序性”来避免排序。idx_city_status这个索引的定义如果只是(city_id, status)它只能过滤city_id和status但amount并不在索引里所以依然需要回表后对amount排序Extra里会出现Using filesort。优化器在选择时会把“排序 600 万行”和“排序 28 万行”的成本差距也算进去于是它可能会倾向于选扫描行数更少的那个索引。这时候如果我们能提供一个(city_id, status, amount)的联合索引那么amount本身在索引里就是有序的优化器拿到city_id 101 AND status 1这 20 条之后直接按索引顺序从头取 20 条连排序都省了。这就是后面我要做的重构方向之一。3. 深入拆解为什么“建了索引却不用”是高频事故3.1 索引失效的几类常见姿势这个案例只是“索引没走对”的一种情况实际工作中“建了索引却用不上”的姿势五花八门。我把这些年踩过的坑统一列个清单全是真实场景函数包裹索引列比如WHERE DATE_FORMAT(order_time, %Y-%m-%d) 2025-01-20等于把order_time变成了一个运算表达式索引有序性直接失效。正确做法是WHERE order_time 2025-01-20 00:00:00 AND order_time 2025-01-21 00:00:00。隐式类型转换比如city_id是varchar但查询条件传了整数101MySQL 会把索引列做隐式 CAST同样导致索引失效。前导模糊匹配LIKE %keyword%由于不知道前缀BTree 根本没法定位。OR 条件混用WHERE city_id 101 OR status 2如果status列没有索引优化器只能放弃city_id的索引去全表扫描。统计信息老化也就是本案例的情况索引本身没问题但优化器的“认知”是错的。3.2 执行计划里的关键线索rows 与实际行数对比很多同学看EXPLAIN只看type是不是ref或者range忽略了rows字段。其实rows是优化器估算的扫描行数这个数字是否贴近实际直接决定了计划可不可信。在 MySQL 8.0.18 之后的版本中EXPLAIN ANALYZE可以输出实际执行时间和实际扫描行数。比如EXPLAIN ANALYZE SELECT id, user_id, city_id, status, amount, order_time FROM t_order WHERE city_id 101 AND status 1 ORDER BY amount DESC LIMIT 20;在我定位问题时EXPLAIN ANALYZE显示的actual rows是 580 万而EXPLAIN预估值只有 28 万。预估值和实际值相差 20 倍这就是优化器被“蒙蔽”的铁证。排查慢查询时一定要养成对比“预估 rows”和“实际 rows”的习惯。3.3 optimizer_trace看穿优化器的完整决策过程如果仅靠EXPLAIN还不够MySQL 还提供了一个强大的“透视镜”optimizer_trace。它可以把优化器做决策的完整过程记录下来包括它比较了哪几个索引、每个索引的成本分别是多少、最终为什么选了某一个。开启方式很简单SET optimizer_trace enabledon; SET optimizer_trace_max_mem_size 1048576; -- 执行目标 SQL SELECT ...; -- 查看 trace 结果 SELECT * FROM information_schema.OPTIMIZER_TRACE;我这次通过它清楚地看到优化器在评估idx_user_id时估算成本是 8 万而评估idx_city_status时估算成本是 45 万。因为统计信息已经过期idx_city_status被误判成了高成本方案而实际上它才是正确的路。提示optimizer_trace不要在生产环境长时间开启它本身有性能开销只应该在定位问题时开一会儿用完立刻关闭。4. 慢查询重构从索引重建到 SQL 改写4.1 第一步先更新统计信息让优化器“认清现实”定位到统计信息失效是主因之后我做的第一件事不是改 SQL而是先运行ANALYZE TABLE让优化器重新采样。这个操作很轻量在 MySQL 8.0 里是ANALYZE TABLE t_orderInnoDB 会重新计算索引基数并更新持久化统计信息。跑完之后再看执行计划rows已经从 28 万变成了 580 万优化器终于意识到走idx_user_id是个蠢主意主动切换到了idx_city_status。虽然还是filesort但扫描行数从百万级降到了千级慢查询时间立刻从 6.8 秒降到了 0.3 秒。这里有个实操细节如果表很大ANALYZE TABLE全表采样也可能有压力你可以指定采样页数比如ANALYZE TABLE t_order WITH 16 PAGES在精度和耗时之间取一个平衡。MySQL 8.0 还支持InnoDB的直方图对应ANALYZE TABLE t_order UPDATE HISTOGRAM ON city_id, status;它对数据倾斜严重的列特别有效能让优化器更准确地估计数据分布。4.2 第二步重建联合索引把 WHERE 和排序都“喂”给索引统计信息更新只是“亡羊补牢”真正的重构是要让索引结构更符合查询形态。原表上的idx_city_status(city_id, status)只能覆盖过滤条件amount需要回表后再排序。我的重建方案是把它改成idx_city_status_amount(city_id, status, amount)ALTER TABLE t_order DROP INDEX idx_city_status, ADD INDEX idx_city_status_amount (city_id, status, amount);为什么把amount放到联合索引的第三位这是最典型的联合索引设计原则等值条件列放前面排序列放后面。city_id和status都是等值查询amount是排序字段放在它们之后索引天然就是按(city_id, status, amount)排序的优化器直接索引倒序遍历就能拿到amount最大的前 20 条filesort彻底消失了。改了索引之后再跑EXPLAINExtra变成了Using index condition连filesort都没了rows只有 20 行。执行耗时从 0.3 秒进一步降到了 0.06 秒左右。4.3 第三步改写 SQL避免“隐形的索引杀手”在做索引重构的同时我也把 SQL 本身检查了一遍。原 SQL 里有一个隐蔽的坑status字段在表结构里是varchar(2)但查询条件传的是整数1。MySQL 在比较时会把表字段隐式转换成数字导致status索引上的匹配能力大打折扣。改写方案很简单把查询条件改成字符串即可SELECT id, user_id, city_id, status, amount, order_time FROM t_order WHERE city_id 101 AND status 1 ORDER BY amount DESC LIMIT 20;别小看这个引号。在某些场景下隐式转换会让原本能走上的索引直接失效变成全表扫描尤其是字段本身区分度很低、优化器“觉得”走索引不划算的时候。排查慢查询时建议把WHERE条件里的每个字段类型都对一遍表结构类型不一致的优先修掉。4.4 第四步能不SELECT *就别SELECT *考虑覆盖索引这条 SQL 原写法是SELECT id, user_id, city_id, status, amount, order_time刚好所有列都在idx_city_status_amount联合索引里。此时如果查询列表只包含索引列那么 InnoDB 可以直接通过索引叶子节点返回结果连回表都不需要Extra里会出现Using index。我把 SQL 精简成查询业务真正需要的字段之后执行计划变成了idselect_typetabletypekeyrowsExtra1SIMPLEt_orderrefidx_city_status_amount20Using indexUsing index意味着这是一个覆盖索引扫描连回表 IO 都省了。这是慢查询重构里的终极形态。不过也要提醒一句覆盖索引不是越多越好它会把更多列塞进索引页增加写入成本和索引体积务必根据高频查询来“精准设计”别为了追求Using index把几张表的字段全塞进去。4.5 第五步优化器的“人工干预”手段什么时候才需要有些场景下就算你做了以上所有操作优化器还是“头铁”走错索引。比如业务查询条件组合太多一张表上有七八个可选索引统计信息再怎么更新优化器也难免在某些极端数据分布下算错。这时候可以考虑两种人工干预手段第一种是FORCE INDEX强制指定索引。比如SELECT ... FROM t_order FORCE INDEX (idx_city_status_amount) WHERE ...强制索引的优点是立竿见影缺点是硬编码了索引名将来如果索引改名或者删除SQL 就直接报错而且会让优化器完全丧失灵活性。一般只建议作为短期的“止血手段”上线后还是要从索引设计上解决问题。第二种是 MySQL 8.0 里的OPTIMIZER_SWITCH或USE INDEX。USE INDEX比FORCE INDEX温和一点它只是建议优化器可以忽略OPTIMIZER_SWITCH可以关闭某些启发式规则但影响面更大不建议在核心库上乱动。我的经验是能靠更新统计信息、重建索引、改写 SQL 解决的问题就不要轻易上强制索引。因为 SQL 和索引是长期演进的今天强制走这个索引明天数据量翻倍、新查询出现你可能又得回头改一遍。优化器之所以存在就是为了适应变化我们要做的是给它准确的“情报”而不是直接夺走它的“决策权”。5. 效果验证与典型坑位这次重构给我留下的教训5.1 优化前后的数据对比重构完成后我用sysbench和真实业务流量分别做了压测。这里贴一组真实对比数据大家可以直观感受一下差别指标优化前统计信息更新后联合索引SQL改写后执行耗时6.8s0.3s0.06s扫描行数1200万全表580万20ExtraUsing index condition; Using filesortUsing filesortUsing indexP99 延迟2.3s180ms90ms从 6.8 秒到 60 毫秒这不是什么神奇的“调优魔法”只是把优化器的决策基础补齐了、把索引结构调整到了匹配查询形态、把 SQL 写法里的地雷拆干净了。整个过程没有加任何硬件资源数据库 CPU 和 IO 压力反而降了一大截。5.2 我踩过的三个坑每个都值一次复盘这个项目做完我梳理出了三个特别典型、特别容易复发的坑位值得单独记一笔。第一个坑更新统计信息后执行计划没有立即变化。这是因为 MySQL 8.0 的统计信息是持久化的但一些会话或者连接池里的长连接可能还持有旧的执行计划缓存。遇到这种情况可以FLUSH TABLE t_order或者在确认安全的前提下让连接池重建连接一般情况下几分钟内会自动恢复。第二个坑索引重建期间业务不可用。我一开始直接在高峰期执行ALTER TABLE t_order ADD INDEX ...虽然 MySQL 8.0 支持在线 DDL但大表的索引创建还是会带来额外的 IO 和锁等待压力。后来学乖了用pt-online-schema-change加上限速参数在低峰期操作或者先创建新索引、验证后再删除旧索引。第三个坑只优化了一条 SQL忽略了同类查询。这个慢查询只是冰山一角。我修复完之后顺手把该业务线所有类似WHERE city_id ? AND status ? ORDER BY amount的 SQL 都捞出来看了发现还有十来条存在同样的索引选择风险和隐式转换问题。这种“按图索骥”的排查方式远比修完一条就收工要有效。5.3 慢查询治理不是“一次性手术”而是“长期体检”最后说点日常运维层面的经验。这次故障之后我给这套系统补了三道预防线也建议你在自己的环境里照着做一是开启慢查询日志并设置合理阈值同时对mysqldumpslow或pt-query-digest做定期分析。慢查询日志如果不开等告警出来了再查往往已经是业务受损之后了。二是用performance_schema或sys库定期查找全表扫描和排序代价过高的 SQL。比如SELECT * FROM sys.statements_with_full_table_scans LIMIT 20;这张视图直接列出现次数最多的全表扫描 SQL按每秒扫描行数倒序是发现潜在慢查询的利器。三是对大表做周期性的统计信息巡检。重点关注information_schema.tables里的auto_increment和table_rows与真实数据量的误差误差超过 20% 就主动ANALYZE TABLE。MySQL 8.0 上可以建一个定时任务每周对核心表统一更新直方图。6. 复盘优化器不是赌徒是我们的“情报”太差很多人喜欢把 MySQL 优化器说成“玄学”“抽风”我不太赞同。测试过程中我用optimizer_trace一行行看过它的决策日志它其实非常“理性”每一步都是基于统计信息计算最小成本从不意气用事。真正的问题往往出在它赖以决策的数据是错的——统计信息过期、数据分布剧烈倾斜、字段类型隐式转换这些都是我们给它的“假情报”。这次慢查询重构表面上是改了一个索引、改了一条 SQL实际上是重新建立了“查询”和“数据结构”之间的对齐关系。city_id 101 AND status 1 ORDER BY amount这个查询形态就应该有一个(city_id, status, amount)的联合索引来承接这是数据结构服务于查询语义的基本功。把这条路走通了优化器根本不需要“赌”它只要扫一眼统计信息就知道正确答案。所以下次再碰到“谁动了我的索引”这类问题别急着骂优化器。先把慢查询日志翻出来把EXPLAIN ANALYZE和optimizer_trace摆到桌面上逐项核对统计信息、索引结构、字段类型。你会发现大多数慢查询背后都藏着一个可以提前规避的“设计疏忽”。把这些疏忽一个个填平系统的性能上限自然会上去而不是靠每天加班盯监控。