企业官网建设流程全解析

“谁动了我的索引”这个标题先别看成段子，我这次是真碰上了。线上一个核心报表查询，平时跑 300 毫秒，某天直接飙到 6.8 秒，监控告警刷屏。翻慢查询日志定位到一条 SQL，EXPLAIN一看，优化器放着明明更合适的主键范围不用，偏偏选了一个区分度很差的二级索引，来回扫了上百万行回表，能不慢吗。

更气人的是，这条 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联合索引，执行计划非常漂亮：

但线上环境却走了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.0，CPU 处理一行记录的成本大约是 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，但查询条件传了整数101，MySQL 会把索引列做隐式 CAST，同样导致索引失效。
• 前导模糊匹配：LIKE '%keyword%'，由于不知道前缀，B+Tree 根本没法定位。
• 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 = "enabled=on"; 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_order，InnoDB 会重新计算索引基数，并更新持久化统计信息。

跑完之后再看执行计划，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彻底消失了。

改了索引之后，再跑EXPLAIN，Extra变成了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 精简成查询业务真正需要的字段之后，执行计划变成了：

Using 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和真实业务流量分别做了压测。这里贴一组真实对比数据，大家可以直观感受一下差别：

从 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摆到桌面上，逐项核对统计信息、索引结构、字段类型。你会发现，大多数慢查询背后都藏着一个可以提前规避的“设计疏忽”。把这些疏忽一个个填平，系统的性能上限自然会上去，而不是靠每天加班盯监控。