大家好，我是数据库小学妹 👋

上个月我干过一件蠢事。生产环境一张六百万行的订单表，测试时我在status字段加了索引，上线前跑测试查询从8秒降到0.03秒。我信心满满提交了变更，第二天慢查询日志里这条SQL赫然在列，执行时间7.2秒。

EXPLAIN一看：type是ALL，全表扫描。索引就在那儿，优化器看都不看一眼。我当时第一反应：不可能吧？

后来我又翻出另一个案例。一张八百万行的order_item表，我同样在status上加了索引，结果查询从0.3秒变成1.2秒，慢了整整3倍。

两次都是加索引，一次不走索引，一次走了反而更慢。问题到底出在哪？我花了几天时间研究优化器的代价计算模型，才算搞明白：SQL怎么执行不是我们说了算，是优化器说了算，而它的判断基于自己的一套"世界观"。

今天把笔记整理出来。搞懂它，你才知道索引什么时候有用、什么时候帮倒忙。

## 优化器到底在干什么

每次你发一条SELECT，MySQL不会直接执行，而是先由查询优化器决定"怎么执行最好"。MySQL 用的是基于成本的优化器（CBO），它会把所有可能的执行方案列出来逐一计算代价，选最便宜的那个。

就拿这条 SQL 来说：

```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。用法很简单：

```sql
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"这一段，每个方案的代价对比都在这：

```json
{
  "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或者数据变化达到阈值时才更新。

我回去验证了一下那个加索引变慢的案例：

```sql
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层再筛，减少了回表和层间数据传输。

```sql
-- 联合索引 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是优化器把多个单列索引的结果合并起来用，常见的有交集和并集。听起来挺好，实际这是个坑高发区。

```sql
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 500 AND order_type = 3;
-- Extra: Using intersect(idx_customer_id, idx_order_type)
```

EXPLAIN显示它用了两个索引的交集。看着挺聪明，但实际执行起来，两个索引各扫出大量数据，再做交集运算，比一个合适的联合索引慢了好几倍。

我当时不确定是不是Index Merge的问题，关掉它试试看：

```sql
SET optimizer_switch='index_merge=off';
-- 再跑一次查询，对比执行时间
```

关掉之后，优化器被迫选了一个单索引，查询反而快了。两个单列索引选择性都不高的时候，Index Merge最容易出问题。优化器以为交集能大幅缩小结果集，但实际两个索引扫出来的数据有大量重叠，交集运算本身的开销抵消了缩小结果集的好处。解决办法是建合适的联合索引，给优化器一个明确的好选择。

## 怎么判断优化器选错了

大家最关心的问题：我怎么知道优化器选对了还是选错了？我一般分三步。

**第一步：看 EXPLAIN 的 type 和 rows。**

type是ALL且rows接近表总行数，说明优化器选了全表扫。如果表上有合适的索引，这就是可疑信号。type是ref或range但rows仍然很大，超过总行数百分之十，也值得警惕。

**第二步：用 FORCE INDEX 对比实际执行时间。**

```sql
-- 原始 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支持，对数据分布不均匀但又不适合建索引的列比较有用：

```sql
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
```

我之前给一个倾斜的status字段建了直方图后，优化器对涉及该字段的查询计划全部修正了。

optimizer hint 也可以考虑。MySQL 8.0 支持：

```sql
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(*) 验证实际行数的习惯。

小表全表扫是正常的，别强迫症。几百行的表，全表扫比走索引快得多。优化器对小表选全表扫是正确行为，不用干预。

## 最后

回到开头那个问题。加了索引为什么查询反而更慢？

优化器看到了新索引，算了一下觉得这条路更便宜，但它的估算基于过时的统计信息，实际走起来才发现是条堵路。

我以前以为优化器很聪明。学完之后发现，它更像拿着旧地图的向导——地图准的时候带路很准，地图旧了就带错路。理解代价模型的盲区之后，你就知道什么时候该相信它，什么时候该手动干预。

你在实际工作中遇到过优化器"自作聪明"选错计划的情况吗？后来怎么解决的？来评论区聊聊。

我是数据库小学妹，咱们下篇见 👋


上个月我干过一件蠢事。生产环境一张六百万行的订单表，测试时我在status字段加了索引，上线前跑测试查询从8秒降到0.03秒。我信心满满提交了变更，第二天慢查询日志里这条SQL赫然在列，执行时间7.2秒。

订单表加索引查询反而更慢：从CBO代价模型看MySQL优化器选型与干预方案

从 MySQL 查询优化器底层视角，拆解 CBO 代价计算全过程。结合统计信息偏差、ICP、Index Merge、optimizer trace 调试等实战内容，讲清楚优化器为什么选错计划、如何纠正

数据库

后端

开发工具

MySQL索引优化实战：揭秘优化器代价模型盲区。通过真实案例解析索引失效原因，包括统计信息过期、顺序/随机读代价估算偏差、bufferpool命中率忽略等核心问题。掌握optimizer_trace诊断技巧，学会ANALYZE TABLE更新统计信息，避免IndexMerge陷阱，提升SQL执行效率。

代码解释

MySQL

JSON

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

订单表加索引查询反而更慢：从CBO代价模型看MySQL优化器选型与干预方案-腾讯云开发者社区-腾讯云

订单表加索引查询反而更慢：从CBO代价模型看MySQL优化器选型与干预方案

订单表加索引查询反而更慢：从CBO代价模型看MySQL优化器选型与干预方案

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐