zt@dev▊
Database · #mysql#optimization#query#explain

MySQL查询优化：执行计划分析与SQL重写

2024.11.27 9 min 3.6k 字
// 目录 · contents

引言

SQL查询优化是数据库性能调优中最常见也是最有效的手段。一条写法不当的SQL可能导致全表扫描、临时表排序等性能问题，而通过分析执行计划并合理重写，往往能获得数倍甚至数百倍的性能提升。本文将系统讲解MySQL查询的执行过程、EXPLAIN分析方法，以及各类SQL优化技巧。

MySQL 查询执行过程

一条SQL从客户端发送到返回结果，经历以下阶段：

flowchart TD
    A[客户端发送SQL] --> B[连接器<br/>权限验证]
    B --> C{查询缓存<br/>MySQL 8.0已移除}
    C -->|命中| D[直接返回结果]
    C -->|未命中| E[解析器<br/>词法分析 + 语法分析]
    E --> F[预处理器<br/>语义检查 + 权限验证]
    F --> G[优化器<br/>生成执行计划]
    G --> H[执行器<br/>调用存储引擎接口]
    H --> I[存储引擎<br/>InnoDB]
    I --> J[返回结果]

优化器的工作

MySQL优化器负责选择最优的执行计划，其决策包括：

  1. 选择使用哪个索引（或不使用索引）
  2. 多表JOIN时的连接顺序
  3. 子查询的执行策略（物化、转换为JOIN等）
  4. 是否使用临时表和排序
1
2
3
4
5
-- 查看优化器的选择过程（MySQL 8.0+）
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
SELECT * FROM information_schema.optimizer_trace\G
SET optimizer_trace = 'enabled=off';

EXPLAIN 详细解读

EXPLAIN 各列完整说明

1
2
3
4
5
6
EXPLAIN SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.created_at > '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

id 列

id表示查询中SELECT的序号，用来标识查询的执行顺序。

1
2
3
4
5
6
7
-- id相同: 从上到下执行
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- 两行id都是1，先执行上面的表（驱动表）

-- id不同: 数字大的先执行
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level = 3);
-- 子查询id=2先执行，外层id=1后执行

select_type 列

值 含义
SIMPLE 简单查询（无子查询或UNION）
PRIMARY 最外层查询
SUBQUERY SELECT子句中的子查询
DERIVED FROM子句中的子查询（派生表）
UNION UNION中的第二个及后续查询
MATERIALIZED 被物化的子查询

type 列（访问类型）

这是EXPLAIN中最重要的字段，从优到劣排列：

graph LR
    A[system] --> B[const] --> C[eq_ref] --> D[ref] --> E[range] --> F[index] --> G[ALL]
    style A fill:#4caf50,color:#fff
    style B fill:#4caf50,color:#fff
    style C fill:#8bc34a,color:#fff
    style D fill:#8bc34a,color:#fff
    style E fill:#ffeb3b,color:#333
    style F fill:#ff9800,color:#fff
    style G fill:#f44336,color:#fff
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- system: 表只有一行记录
EXPLAIN SELECT * FROM (SELECT 1) t;

-- const: 通过主键或唯一索引的等值查询，结果最多一行
EXPLAIN SELECT * FROM users WHERE id = 1;

-- eq_ref: JOIN时对被驱动表使用主键或唯一索引
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- ref: 使用非唯一索引的等值查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- range: 范围扫描
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2025-01-01' AND '2025-03-01';
EXPLAIN SELECT * FROM orders WHERE id IN (1, 2, 3);

-- index: 全索引扫描（比ALL好，但仍然扫描整个索引）
EXPLAIN SELECT COUNT(*) FROM orders;

-- ALL: 全表扫描（最差，应该尽量避免）
EXPLAIN SELECT * FROM orders WHERE description LIKE '%keyword%';

key_len 计算规则

key_len表示使用的索引长度（字节），可以用来判断联合索引用了几个列：

1
2
3
4
5
6
7
8
9
10
-- 字符类型: 字符数 * 字符集字节数 + (是否可NULL ? 1 : 0) + (变长类型 ? 2 : 0)
-- int: 4 + (是否可NULL ? 1 : 0)
-- bigint: 8 + (是否可NULL ? 1 : 0)
-- datetime: 5 + (是否可NULL ? 1 : 0)
-- varchar(50) utf8mb4: 50 * 4 + 2 + (是否可NULL ? 1 : 0) = 203

-- 联合索引 idx(user_id INT NOT NULL, status VARCHAR(20) NOT NULL, created_at DATETIME)
-- 只用了 user_id: key_len = 4
-- 用了 user_id + status: key_len = 4 + (20*4+2) = 86
-- 用了全部三列: key_len = 4 + 82 + 6 = 92

Extra 列

值 含义 优化建议
Using index 覆盖索引 性能好
Using where Server层过滤 检查是否可以下推到引擎层
Using index condition 索引下推ICP 较好
Using temporary 使用临时表 需要优化
Using filesort 额外排序 需要优化
Using join buffer JOIN使用了缓冲区 被驱动表无索引

EXPLAIN FORMAT=JSON/TREE

MySQL 8.0提供了更详细的执行计划输出：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- JSON格式，包含成本估算
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid'\G

-- TREE格式，更直观的层次结构（MySQL 8.0.16+）
EXPLAIN FORMAT=TREE
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'\G

-- 实际执行并输出统计信息（MySQL 8.0.18+）
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid'\G

EXPLAIN ANALYZE的输出示例：

1
2
-> Filter: (orders.status = 'paid')  (cost=2.50 rows=5) (actual time=0.035..0.042 rows=3 loops=1)
    -> Index lookup on orders using idx_user_id (user_id=1)  (cost=2.50 rows=10) (actual time=0.030..0.036 rows=10 loops=1)

优化器 Hints

当优化器的选择不是最优时，可以通过Hint引导优化器：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 强制使用某个索引
SELECT * FROM orders FORCE INDEX (idx_user_status)
WHERE user_id = 1 AND status = 'paid';

-- 忽略某个索引
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = 'paid';

-- MySQL 8.0 Optimizer Hints（更细粒度）
SELECT /*+ INDEX(o idx_user_status) */ o.*
FROM orders o
WHERE o.user_id = 1 AND o.status = 'paid';

-- 控制JOIN顺序
SELECT /*+ JOIN_ORDER(u, o) */ o.*, u.name
FROM orders o JOIN users u ON o.user_id = u.id;

-- 禁用合并派生表
SELECT /*+ NO_MERGE(derived) */ *
FROM (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) derived
WHERE cnt > 10;

-- 设置查询超时（毫秒）
SELECT /*+ MAX_EXECUTION_TIME(5000) */ * FROM orders WHERE status = 'paid';

SQL 重写优化技巧

JOIN 优化

flowchart TD
    A[JOIN优化] --> B[小表驱动大表]
    A --> C[被驱动表JOIN列建索引]
    A --> D[避免过多表JOIN]
    A --> E[选择合适的JOIN类型]

    B --> B1["驱动表: 过滤后结果集小的表"]
    C --> C1["确保 ON 条件列有索引"]
    D --> D1["单次查询不超过3-4张表"]
    E --> E1["INNER JOIN 让优化器选择驱动表<br/>LEFT JOIN 左表为驱动表"]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 不良写法: 大表驱动小表
SELECT o.* FROM orders o
LEFT JOIN (SELECT id FROM users WHERE vip_level = 3) u ON o.user_id = u.id
WHERE u.id IS NOT NULL;

-- 优化写法: 小表驱动大表
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip_level = 3;
-- 优化器会自动选择 users（小结果集）作为驱动表

-- 进一步优化: 如果只需要检查存在性
SELECT o.* FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip_level = 3);

Nested Loop Join 原理

MySQL使用Nested Loop Join算法执行JOIN：

1
2
3
4
5
for (row in 驱动表) {
    for (row in 被驱动表 where 匹配条件) {
        输出结果
    }
}
1
2
3
4
5
6
7
8
9
10
-- 被驱动表无索引时使用 Block Nested Loop Join（MySQL 8.0.18之前）
-- MySQL 8.0.18+ 使用 Hash Join

-- Hash Join (MySQL 8.0.18+)
EXPLAIN FORMAT=TREE
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- -> Hash join (u.id = o.user_id)
--     -> Table scan on o
--     -> Hash
--         -> Table scan on u

子查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 低效: 相关子查询（每行都执行一次子查询）
SELECT *
FROM orders o
WHERE o.amount > (SELECT AVG(amount) FROM orders WHERE user_id = o.user_id);

-- 优化: 转为JOIN
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT user_id, AVG(amount) AS avg_amount
    FROM orders
    GROUP BY user_id
) t ON o.user_id = t.user_id AND o.amount > t.avg_amount;

-- IN子查询优化
-- MySQL优化器通常会自动将 IN 子查询转为 semi-join
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level = 3);
-- 等价于
SELECT o.* FROM orders o SEMI JOIN users u ON o.user_id = u.id WHERE u.vip_level = 3;

-- NOT IN 的陷阱: 如果子查询结果包含NULL，整个NOT IN返回空
SELECT * FROM orders WHERE user_id NOT IN (SELECT manager_id FROM departments);
-- 如果 manager_id 有NULL值，结果为空！
-- 改用 NOT EXISTS
SELECT * FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.manager_id = o.user_id);

分页查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 慢查询: 深分页
SELECT * FROM orders ORDER BY created_at DESC LIMIT 500000, 20;
-- 实际扫描 500020 行

-- 优化方案1: 延迟关联（deferred join）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 500000, 20
) t ON o.id = t.id;
-- 子查询使用覆盖索引，只扫描索引不回表

-- 优化方案2: 游标分页（keyset pagination）
-- 前端传递上一页最后一条记录的标识
SELECT * FROM orders
WHERE created_at < '2025-03-15 10:30:00'
    OR (created_at = '2025-03-15 10:30:00' AND id < 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 优化方案3: 业务层面限制（不允许跳页）
-- 只提供"上一页"和"下一页"按钮
sequenceDiagram
    participant App as 应用层
    participant DB as MySQL

    Note over App, DB: 传统分页 LIMIT offset, size
    App->>DB: SELECT * FROM orders LIMIT 500000, 20
    DB->>DB: 扫描500020行,丢弃前500000行
    DB->>App: 返回20行

    Note over App, DB: 游标分页 Keyset Pagination
    App->>DB: SELECT * FROM orders WHERE id < 12345 LIMIT 20
    DB->>DB: 通过索引直接定位,扫描20行
    DB->>App: 返回20行

COUNT 优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- COUNT(*) vs COUNT(1) vs COUNT(column)
-- COUNT(*) 和 COUNT(1) 完全等价，MySQL会自动优化
-- COUNT(column) 会排除 NULL 值

-- 精确计数的优化
-- 方案1: 使用覆盖索引
SELECT COUNT(*) FROM orders WHERE status = 'paid';
-- 确保 status 有索引

-- 方案2: 近似计数（适用于不需要精确值的场景）
SHOW TABLE STATUS LIKE 'orders';  -- Rows 列是近似值

-- 方案3: 维护计数表（适用于频繁统计的场景）
CREATE TABLE order_counts (
    status VARCHAR(20) PRIMARY KEY,
    cnt BIGINT NOT NULL DEFAULT 0
);
-- 每次INSERT/UPDATE/DELETE orders时同步更新计数表

GROUP BY 与 ORDER BY 优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Using filesort 优化
-- 确保 GROUP BY / ORDER BY 使用的列与索引顺序一致

-- 索引: idx_user_status(user_id, status)
-- 可以利用索引排序
SELECT user_id, status, COUNT(*) FROM orders
WHERE user_id IN (1, 2, 3)
GROUP BY user_id, status;

-- 不能利用索引排序（顺序不匹配）
SELECT user_id, status, COUNT(*) FROM orders
WHERE user_id IN (1, 2, 3)
GROUP BY status, user_id;

-- Using temporary 优化
-- GROUP BY 和 ORDER BY 的列不同时，会产生临时表
SELECT user_id, COUNT(*) AS cnt FROM orders
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10;
-- 优化: 使用派生表
SELECT * FROM (
    SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
) t ORDER BY cnt DESC LIMIT 10;

OR 条件优化

1
2
3
4
5
6
7
8
9
10
11
12
-- OR 可能导致索引失效
SELECT * FROM orders WHERE user_id = 1 OR status = 'paid';
-- 如果 user_id 和 status 分别有索引，MySQL可能用 Index Merge
-- 但效率不如联合索引

-- 优化方案1: UNION ALL 替代 OR
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE status = 'paid' AND user_id != 1;

-- 优化方案2: 设计合理的联合索引
ALTER TABLE orders ADD INDEX idx_user_status(user_id, status);

实战：慢查询分析流程

flowchart TD
    A[发现慢查询] --> B[慢查询日志/监控告警]
    B --> C[EXPLAIN 分析执行计划]
    C --> D{type 是 ALL?}
    D -->|是| E[添加合适的索引]
    D -->|否| F{Extra 有 filesort/temporary?}
    F -->|是| G[优化 ORDER BY/GROUP BY]
    F -->|否| H{rows 行数过大?}
    H -->|是| I[检查索引选择性<br/>考虑SQL重写]
    H -->|否| J{key_len 合理?}
    J -->|否| K[检查联合索引使用情况]
    J -->|是| L[检查是否回表过多]
    L --> M{覆盖索引可行?}
    M -->|是| N[调整索引覆盖查询列]
    M -->|否| O[考虑业务层优化<br/>缓存/异步/分页]
    E --> P[验证优化效果]
    G --> P
    I --> P
    K --> P
    N --> P
    O --> P

实际案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 原始慢查询（执行时间 3.2s）
SELECT o.id, o.order_no, o.amount, o.created_at, u.name, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2025-01-01'
    AND o.status IN ('paid', 'shipped')
ORDER BY o.amount DESC
LIMIT 20;

-- 第一步: EXPLAIN分析
-- type=ALL, Using where; Using temporary; Using filesort
-- 问题: 无合适索引，全表扫描 + 临时表 + 文件排序

-- 第二步: 创建索引
ALTER TABLE orders ADD INDEX idx_status_created(status, created_at);

-- 第三步: 重新EXPLAIN
-- type=range, key=idx_status_created
-- 但仍有 Using filesort（ORDER BY amount 不在索引中）

-- 第四步: 进一步优化
-- 方案A: 如果 status 的值固定，可以拆分查询
SELECT o.id, o.order_no, o.amount, o.created_at, u.name, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.created_at >= '2025-01-01'
UNION ALL
SELECT o.id, o.order_no, o.amount, o.created_at, u.name, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped' AND o.created_at >= '2025-01-01'
ORDER BY amount DESC LIMIT 20;

-- 方案B: 使用延迟关联减少回表和排序数据量
SELECT o.id, o.order_no, o.amount, o.created_at, u.name, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN (
    SELECT id FROM orders
    WHERE status IN ('paid', 'shipped') AND created_at >= '2025-01-01'
    ORDER BY amount DESC LIMIT 20
) t ON o.id = t.id;
-- 子查询在索引内完成过滤，只对20行做JOIN和回表

总结

MySQL查询优化是一个从分析到验证的闭环过程：

  • EXPLAIN是诊断利器：重点关注type、key、rows、Extra四个字段
  • 索引是优化基础：合理的索引设计可以解决90%的慢查询问题
  • SQL重写是进阶手段：JOIN优化、子查询转换、分页优化等技巧需要根据具体场景选择
  • 优化器Hints是最后手段：当优化器判断不准确时，通过Hint引导执行计划
  • EXPLAIN ANALYZE（MySQL 8.0.18+）提供了实际执行统计，是验证优化效果的最佳工具
  • 不要忽视业务层面的优化：缓存、异步处理、合理分页等同样重要
作者 · authorzt
发布 · date2024-11-27
篇幅 · length3.6k 字 · 9 min
许可 · licenseCC BY-SA 4.0
$ echo "comments" · 评论
Java类加载机制与自定义ClassLoader JVM垃圾回收器选择与调优实战