从表设计到磁盘 I/O：一套完整的 MySQL 性能优化与索引设计方法

很多 SQL 优化文章会从「给 WHERE 条件加索引」开始，但如果只停留在这一层，很容易形成一种错觉：慢 SQL = 缺索引，数据库优化 = 加索引。

实际上，数据库性能问题是一条完整链路上的结果：

表怎么设计 → SQL 怎么写 → 索引怎么组织 → B+ 树怎么定位 → 数据页怎么读取 → Buffer Pool 是否命中 → 最终产生多少 I/O。

真正理解这条链路以后，很多看似零散的知识点会自动串起来：为什么要做范式设计、为什么又要反范式；为什么 MySQL 选择 B+ 树而不是二叉树；为什么联合索引有最左匹配；为什么覆盖索引能减少回表；为什么索引越多反而可能越慢；以及为什么 SQL 优化最终绕不开磁盘 I/O。

本文基于一组关于数据库调优、范式、索引、B+ 树、Hash、数据页和磁盘 I/O 的文章进行重新整理，目标不是逐篇复述，而是形成一套可以直接用于实际开发和排查问题的知识体系。

一、先建立一个总框架：数据库优化到底在优化什么

数据库调优的直接目标通常可以归结为两个：

• 更低的响应时间
• 更高的吞吐量

但在实际系统里，“让数据库更快”过于模糊。真正进行性能分析时，需要先知道瓶颈发生在哪里。

可以把数据库优化拆成六个层次：

数据库选型
   ↓
表结构设计
   ↓
SQL 逻辑优化
   ↓
索引与执行计划优化
   ↓
缓存与内存优化
   ↓
数据库架构优化

对应到工程实践，大致可以这样理解：

层次	主要问题
DBMS 选型	当前业务更适合关系型、KV、搜索、列式还是其他存储
表结构	字段类型、主键、范式、冗余、冷热数据是否合理
SQL 逻辑	是否存在无效计算、重复扫描、不必要 JOIN、子查询问题
物理查询	是否命中合理索引、访问路径是否正确、扫描行数是否过大
缓存层	热点数据是否反复打数据库，Buffer Pool 是否充分利用
架构层	是否需要读写分离、分库分表、归档、数据仓库等

所以，一条 SQL 很慢时，不应该第一反应就是：

加索引！

更合理的问题是：

慢在哪里？
是扫描数据太多？
是 JOIN 太重？
是索引不合适？
是回表太多？
是随机 I/O 太多？
还是表本身已经不适合当前业务？

1.1 如何发现数据库瓶颈

常见的入口有四类：

1. 用户反馈
   页面慢、接口超时、报表卡顿，往往是最直接的信号。

2. 应用与数据库日志
   慢 SQL、锁等待、超时、死锁、连接池耗尽等问题都可以留下线索。

3. 服务器资源监控
   CPU、内存、磁盘 I/O、网络吞吐是判断资源瓶颈的重要指标。

4. 数据库内部状态
   活跃会话、锁等待、事务状态、Buffer Pool、执行计划等指标可以进一步定位问题。

数据库优化不是“凭感觉改 SQL”，而是一个：

观察 → 定位 → 修改 → 验证

的闭环。

---

二、表设计：范式不是教条，而是控制数据依赖关系

性能优化其实从建表那一刻就已经开始了。

如果表设计本身有问题，后面再怎么优化 SQL，往往也只能修修补补。

2.1 什么是范式

关系型数据库常见范式包括：

1NF → 2NF → 3NF → BCNF → 4NF → 5NF

在业务开发中，最常接触的是：

• 1NF
• 2NF
• 3NF
• BCNF
• 反范式

通常业务表设计以 3NF 作为重要参考，而不是机械追求更高范式。

2.2 1NF：字段必须保持原子性

第一范式关注的是：

一个字段应该表达一个不可继续拆分的属性。

例如：

错误：
user_address = "浙江省杭州市西湖区"

视业务需要拆分：
province
city
district

当然，“是否应该拆”仍然取决于业务是否需要独立查询和处理这些属性。

2.3 2NF：非主属性必须完全依赖候选键

第二范式主要解决部分依赖。

假设有一张球员比赛表：

player_game(
    player_id,
    game_id,
    player_name,
    age,
    game_time,
    game_address,
    score
)

候选键是：

(player_id, game_id)

但是：

player_id → player_name, age
game_id   → game_time, game_address

也就是说，一部分字段只依赖联合键中的一部分。

这会造成：

• 数据冗余
• 插入异常
• 删除异常
• 更新异常

更合理的结构是：

player
- player_id
- player_name
- age

game
- game_id
- game_time
- game_address

player_game
- player_id
- game_id
- score

一句话理解 2NF：

一张表尽量只描述一个独立的业务对象或关系。

2.4 3NF：消除非主属性之间的传递依赖

假设：

player_id → team_name
team_name → coach_name

那么：

player_id → team_name → coach_name

coach_name 是通过另一个非主属性 team_name 间接依赖主键的，这就是传递依赖。

更合理的设计：

player
- player_id
- player_name
- team_id

team
- team_id
- team_name
- coach_name

可以把前三个范式简单记成：

1NF：字段不可再分
2NF：不能只依赖联合键的一部分
3NF：不能通过其他普通字段间接依赖主键

---

三、3NF 仍然不够：BCNF 与反范式

3.1 为什么符合 3NF 仍然可能有问题

假设一张仓库库存表：

warehouse_keeper(
    warehouse_name,
    keeper_name,
    product_name,
    quantity
)

业务规则：

warehouse_name ↔ keeper_name
(warehouse_name, product_name) → quantity

即：

• 一个仓库只有一个管理员；
• 一个管理员只管理一个仓库。

候选键可能有：

(warehouse_name, product_name)
(keeper_name, product_name)

这张表即使满足 3NF，仍然可能出现：

• 新仓库还没有商品时无法插入；
• 修改管理员要改多条记录；
• 最后一件商品删除时，仓库与管理员信息也可能一起丢失。

BCNF 在 3NF 的基础上继续处理候选键之间的依赖问题。

可以拆成：

warehouse
- warehouse_name
- keeper_name

inventory
- warehouse_name
- product_name
- quantity

3.2 为什么又需要反范式

数据库设计并不是范式越高越好。

范式化的优势是：

• 冗余少
• 数据一致性高
• 更新逻辑清晰

代价是：

• 表会越来越多
• JOIN 增多
• 查询链路更长
• 查询时可能产生更多随机 I/O

反范式的核心思想就是：

允许可控冗余，用空间换时间。

例如：

product_comment
- comment_id
- product_id
- user_id
- comment_text

如果展示评论时每次都需要：

SELECT
    p.comment_text,
    p.comment_time,
    u.user_name
FROM product_comment p
LEFT JOIN user u
    ON p.user_id = u.user_id
WHERE p.product_id = 10001
ORDER BY p.comment_id DESC
LIMIT 1000;

那么可以在评论表中冗余：

user_name

把多表 JOIN 变成单表查询。

这就是典型的：

更多存储空间
        ↓
减少 JOIN
        ↓
减少访问路径
        ↓
降低查询成本

3.3 反范式适合什么场景

比较常见的场景：

• 订单历史快照
• 收货人姓名、电话、地址
• 报表宽表
• OLAP / 数据仓库
• 高频读取、低频修改的数据
• 可以接受最终一致性的场景

例如订单中的地址不应该始终指向用户当前地址。

用户今天修改地址，不应该改变三年前订单中的收货地址。

因此订单表保存：

receiver_name
receiver_phone
receiver_address

虽然属于冗余，但这种冗余本身就是业务数据。

3.4 范式与反范式的本质

不要问：

范式和反范式谁更正确？

应该问：

这个数据更偏向写一致性，还是读性能？
这个字段是事实本身，还是可以重新计算出来的数据？
更新频率高不高？
冗余数据如何同步？

数据库设计从来不是追求“最标准”，而是追求：

成本可控的正确设计。

---

四、索引到底是什么

数据库索引可以类比一本书的目录。

如果没有目录，要找一个知识点，只能：

第 1 页
第 2 页
第 3 页
……

数据库也是一样。

没有合适索引时，最典型的代价就是：

全表扫描

索引本质上是：

帮助数据库快速定位数据的数据结构。

但索引不是免费的。

增加索引意味着同时增加：

• 存储空间
• INSERT 维护成本
• UPDATE 维护成本
• DELETE 维护成本
• Buffer Pool 压力
• 优化器索引选择成本

因此：

索引不是越多越好，而是让最重要的查询路径变短。

---

五、什么时候应该建索引

以下场景通常值得优先考虑索引。

5.1 唯一字段

例如：

username
email
order_no

可以使用：

CREATE UNIQUE INDEX uk_user_email
ON user(email);

索引在这里同时承担：

查询加速 + 数据约束

5.2 高频 WHERE 条件

例如：

SELECT *
FROM product_comment
WHERE user_id = 785110;

如果表很大，user_id 又经常作为查询条件，那么它通常是非常明确的索引候选列。

5.3 UPDATE / DELETE 的过滤条件

例如：

UPDATE product_comment
SET product_id = 10002
WHERE comment_id = 123456;

数据库首先还是要：

找到这条数据

再做 UPDATE。

因此 UPDATE / DELETE 的 WHERE 条件同样需要关注索引。

5.4 JOIN 条件

例如：

SELECT ...
FROM order_detail d
JOIN orders o
    ON d.order_id = o.id
WHERE o.user_id = ?;

需要重点关注：

d.order_id
o.id
o.user_id

并且 JOIN 两端字段类型应该一致。

不要一边：

BIGINT

另一边：

VARCHAR

然后指望优化器替你擦屁股。

5.5 GROUP BY / ORDER BY

B+ 树索引天然具有顺序性，因此合适的索引可以降低排序成本。

例如：

SELECT user_id, COUNT(*)
FROM product_comment
GROUP BY user_id;

user_id 上有索引时，可以更高效地完成分组。

5.6 DISTINCT

SELECT DISTINCT user_id
FROM product_comment;

如果 user_id 已有合适索引，数据库可以利用索引中的有序数据完成去重。

---

六、什么时候不要急着建索引

6.1 表本身非常小

几十、几百行的数据，即使全表扫描也非常快。

这时：

扫描表

可能比：

访问索引 → 再访问数据

更简单。

文章中给出了“小于 1000 行”这样的经验例子，但它并不是绝对阈值。

真正应该关注的是：

• 表大小
• 数据页数量
• 查询频率
• Buffer Pool 命中情况
• 实际执行计划

6.2 区分度太差

典型例子：

gender = 0 / 1
deleted = 0 / 1
status = 0 / 1

如果一个条件命中整张表 50% 甚至 90% 的数据，优化器很可能认为：

走索引再回表

还不如：

直接全表扫描

但是要注意：

低基数字段不能简单等价于“不能建索引”。

如果数据分布极度倾斜，例如：

100 万人
999990 个 value = 0
10 个 value = 1

而业务经常查询：

WHERE value = 1

那么这个字段仍然可能非常适合索引。

真正决定索引价值的不是“有几个值”，而是：

目标查询最终要过滤掉多少数据。

6.3 高频更新字段

如果一个字段每秒被大量更新，而它的索引又很少被查询使用，那么维护这个索引可能得不偿失。

6.4 查询根本不用这个字段

SELECT 出来的字段并不等于必须建索引。

例如：

SELECT id, name, age, address
FROM user
WHERE user_id = ?;

真正负责定位的是：

user_id

不能因为 SELECT 里出现了 address 就给 address 建索引。

---

七、索引的几种常见分类

7.1 从约束角度

常见有：

• 普通索引
• 唯一索引
• 主键索引
• 全文索引

7.2 从物理组织方式

可以理解为：

• 聚集索引
• 二级索引 / 辅助索引

在 InnoDB 中，主键对应聚簇 B+ 树。

二级索引的叶子节点保存的通常不是完整数据行，而是：

二级索引键 + 主键值

因此很多查询会经历：

二级索引
   ↓
找到主键
   ↓
主键 B+ 树
   ↓
找到完整记录

这个过程就是：

回表。

---

八、联合索引为什么强调最左匹配

假设建立：

CREATE INDEX idx_xyz
ON table_name(x, y, z);

索引内部并不是：

分别维护 x、y、z 三棵树

而是按照组合键排序：

先 x
x 相同再 y
x、y 都相同再 z

可以想象成电话簿：

姓 → 名 → 其他信息

因此它天然适合：

WHERE x = ?

WHERE x = ? AND y = ?

WHERE x = ? AND y = ? AND z = ?

但如果直接查询：

WHERE y = ?

就失去了 x 这一层的有序前提。

这就是所谓：

最左前缀原则。

8.1 SQL 条件书写顺序不等于索引顺序

例如：

WHERE z = 3
  AND x = 1
  AND y = 2

优化器通常可以进行条件重写。

因此真正关键的是：

联合索引自身的列顺序

而不是你在 WHERE 中把 x 写在第一行还是第三行。

8.2 联合索引的顺序怎么设计

通常要综合考虑：

• 等值条件
• 过滤能力
• 范围条件
• ORDER BY
• GROUP BY
• 查询覆盖

不要机械套：

区分度最高的永远放最左

因为真实索引设计是为具体访问模式服务的。

---

九、为什么 MySQL 主要使用 B+ 树做索引

理解索引最关键的一点不是：

B+ 树查找复杂度低

而是：

数据库真正昂贵的是磁盘 I/O。

9.1 为什么普通二叉树不适合数据库索引

二叉搜索树的问题在于：

每个节点最多 2 个孩子

即使使用 AVL 等平衡结构，在海量数据下，树依然容易变得很高。

如果：

访问一个树节点
≈
读取一个磁盘页

那么树越高意味着：

更多磁盘 I/O

数据库更喜欢：

矮胖

而不是：

瘦高

9.2 B 树解决了什么

B 树是多路搜索树。

一个节点不再只有两个分支，而是可以保存很多键和很多子节点。

假设一个节点可以指向 100 个子节点，那么 3 层结构理论上就已经可以覆盖非常大的数据量。

树高下降意味着：

根节点
  ↓ 1 次 I/O
中间节点
  ↓ 1 次 I/O
叶子节点
  ↓ 1 次 I/O

定位数据可能只需要很少的页面读取。

9.3 B+ 树相比 B 树的关键区别

B+ 树中：

• 非叶子节点主要保存索引键和页面指针；
• 数据最终位于叶子节点；
• 叶子节点之间保持有序链式连接。

因此它有几个明显优势。

优势一：树可以更矮

中间节点不需要携带完整数据，同一个数据页可以塞进更多索引项：

单页更多 key
    ↓
分叉更多
    ↓
树高更低
    ↓
I/O 更少

优势二：查询性能更稳定

最终数据都在叶子节点，所以通常都会走：

Root → Branch → Leaf

查询路径更可预测。

优势三：范围查询非常适合

例如：

WHERE id BETWEEN 10000 AND 20000

先找到：

id = 10000

然后沿着叶子页继续向后扫描即可。

这也是 B+ 树相比 Hash 更适合通用数据库索引的重要原因。

---

十、Hash 索引：等值查询很快，但能力太单一

Hash 的基本模型：

key
 ↓
Hash(key)
 ↓
bucket
 ↓
record

理想情况下，等值查询可以接近：

O(1)

例如：

key = user_10001

直接通过 Hash 定位 bucket。

10.1 Hash 冲突

不同 key 可能计算出相同 Hash 值：

keyA ─┐
      ├─→ bucket 101
keyB ─┘

此时需要继续在 bucket 中比较具体 key。

冲突越多，性能越差。

10.2 Hash 索引为什么没有取代 B+ 树

因为 Hash 数据天然无序。

它很适合：

WHERE id = ?

但不适合：

WHERE id > ?

WHERE id BETWEEN ? AND ?

也无法天然利用：

ORDER BY id

对于联合键，也不存在 B+ 树那种自然的最左前缀扫描能力。

可以简单对比：

能力	B+ 树	Hash
等值查询	好	非常好
范围查询	好	不适合
ORDER BY	可利用顺序	不适合
最左前缀	支持	不具备同类能力
模糊前缀匹配	部分场景可用	不适合
通用数据库索引	很适合	场景受限

10.3 InnoDB 的自适应 Hash

InnoDB 的主要索引结构仍然是 B+ 树。

但对于一些非常热点的访问模式，存储引擎可以自动建立自适应 Hash 结构，加速热点页面定位。

可以把它粗略理解成：

B+ 树仍然是主结构
        +
热点路径增加 Hash 快速通道

这不是让开发者手工把 InnoDB 索引改成 Hash，而是存储引擎内部的优化策略。

---

十一、从数据页理解 B+ 树，很多问题一下就通了

数据库不会每次只从磁盘读取一行。

真正的最小 I/O 单位是：

页 Page

InnoDB 默认页大小通常为：

16KB

11.1 InnoDB 的存储层级

可以理解为：

Tablespace
   ↓
Segment
   ↓
Extent
   ↓
Page
   ↓
Row

其中：

• 一个页可以存多行；
• 一个区由多个连续页组成；
• 段由多个区组成；
• 表空间是更高层的逻辑容器。

11.2 数据页为什么重要

当你查询一条记录：

SELECT *
FROM user
WHERE id = 100;

数据库不是：

从磁盘读取这一行

而是：

找到这一行所在的数据页
        ↓
把整个页读入内存
        ↓
在页内找到具体记录

所以数据库优化里常常不是简单考虑：

扫描多少行

还要考虑：

最终访问多少页

11.3 数据页内部也有自己的索引机制

一个数据页内部会保存：

• 文件头
• 页头
• 用户记录
• 空闲空间
• 页目录
• 文件尾等结构

记录之间可以使用链式方式组织，但纯链表查找太慢，因此页目录会将记录进行分组，并通过槽位进行近似二分定位。

于是一次 B+ 树查询可以理解为：

1. 从根页开始
2. 逐层找到目标叶子页
3. 将目标页加载到 Buffer Pool
4. 通过页目录快速定位记录所在分组
5. 在分组内部找到最终记录

这就是索引从“抽象数据结构”落到磁盘页之后的真实含义。

---

十二、聚簇索引与二级索引为什么会产生“回表”

假设：

PRIMARY KEY(id)

INDEX idx_user_id(user_id)

执行：

SELECT id, user_id, user_name, address
FROM user
WHERE user_id = 10001;

二级索引大致可以理解为：

user_id → id

第一步：

idx_user_id
↓
得到主键 id

第二步：

PRIMARY KEY B+ tree
↓
得到 user_name、address 等完整记录

第二次访问主键树就是回表。

如果一次查询匹配 10 万条记录，那么大量回表可能变成非常昂贵的随机访问。

这也是为什么：

索引命中了

并不等于：

SQL 一定快

---

十三、覆盖索引：为什么宽索引有时反而更快

假设查询：

SELECT user_id, product_id, comment_text
FROM product_comment
WHERE user_id = ?;

只有：

INDEX(user_id)

时，需要：

二级索引 → 主键 → 回表

如果建立：

INDEX(user_id, product_id, comment_text)

那么查询需要的列已经全部在索引里。

这时可以：

直接从索引返回结果

这种情况就是：

覆盖索引。

13.1 窄索引 vs 宽索引

窄索引：

(user_id)

优点：

• 单页可以存更多索引项；
• B+ 树更紧凑；
• 写入维护成本更低。

宽索引：

(user_id, product_id, comment_text)

优点：

• 可能减少回表；
• 特定查询可以直接由索引完成。

缺点：

• 索引更大；
• 单页容纳 key 更少；
• 占用更多 Buffer Pool；
• INSERT / UPDATE 维护成本更高；
• 更容易产生页分裂。

所以覆盖索引不是：

把 SELECT 所有字段全部塞进联合索引

而应该判断：

减少的回表成本
是否大于
索引膨胀和维护成本

---

十四、选择性：决定一个索引能过滤掉多少数据

索引最核心的价值不是：

这个字段有没有索引

而是：

通过这个条件最终能排除多少数据

可以用一个简单概念理解：

选择性 ≈ 满足条件的记录数 / 总记录数

假设：

gender = male          → 100%
team_id = 1001         → 54%
height = 2.08          → 14%
name = '某个具体姓名' → 2%

显然：

name

比：

gender

有更强的过滤能力。

14.1 联合条件也要看相关性

假设：

city
area_code

两个字段高度相关。

把它们组合在一起，不一定能大幅提高过滤能力。

理想的联合条件应该尽量同时满足：

业务高频
+
具有过滤能力
+
字段之间不是完全重复表达同一信息

---

十五、所谓“三星索引”，本质是在优化三件事

一种理想化的索引设计思路，可以归纳为三个目标。

第一颗星：尽量缩小扫描范围

把 WHERE 中高价值的等值条件组织到索引前部。

目标：

最小化需要扫描的索引片

第二颗星：避免额外排序

将常用的：

ORDER BY
GROUP BY

顺序纳入索引设计。

目标：

尽量利用 B+ 树已有顺序

而不是重新 filesort。

第三颗星：减少回表

让高频查询尽量被索引覆盖。

目标：

Index Only / Covering Index

15.1 为什么不可能给所有 SQL 都做“三星索引”

因为理想查询索引往往意味着：

更多索引列
+
更多联合索引
+
更大的索引文件

查询可能变快，但写入成本会变成：

INSERT 一行
↓
不仅修改数据页
↓
还要维护 N 棵索引树

因此：

对单条 SELECT 最优的索引，不一定是对整个系统最优的索引。

数据库设计最终是在平衡：

查询性能
写入性能
磁盘空间
Buffer Pool
维护复杂度

---

十六、常见索引失效场景

“已经建索引”与“执行时会使用索引”完全是两件事。

16.1 对索引列做计算

不推荐：

WHERE id + 1 = 1000

更合理：

WHERE id = 999

原则：

尽量让索引列保持原样参与比较。

16.2 对索引列套函数

例如：

WHERE DATE(create_time) = '2026-08-11'

这种写法通常会破坏普通 B+ 树索引直接按照 create_time 排序定位的能力。

更适合改成范围：

WHERE create_time >= '2026-08-11 00:00:00'
  AND create_time <  '2026-08-12 00:00:00'

这类 SQL 重写非常重要。

16.3 LIKE 前面是通配符

可以利用前缀有序性：

WHERE name LIKE 'Mario%'

但：

WHERE name LIKE '%Mario'

无法从字符串最左侧开始定位，普通 B+ 树索引通常很难发挥作用。

16.4 OR 两侧条件差异太大

例如：

WHERE indexed_col = ?
   OR no_index_col = ?

优化器可能直接选择全表扫描。

有些场景可以使用 index merge，但它并不意味着“OR 随便写都没问题”。

对于核心 SQL，仍然应该用 EXPLAIN 看真实执行计划。

16.5 联合索引缺失左侧列

索引：

(a, b, c)

查询：

WHERE b = ? AND c = ?

通常无法像：

WHERE a = ?

那样直接使用最左前缀进行定位。

---

十七、Buffer Pool：数据库为什么不是每次都读磁盘

磁盘 I/O 相比内存访问昂贵得多。

因此 InnoDB 会使用 Buffer Pool 缓存：

• 数据页
• 索引页
• 热点页面
• 部分内部结构

执行查询时，大致过程：

需要 Page X
    ↓
Buffer Pool 里有？
   / \
 是   否
 |    |
直接  从存储读取
访问  加入 Buffer Pool

这就是数据库性能中非常重要的一条规律：

热点数据在哪里，比它理论上在磁盘的什么位置更重要。

17.1 脏页

UPDATE 并不意味着每次都立即把数据同步写回最终数据页。

修改通常先发生在 Buffer Pool 中。

被修改、尚未与磁盘数据页保持一致的页面称为：

Dirty Page

数据库会通过 checkpoint 等机制分批刷新。

这样可以避免：

每次修改一次
就做一次昂贵的随机磁盘写

---

十八、随机 I/O 与顺序 I/O 的差距

传统磁盘访问中：

随机读取一个页面

往往需要：

• 寻道
• 旋转
• 等待
• 传输

如果一次只随机读一个页，成本很高。

但是顺序扫描时：

Page 100
Page 101
Page 102
Page 103
...

数据库可以批量读取连续页面。

因此经常出现一种有趣现象：

读取 1 个随机页

与：

顺序读取一批页

相比，后者平均到单页上的成本可能低得多。

这也是为什么优化器有时会判断：

返回数据比例太高
↓
走二级索引需要大量随机回表
↓
不如直接顺序扫描整张表

于是你会看到：

明明有索引
却没有使用

这不一定是优化器傻了。

很多时候，它是在做：

随机 I/O 与顺序 I/O 的成本比较。

---

十九、为什么“命中索引”仍然可能很慢

一条 SQL：

possible_keys 有值
key 也有值

只能证明：

使用了某个索引

不能证明它是高效查询。

还要继续关注：

• 扫描多少索引记录
• 返回多少记录
• 是否大量回表
• 是否 filesort
• 是否临时表
• 是否多表嵌套循环
• 是否读取大量数据页
• 是否命中 Buffer Pool

例如一个查询：

WHERE status = 1

即使 status 有索引，但如果：

95% 数据 status = 1

那么：

索引扫描 + 95% 回表

可能比全表扫描更慢。

---

二十、把这些知识落地：一套可执行的慢 SQL 优化流程

实际开发时，可以按照下面的顺序排查。

Step 1：先明确业务目标

先问：

这条 SQL 正常应该返回多少行？
现在返回多少行？
允许的响应时间是多少？
每秒调用多少次？

没有业务目标就没有所谓“快”与“慢”。

Step 2：检查 SQL 本身

重点看：

• SELECT 是否取了大量无用列
• 是否存在 SELECT *
• WHERE 是否足够提前过滤
• 是否存在无意义 DISTINCT
• 是否存在不必要 JOIN
• 是否对索引列做函数
• 是否有大范围 LIKE
• 是否可以拆分复杂查询

Step 3：看执行计划

至少关注：

访问方式
使用索引
估算扫描行
过滤比例
排序
临时表
JOIN 顺序

不要只看：

key != NULL

Step 4：分析索引

判断：

现有单列索引能否合并成联合索引？
联合索引顺序是否与查询模式一致？
是否存在高价值覆盖索引？
是否存在大量无效索引？
是否因为字段区分度太差导致索引收益低？

Step 5：判断是否有大量回表

例如：

SELECT *
FROM order_detail
WHERE shop_id = ?
  AND biz_date BETWEEN ? AND ?;

即使：

(shop_id, biz_date)

命中了索引，如果结果有几十万行，又查询大量字段，回表仍然可能非常重。

此时应该重新思考：

• 返回的数据是不是太多
• 能否分页
• 能否做覆盖索引
• 能否做汇总表
• 是否应该走离线报表

Step 6：看表设计

如果一个查询永远需要 JOIN 七八张表才能得到常用页面数据，那么问题可能已经不再是索引，而是：

读模型设计

可以考虑：

• 冗余字段
• 宽表
• 汇总表
• 历史快照
• Redis
• ES
• OLAP

Step 7：看 I/O 与缓存

高频数据是否能够稳定命中 Buffer Pool？

如果数据集远大于内存，随机访问又非常多，那么再漂亮的 SQL 也可能频繁打磁盘。

Step 8：最后再考虑架构级手段

只有单库本身已经到了合理极限，才继续考虑：

• 读写分离
• 分区
• 垂直拆分
• 水平分片
• 冷热分离
• 历史数据归档

不要一上来就：

分库分表

否则只是把一个 SQL 问题升级成一个分布式系统问题。

---

二十一、一个更实用的索引设计原则

如果让我把整套内容压缩成实际工作中的几个原则，我会保留下面这些。

原则 1：索引首先服务于访问模式，而不是字段本身

不要问：

这个字段要不要建索引？

应该问：

哪些 SQL 会用它？
怎么用？
一次返回多少数据？
调用频率多高？

原则 2：过滤能力比“字段类型”更重要

不要看到：

status
gender
deleted

就机械判断“不适合索引”。

先看：

真实数据分布
+
业务查询目标

原则 3：优先设计联合索引，而不是无限堆单列索引

一个高频 SQL：

WHERE tenant_id = ?
  AND biz_type = ?
  AND biz_date BETWEEN ? AND ?

更应该思考：

(tenant_id, biz_type, biz_date)

而不是无脑建立：

idx_tenant_id
idx_biz_type
idx_biz_date

原则 4：索引列越少越好，但够用更重要

这是一个平衡：

太窄 → 回表多
太宽 → 索引膨胀

最合理的是：

用最少的列覆盖最重要的访问路径。

原则 5：不要迷信“索引命中”

最终性能来自：

扫描范围
+
回表次数
+
排序成本
+
JOIN 成本
+
访问页数量
+
缓存命中

不是来自：

EXPLAIN 里 key 有值

原则 6：优化一定要验证

任何优化都应该：

优化前执行计划
优化前耗时
优化前扫描规模

          ↓

修改 SQL / 索引

          ↓

优化后执行计划
优化后耗时
优化后扫描规模

没有对比的优化，只能叫猜。

---

二十二、最终把整个知识链串起来

最后把全文压缩成一条链：

业务查询模式
      ↓
决定表结构
      ↓
范式控制一致性
反范式控制查询成本
      ↓
索引决定如何定位数据
      ↓
B+ 树降低树高与随机 I/O
      ↓
联合索引利用有序前缀
      ↓
覆盖索引减少回表
      ↓
数据最终按 Page 读取
      ↓
Buffer Pool 降低磁盘访问
      ↓
优化器比较不同访问路径成本
      ↓
选择最终执行计划

所以 SQL 性能优化真正优化的，并不是一句 SQL 文本，而是：

让数据库以更低的成本找到、更少的数据页，并尽可能少做无意义的随机访问。

当你开始从“访问路径”和“I/O 成本”看 SQL，而不是只从“语法”和“有没有索引”看 SQL 时，数据库优化才算真正进入了下一层。

---

二十三、日常排查 Checklist

最后给一份可以直接放在工作里的清单。

[ ] 这条 SQL 一次应该返回多少行？
[ ] 是否 SELECT 了不需要的字段？
[ ] WHERE 是否足够提前过滤数据？
[ ] WHERE 条件列是否有合适索引？
[ ] 是否对索引列做了函数或表达式？
[ ] LIKE 是否以 % 开头？
[ ] 联合索引是否符合主要查询模式？
[ ] 联合索引最左侧字段是否可用？
[ ] 是否存在大量回表？
[ ] 是否可以做覆盖索引？
[ ] 字段选择性是否足够？
[ ] 是否存在 GROUP BY / ORDER BY filesort？
[ ] JOIN 字段类型是否一致？
[ ] JOIN 表是否过多？
[ ] 是否存在大量无效单列索引？
[ ] 是否读取了远超业务需要的数据？
[ ] 是否应该分页或做汇总表？
[ ] 是否应该采用反范式或历史快照？
[ ] 热点数据是否能稳定命中 Buffer Pool？
[ ] 优化前后是否使用 EXPLAIN 和耗时进行对比？