07.2 - 慢查询排查方法论

定位：慢查询排查是 MySQL 生产环境最常用的技能——系统性的排查流程远比死记硬背技巧更重要 面试高频度：⭐⭐⭐⭐ 考查方式：慢查询排查的完整流程、怎么定位瓶颈、典型案例分析

---

一、排查流程总览

                   慢查询排查方法论

  1. 发现慢查询
  2. 获取执行计划
  3. 分析瓶颈
  4. 优化方案
  5. 验证效果

  ┌──────────────────────────────────────────────────┐
  │ Step 1: 发现                                     │
  │   slow_query_log → SET long_query_time=1         │
  │   SHOW PROCESSLIST → 看当前运行中的慢查询         │
  │   业务监控 → 用户反馈"页面上得慢"                 │
  └──────────────────┬───────────────────────────────┘
                     ▼
  ┌──────────────────────────────────────────────────┐
  │ Step 2: 获取执行计划                              │
  │   DESCRIBE / EXPLAIN [慢 SQL]                    │
  │   EXPLAIN FORMAT=TREE [慢 SQL] (8.0+)            │
  │   SHOW WARNINGS → 查看优化器改写后的 SQL          │
  └──────────────────┬───────────────────────────────┘
                     ▼
  ┌──────────────────────────────────────────────────┐
  │ Step 3: 分析瓶颈                                  │
  │                                                   │
  │  ① 是不是全表扫描？ (type=ALL)                    │
  │  ② 有没有用索引？ (key=NULL?)                     │
  │  ③ 扫描行数多吗？ (rows >> 实际结果)              │
  │  ④ 排序用临时表？ (Extra: Using filesort)        │
  │  ⑤ 分组用临时表？ (Extra: Using temporary)       │
  └──────────────────┬───────────────────────────────┘
                     ▼
  ┌──────────────────────────────────────────────────┐
  │ Step 4: 优化方案                                  │
  │   ① 添加或优化索引                                │
  │   ② 改写 SQL（避免函数、隐式转换等）               │
  │   ③ 修改表结构（加冗余字段、拆分表等）             │
  │   ④ 业务逻辑优化（减少查询频次、缓存）             │
  └──────────────────┬───────────────────────────────┘
                     ▼
  ┌──────────────────────────────────────────────────┐
  │ Step 5: 验证效果                                  │
  │   再次 EXPLAIN 确认执行计划变了                    │
  │   实际执行查看耗时                                 │
  │   观察慢查询日志不再记录                           │
  └──────────────────────────────────────────────────┘

---

二、典型案例

案例1：索引失效

-- 有索引的列上用了函数
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';
-- EXPLAIN: type=ALL, rows=500000

-- 优化后: 用范围查询替代函数
SELECT * FROM orders 
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';
-- EXPLAIN: type=range, rows=200

案例2：隐式类型转换

-- phone 列是 VARCHAR，但传入的是数字
SELECT * FROM users WHERE phone = 13800138000;
-- 隐式转换导致索引失效
-- EXPLAIN: type=ALL

-- 优化后: 用字符串查询
SELECT * FROM users WHERE phone = '13800138000';
-- EXPLAIN: type=ref

案例3：大分页查询

-- MySQL 需要扫到 offset+limit 行再丢弃
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 优化后: 基于游标的分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

---

三、常用诊断命令

-- 当前正在运行的查询
SHOW PROCESSLIST;
SELECT * FROM performance_schema.processlist;

-- 查看超过 3 秒的查询
SELECT * FROM sys.processlist 
WHERE conn_id > 0 AND time > 3;

-- 查看访问最多的表
SELECT * FROM sys.schema_table_statistics 
ORDER BY rows_fetched DESC;

-- 查看没有用索引的查询
SELECT * FROM sys.statements_with_full_table_scans;

---

📚 相关链接

• EXPLAIN 详解：**EXPLAIN与查询优化**
• 索引优化：**索引优化策略**
• Buffer Pool：**Buffer Pool配置与优化**
• ← 返回 **性能调优索引**