数据库03：SQL优化

SQL 优化是后端开发的必修课。一个慢 SQL 可能拖垮整个系统，而一次有效的优化能让查询速度提升几十倍甚至上百倍。

这篇文章围绕四个核心点：慢查询分析流程、执行计划解读、索引设计原则、分库分表策略，建立一套完整的 SQL 性能优化方法论。

慢查询分析：从现象到根因

优化 SQL 的第一步是找到慢查询，然后分析它为什么慢。

慢查询日志配置

# MySQL 配置
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # 超过 2 秒记录
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10  # 每分钟最多记录 10 条

分析工具

工具	功能	适用场景
EXPLAIN	查看执行计划	单条 SQL 分析
pt-query-digest	分析慢查询日志	批量慢查询分析
Performance Schema	实时性能监控	正在执行的查询
pg_stat_statements	PG 性能统计	PG 查询分析

分析流程

1. 采集慢查询：开启慢查询日志，收集超过阈值的 SQL
2. 排序统计：按执行次数、总耗时、平均耗时排序
3. 执行计划：对 Top 慢查询执行 EXPLAIN
4. 定位瓶颈：索引缺失、全表扫描、JOIN 顺序不合理
5. 优化验证：修改 SQL，验证执行时间
6. 监控回归：确认优化后不再出现在慢查询日志中

EXPLAIN：解读执行计划

EXPLAIN 是 SQL 优化的核心工具，它告诉你 MySQL 如何执行这条 SQL。

EXPLAIN 输出字段解读

字段	含义	重点关注
id	查询序号	子查询或 UNION 的顺序
select_type	查询类型	SIMPLE、SUBQUERY、DERIVED、UNION
table	表名	表的访问顺序
type	访问类型	ALL、index、range、ref、eq_ref、const
key	使用的索引	NULL 表示未使用索引
rows	预估扫描行数	越少越好
Extra	额外信息	Using index、Using where、Using filesort、Using temporary

type 类型优先级（从快到慢）

1. const：常量查询，只查一行
2. eq_ref：主键或唯一索引等值查询
3. ref：普通索引等值查询
4. range：索引范围查询（>、<、BETWEEN、IN）
5. index：扫描整个索引树
6. ALL：全表扫描（最慢）

Extra 关键字解读

• Using index：覆盖索引，不需要回表
• Using where：需要过滤数据
• Using filesort：需要额外排序（性能差）
• Using temporary：需要临时表（性能差）
• Using join buffer：JOIN 缓冲区不够（数据量大）

实战示例

-- 慢查询
SELECT * FROM orders WHERE status = 'completed' AND created_at > '2024-01-01';

-- EXPLAIN 输出
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 10000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+

-- 问题：type=ALL，全表扫描，没有使用索引
-- 优化：创建联合索引
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

索引设计原则：不是越多越好

索引能加速查询，但会减慢写入。设计索引需要在查询性能和写入性能之间找到平衡。

索引设计原则

1. 最左前缀原则：联合索引 (a, b, c) 能匹配 a、a+b、a+b+c
2. 选择度高：区分度高的列适合做索引（性别列区分度低，不适合）
3. 覆盖索引：查询只需要索引列，不需要回表
4. 避免重复索引：(a, b) 和 (a) 是重复的，(a) 是冗余的
5. 删除无用索引：定期清理未使用的索引

索引类型选择

场景	推荐索引
等值查询	B-Tree 索引
范围查询	B-Tree 索引
全文搜索	全文索引（PG）或 Elasticsearch
模糊查询前缀	B-Tree 索引（如 name LIKE 'abc%'）
模糊查询后缀	无法使用索引，考虑全文搜索

反模式

• 过度索引：每个字段都建索引，写入性能急剧下降
• 索引列使用函数：WHERE DATE(create_time) = ? 会导致索引失效
• 隐式类型转换：WHERE id = '123'（字符串转数字）会导致索引失效
• OR 条件：WHERE a = ? OR b = ? 需要两个独立索引

SQL 改写技巧

同样的查询，不同的写法性能差异巨大。

避免 SELECT *

-- 慢
SELECT * FROM users WHERE id = 1;

-- 快（覆盖索引）
SELECT id, name, email FROM users WHERE id = 1;

子查询改 JOIN

-- 慢（相关子查询）
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

-- 快
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'active';

LIMIT 优化

-- 慢（跳过大量数据）
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 10;

-- 快（使用游标或覆盖索引）
SELECT * FROM orders WHERE id > (SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 1) LIMIT 10;

避免大表 JOIN

-- 慢（两个大表 JOIN）
SELECT * FROM orders o JOIN order_items oi ON o.id = oi.order_id WHERE o.status = 'completed';

-- 快（先过滤小结果集）
SELECT * FROM (SELECT id FROM orders WHERE status = 'completed') o JOIN order_items oi ON o.id = oi.order_id;

分库分表：突破单库瓶颈

当单库数据量达到千万级别，索引优化已经不够了，需要分库分表。

分库分表策略

策略	方式	优点	缺点
垂直分库	按业务拆分	简单，隔离性好	单表数据量大
垂直分表	按列拆分	减少行大小，提升缓存命中率	需要 JOIN
水平分库	按数据范围拆分	扩展性好	跨库查询复杂
水平分表	按数据范围拆分	单表数据量可控	需要路由规则

分片键选择

类型	选择	适用场景
范围分片	created_at、id	时间序列数据，冷热分离
Hash 分片	user_id、order_id	均匀分布，读写均衡
列表分片	region、status	业务规则明确

跨分片查询问题

• 分页查询：需要合并多个分片的结果
• 聚合查询：需要在应用层做二次聚合
• 事务：分布式事务，复杂度高

常用方案

方案	工具	适用场景
分库分表中间件	ShardingSphere、MyCat	已有项目迁移
分布式数据库	TiDB、OceanBase	新项目，需要分布式 SQL
应用层路由	自定义分片逻辑	简单场景，可控性强

项目判断清单

• SQL 执行超过 2 秒 → 开启慢查询日志，分析执行计划
• type=ALL 且 rows 很大 → 添加索引
• Extra 出现 Using filesort / Using temporary → 优化排序和分组
• 单表数据量超过 1000 万 → 考虑分表
• QPS 超过 1 万 → 考虑读写分离或缓存
• 索引数量超过 5 个 → 评估是否有冗余索引