引言

“过早优化是万恶之源。但当性能问题真正出现时,优化就是救命稻草。” —— Donald Knuth

在前三篇文章中,我们学习了索引、事务、锁的原理。但光有理论还不够,如何定位和优化慢查询?

想象这样的场景:

凌晨3点,你被一通电话吵醒:
"数据库快挂了,所有查询都超时!"

你打开监控,发现:
- CPU 100%
- 慢查询日志爆满
- 某个SQL执行了10秒还没返回

如何快速定位问题?如何优化这个慢查询?

这就是查询优化的核心价值:让慢查询变快,让系统起死回生。

今天,我们从第一性原理出发,深度剖析MySQL的查询优化:

SQL执行流程:
  客户端 → 连接器 → 解析器 → 优化器 → 执行器 → 存储引擎
          ↓        ↓        ↓        ↓        ↓
        权限检查  语法解析  生成计划  执行查询  返回数据

性能优化:
  慢查询 → EXPLAIN → 找到瓶颈 → 优化索引 → 改写SQL → 性能飞跃
  10秒    分析      全表扫描    建索引     覆盖索引  10ms

我们还将通过10个真实案例,将慢查询从10秒优化到10ms,性能提升1000倍。

一、SQL执行流程:从SQL到结果集

理解查询优化,首先要理解SQL是如何执行的。

1.1 MySQL的架构:两层结构

┌─────────────────────────────────────────────────────────────┐
│                     MySQL Server层                          │
├─────────────────────────────────────────────────────────────┤
│  连接器    解析器    优化器    执行器                        │
│  ↓         ↓        ↓        ↓                              │
│  权限验证  语法解析  生成计划  执行查询                       │
└─────────────────────────────────────────────────────────────┘
                        ↓
┌─────────────────────────────────────────────────────────────┐
│                     存储引擎层                               │
├─────────────────────────────────────────────────────────────┤
│  InnoDB    MyISAM    Memory    Archive                      │
│  ↓         ↓        ↓          ↓                            │
│  事务支持  不支持    内存存储   压缩存储                      │
└─────────────────────────────────────────────────────────────┘

Server层与存储引擎的职责分工:

层级职责核心组件
Server层SQL处理、查询优化连接器、解析器、优化器、执行器
存储引擎层数据存储、索引实现InnoDB、MyISAM等

1.2 SQL执行的5个阶段

示例SQL:

SELECT name, age FROM users WHERE id = 123;

执行流程:

阶段1: 连接器
  ├─ 验证用户名密码
  ├─ 查询权限（users表的SELECT权限）
  └─ 建立连接（长连接 or 短连接）

阶段2: 解析器
  ├─ 词法分析: SELECT、name、age、FROM、users...
  ├─ 语法分析: 检查语法是否正确
  ├─ 生成解析树（AST, Abstract Syntax Tree）
  └─ 检查表和列是否存在

阶段3: 优化器
  ├─ 决定使用哪个索引（如果有多个索引）
  ├─ 决定表的连接顺序（如果是JOIN）
  ├─ 决定是否使用覆盖索引
  ├─ 决定是否使用索引下推
  └─ 生成执行计划（最优的执行路径）

阶段4: 执行器
  ├─ 调用存储引擎接口
  ├─ 读取数据（通过索引或全表扫描）
  ├─ 过滤数据（WHERE条件）
  └─ 返回结果集

阶段5: 存储引擎
  ├─ 读取索引（B+树）
  ├─ 读取数据页（Buffer Pool）
  └─ 返回数据给执行器

时间分布:

总耗时: 10ms

连接器:   0.1ms   (1%)
解析器:   0.5ms   (5%)
优化器:   1ms     (10%)
执行器:   8ms     (80%)   ← 主要耗时
存储引擎: 0.4ms   (4%)

结论:
  查询性能的核心在执行器
  优化的关键是减少执行器的工作量

1.3 查询缓存:已废弃的功能

MySQL 5.7及之前的版本有查询缓存:

-- 开启查询缓存
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 128MB;

-- 查询1
SELECT * FROM users WHERE id = 123;
-- 结果缓存到query_cache

-- 查询2（完全相同的SQL）
SELECT * FROM users WHERE id = 123;
-- 直接从缓存返回,跳过解析、优化、执行

-- 查询3（SQL不同）
SELECT * FROM users WHERE id = 456;
-- 缓存未命中,正常执行

查询缓存的问题:

1. 失效频繁:
   只要表有任何更新,所有缓存全部失效
   UPDATE users SET age = 25 WHERE id = 1;
   → users表的所有查询缓存失效

2. 命中率低:
   SQL必须完全相同才能命中（包括空格、大小写）
   SELECT * FROM users WHERE id = 123;  -- 缓存1
   SELECT * FROM users WHERE id=123;    -- 缓存2（未命中缓存1）

3. 维护成本高:
   每次查询都要检查缓存,维护缓存键值对
   高并发下成为性能瓶颈

结论:
  MySQL 8.0已完全移除查询缓存
  推荐使用应用层缓存（Redis、Memcached）

二、EXPLAIN:查询优化的瑞士军刀

定位慢查询的第一步:使用EXPLAIN分析执行计划。

2.1 EXPLAIN基础用法

-- 分析执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

输出:
+----+-------------+--------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_user_id   | idx_user_id  | 8       | const | 100  | NULL  |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+-------+

关键字段:
  type:     访问类型（const、ref、range、index、ALL）
  key:      实际使用的索引
  rows:     预计扫描的行数
  Extra:    额外信息（Using index、Using filesort等）

EXPLAIN的三种格式:

-- 1. 传统格式（表格）
EXPLAIN SELECT ...;

-- 2. JSON格式（更详细）
EXPLAIN FORMAT=JSON SELECT ...;

-- 3. TREE格式（MySQL 8.0.16+,树状结构）
EXPLAIN FORMAT=TREE SELECT ...;

输出:
-> Index lookup on orders using idx_user_id (user_id=123)  (cost=50.00 rows=100)

2.2 EXPLAIN输出字段详解

1. id: 查询的序列号

-- 简单查询: id=1
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 子查询: id不同,数字越大越先执行
EXPLAIN SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE age > 18);

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | key           | ref     | rows    | Extra                   |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | orders | ALL   | NULL          | NULL    | 100000  | Using where             |
|  2 | SUBQUERY    | users  | range | idx_age       | NULL    | 5000    | Using where; Using index|
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

执行顺序:
  id=2先执行（子查询）
  id=1后执行（主查询）

-- UNION: id相同,从上到下执行
EXPLAIN SELECT * FROM users WHERE id = 1
UNION
SELECT * FROM users WHERE id = 2;

+----+--------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type  | table  | type  | key           | ref     | rows    | Extra |
+----+--------------+--------+-------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY      | users  | const | PRIMARY       | const   | 1       | NULL  |
|  2 | UNION        | users  | const | PRIMARY       | const   | 1       | NULL  |
|NULL| UNION RESULT |<union1,2>|ALL  | NULL          | NULL    | NULL    | NULL  |
+----+--------------+--------+-------+---------------+---------+---------+-------+------+-------+

2. select_type: 查询类型

select_type说明示例
SIMPLE简单查询（不包含子查询和UNION）SELECT * FROM users
PRIMARY最外层查询主查询
SUBQUERY子查询WHERE id IN (SELECT ...)
DERIVED派生表（FROM子查询）FROM (SELECT ...) AS t
UNIONUNION中的第二个及后续查询SELECT ... UNION SELECT ...
UNION RESULTUNION的结果集UNION去重

3. type: 访问类型（性能从好到坏）

性能排序:
  system > const > eq_ref > ref > range > index > ALL
  ↑                                                 ↓
  最快                                              最慢

各类型详解:

system: 表只有一行（系统表）

EXPLAIN SELECT * FROM mysql.time_zone LIMIT 1;

type: system
说明: 表只有一行,最快

const: 主键或唯一索引等值查询

EXPLAIN SELECT * FROM users WHERE id = 1;

type: const
说明:
  通过主键或唯一索引查询,最多返回1行
  MySQL会将查询转换为常量
  性能极好

eq_ref: 唯一索引连接

EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;

type: eq_ref
说明:
  连接时使用主键或唯一索引
  对于前表的每一行,后表最多返回1行
  性能很好

ref: 非唯一索引等值查询

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

type: ref
说明:
  使用非唯一索引等值查询
  可能返回多行
  性能良好

range: 索引范围查询

EXPLAIN SELECT * FROM orders
WHERE created_time BETWEEN '2024-01-01' AND '2024-12-31';

type: range
说明:
  使用索引进行范围查询（BETWEEN、>、<、IN）
  性能中等

index: 索引全扫描

EXPLAIN SELECT id FROM orders;

type: index
说明:
  扫描整个索引树
  比全表扫描快（索引通常比数据小）
  性能较差

ALL: 全表扫描

EXPLAIN SELECT * FROM orders WHERE amount > 100;
-- 如果amount没有索引

type: ALL
说明:
  扫描整个表
  性能最差
  需要优化

性能对比:

假设表有100万行:

type=const:     1次IO      (0.1ms)
type=ref:       100次IO    (10ms)
type=range:     1000次IO   (100ms)
type=index:     10000次IO  (1秒)
type=ALL:       100000次IO (10秒)

优化目标:
  ✅ type至少达到range
  ✅ 最好达到ref或const
  ❌ 避免index和ALL

4. possible_keys vs key

EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND status = 1;

假设有两个索引:
  idx_user_id (user_id)
  idx_status (status)

possible_keys: idx_user_id, idx_status  -- 可能使用的索引
key: idx_user_id                        -- 实际使用的索引

说明:
  优化器选择了idx_user_id
  因为user_id的选择性更高（区分度更大）

5. key_len: 索引使用长度

-- 单列索引
CREATE INDEX idx_user_id ON orders(user_id);  -- BIGINT, 8字节

EXPLAIN SELECT * FROM orders WHERE user_id = 123;
key_len: 8

-- 联合索引
CREATE INDEX idx_user_time ON orders(user_id, created_time);
-- user_id: BIGINT, 8字节
-- created_time: TIMESTAMP, 4字节

-- 查询1: 只用到user_id
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
key_len: 8

-- 查询2: 用到user_id和created_time
EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND created_time > '2024-01-01';
key_len: 12  (8 + 4)

-- 查询3: 跳过user_id,无法使用索引
EXPLAIN SELECT * FROM orders WHERE created_time > '2024-01-01';
key_len: NULL

结论:
  key_len越大,使用的索引列越多
  联合索引需要遵循最左前缀原则

key_len的计算规则:

1. 定长类型:
   INT:         4字节
   BIGINT:      8字节
   DATETIME:    8字节
   TIMESTAMP:   4字节

2. 变长类型:
   VARCHAR(n):  n × 字符集字节数 + 2

   VARCHAR(50) CHARSET utf8mb4:
   50 × 4 + 2 = 202字节

3. NULL列:
   额外增加1字节标识是否为NULL

示例:
  user_id BIGINT NOT NULL
  → key_len = 8

  name VARCHAR(50) CHARSET utf8mb4 NULL
  → key_len = 50 × 4 + 2 + 1 = 203

6. rows: 预计扫描行数

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

rows: 100

说明:
  优化器估计需要扫描100行
  不是精确值,而是统计信息的估算
  rows越小,性能越好

优化目标:
  ✅ 将rows从100万降到1000
  ✅ 通过索引减少扫描行数

rows的来源:统计信息

-- 查看表的统计信息
SHOW TABLE STATUS LIKE 'orders'\G

Rows: 1000000  -- 估算的行数

-- 更新统计信息
ANALYZE TABLE orders;

-- 查看索引的基数（cardinality）
SHOW INDEX FROM orders;

Cardinality: 区分度,值越大越好
  主键ID的Cardinality = 行数（100%区分度）
  性别字段的Cardinality ≈ 2（只有男/女）

7. Extra: 额外信息（最重要的字段）

Using index（覆盖索引）

EXPLAIN SELECT user_id, created_time FROM orders
WHERE user_id = 123;

-- 假设有索引idx_user_time(user_id, created_time)
Extra: Using index

说明:
  查询只需要索引列,无需回表
  性能最好
  ✅ 优化目标

Using where

EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND amount > 100;

Extra: Using where

说明:
  存储引擎返回数据后,Server层再次过滤（amount > 100）
  如果amount没有索引,需要在Server层过滤

Using index condition（索引下推）

EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND created_time > '2024-01-01';

-- 假设有索引idx_user_time(user_id, created_time)
Extra: Using index condition

说明:
  将部分WHERE条件下推到存储引擎层
  减少回表次数
  MySQL 5.6+支持

Using filesort（需要优化）

EXPLAIN SELECT * FROM orders
WHERE user_id = 123
ORDER BY amount DESC;

Extra: Using filesort

说明:
  无法利用索引排序,需要额外的排序操作
  如果数据量大,会使用磁盘临时文件
  ❌ 性能差,需要优化

优化方案:
  创建索引idx_user_amount(user_id, amount)
  利用索引的有序性,避免filesort

Using temporary（需要优化）

EXPLAIN SELECT user_id, COUNT(*) FROM orders
GROUP BY user_id;

Extra: Using temporary

说明:
  需要创建临时表进行分组
  ❌ 性能差,需要优化

优化方案:
  创建索引idx_user_id(user_id)
  利用索引的有序性,避免临时表

Using join buffer（需要优化）

EXPLAIN SELECT * FROM orders o
JOIN products p ON o.product_name = p.name;

Extra: Using join buffer (Block Nested Loop)

说明:
  JOIN列没有索引,使用Block Nested Loop算法
  将驱动表数据加载到join buffer
  ❌ 性能差,需要优化

优化方案:
  在p.name上创建索引
  使用索引加速JOIN

Extra字段总结:

Extra说明性能是否需要优化
Using index覆盖索引✅ 最好无需优化
Using index condition索引下推✅ 很好无需优化
Using whereServer层过滤⚠️ 一般考虑优化
Using filesort额外排序❌ 差需要优化
Using temporary临时表❌ 差需要优化
Using join bufferJOIN缓冲❌ 差需要优化

三、索引优化的高级技巧

理解了EXPLAIN,接下来学习具体的优化技巧。

3.1 覆盖索引:避免回表

什么是覆盖索引?

定义:
  查询的所有列都在索引中,无需回表读取数据

示例:
  表结构:
    CREATE TABLE orders (
      id BIGINT PRIMARY KEY,
      user_id BIGINT,
      product_id BIGINT,
      amount DECIMAL(10,2),
      created_time TIMESTAMP,
      INDEX idx_user_time (user_id, created_time)
    );

  覆盖索引查询:
    SELECT user_id, created_time FROM orders
    WHERE user_id = 123;

  执行流程:
    1. 在idx_user_time索引中查找user_id=123
    2. 索引叶子节点存储了user_id和created_time
    3. 直接返回,无需回表
    4. Extra: Using index ✅

  非覆盖索引查询:
    SELECT user_id, amount FROM orders
    WHERE user_id = 123;

  执行流程:
    1. 在idx_user_time索引中查找user_id=123
    2. 索引叶子节点只有user_id和created_time,没有amount
    3. 根据主键ID回表,读取完整行数据
    4. 返回user_id和amount
    5. Extra: NULL（需要回表）

性能对比:

-- 表:1000万行,每行1KB

-- 查询1:覆盖索引
SELECT user_id, created_time FROM orders
WHERE user_id = 123;

执行计划:
  type: ref
  key: idx_user_time
  rows: 100
  Extra: Using index

性能:
  IO次数: 4（索引树高度3 + 扫描叶子节点1）
  数据量: 4 × 16KB = 64KB
  耗时: 4ms

-- 查询2:需要回表
SELECT * FROM orders
WHERE user_id = 123;

执行计划:
  type: ref
  key: idx_user_time
  rows: 100
  Extra: NULL

性能:
  IO次数: 4（索引）+ 100（回表）= 104
  数据量: 4 × 16KB + 100 × 16KB = 1.6MB
  耗时: 104ms

性能差异: 26倍

覆盖索引的设计技巧:

-- 1. 查询频繁的列组合成联合索引
CREATE INDEX idx_user_product_time ON orders(user_id, product_id, created_time);

-- 覆盖查询
SELECT user_id, product_id, created_time FROM orders
WHERE user_id = 123;

-- 2. 使用延迟关联优化分页
-- 差:需要回表10000次
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_time DESC
LIMIT 10000, 10;

-- 好:先用覆盖索引查ID,再回表
SELECT * FROM orders
WHERE id IN (
  SELECT id FROM orders
  WHERE user_id = 123
  ORDER BY created_time DESC
  LIMIT 10000, 10
);

-- 3. 添加冗余列到索引
-- 如果经常查询user_id + amount,可以将amount加入索引
CREATE INDEX idx_user_time_amount ON orders(user_id, created_time, amount);

3.2 索引下推（ICP）:减少回表

什么是索引下推?

定义:
  将部分WHERE条件下推到存储引擎层,在索引遍历时就过滤数据
  减少回表次数

版本要求:
  MySQL 5.6+

示例:没有ICP（MySQL 5.5）

-- 表结构
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  INDEX idx_name_age (name, age)
);

-- 查询
SELECT * FROM users
WHERE name LIKE 'Zhang%' AND age > 25;

执行流程（无ICP）:
  1. 在idx_name_age索引中查找name LIKE 'Zhang%'
  2. 找到1000条匹配name的记录
  3. 对这1000条记录,逐一回表读取完整行
  4. 在Server层过滤age > 25
  5. 最终返回100条记录

问题:
  回表1000次,但只有100条满足age > 25
  浪费了900次回表

示例:有ICP（MySQL 5.6+）

-- 相同查询
SELECT * FROM users
WHERE name LIKE 'Zhang%' AND age > 25;

执行流程（有ICP）:
  1. 在idx_name_age索引中查找name LIKE 'Zhang%'
  2. 在索引层就判断age > 25
  3. 只对满足两个条件的100条记录回表
  4. 返回100条记录

优势:
  回表次数: 1000 → 100
  性能提升: 10倍

EXPLAIN:
  Extra: Using index condition ✅

ICP的适用条件:

✅ 支持的情况:
  - 联合索引的非第一列
  - 范围查询
  - LIKE查询

❌ 不支持的情况:
  - 聚簇索引（主键索引）
  - 覆盖索引（已经无需回表）
  - 索引列参与计算（WHERE age + 1 > 26）

性能对比:

-- 测试数据:100万行

-- 查询1:无ICP（关闭）
SET optimizer_switch='index_condition_pushdown=off';

SELECT * FROM users
WHERE name LIKE 'Zhang%' AND age > 25;

性能:
  扫描索引: 10000行
  回表: 10000次
  Server层过滤: 10000行 → 1000行
  耗时: 1000ms

-- 查询2:有ICP（开启）
SET optimizer_switch='index_condition_pushdown=on';

SELECT * FROM users
WHERE name LIKE 'Zhang%' AND age > 25;

性能:
  扫描索引: 10000行
  索引层过滤: 10000行 → 1000行
  回表: 1000次
  耗时: 100ms

性能提升: 10倍

3.3 MRR（多范围读）:顺序IO

什么是MRR?

Multi-Range Read,多范围读优化

问题:
  回表时,主键ID可能是乱序的
  导致随机IO,性能差

解决:
  1. 先读取所有主键ID
  2. 对ID排序
  3. 按顺序回表
  4. 随机IO → 顺序IO

版本要求:
  MySQL 5.6+

示例:没有MRR

-- 查询
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_time DESC
LIMIT 100;

执行流程（无MRR）:
  1. 在idx_user_time索引中查找user_id=123
  2. 按created_time倒序扫描,找到100个主键ID:
     [9999, 8888, 7777, 5555, 3333, 2222, ...]
  3. 按这个顺序回表:
     读取id=9999 → 随机IO
     读取id=8888 → 随机IO
     读取id=7777 → 随机IO
     ...

问题:
  主键ID乱序,导致随机IO
  性能差

示例:有MRR

-- 相同查询
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_time DESC
LIMIT 100;

执行流程（有MRR）:
  1. 在idx_user_time索引中查找user_id=123
  2. 按created_time倒序扫描,找到100个主键ID:
     [9999, 8888, 7777, 5555, 3333, 2222, ...]
  3. 对ID排序:
     [2222, 3333, 5555, 7777, 8888, 9999, ...]
  4. 按排序后的顺序回表:
     读取id=2222 → 顺序IO
     读取id=3333 → 顺序IO
     读取id=5555 → 顺序IO
     ...
  5. 在Server层按created_time重新排序

优势:
  顺序IO比随机IO快10-100倍

性能对比:

-- 开启MRR
SET optimizer_switch='mrr=on,mrr_cost_based=off';

-- 查询
SELECT * FROM orders
WHERE user_id BETWEEN 100 AND 200;

性能:
  无MRR: 随机IO,耗时500ms
  有MRR: 顺序IO,耗时50ms
  性能提升: 10倍

EXPLAIN:
  Extra: Using MRR ✅

MRR的代价:

优势:
  ✅ 顺序IO,性能提升10倍

劣势:
  ❌ 需要额外的排序
  ❌ 需要内存缓冲区（read_rnd_buffer_size）
  ❌ 如果回表数量少,MRR的收益不明显

适用场景:
  ✅ 回表数量多（>100）
  ✅ 磁盘为HDD（顺序IO优势明显）
  ⚠️ SSD效果不如HDD明显

3.4 BKA（批量键访问）:优化JOIN

什么是BKA?

Batched Key Access,批量键访问

结合了:
  MRR（顺序IO）
  +
  JOIN Buffer（批量处理）

版本要求:
  MySQL 5.6+

示例:没有BKA

-- JOIN查询
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_time > '2024-01-01';

执行流程（无BKA）:
  1. 全表扫描orders（驱动表）
  2. 对每一行orders,在users中查找user_id:
     orders[1].user_id=9999 → 在users中查找id=9999
     orders[2].user_id=8888 → 在users中查找id=8888
     orders[3].user_id=7777 → 在users中查找id=7777
     ...

问题:
  逐行JOIN,随机IO
  性能差

示例:有BKA

-- 开启BKA
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

-- 相同查询
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_time > '2024-01-01';

执行流程（有BKA）:
  1. 全表扫描orders（驱动表）
  2. 将orders的user_id批量加载到join buffer:
     [9999, 8888, 7777, 5555, 3333, ...]
  3. 对user_id排序:
     [3333, 5555, 7777, 8888, 9999, ...]
  4. 批量在users中查找（顺序IO）:
     查找id=3333
     查找id=5555
     查找id=7777
     ...

优势:
  批量 + 顺序IO
  性能提升10-100倍

性能对比:

-- 测试:JOIN 10万行

无BKA:
  逐行JOIN,随机IO
  耗时: 5秒

有BKA:
  批量JOIN,顺序IO
  耗时: 500ms

性能提升: 10倍

四、JOIN优化:从嵌套循环到哈希JOIN

JOIN是最常见也是最容易出现性能问题的查询。

4.1 JOIN的三种算法

1. Simple Nested Loop Join（简单嵌套循环）

伪代码:
  for each row in table1:        -- 外层循环
    for each row in table2:      -- 内层循环
      if row1.id == row2.id:
        return row1 + row2

时间复杂度: O(n × m)

示例:
  table1: 1000行
  table2: 1000行
  比较次数: 1000 × 1000 = 100万次

MySQL不使用这种算法,太慢

2. Index Nested Loop Join（索引嵌套循环）

伪代码:
  for each row in table1:        -- 外层循环
    使用索引在table2中查找     -- O(log n)
    if found:
      return row1 + row2

时间复杂度: O(n × log m)

示例:
  table1: 1000行（驱动表）
  table2: 100万行（被驱动表,有索引）
  比较次数: 1000 × log(1000000) ≈ 1000 × 20 = 2万次

MySQL默认使用这种算法

3. Block Nested Loop Join（块嵌套循环）

伪代码:
  将table1批量加载到join buffer（内存）
  for each batch in table1:     -- 批次循环
    for each row in table2:     -- 全表扫描
      for each row in batch:    -- 内存比较
        if row1.id == row2.id:
          return row1 + row2

优势:
  减少table2的扫描次数
  内存比较比磁盘IO快

示例:
  table1: 10万行
  join_buffer_size: 256KB（可放入1000行）
  批次: 10万 / 1000 = 100批
  table2扫描次数: 100次（而不是10万次）

4. Hash Join（哈希连接,MySQL 8.0.18+）

伪代码:
  -- 阶段1:构建哈希表
  for each row in table1:
    hash_table[row.id] = row

  -- 阶段2:探测
  for each row in table2:
    if row.id in hash_table:
      return hash_table[row.id] + row

时间复杂度: O(n + m)

优势:
  比Nested Loop快
  不需要索引

适用场景:
  没有索引的等值JOIN

算法选择:

情况1:被驱动表有索引
  → Index Nested Loop Join ✅
  性能最好

情况2:被驱动表无索引,数据量小
  → Block Nested Loop Join
  性能一般

情况3:被驱动表无索引,数据量大（MySQL 8.0.18+）
  → Hash Join ✅
  性能好

情况4:被驱动表无索引,数据量大（MySQL 8.0.18之前）
  → Block Nested Loop Join
  性能差,建议加索引

4.2 JOIN优化实战

案例1:驱动表选择

-- 查询:订单JOIN用户
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_time > '2024-01-01';

表数据量:
  orders: 1000万行,过滤后10万行
  users: 100万行

-- 优化前:users作为驱动表（错误）
EXPLAIN:
  id=1, table=users, rows=1000000   -- 驱动表
  id=1, table=orders, rows=10       -- 被驱动表

执行:
  扫描users: 100万行
  对每一行users,在orders中查找: 100万次
  耗时: 很慢

-- 优化后:orders作为驱动表（正确）
EXPLAIN:
  id=1, table=orders, rows=100000   -- 驱动表（WHERE过滤后）
  id=1, table=users, rows=1         -- 被驱动表

执行:
  扫描orders（过滤后）: 10万行
  对每一行orders,在users中查找: 10万次
  耗时: 快

优化原则:
  ✅ 小表驱动大表
  ✅ 过滤后的结果集作为驱动表

案例2:添加索引

-- 查询
SELECT * FROM orders o
JOIN products p ON o.product_name = p.name;

-- 优化前:product_name无索引
EXPLAIN:
  Extra: Using join buffer (Block Nested Loop)

执行:
  orders全表扫描: 1000万行
  对每一行orders,全表扫描products: 1000万 × 10万 = 1万亿次比较
  耗时: 超时

-- 优化后:在p.name上创建索引
CREATE INDEX idx_name ON products(name);

EXPLAIN:
  type: ref
  key: idx_name
  Extra: NULL

执行:
  orders全表扫描: 1000万行
  对每一行orders,索引查找products: 1000万 × log(10万) ≈ 1.7亿次比较
  耗时: 可接受

性能提升: 约500倍

案例3:使用子查询优化

-- 查询:获取每个用户的最新订单
SELECT u.*, o.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.id IN (
  SELECT MAX(id) FROM orders GROUP BY user_id
);

-- 优化前:直接JOIN
耗时: 10秒

-- 优化后:先用子查询找到最新订单ID
WITH latest_orders AS (
  SELECT user_id, MAX(id) AS max_id
  FROM orders
  GROUP BY user_id
)
SELECT u.*, o.* FROM users u
JOIN orders o ON u.id = o.user_id
JOIN latest_orders lo ON o.id = lo.max_id;

耗时: 1秒

性能提升: 10倍

五、慢查询优化实战案例

综合运用前面的知识,优化10个真实的慢查询。

案例1:全表扫描 → 索引查询

问题SQL:

SELECT * FROM orders WHERE user_id = 123;

执行时间: 10秒

EXPLAIN分析:

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | key           | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | 10000000| Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

问题:
  type=ALL: 全表扫描
  rows=10000000: 扫描1000万行

优化方案:

-- 创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 再次执行
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table  | type | key           | ref         | rows  | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_user_id   | const       | 100   | NULL  |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+

改进:
  type=ref: 索引查询
  rows=100: 只扫描100行

执行时间: 10ms

性能提升: 1000倍

案例2:Using filesort → 利用索引排序

问题SQL:

SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_time DESC
LIMIT 10;

执行时间: 500ms

EXPLAIN分析:

EXPLAIN ...;

Extra: Using where; Using filesort

问题:
  Using filesort: 需要额外排序
  数据量大时,使用磁盘临时文件

优化方案:

-- 创建联合索引（包含排序列）
CREATE INDEX idx_user_time ON orders(user_id, created_time DESC);

-- 再次执行
EXPLAIN ...;

Extra: Using where

改进:
  无Using filesort
  利用索引的有序性,避免排序

执行时间: 10ms

性能提升: 50倍

案例3:深分页优化

问题SQL:

SELECT * FROM orders
ORDER BY id
LIMIT 1000000, 10;

执行时间: 5秒

问题分析:

深分页问题:
  需要扫描1000010行
  丢弃前1000000行
  只返回最后10行
  浪费严重

优化方案1:子查询

-- 先用覆盖索引查ID,再回表
SELECT * FROM orders
WHERE id >= (
  SELECT id FROM orders
  ORDER BY id
  LIMIT 1000000, 1
)
ORDER BY id
LIMIT 10;

执行时间: 500ms

性能提升: 10倍

优化方案2:记住上次ID

-- 第1页
SELECT * FROM orders
WHERE id > 0
ORDER BY id
LIMIT 10;
-- 返回id=1~10,最大ID=10

-- 第2页
SELECT * FROM orders
WHERE id > 10
ORDER BY id
LIMIT 10;
-- 返回id=11~20,最大ID=20

执行时间: 10ms

性能提升: 500倍

案例4:COUNT(*)优化

问题SQL:

SELECT COUNT(*) FROM orders WHERE status = 1;

执行时间: 3秒

EXPLAIN分析:

EXPLAIN ...;

type: ALL
rows: 10000000

问题:
  全表扫描计数

优化方案1:覆盖索引

-- 创建索引
CREATE INDEX idx_status ON orders(status);

-- COUNT(*)会使用索引
SELECT COUNT(*) FROM orders WHERE status = 1;

执行时间: 500ms

性能提升: 6倍

优化方案2:近似值

-- 如果不需要精确值,使用EXPLAIN估算
EXPLAIN SELECT * FROM orders WHERE status = 1;
rows: 约500000

-- 或者维护计数表
CREATE TABLE order_count (
  status INT PRIMARY KEY,
  count INT
);

-- 每次INSERT/UPDATE/DELETE时更新计数
-- 查询时直接读取
SELECT count FROM order_count WHERE status = 1;

执行时间: 1ms

性能提升: 3000倍

案例5:OR条件优化

问题SQL:

SELECT * FROM orders
WHERE user_id = 123 OR product_id = 456;

执行时间: 2秒

EXPLAIN分析:

EXPLAIN ...;

type: ALL
key: NULL

问题:
  OR条件无法使用索引（如果其中一个列无索引）

优化方案:改写为UNION

SELECT * FROM orders WHERE user_id = 123
UNION ALL
SELECT * FROM orders WHERE product_id = 456 AND user_id != 123;

-- 或者使用IN
SELECT * FROM orders
WHERE user_id IN (123)
OR product_id IN (456);

执行时间: 50ms

性能提升: 40倍

六、总结与最佳实践

通过本文,我们从第一性原理出发,深度剖析了MySQL的查询优化:

SQL执行流程:
  连接器 → 解析器 → 优化器 → 执行器 → 存储引擎
  ↓        ↓        ↓        ↓        ↓
  权限     语法     计划     查询     数据

EXPLAIN分析:
  type: const > ref > range > index > ALL
  Extra: Using index > Using index condition > Using where
        > Using filesort/temporary（需要优化）

索引优化:
  覆盖索引:避免回表,性能提升10-100倍
  索引下推:减少回表,性能提升10倍
  MRR:顺序IO,性能提升10倍

JOIN优化:
  小表驱动大表
  被驱动表建索引
  Hash Join（MySQL 8.0.18+）

查询优化的黄金法则:

1. 定位慢查询
   ✅ 开启慢查询日志
   ✅ 设置long_query_time=1
   ✅ 定期分析慢查询日志

2. EXPLAIN分析
   ✅ type至少达到range
   ✅ 避免ALL和index
   ✅ 避免Using filesort和Using temporary

3. 索引优化
   ✅ 高频查询字段建索引
   ✅ 覆盖索引避免回表
   ✅ 联合索引遵循最左前缀

4. SQL改写
   ✅ OR改写为UNION
   ✅ 子查询改写为JOIN
   ✅ 深分页使用延迟关联

5. 参数调优
   ✅ 增大innodb_buffer_pool_size
   ✅ 开启MRR和BKA
   ✅ 调整join_buffer_size

下一篇预告:

在下一篇文章《高可用架构:主从复制与分库分表》中,我们将深度剖析:

  • 主从复制原理:binlog三种格式
  • 主从延迟优化:从秒级到毫秒级
  • 分库分表策略:垂直拆分 vs 水平拆分
  • 分布式ID生成:雪花算法、号段模式
  • 数据迁移与扩容:在线平滑迁移

敬请期待!

字数统计:约18,000字

阅读时间:约90分钟

难度等级:⭐⭐⭐⭐⭐ (高级)

适合人群:

  • 后端开发工程师
  • 数据库工程师
  • 性能优化工程师
  • 对MySQL查询优化感兴趣的技术爱好者

参考资料:

  • 《高性能MySQL（第4版）》- Baron Schwartz, 第6章《查询性能优化》
  • 《MySQL技术内幕:InnoDB存储引擎（第2版）》- 姜承尧, 第7章《查询优化》
  • MySQL 8.0 Reference Manual - Optimization
  • 《数据库查询优化器的艺术》- 李海翔

版权声明: 本文采用 CC BY-NC-SA 4.0 许可协议。 转载请注明出处。