一、执行计划概述
执行计划是数据库优化器生成的查询执行方案，描述了数据库如何执行SQL查询。通过分析执行计划，可以了解查询的执行路径、索引使用情况、数据扫描方式等，从而找出性能瓶颈。
二、执行计划分析
2.1 EXPLAIN命令
使用EXPLAIN命令查看执行计划：
EXPLAIN SELECT * FROM orders WHERE status = 'completed' AND order_date > '2024-01-01';
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'completed'; -- PostgreSQL执行并分析2.2 执行计划输出
| 字段 | 说明 | 重要性 |
|---|---|---|
| id | 查询序列号 | 中 |
| select_type | 查询类型 | 高 |
| table | 表名 | 高 |
| type | 访问类型 | 高 |
| key | 使用的索引 | 高 |
| rows | 扫描行数 | 高 |
| Extra | 额外信息 | 高 |
2.3 访问类型（type）
访问类型从优到劣排序：
graph LR
A[system] --> B[const]
B --> C[eq_ref]
C --> D[ref]
D --> E[fulltext]
E --> F[ref_or_null]
F --> G[index_merge]
G --> H[unique_subquery]
H --> I[index_subquery]
I --> J[range]
J --> K[index]
K --> L[ALL]
style A fill:#4CAF50
style B fill:#8BC34A
style C fill:#CDDC39
style D fill:#FFC107
style E fill:#FF9800
style F fill:#FF5722
style G fill:#F44336
style H fill:#E91E63
style I fill:#9C27B0
style J fill:#673AB7
style K fill:#3F51B5
style L fill:#2196F3
2.4 Extra字段解读
| Extra值 | 说明 | 优化建议 |
|---|---|---|
| Using index | 使用覆盖索引 | 良好 |
| Using where | 使用WHERE过滤 | 良好 |
| Using filesort | 外部排序 | 需要优化 |
| Using temporary | 使用临时表 | 需要优化 |
| Range checked for each record | 逐行范围检查 | 需要优化 |
三、查询优化策略
3.1 避免全表扫描
-- 不好：全表扫描
SELECT * FROM orders WHERE amount > 1000;
-- 好：使用索引
CREATE INDEX idx_amount ON orders(amount);
SELECT * FROM orders WHERE amount > 1000;3.2 优化JOIN查询
-- 不好：驱动表选择不当
SELECT * FROM small_table s JOIN large_table l ON s.id = l.id;
-- 好：小表作为驱动表
SELECT /*+ STRAIGHT_JOIN */ * FROM small_table s JOIN large_table l ON s.id = l.id;
-- 添加JOIN索引
CREATE INDEX idx_large_id ON large_table(id);3.3 优化子查询
-- 不好：相关子查询
SELECT * FROM orders o WHERE EXISTS (
SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 'active'
);
-- 好：使用JOIN替代
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'active';
-- 不好：IN子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active');
-- 好：使用JOIN替代
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'active';3.4 优化聚合查询
-- 不好：无法使用索引
SELECT COUNT(*) FROM orders WHERE YEAR(order_date) = 2024;
-- 好：可以使用索引
SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- 添加聚合索引
CREATE INDEX idx_order_date_status ON orders(order_date, status);
SELECT status, COUNT(*) FROM orders WHERE order_date > '2024-01-01' GROUP BY status;3.5 优化分页查询
-- 不好：OFFSET效率低
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10000, 10;
-- 好：使用索引条件
SELECT * FROM orders WHERE order_date < last_date ORDER BY order_date DESC LIMIT 10;
-- 使用覆盖索引
SELECT id FROM orders ORDER BY order_date DESC LIMIT 10000, 10;
SELECT * FROM orders WHERE id IN (...);四、执行计划分析案例
4.1 案例一：全表扫描优化
-- 原始查询
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- 执行计划显示：type = ALL（全表扫描）
-- Extra = Using where
-- 优化方案：添加索引
CREATE INDEX idx_email ON users(email);
-- 优化后：type = ref（索引查找）4.2 案例二：JOIN优化
-- 原始查询
EXPLAIN SELECT * FROM orders o JOIN products p ON o.product_id = p.id WHERE p.category = 'electronics';
-- 执行计划显示：orders表全表扫描
-- 优化方案：添加索引
CREATE INDEX idx_products_category_id ON products(category, id);
CREATE INDEX idx_orders_product_id ON orders(product_id);
-- 优化后：使用索引连接4.3 案例三：排序优化
-- 原始查询
EXPLAIN SELECT * FROM orders WHERE status = 'completed' ORDER BY order_date DESC;
-- 执行计划显示：Extra = Using filesort
-- 优化方案：添加覆盖索引
CREATE INDEX idx_status_order_date ON orders(status, order_date DESC);
-- 优化后：Extra = Using index五、查询重写技巧
5.1 使用UNION替代OR
-- 不好
SELECT * FROM users WHERE status = 1 OR status = 2;
-- 好
SELECT * FROM users WHERE status = 1
UNION ALL
SELECT * FROM users WHERE status = 2;5.2 使用IN替代OR
-- 不好
SELECT * FROM users WHERE status = 1 OR status = 2 OR status = 3;
-- 好
SELECT * FROM users WHERE status IN (1, 2, 3);5.3 使用LIMIT限制结果
-- 不好
SELECT * FROM logs WHERE level = 'error';
-- 好
SELECT * FROM logs WHERE level = 'error' LIMIT 100;5.4 避免SELECT *
-- 不好
SELECT * FROM users WHERE id = 1;
-- 好
SELECT id, name, email FROM users WHERE id = 1;六、优化器提示
6.1 MySQL优化器提示
-- 使用指定索引
SELECT /*+ USE INDEX (idx_name) */ * FROM users WHERE name = 'John';
-- 忽略索引
SELECT /*+ IGNORE INDEX (idx_name) */ * FROM users WHERE name = 'John';
-- 强制索引
SELECT /*+ FORCE INDEX (idx_name) */ * FROM users WHERE name = 'John';
-- 优化JOIN顺序
SELECT /*+ STRAIGHT_JOIN */ * FROM a JOIN b ON a.id = b.id;6.2 PostgreSQL优化器提示
-- 使用指定索引
SELECT * FROM users WHERE name = 'John' INDEX idx_name;
-- 设置查询成本
SET enable_seqscan = OFF;
-- 设置并行度
SET max_parallel_workers_per_gather = 4;七、性能监控与调优
7.1 慢查询日志
# MySQL慢查询配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
# 分析慢查询
mysqldumpslow /var/log/mysql/slow.log7.2 性能监控工具
| 工具 | 用途 | 适用数据库 |
|---|---|---|
| pt-query-digest | 分析慢查询日志 | MySQL |
| pg_stat_statements | 统计查询性能 | PostgreSQL |
| SQL Profiler | 实时监控查询 | SQL Server |
7.3 性能调优流程
flowchart TD
A[发现性能问题] --> B[收集慢查询]
B --> C[分析执行计划]
C --> D{问题类型}
D --> E[全表扫描]
D --> F[索引失效]
D --> G[JOIN效率低]
D --> H[排序效率低]
E --> E1[添加索引]
F --> F1[修复索引条件]
G --> G1[优化JOIN]
H --> H1[优化排序]
E1 --> I[验证效果]
F1 --> I
G1 --> I
H1 --> I
I --> J{是否达标}
J --> K[是]
J --> L[否]
K --> M[完成]
L --> C
八、查询优化最佳实践
8.1 先分析后优化
使用EXPLAIN分析执行计划，不要盲目优化。
8.2 优先优化慢查询
根据慢查询日志，优先优化最耗时的查询。
8.3 测试优化效果
优化前后进行性能测试，确保优化有效。
8.4 避免过度优化
不要为了微小的性能提升而牺牲代码可读性。
8.5 定期审查查询
定期审查系统中的SQL查询，发现潜在问题。
九、总结
执行计划分析是查询优化的核心，通过理解执行计划的各个字段，能够准确找出查询性能瓶颈。结合索引优化、查询重写和优化器提示，能够显著提升数据库查询性能。