当 SQL 索引“突然失明”：一次从执行计划到底层代价模型的深度追踪

化风行千里
14 阅读11分钟

当 SQL 索引“突然失明”：一次从执行计划到底层代价模型的深度追踪

很多开发者以为：

“我建了索引，数据库就一定会用。”

但真实世界里，优化器比你更“现实”。

它不在乎你辛苦建索引，

它只在乎：

“谁成本低，我就选谁。”

在大部分线上慢 SQL 事故里，“索引不生效”几乎都是核心原因。

但真正的问题往往不是：

  • 没有索引
  • 索引失效
  • 忘记建索引

而是：

你以为自己在“使用索引”，
数据库却认为“全表扫描更便宜”。

本文不会再重复那些已经被写烂的：

  • 最左匹配
  • like '%xx'
  • 函数导致索引失效

而是从：

  • 优化器决策
  • 回表成本
  • 数据分布
  • 隐式转换
  • 索引选择性
  • Buffer Pool 命中率
  • ICP / MRR / 覆盖索引
  • 统计信息误判

这些真正影响线上系统的问题出发。

你会发现：

“索引不生效”其实是数据库和程序员之间的一场认知战争。

一、先理解一个反直觉事实

很多人第一次看到这个现象都会困惑：

SELECT * FROM user WHERE age > 18;

明明 age 上有索引。

为什么执行计划还是：

type: ALL

也就是：

全表扫描。

很多人第一反应是：

  • MySQL 出 bug 了
  • 索引坏了
  • 优化器太蠢

实际上：

是因为数据库比你更懂“代价”。

数据库优化器本质上是一个：

成本评估系统

它会计算：

走索引成本 VS 全表扫描成本

如果：

索引扫描 + 回表 > 全表扫描

那么它就会直接放弃索引。

也就是说：

“不用索引”有时候恰恰是优化器最聪明的选择。

二、为什么索引有时候反而更慢？

很多开发者脑海中的索引结构是这样的：

查索引 -> 找到数据 -> 返回结果

但真实过程其实是：

B+树定位
    ↓
拿到主键ID
    ↓
回主键索引树
    ↓
再取完整数据

这一步：

回表（Back To Table）

才是真正昂贵的地方。

尤其在下面这种 SQL：

SELECT * FROM order_info WHERE status = 1;

如果：

  • status = 1 的数据占 80%
  • 表有 3000 万数据
  • 每行记录很大

那么数据库会发现：

走索引：
3000万次随机IO

全表扫描：
顺序IO

在机械硬盘时代：

随机 IO 几乎是灾难。

即便 SSD 时代已经改善，

但：

大量随机页访问仍然会击穿缓存命中率。

于是优化器会说：

“算了，我直接扫全表。”

这就是很多人无法理解的：

为什么“建了索引”反而更慢。

三、真正决定索引命运的：选择性

数据库不会关心：

有没有索引

它关心的是：

选择性（Selectivity）

公式大概是：

不同值数量 / 总记录数

例如：

字段不同值数量总行数选择性
gender21000万极低
user_id1000万1000万极高

于是：

WHERE gender = '男'

这种索引几乎没意义。

因为数据库发现：

一半数据都要查

那么：

索引扫描 + 回表

不如：

全表顺序扫描

更快。

很多团队喜欢给：

  • status
  • gender
  • deleted
  • enable_flag

单独建索引。

这是线上系统里极其常见的：

伪优化

看起来建了索引。

实际上：

优化器根本不会用。

四、你以为索引失效，其实是类型在背刺你

下面这段 SQL 很经典：

SELECT * FROM user WHERE phone = 13800138000;

phone 字段类型：

VARCHAR(20)

很多人会发现：

索引没走

原因是什么？

隐式类型转换

数据库内部其实变成了：

CAST(phone AS SIGNED) = 13800138000

也就是说：

你对索引列做了函数操作。

于是：

索引树失去有序性

优化器直接放弃索引。

这类问题在线上极其隐蔽。

因为：

SQL 看起来完全正常。

但执行计划已经悄悄变成：

ALL

全表扫描。

最危险的是：

开发环境数据量小。

你根本察觉不到。

等到了生产：

5000 万数据。

CPU 瞬间拉满。

五、like '%xx%' 为什么这么致命？

很多文章只是告诉你：

LIKE '%abc'

索引失效。

但很少有人解释：

为什么失效。

B+树索引本质上依赖：

有序性

例如：

abc001
abc002
abc003

数据库可以快速定位：

abc开头的数据范围

但：

LIKE '%abc'

意味着：

数据库根本不知道：

abc 会出现在哪

于是只能：

全索引扫描

甚至直接：

全表扫描

很多搜索需求：

  • 用户名模糊搜索
  • 商品关键字
  • 文章内容检索

如果直接用 %xx%。

系统在数据量大后会迅速崩塌。

正确方案往往不是：

“继续优化 SQL”。

而是：

引入搜索引擎

例如：

  • Elasticsearch
  • OpenSearch
  • Meilisearch

因为：

B+树从来就不是为全文检索设计的。

六、联合索引不是“越多越好”

很多人喜欢这样建索引：

INDEX(a,b,c,d,e)

觉得：

一劳永逸

实际上：

这可能导致：

  • 索引膨胀
  • Buffer Pool 污染
  • 更新成本飙升
  • 页分裂增加
  • 写入性能下降

更严重的是：

联合索引本质上只有一种排序方式。

例如：

INDEX(a,b,c)

本质排序：

先按a
再按b
再按c

所以：

WHERE b = ?

通常无法有效利用索引。

这不是规则限制。

而是：

B+树物理结构决定的。

很多人把“最左匹配”背得滚瓜烂熟。

却从没真正理解：

为什么必须最左。

因为：

索引不是 HashMap。

它是：

排序后的树结构。

七、范围查询会“截断”索引

例如：

INDEX(a,b,c)

SQL：

WHERE a = 1 AND b > 10 AND c = 3

很多人以为：

abc 全用了

实际上：

通常只能有效使用：

a,b

因为：

范围查询会破坏后续有序性。

当：

b > 10

出现时。

数据库已经无法继续精准定位：

c

只能继续扫描。

于是：

后续列利用率急剧下降。

很多复杂查询性能差。

根源其实是：

联合索引列顺序设计错误。

八、为什么优化器“偶尔抽风”？

有些 SQL 非常诡异：

昨天还走索引。

今天突然全表扫描。

然后 DBA 执行：

ANALYZE TABLE

又恢复正常。

这是为什么？

统计信息过期

优化器其实并不会真的扫描全表分析。

它会基于：

  • 采样
  • 统计信息
  • 基数估算

决定执行计划。

如果统计信息失真。

优化器可能会误判：

“这个条件返回数据很少”

于是选择索引。

结果实际上：

返回了几百万数据

瞬间雪崩。

这也是为什么：

很多线上慢 SQL：

不是一直慢

而是：

偶发性慢。

这种问题比持续慢 SQL 更难排查。

九、覆盖索引：真正的性能核武器

很多人优化 SQL 时只关注：

有没有走索引

但真正的高手关注的是：

有没有回表

例如：

SELECT id,name FROM user WHERE age = 20;

如果索引：

INDEX(age,name)

那么数据库甚至不需要回主键树。

因为：

索引叶子节点已经包含：

  • age
  • name
  • 主键id

这就是：

覆盖索引

执行计划里通常会出现：

Using index

这意味着：

查询直接在索引层完成。

很多高并发系统性能提升数倍。

本质原因并不是：

“用了索引”。

而是：

避免了回表随机IO。

十、order by 才是隐藏的性能杀手

很多系统慢 SQL：

问题并不在 where。

而在：

ORDER BY create_time DESC

如果排序无法利用索引。

数据库会：

filesort

很多人看到这个词以为：

“磁盘排序”。

实际上：

filesort 是 MySQL 的一种排序算法名。

但问题依旧严重。

因为：

大量数据排序意味着：

  • 内存消耗
  • 临时表
  • 磁盘 spill
  • CPU 飙升

尤其：

LIMIT 100000,20

这种深分页。

数据库往往需要：

先排序10万条
再丢弃前99980条

这才是很多分页接口越来越慢的根源。

不是数据库不行。

而是：

你在逼数据库做无意义劳动。

十一、索引下推（ICP）为什么重要？

MySQL 5.6 之后有个重要优化：

Index Condition Pushdown

简称：

ICP

很多人知道这个词。

但不知道它真正解决什么。

假设：

INDEX(a,b)

SQL：

WHERE a = 1 AND b LIKE '%xx'

以前：

数据库会：

先回表
再过滤 b

现在：

可以在索引层先过滤部分数据。

减少回表次数。

本质上：

ICP 的价值在于：

减少随机IO。

现代数据库优化。

很多时候优化的已经不是：

“算力”。

而是：

IO访问路径。

十二、为什么分页越翻越慢？

经典 SQL：

SELECT * FROM article ORDER BY id LIMIT 100000,10;

很多系统前几页很快。

到了后面：

突然卡死。

原因其实很简单：

数据库不是：

直接跳到100000

而是：

扫描前100000条
再丢弃

如果还伴随：

回表

那么性能会指数级恶化。

真正成熟的方案通常是：

游标分页

例如：

WHERE id > last_id
LIMIT 10

因为：

B+树最擅长的是顺序遍历。

而不是：

随机跳页

十三、为什么 count(*) 有时候也慢得离谱？

很多人以为：

COUNT(*)

只是：

统计一下行数

但在 InnoDB 里。

数据库通常需要：

扫描数据

因为：

InnoDB 并不保存精确总行数。

尤其：

WHERE status = 1

这种条件统计。

如果选择性差。

数据库甚至可能直接全表扫描。

很多后台统计接口：

  • UV
  • 订单数
  • 消息数
  • 未读数

最后都死在：

高频 count(*)

真正大型系统通常会：

  • 缓存计数
  • 异步聚合
  • Redis Counter
  • ClickHouse
  • ES 聚合

而不是把 MySQL 当实时统计引擎。

十四、索引不是越多越好

很多团队出现性能问题后。

第一反应：

加索引

然后：

继续加

最后：

一个表几十个索引。

结果：

查询确实快了一点。

但：

写入彻底崩了。

因为：

每次：

  • INSERT
  • UPDATE
  • DELETE

都需要维护索引树。

索引越多：

  • 页分裂越频繁
  • B+树调整越频繁
  • redo/undo 越大
  • buffer pool 压力越高

于是系统会出现：

“读优化把写拖死”

这是很多中大型系统常见问题。

十五、真正的高手怎么分析索引问题？

不是上来就：

EXPLAIN

然后看：

type = ref

就结束了。

真正完整的分析链路通常是：

1. 看执行计划

关注：

  • type
  • rows
  • filtered
  • extra

2. 看回表次数

核心问题：

随机IO有多少

3. 看数据分布

是否：

  • 倾斜
  • 热点
  • 低选择性

4. 看统计信息

是否失真。

5. 看 Buffer Pool 命中率

很多 SQL：

测试环境快。

线上慢。

本质原因：

缓存命中率不同

6. 看是否发生 filesort

排序往往比 where 更耗性能。

7. 看是否产生临时表

Using temporary

通常意味着：

额外资源消耗。

十六、索引优化的本质到底是什么？

很多人学 SQL 优化。

最后会陷入：

  • 背规则
  • 记口诀
  • 套模板

但数据库优化真正核心其实只有一句话：

减少数据访问量

一切优化本质都是：

  • 少扫描
  • 少回表
  • 少排序
  • 少随机IO
  • 少磁盘访问

因为：

CPU 的速度增长远远快于 IO。

现代数据库性能瓶颈。

绝大部分时候：

不是算不动。

而是：

数据搬不动。

十七、一个很多人忽略的真相

很多后端开发喜欢说：

数据库性能不行

但真实情况往往是：

数据库已经在拼命替程序员擦屁股。

优化器会：

  • 自动改写 SQL
  • 自动选择索引
  • 自动调整 Join 顺序
  • 自动评估成本
  • 自动做 ICP
  • 自动 MRR
  • 自动 Buffer 管理

它已经在尽最大努力。

真正的问题通常是：

业务层正在疯狂制造低效访问。

比如：

  • 深分页
  • 大宽表
  • select *
  • 无限制搜索
  • 非必要排序
  • ORM 自动生成垃圾 SQL

数据库并不是慢。

而是：

你在逼它做错误的事。

十八、最后总结：索引不生效，其实是“认知错位”

程序员经常认为：

我建了索引
数据库就应该使用

但数据库真正思考的是：

使用索引到底值不值

于是：

很多所谓“索引失效”。

本质其实是：

优化器拒绝做亏本生意。

而一个成熟工程师真正该学习的。

不是：

  • 死记规则
  • 背诵口诀
  • 强行 hint

而是：

从数据库视角理解成本。

因为：

当你真正理解：

  • IO
  • B+树
  • 回表
  • 数据分布
  • 统计信息
  • 成本模型

你会发现：

SQL 优化从来不是“语法问题”。

而是：

数据访问路径设计问题。

这也是：

初级 CRUD 工程师。

和真正数据库工程师之间。

最大的区别。

avatar
文章
阅读
粉丝