1. 查询执行流程
SQL 语句
→ 词法/语法分析 → 解析树（Parse Tree）
→ 语义检查（表/列存在性、权限）
→ 查询重写（视图展开、子查询优化、常量传播）
→ 生成候选执行计划
→ 代价估算（统计信息 + 代价模型）
→ 选择最优计划
→ 执行引擎执行
2. EXPLAIN 详解
2.1 EXPLAIN 输出列
EXPLAIN ANALYZE
SELECT e.name, d.name, SUM(s.amount)
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN sales s ON e.id = s.emp_id
WHERE s.date > '2024-01-01'
GROUP BY e.id, d.name;
| 列 | 说明 | 关键值 |
|---|---|---|
id | 执行顺序编号 | 相同 id 从上到下执行 |
select_type | 查询类型 | SIMPLE/PRI/ SUBQUERY/DERIVED |
table | 访问的表 | 别名/子查询 |
type | 访问类型 | ** ALL < index < range < ref < eq_ref < const ** |
possible_keys | 可能用的索引 | |
key | 实际用的索引 | |
key_len | 索引使用长度 | 越短越好（但需完整匹配） |
ref | 索引对比值 | const/列名 |
rows | 估算扫描行数 | 越小越好 |
filtered | 过滤后剩余比例 | 越高越好 |
Extra | 额外信息 | Using index 好，Using filesort 差 |
2.2 type 列优化目标
| type | 说明 | 场景 |
|---|---|---|
system | 系统表，仅一行 | 极罕见 |
const | 主键/唯一索引等值 | WHERE id = 1 |
eq_ref | JOIN 主键/唯一索引 | JOIN 条件为主键 |
ref | 普通索引等值 | WHERE status = 'active' |
range | 索引范围 | WHERE id BETWEEN 1 AND 100 |
index | 全索引扫描 | 索引覆盖 SELECT 列 |
ALL | 全表扫描 | 无索引或索引未命中 |
优化目标：至少要达到 range，最好是 ref 或 eq_ref。
2.3 Extra 列关注
| 值 | 含义 | 好坏 |
|---|---|---|
| Using index | 覆盖索引 | ✅ 好 |
| Using where | WHERE 过滤 | ⚠️ 正常 |
| Using filesort | 额外排序 | ❌ 差（内存/磁盘排序） |
| Using temporary | 临时表 | ❌ 差（GROUP BY/DISTINCT） |
| Using join buffer | 连接缓存 | ⚠️ 大表 JOIN 需要 |
| Impossible WHERE | 永远为假 | ✅ 优化器直接返回空 |
3. 统计信息与代价模型
3.1 统计信息类型
表级统计：
- 行数（rows）
- 数据页数
- 平均行长度
列级统计（直方图/采样）：
- 不同值数量（Cardinality）
- 最小/最大值
- NULL 比例
- 数据分布（直方图，8.0+）
索引统计：
- 索引页数
- 索引 Cardinality
3.2 代价估算
代价 = I/O 代价 + CPU 代价
单表查询估算：
- 全表扫描代价 = 数据页数 × 每页 I/O 代价
- 索引扫描代价 = 索引页数 × I/O 代价 + 回表行数 × I/O 代价
多表 JOIN 估算：
- Nested Loop：外表行数 × 内表单次查找代价
- Hash Join：构建哈希表代价 + 探测代价
- Merge Join：排序代价 + 合并代价
4. 优化技巧
4.1 常见慢查询优化
-- 1. 避免 SELECT *
SELECT id, name FROM users WHERE status = 'active';
-- 比 SELECT * 更可能走覆盖索引
-- 2. 范围查询后的列用不了索引
CREATE INDEX idx_a_b ON t(a, b);
SELECT * FROM t WHERE a > 1 AND b = 2;
-- a 是范围，b 不走索引
-- 解决：INDEX(a) 或改写为多个 = 条件
-- 3. 大偏移分页优化（1000000, 10）
-- ❌ 慢：扫描 1000010 行，取 10 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- ✅ 快：先查 id，再关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) tmp
ON o.id = tmp.id;
-- 4. 隐式转换导致索引失效
WHERE phone = 13800138000 -- phone 是 varchar，先转 int
-- ✅ WHERE phone = '13800138000'
-- 5. IN vs EXISTS
-- 大表 IN 小表 → EXISTS 更优
-- 小表 IN 大表 → IN 更优
4.2 JOIN 优化
-- 驱动表选择：小表驱动大表
SELECT * FROM small_table s
JOIN big_table b ON s.id = b.small_id;
-- small_table 作为驱动表（外表）
-- 对 big_table 走索引查找
-- 避免笛卡尔积
SELECT * FROM a, b; -- 如果没有连接条件 → CROSS JOIN
SELECT * FROM a JOIN b ON a.id = b.a_id; -- ✅ 正确
5. 慢查询分析流程
1. 发现慢查询
- 开启慢查询日志：slow_query_log = ON, long_query_time = 1
- 或 performance_schema.events_statements_summary_by_digest
2. 获取执行计划
EXPLAIN ANALYZE SELECT ...;
3. 分析瓶颈
- type = ALL？→ 加索引
- Extra = Using filesort？→ 排序字段加入索引
- Extra = Using temporary？→ 优化 GROUP BY
- rows 很大？→ 减少扫描范围或加索引
4. 验证优化效果
- 对比优化前后的 EXPLAIN
- 使用 bench 测试真实性能
延伸阅读
继续阅读
探索更多技术文章
浏览归档，发现更多关于系统设计、工具链和工程实践的内容。