大家好，我是数据库小学妹 👋
上个月我干过一件蠢事。生产环境一张六百万行的订单表，测试时我在status字段加了索引，上线前跑测试查询从8秒降到0.03秒。我信心满满提交了变更，第二天慢查询日志里这条SQL赫然在列，执行时间7.2秒。
EXPLAIN一看：type是ALL，全表扫描。索引就在那儿，优化器看都不看一眼。我当时第一反应：不可能吧？
后来我又翻出另一个案例。一张八百万行的order_item表，我同样在status上加了索引，结果查询从0.3秒变成1.2秒，慢了整整3倍。
两次都是加索引，一次不走索引，一次走了反而更慢。问题到底出在哪？我花了几天时间研究优化器的代价计算模型，才算搞明白：SQL怎么执行不是我们说了算，是优化器说了算，而它的判断基于自己的一套"世界观"。
今天把笔记整理出来。搞懂它，你才知道索引什么时候有用、什么时候帮倒忙。
优化器到底在干什么
每次你发一条SELECT，MySQL不会直接执行，而是先由查询优化器决定"怎么执行最好"。MySQL 用的是基于成本的优化器（CBO），它会把所有可能的执行方案列出来逐一计算代价，选最便宜的那个。
就拿这条 SQL 来说：
SELECT * FROM orders
WHERE status = 'paid' AND create_time > '2026-07-01'
ORDER BY create_time DESC
LIMIT 20;
假设表上有三个索引：主键PRIMARY、status的普通索引idx_status、create_time的普通索引idx_create_time。
优化器要比较的方案大致有这些：
- 用idx_status过滤，再回表排序
- 用idx_create_time范围扫描，再回表过滤status
- 两个索引做 Index Merge合并
- 直接全表扫描，在内存中过滤和排序
每个方案优化器都会算一个"代价"。选代价最低的执行。
代价不是执行时间，而是一个抽象数值，代表优化器认为这个方案要"花多少力气"。关键就在于，优化器对"力气"的理解，可能和实际硬件对不上。这就是所有问题的根源。
代价模型的底层公式
要搞清楚优化器为什么选错，先要知道它怎么算代价。
MySQL的查询代价由I/O代价和CPU代价两部分组成。I/O 代价指从磁盘或缓冲池读取数据页的成本，MySQL用innodb_page_size（默认 16KB）来衡量页大小并估算需要读多少页。CPU 代价指在内存中评估 WHERE 条件、排序、关联等操作的计算量。
我用具体数字算一遍。假设orders表有600万行，每行平均200字节。
全表扫描的 I/O 代价：
总数据量=6,000,000 × 200 字节 ≈ 1,144 MB
总页数 =1,144 MB / 16 KB ≈ 73,216 页
I/O 代价 =73,216 × 1.0 = 73,216
CPU代价，评估六百万行的 WHERE 条件：
CPU代价 = 6,000,000 × 0.2 = 1,200,000
全表扫描总代价大约127万。
那走索引的代价呢？假设idx_status过滤后只剩8000行。B+树高度三层，遍历索引页代价很小，关键在回表：
索引扫描 I/O 代价 = 8,000 × 1.0 = 8,000
回表 I/O 代价 = 8,000 × 4.0 = 32,000
CPU 代价 = 8,000 × 0.2 = 1,600
索引方案总代价 = 41,600
41600，远小于127万。理论上优化器应该选索引。但我的情况是，优化器估算 idx_status要扫120万行，不是8000行。用120万代入：
回表 I/O 代价 = 1,200,000 × 4.0 = 4,800,000
总代价 = 5,000,000+
500万大于127万，优化器当然选了全表扫描。统计信息过期，行数估算错了，代价计算跟着错。这就是我遇到的情况。
optimizer trace：看优化器怎么算的
光看EXPLAIN只能看到结果，看不到过程。想看优化器到底怎么算的，得用optimizer trace。用法很简单：
SET optimizer_trace='enabled=on';
SET optimizer_trace_max_mem_size=1000000;
-- 执行你的查询
SELECT * FROM orders
WHERE status = 'paid' AND create_time > '2026-07-01'
ORDER BY create_time DESC
LIMIT 20;
-- 查看 trace 结果
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
trace输出的JSON很长。重点看"considered_execution_plans"这一段，每个方案的代价对比都在这：
{
"table": "`orders`",
"range_analysis": {
"rows_estimation": [{
"table": "`orders`",
"range": "status = 'paid'",
"rows": 1200000,
"cost": 241060
}],
"analyzing_range_alternatives": {
"range_scan_alternatives": [{
"index": "idx_status",
"rows": 1200000,
"cost": 1440001,
"chosen": false,
"cause": "cost"
}]
}
}
}
优化器估算idx_status扫120万行，代价144万。全表扫描代价24万。一比六的差距，索引直接被排除。
我第一次看trace的时候，发现优化器估算只要扫2000行，实际扫了40万行，差距200倍。那一刻我才真正理解统计信息有多重要。
统计信息
优化器做判断全靠统计信息，统计信息不准，选出来的执行计划自然有问题。MySQL InnoDB的统计信息有几个关键点：基数决定索引选择性，基数是索引列有多少个不同值，越高越好，InnoDB通过随机采样数据页来估算基数，本身就不精确。数据频繁更新时更麻烦，插入、删除、更新会改变数据分布，但统计信息不会实时更新，它只在ANALYZE TABLE或者数据变化达到阈值时才更新。
我回去验证了一下那个加索引变慢的案例：
ANALYZE TABLE order_item;
EXPLAIN SELECT * FROM order_item
WHERE order_no = 'ORD202601150001'
AND status = 1;
跑完ANALYZE TABLE再看执行计划，优化器切回了order_no索引，查询恢复正常。统计信息过期，是我遇到最多的优化器选错计划的原因。
但统计信息修好之后，我本以为没问题了。深入研究才发现，代价模型还有三个盲区，即便统计信息完全准确，它照样可能选错。
盲区一：不区分顺序读和随机读的真实差距
代价模型里，顺序读一页的代价系数是1.0，随机读一页是4.0。看起来区分了，但差距只有四倍。实际硬件上，SSD的顺序读吞吐量是随机读的十倍到五十倍，机械硬盘更夸张，差距在一百倍以上。
这意味着优化器比较"全表顺序扫"和"索引随机回表"时，严重低估了顺序读的优势。如果索引回表的行数超过某个阈值（大约是总行数的百分之五到二十），优化器会倾向于全表扫描。哪怕数据全在buffer pool里，走索引实际上更快。
我搞错过一次。一张一百万行的用户表有个状态字段，选择性很低，但查询过滤时偏偏条件就落在这个字段上。我以为是索引没生效，后来才知道优化器根据代价模型判断走全表扫更划算——因为它算出来回表的代价比顺序扫一遍还高，虽然实际上buffer pool已经缓存了大部分数据。
盲区二：不考虑 buffer pool 命中率
代价模型假设所有I/O都要从磁盘读，但hot data几乎全在buffer pool里，读取代价接近于零。一个极端例子：一千万行的表全在buffer pool里，全表扫描只需在内存中顺序遍历，实际耗时可能不到一秒。但优化器按磁盘I/O算出来的代价会非常高，可能选一个"代价更低"但实际上慢得多的方案。
代价模型按磁盘I/O算代价，但数据可能在内存里。这就是为什么优化器的估算和实际执行时间经常对不上。
盲区三：列间相关性完全忽略
假设表里有province和city两个字段。province有三十个不同值，city有五百个，优化器认为它们是独立的。但实际数据中，province='北京'的情况下，city大概率是北京的某个区，两列高度相关。
当查询条件是WHERE province='北京' AND city='朝阳区'时，优化器按独立概率计算：
估算行数 = 总行数 × (1/30) × (1/500)
实际行数远大于这个值，因为北京的数据集中在少数几个city值上。优化器低估了行数，选了索引方案，结果回表代价远超预期。MySQL 8.0引入了直方图来改善单列数据分布的估算，但列间相关性目前还是没有好的解决方案。
索引条件下推（ICP）
ICP从MySQL 5.6开始支持。正常情况下，联合索引只能用到最左前缀，匹配到范围条件后索引就停了，剩下的条件要到Server层去过滤。ICP让存储引擎自己在索引层做过滤，不用把数据推到Server层再筛，减少了回表和层间数据传输。
-- 联合索引 idx_user_status(user_id, name, status)
SELECT * FROM users
WHERE user_id = 100
AND name LIKE '张%'
AND status = 1;
name是范围匹配，索引在name这里停了。没有ICP的话，所有name LIKE'张%' 的数据都要回表，再去Server层过滤status。开了ICP之后，存储引擎在索引层就能把status 不等于1的过滤掉，只把status=1的回表。用EXPLAIN看Extra列，出现Using index condition说明ICP生效了。
不过ICP不是万能药。如果索引层过滤掉的行很少，反而增加计算开销。看到Using index condition但查询没变快的情况，多半是这个原因。
Index Merge：索引合并的陷阱
代价模型不仅影响索引选择，还影响Index Merge的决策。Index Merge是优化器把多个单列索引的结果合并起来用，常见的有交集和并集。听起来挺好，实际这是个坑高发区。
EXPLAIN SELECT * FROM orders
WHERE customer_id = 500 AND order_type = 3;
-- Extra: Using intersect(idx_customer_id, idx_order_type)
EXPLAIN显示它用了两个索引的交集。看着挺聪明，但实际执行起来，两个索引各扫出大量数据，再做交集运算，比一个合适的联合索引慢了好几倍。
我当时不确定是不是Index Merge的问题，关掉它试试看：
SET optimizer_switch='index_merge=off';
-- 再跑一次查询，对比执行时间
关掉之后，优化器被迫选了一个单索引，查询反而快了。两个单列索引选择性都不高的时候，Index Merge最容易出问题。优化器以为交集能大幅缩小结果集，但实际两个索引扫出来的数据有大量重叠，交集运算本身的开销抵消了缩小结果集的好处。解决办法是建合适的联合索引，给优化器一个明确的好选择。
怎么判断优化器选错了
大家最关心的问题：我怎么知道优化器选对了还是选错了？我一般分三步。
第一步：看 EXPLAIN 的 type 和 rows。
type是ALL且rows接近表总行数，说明优化器选了全表扫。如果表上有合适的索引，这就是可疑信号。type是ref或range但rows仍然很大，超过总行数百分之十，也值得警惕。
第二步：用 FORCE INDEX 对比实际执行时间。
-- 原始 SQL（让优化器自选）
SELECT * FROM orders
WHERE status='paid' AND create_time > '2026-07-01';
-- 强制走索引
SELECT * FROM orders FORCE INDEX(idx_status)
WHERE status='paid' AND create_time > '2026-07-01';
对比两者的实际执行时间。如果FORCE INDEX明显更快，并且你确认统计信息是最新的，那优化器大概率选错了。
第三步：开 optimizer_trace 看代价明细。
在"analyzing_range_alternatives"里找到每个索引方案的代价，和table_scan的代价对比。如果索引方案代价更低但优化器还是选了table_scan，说明有其他因素干扰。
怎么纠正优化器的选择
遇到优化器选错，有几个处理思路。先跑一遍ANALYZE TABLE 表名，大多数情况就够了。数据大量变更后养成习惯跑一次。MySQL默认会自动更新统计信息，但触发阈值是表数据的10%变化。1000万行的表，要变100万行才会触发。中间这段空窗期，优化器一直在用错误信息做决策。
如果统计信息准了还是选错，可以考虑直方图。MySQL 8.0支持，对数据分布不均匀但又不适合建索引的列比较有用：
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
我之前给一个倾斜的status字段建了直方图后，优化器对涉及该字段的查询计划全部修正了。
optimizer hint 也可以考虑。MySQL 8.0 支持：
SELECT /*+ INDEX(orders idx_status) */ *
FROM orders WHERE status='paid' AND create_time > '2026-07-01';
hint写死在SQL里，数据分布变了以后可能反而不合适，适合临时救急，不适合长期依赖。
还有一招是检查索引设计。优化器选错很多时候是索引本身的问题，单列索引太多、联合索引缺失、重复索引干扰判断。把多余的单列索引清理掉，给优化器一个明确的好选择，它反而更容易选对。
避坑清单
大批量变更后必须先跑ANALYZE TABLE。这是优化器选错方案最常见的原因。我见过太多人加了索引但不更新统计信息，然后抱怨索引没用。养成习惯，别等慢查询日志报警了才想起来。
Index Merge看着聪明，实际可能是性能杀手。看到EXPLAIN的 Extra里有intersect或union，别高兴太早，关掉它对比一下执行时间。两个条件字段各建一个单列索引，优化器很容易走 Index Merge，不如直接建联合索引，给优化器一个明确的好选择。
别盲目相信EXPLAIN的rows。rows是估算值，不是实际扫描行数。对于数据倾斜严重的列，比如状态字段百分之九十的行是同一个值，rows估算可能偏差几个数量级。养成用COUNT(*) 验证实际行数的习惯。
小表全表扫是正常的，别强迫症。几百行的表，全表扫比走索引快得多。优化器对小表选全表扫是正确行为，不用干预。
最后
回到开头那个问题。加了索引为什么查询反而更慢？
优化器看到了新索引，算了一下觉得这条路更便宜，但它的估算基于过时的统计信息，实际走起来才发现是条堵路。
我以前以为优化器很聪明。学完之后发现，它更像拿着旧地图的向导——地图准的时候带路很准，地图旧了就带错路。理解代价模型的盲区之后，你就知道什么时候该相信它，什么时候该手动干预。
你在实际工作中遇到过优化器"自作聪明"选错计划的情况吗？后来怎么解决的？来评论区聊聊。
我是数据库小学妹，咱们下篇见 👋
