面对复杂SQL慢查询，有哪些底层分析方法？

面试场景

面试官：”给你一条慢SQL，你的分析思路是什么？”

SQL优化不是猜测，而是有方法论的系统分析。本文讲解底层分析方法，帮助你形成体系化的优化思维。

---

方法论框架

慢SQL
  │
  ├── 1. 定量分析：慢在哪里？（Profile）
  │
  ├── 2. 执行计划：怎么执行的？（EXPLAIN）
  │
  ├── 3. 成本分析：为什么这样执行？（成本模型）
  │
  └── 4. 优化方案：如何改进？（索引/SQL重写）

---

方法一：EXPLAIN执行计划

基础用法

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

关键字段解读

字段	含义	关注点
type	访问类型	system > const > eq_ref > ref > range > index > ALL
key	实际使用的索引	NULL表示没用索引
rows	预估扫描行数	越小越好
Extra	额外信息	Using filesort/Using temporary要警惕

type详解

const     ─── 主键/唯一索引等值查询
eq_ref    ─── 多表JOIN，被驱动表主键/唯一索引
ref       ─── 普通索引等值查询
range     ─── 索引范围扫描
index     ─── 全索引扫描
ALL       ─── 全表扫描（最差）

Extra关键信息

Extra	含义	是否需优化
Using index	覆盖索引	✅ 好
Using where	需要回表过滤	⚠️ 一般
Using filesort	额外排序	❌ 需优化
Using temporary	使用临时表	❌ 需优化
Using index condition	索引下推（ICP）	✅ 好

---

方法二：EXPLAIN ANALYZE（MySQL 8.0.18+）

执行并获取实际指标

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE user_id = 123;

输出示例

-> Index lookup on orders using idx_user_id (user_id=123)
   (cost=35.13 rows=50) 
   (actual time=0.089..0.241 rows=47 loops=1)

字段	含义
cost	优化器预估成本
rows	预估行数
actual time	实际执行时间
actual rows	实际返回行数

价值：可以看到预估与实际的差距，判断统计信息是否准确。

---

方法三：Optimizer Trace

查看优化器选择执行计划的完整决策过程。

开启Trace

SET optimizer_trace = 'enabled=on';

SELECT * FROM orders WHERE user_id = 123 AND status = 1;

SELECT * FROM information_schema.optimizer_trace\G

关键信息

{
  "considered_execution_plans": [
    {
      "plan_prefix": [],
      "table": "orders",
      "best_access_path": {
        "considered_access_paths": [
          {
            "access_type": "ref",
            "index": "idx_user_id",
            "rows": 50,
            "cost": 35.13,
            "chosen": true
          },
          {
            "access_type": "ref", 
            "index": "idx_status",
            "rows": 500000,
            "cost": 3500.00,
            "chosen": false  // 成本高，未选择
          }
        ]
      }
    }
  ]
}

---

方法四：Profile性能分析

开启Profile

SET profiling = 1;

SELECT * FROM orders WHERE user_id = 123;

SHOW PROFILE FOR QUERY 1;

输出示例

Status	Duration
starting	0.000012
checking permissions	0.000005
Opening tables	0.000021
Sending data	0.523456
end	0.000003

分析：Sending data耗时最长，说明是数据读取慢（可能涉及大量回表）。

详细分析

SHOW PROFILE ALL FOR QUERY 1;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

---

方法五：索引统计信息分析

查看索引基数

SHOW INDEX FROM orders;

Column_name	Cardinality	含义
user_id	100000	不同值的数量
status	5	不同值很少，区分度低

原则：高区分度的字段适合建索引。

查看数据分布

SELECT status, COUNT(*) 
FROM orders 
GROUP BY status;

如果90%的数据status=1，那么WHERE status=1的索引效果会很差。

---

方法六：慢查询日志

开启慢查询日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

分析工具

# mysqldumpslow汇总分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# pt-query-digest更强大
pt-query-digest /var/log/mysql/slow.log

---

实战分析案例

案例：ORDER BY优化

原始SQL：

SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY create_time DESC 
LIMIT 10;

执行计划：

type: ref, key: idx_user_id
Extra: Using filesort  -- 有额外排序！

分析：

• 用了idx_user_id定位数据
• 但还需要额外排序（filesort）

优化方案：建立联合索引

ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);

优化后执行计划：

type: ref, key: idx_user_time
Extra: Using index condition  -- 无filesort

---

优化决策树

慢SQL
  │
  ├─ type=ALL? → 考虑添加索引
  │
  ├─ Using filesort? → 考虑优化ORDER BY（联合索引）
  │
  ├─ Using temporary? → 考虑优化GROUP BY/DISTINCT
  │
  ├─ rows很大? → 检查WHERE条件是否命中索引
  │
  └─ key=NULL? → 检查索引是否存在/是否失效

---

面试答题框架

分析工具五件套：
1. EXPLAIN - 查看执行计划
2. EXPLAIN ANALYZE - 对比预估与实际（8.0+）
3. Optimizer Trace - 了解优化器决策过程
4. Profile - 定位具体耗时环节
5. 慢查询日志 - 发现线上问题SQL

分析思路：
- 看type：ALL/index需警惕
- 看rows：预估扫描行数是否合理
- 看Extra：filesort/temporary需优化
- 看统计信息：是否准确

---

总结

方法	作用	使用时机
EXPLAIN	查看执行计划	必备，每次优化都用
EXPLAIN ANALYZE	获取实际执行指标	需要对比预估与实际
Optimizer Trace	了解优化器决策	理解为什么选这个计划
Profile	定位耗时环节	分析时间花在哪里
慢查询日志	发现问题SQL	线上监控