SQL 优化

  SQL 优化是数据库性能调优的核心环节。掌握 EXPLAIN 执行计划分析、慢查询定位和常见优化技巧，是每个后端开发者的必备技能。

---

1. EXPLAIN 执行计划详解

  EXPLAIN 是 MySQL 提供的 SQL 分析工具，用于查看 SQL 语句的执行计划，帮助定位性能瓶颈。

1.1 基本用法

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

-- MySQL 8.0+ 支持 EXPLAIN ANALYZE（实际执行并统计）
EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'Alice';

-- 查看格式化的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'Alice';
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE name = 'Alice';

1.2 EXPLAIN 输出字段

字段	含义
id	SELECT 标识符，同一查询中 id 越大越先执行，id 相同从上到下执行
select_type	查询类型：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION 等
table	访问的表名
partitions	匹配的分区
type	访问类型，性能从好到差：system > const > eq_ref > ref > range > index > ALL
possible_keys	可能使用的索引
key	实际使用的索引
key_len	使用的索引长度（字节数）
ref	与索引比较的列或常量
rows	预估需要扫描的行数
filtered	按表条件过滤后剩余行数的百分比
Extra	额外信息，非常重要的字段

1.3 select_type 详解

select_type	含义
SIMPLE	简单查询，不包含子查询或 UNION
PRIMARY	最外层查询
SUBQUERY	SELECT/WHERE 中的子查询（非 FROM 子句）
DERIVED	FROM 子句中的子查询（派生表）
UNION	UNION 中第二个或之后的 SELECT
UNION RESULT	UNION 的结果集
DEPENDENT SUBQUERY	依赖外部查询的子查询
DEPENDENT UNION	UNION 中依赖外部查询的子查询
MATERIALIZED	子查询被物化（MySQL 5.6+）
UNCACHEABLE SUBQUERY	无法缓存的子查询

1.4 type 字段详解（访问类型）

  type 是 EXPLAIN 中最关键的字段，表示 MySQL 如何查找表中的行，从最好到最差依次为：

type	说明	示例
system	表只有一行记录（系统表），const 的特例	极少见
const	通过主键或唯一索引等值匹配，最多返回一行	WHERE id = 1
eq_ref	关联查询中，使用主键或唯一索引等值匹配，最多返回一行	JOIN ON a.id = b.user_id
ref	使用非唯一索引等值匹配，可能返回多行	WHERE name = 'Alice'
fulltext	全文索引	MATCH ... AGAINST
ref_or_null	类似 ref，但包含 NULL 值搜索	WHERE name = 'Alice' OR name IS NULL
index_merge	索引合并优化（多个索引的交集或并集）	WHERE name = 'Alice' OR age = 25
unique_subquery	子查询中使用主键或唯一索引的 IN	WHERE id IN (SELECT ...)
index_subquery	子查询中使用非唯一索引的 IN	WHERE name IN (SELECT ...)
range	范围扫描，使用索引查找范围内的行	WHERE id > 10, WHERE name LIKE 'A%'
index	全索引扫描（扫描整个索引，比 ALL 好）	SELECT name FROM users ORDER BY name
ALL	全表扫描，性能最差	SELECT * FROM users WHERE email = 'x'（无索引）

优化目标：

• 至少达到 range 级别，最好是 ref 级别。
• 避免 ALL 全表扫描。
• const 和 eq_ref 是最优的。

1.5 Extra 字段详解

Extra 值	含义	是否需要优化
Using index	覆盖索引，不需要回表	最佳状态
Using where	使用 WHERE 过滤	正常
Using index condition	使用了 ICP 优化	正常
Using temporary	使用了临时表（如 GROUP BY、DISTINCT、UNION 等）	需要优化
Using filesort	无法使用索引排序，需要额外排序	需要优化
Using join buffer	使用了 JOIN 缓冲区（BNL 或 BKA）	需要优化
Impossible WHERE	WHERE 条件永远为假	检查 SQL
Select tables optimized away	优化器直接返回结果（如 COUNT(*) 无 WHERE）	最优
Using MRR	使用了 MRR 优化	正常
Using index for group-by	使用索引优化 GROUP BY	良好

需要重点关注的 Extra 值：

-- Using temporary + Using filesort（最差情况）
EXPLAIN SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id ORDER BY COUNT(*);
-- 优化：给 GROUP BY 和 ORDER BY 列建索引

-- Using filesort（排序未用索引）
EXPLAIN SELECT * FROM users ORDER BY create_time;
-- 优化：给排序创建索引

-- Using join buffer（JOIN 未用索引）
EXPLAIN SELECT * FROM a JOIN b ON a.name = b.name;
-- 优化：给 JOIN 列建索引

1.6 EXPLAIN 实战示例

-- 创建测试表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    city VARCHAR(50),
    create_time DATETIME,
    INDEX idx_name (name),
    INDEX idx_age_city (age, city)
);

-- 示例 1：const 类型
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const, key: PRIMARY

-- 示例 2：ref 类型
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
-- type: ref, key: idx_name

-- 示例 3：range 类型
EXPLAIN SELECT * FROM users WHERE age > 25;
-- type: range, key: idx_age_city

-- 示例 4：index 类型
EXPLAIN SELECT name FROM users ORDER BY name;
-- type: index, Extra: Using index

-- 示例 5：覆盖索引
EXPLAIN SELECT age, city FROM users WHERE age = 25;
-- type: ref, key: idx_age_city, Extra: Using index

-- 示例 6：Using temporary
EXPLAIN SELECT city, COUNT(*) FROM users GROUP BY city;
-- Extra: Using temporary（city 没有索引）

-- 示例 7：Using filesort
EXPLAIN SELECT * FROM users ORDER BY email;
-- Extra: Using filesort（email 没有索引）

---

2. 慢查询日志

2.1 慢查询配置

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询被记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询
SET GLOBAL log_slow_admin_statements = ON;  -- 记录慢管理语句

-- MySQL 8.0+ 额外参数
SET GLOBAL log_slow_extra = ON;  -- 记录更多详细信息

my.cnf 永久配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

2.2 慢查询分析工具

mysqldumpslow：

# 查看访问次数最多的 10 条 SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 查看返回记录最多的 10 条 SQL
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 查看查询时间最长的 10 条 SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按平均查询时间排序
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# 结合 grep 过滤
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log | grep 'users'

pt-query-digest（Percona Toolkit 中的工具，功能更强大）：

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

# 分析指定时间范围
pt-query-digest --since='2024-01-01 00:00:00' --until='2024-01-02 00:00:00' slow.log

# 分析 tcpdump 抓取的查询
pt-query-digest --type tcpdump tcpdump.out

# 分析 processlist
pt-query-digest --type processlist

---

3. 常见 SQL 优化技巧

3.1 LIMIT 深分页优化

问题：

-- 传统分页，越往后越慢
SELECT * FROM users ORDER BY id LIMIT 1000000, 20;
-- 需要扫描 1000020 行，丢弃前 1000000 行

优化方案：

-- 方案一：基于主键的延迟关联
SELECT * FROM users 
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 1000000, 20) AS tmp 
USING(id);

-- 方案二：记录上次查询的最大 ID（业务层游标分页）
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 20;

-- 方案三：使用覆盖索引 + 子查询
SELECT * FROM users WHERE id >= (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 1
) ORDER BY id LIMIT 20;

方案对比：

方案	优点	缺点
延迟关联	通用性好	需要两次查询
游标分页	性能最优	不支持跳页，需要业务配合
覆盖索引	减少回表	需要合适的索引

3.2 JOIN 优化

Nested-Loop Join (NLJ)：

-- NLJ：驱动表用索引关联被驱动表
-- 如果被驱动表关联字段有索引，使用 NLJ
SELECT * FROM a JOIN b ON a.id = b.a_id;
-- 驱动表 a 的每一行，通过索引在 b 中查找匹配行

Block Nested-Loop Join (BNL)：

-- BNL：被驱动表没有索引，使用 JOIN BUFFER
-- 将驱动表数据读入 JOIN BUFFER，批量扫描被驱动表
SELECT * FROM a JOIN b ON a.name = b.name;
-- Extra: Using join buffer (Block Nested Loop)

JOIN 优化策略：

1. 小表驱动大表：结果集小的表作为驱动表。
2. 被驱动表关联字段建索引：避免 BNL。
3. 增大 JOIN BUFFER：join_buffer_size（默认 256K）。
4. **避免 SELECT ***：只取需要的列。

-- 查看 JOIN 相关参数
SHOW VARIABLES LIKE 'join_buffer_size';

-- 优化前
EXPLAIN SELECT * FROM a JOIN b ON a.name = b.name;
-- Extra: Using join buffer (Block Nested Loop)

-- 优化后：给 b.name 建索引
CREATE INDEX idx_b_name ON b(name);
EXPLAIN SELECT * FROM a JOIN b ON a.name = b.name;
-- type: ref, Extra: NULL

3.3 子查询优化

  子查询在很多情况下效率不如 JOIN，应尽量将子查询改写为 JOIN。

IN 子查询优化：

-- 子查询（MySQL 5.5 及之前效率低，5.6+ 有优化）
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 改写为 JOIN
SELECT DISTINCT u.* FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 100;

-- 或使用 EXISTS
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100
);

NOT IN 优化：

-- NOT IN（可能返回 NULL 导致结果异常）
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

-- 改写为 LEFT JOIN + IS NULL
SELECT u.* FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE o.user_id IS NULL;

-- 或 NOT EXISTS
SELECT * FROM users u WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

派生表优化：

-- 派生表
SELECT * FROM (SELECT user_id, COUNT(*) cnt FROM orders GROUP BY user_id) t 
WHERE t.cnt > 10;

-- MySQL 8.0+ 优化器自动优化，5.7 可改写为：
SELECT user_id, COUNT(*) cnt FROM orders 
GROUP BY user_id HAVING cnt > 10;

3.4 IN 与 EXISTS 选择

场景	推荐	原因
外层表大，内层表小	IN	IN 先执行子查询
外层表小，内层表大	EXISTS	EXISTS 逐行检查外层表
外层表大，内层表大	JOIN	两者都不适合

-- IN：先执行子查询，结果集缓存，再查外层表
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- EXISTS：外层表逐行检查，内层查询每次用外层表的值
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100
);

3.5 ORDER BY 优化

-- 问题：Using filesort
EXPLAIN SELECT * FROM users ORDER BY create_time;

-- 优化：使用索引排序
CREATE INDEX idx_create_time ON users(create_time);
EXPLAIN SELECT * FROM users ORDER BY create_time;

-- 复合索引排序
-- 索引 idx_a_b(a, b)
SELECT * FROM t WHERE a = 1 ORDER BY b;       -- 使用索引
SELECT * FROM t WHERE a = 1 ORDER BY b DESC;  -- 使用索引
SELECT * FROM t WHERE a = 1 ORDER BY a, b;    -- 使用索引
SELECT * FROM t WHERE a > 1 ORDER BY a;       -- 使用索引（范围查询第一个列）
SELECT * FROM t WHERE a = 1 ORDER BY c;       -- filesort（跳过了 b）
SELECT * FROM t WHERE a > 1 ORDER BY b;       -- filesort（范围查询后不能用 b）

3.6 GROUP BY 优化

-- 使用索引避免临时表
-- 索引 idx_dept_id(dept_id)
EXPLAIN SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;
-- Extra: Using index（如果索引覆盖）

-- 使用 WHERE 过滤减少分组数据
-- 优化前
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;

-- 优化后
SELECT dept_id, COUNT(*) FROM employees 
WHERE create_time > '2024-01-01' 
GROUP BY dept_id;

3.7 COUNT 优化

-- COUNT(*)  vs COUNT(1) vs COUNT(col)
-- InnoDB 中 COUNT(*) 和 COUNT(1) 性能相同，MySQL 8.0 优化了 COUNT(*)
-- COUNT(col) 不统计 NULL 值

-- 大表 COUNT 优化
-- 方案一：使用覆盖索引
SELECT COUNT(*) FROM users FORCE INDEX(idx_name);

-- 方案二：使用汇总表
CREATE TABLE user_count (
    id INT PRIMARY KEY,
    cnt BIGINT
);
-- 定期更新汇总表

-- 方案三：使用 EXPLAIN 估算
EXPLAIN SELECT COUNT(*) FROM users;
-- rows 是估算值，非精确值

-- 方案四：使用 Redis 计数
-- 适用于允许一定误差的场景

3.8 DISTINCT 优化

-- DISTINCT 和 GROUP BY 在大多数情况下等价
SELECT DISTINCT dept_id FROM employees;
SELECT dept_id FROM employees GROUP BY dept_id;

-- 使用索引优化
CREATE INDEX idx_dept_id ON employees(dept_id);
SELECT DISTINCT dept_id FROM employees;
-- Extra: Using index for group-by

---

4. 表设计优化

4.1 数据类型选择

场景	推荐类型	避免
主键	BIGINT UNSIGNED	VARCHAR/GUID
状态/类型	TINYINT	VARCHAR
时间	DATETIME / TIMESTAMP	VARCHAR
金额	DECIMAL(18,2)	FLOAT/DOUBLE
手机号	CHAR(11)	VARCHAR
文本	VARCHAR(N)	TEXT（除非需要）
布尔值	TINYINT(1)	CHAR(1)
IP 地址	INT UNSIGNED	VARCHAR(15)
枚举值	ENUM 或 TINYINT	VARCHAR

数据类型的存储空间：

类型	存储空间
TINYINT	1 字节
SMALLINT	2 字节
INT	4 字节
BIGINT	8 字节
FLOAT	4 字节
DOUBLE	8 字节
DECIMAL(M,D)	取决于 M 和 D
CHAR(N)	N 字符（定长）
VARCHAR(N)	实际长度 + 1-2 字节
TEXT	实际长度 + 2 字节
DATETIME	5 字节（5.6.4+）
TIMESTAMP	4 字节
DATE	3 字节

4.2 范式化与反范式化

范式化：

• 减少数据冗余，保证数据一致性。
• 查询需要 JOIN 多张表，可能影响性能。

反范式化：

• 增加冗余字段，避免 JOIN。
• 提高读取性能，但增加写入成本和数据不一致风险。

实战建议：

• OLTP 系统（高并发读写）：3NF 为主，热点数据适当反范式。
• OLAP 系统（数据分析）：反范式化，使用星型/雪花模型。
• 订单表：通常保留冗余字段（如商品名称、价格快照），避免历史数据变更影响。

-- 范式化：订单表只存 product_id
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    product_id BIGINT,
    price DECIMAL(10,2),
    -- 需要 JOIN 获取商品名称
);

-- 反范式化：冗余商品名称
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    product_id BIGINT,
    product_name VARCHAR(100),  -- 冗余字段
    price DECIMAL(10,2),
    -- 不需要 JOIN，直接获取商品名称
);

4.3 垂直分表

  将表的列按访问频率拆分，常用列放主表，不常用列放扩展表。

-- 主表（高频访问）
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

-- 扩展表（低频访问）
CREATE TABLE users_ext (
    id BIGINT PRIMARY KEY,
    bio TEXT,
    avatar VARCHAR(255),
    settings JSON
);

优势：

• 减少单行数据大小，提高 Buffer Pool 缓存效率。
• 减少 SELECT * 带来的不必要 I/O。

4.4 水平分表

  将表的数据按行拆分，每张表结构相同，数据不同。

-- 按年分表
CREATE TABLE orders_2023 (
    id BIGINT PRIMARY KEY,
    ...
);
CREATE TABLE orders_2024 (
    id BIGINT PRIMARY KEY,
    ...
);

优势：

• 减少单表数据量，提高查询性能。
• 便于数据归档和清理。

缺点：

• 查询需要跨表时增加复杂度。
• 需要额外的路由逻辑。

---

5. SQL 优化实战案例

5.1 案例一：多表分页查询优化

-- 原始 SQL（慢）
SELECT o.*, u.name AS user_name, p.name AS product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
ORDER BY o.create_time DESC
LIMIT 100000, 20;
-- 问题：JOIN 后排序，深分页扫描大量数据

-- 优化后
SELECT o.*, u.name AS user_name, p.name AS product_name
FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20
) AS tmp ON o.id = tmp.id
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
ORDER BY o.create_time DESC;
-- 先通过覆盖索引分页获取主键，再 JOIN

5.2 案例二：OR 条件优化

-- 原始 SQL（OR 两边索引可能都不能用）
SELECT * FROM users WHERE name = 'Alice' OR age = 25;

-- 优化：使用 UNION ALL
SELECT * FROM users WHERE name = 'Alice'
UNION ALL
SELECT * FROM users WHERE age = 25 AND name != 'Alice';
-- 需要 name 和 age 各自有索引

5.3 案例三：批量插入优化

-- 优化前：逐条插入
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
-- 每条都需要事务提交和索引维护

-- 优化后：批量插入
INSERT INTO users (name, age) VALUES 
('Alice', 25),
('Bob', 30),
('Charlie', 35);

-- 大批量插入（百万级）
-- 方案一：关闭自动提交
SET autocommit = 0;
INSERT INTO users ...;
INSERT INTO users ...;
COMMIT;
SET autocommit = 1;

-- 方案二：使用 LOAD DATA
LOAD DATA INFILE '/path/to/data.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
(name, age);

5.4 案例四：大表 DDL 优化

-- 直接 ALTER 会锁表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- MySQL 8.0+ 支持 INSTANT DDL（仅修改元数据，秒级完成）
ALTER TABLE users ADD COLUMN phone VARCHAR(20), ALGORITHM=INSTANT;

-- 大表添加索引（MySQL 5.6+ 支持 Online DDL）
ALTER TABLE users ADD INDEX idx_phone(phone), ALGORITHM=INPLACE, LOCK=NONE;

-- 使用 pt-online-schema-change（Percona Toolkit，5.5 及之前）
-- pt-online-schema-change --alter "ADD COLUMN phone VARCHAR(20)" D=test,t=users

---

6. 生产环境监控与调优

6.1 关键性能指标

-- 查看连接数
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connections';

-- 查看查询统计
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Slow_queries';

-- 查看表锁等待
SHOW STATUS LIKE 'Table_locks%';

-- 查看临时表使用
SHOW STATUS LIKE 'Created_tmp%';

-- 查看排序
SHOW STATUS LIKE 'Sort%';

-- 查看 Handler 统计
SHOW STATUS LIKE 'Handler%';

6.2 实时监控

-- 查看当前运行的查询
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM performance_schema.data_lock_waits;

-- 查看最近的事务
SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_started DESC LIMIT 10;

6.3 Profile 分析

-- 开启 profiling
SET profiling = 1;

-- 执行查询
SELECT * FROM users WHERE name = 'Alice';

-- 查看所有 profile
SHOW PROFILES;

-- 查看具体查询的详细 profile
SHOW PROFILE FOR QUERY 1;

-- 查看 CPU、IO 等详细信息
SHOW PROFILE CPU, BLOCK IO, SWAPS FOR QUERY 1;

---

7. 总结

• EXPLAIN 是 SQL 优化的核心工具，重点关注 type、key、rows、Extra 字段。
• 慢查询日志配合 mysqldumpslow/pt-query-digest 是定位慢 SQL 的利器。
• LIMIT 深分页用游标分页或延迟关联优化。
• JOIN 优化核心是小表驱动大表，被驱动表关联字段建索引。
• 子查询尽量改写为 JOIN，IN 和 EXISTS 根据表大小选择。
• ORDER BY 和 GROUP BY 尽量利用索引，避免 Using filesort 和 Using temporary。
• 表设计要考虑数据类型、范式与反范式、垂直/水平分表。
• 生产环境定期监控关键指标，使用 Performance Schema 分析性能。