MySQL 慢 SQL详解

MySQL 慢 SQL 是指执行时间超过预设阈值的 SQL 语句，是数据库性能优化的核心切入点。深入理解慢 SQL 的分析与优化，对提升系统性能至关重要。以下从原因、定位、分析、优化、监控五个维度详解：

---

一、慢 SQL 的常见原因

类别	具体原因
索引失效	1. 未建索引 2. 索引被忽略（如函数操作、隐式类型转换） 3. 使用 !=、NOT IN 4. 最左前缀原则失效 5. 索引统计信息不准确
SQL 写法问题	1. SELECT * 导致回表 2. 复杂 JOIN 或子查询 3. GROUP BY/ORDER BY 未用索引 4. 深分页（LIMIT 1000000,10）
数据量过大	1. 单表数据量超千万 2. 未合理分库分表 3. 大字段（TEXT/BLOB）频繁读写
锁竞争	1. 长事务阻塞 2. 行锁升级为表锁 3. 热点行更新（如库存超卖场景）
系统资源瓶颈	1. CPU/内存/磁盘 IO 过载 2. 网络延迟 3. Buffer Pool 命中率低
配置不当	1. 参数不合理（如 join_buffer_size 过小） 2. 未开启查询缓存（仅限特定场景）

---

二、定位慢 SQL：四大工具

1. 慢查询日志（Slow Query Log）

• 开启方式：

  SET GLOBAL slow_query_log = ON;
  SET GLOBAL long_query_time = 1;  -- 阈值设为1秒
  SET GLOBAL slow_query_log_file = '/path/to/slow.log';
  

• 分析工具：
  • mysqldumpslow：MySQL 自带工具

    mysqldumpslow -s t -t 10 /path/to/slow.log  # 按耗时排序，取TOP10
    

  • pt-query-digest（Percona Toolkit）：

    pt-query-digest /path/to/slow.log > slow_report.txt
    

2. EXPLAIN 执行计划

• 关键字段解读：

  字段	说明
  type	ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（索引查找）
  key	实际使用的索引
  rows	预估扫描行数（值越大性能越差）
  Extra	Using filesort（额外排序）、Using temporary（临时表）

• 使用示例：

  EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
  

3. 性能模式（Performance Schema）

• 监控 SQL 执行细节：

  -- 查看耗时TOP10的SQL
  SELECT query, exec_count, avg_latency 
  FROM sys.statement_analysis 
  ORDER BY avg_latency DESC 
  LIMIT 10;
  

4. 实时监控（SHOW PROCESSLIST）

• 抓取当前运行的长查询：

  SHOW FULL PROCESSLIST;  -- 查看State为"Sending data"或"Sorting result"的SQL
  

---

三、深度分析：慢 SQL 的优化手段

1. 索引优化

• 场景示例：

  -- 原始SQL（未用索引）
  SELECT * FROM users WHERE DATE(create_time) = '2023-10-01';
  
  -- 优化方案：避免函数操作
  SELECT * FROM users 
  WHERE create_time BETWEEN '2023-10-01 00:00:00' AND '2023-10-01 23:59:59';
  

• 索引设计原则：
  • 联合索引遵循最左前缀原则
  • 区分度高的字段在前（如 user_id 比 status 更适合做前缀）

2. SQL 重写

• 子查询 → JOIN：

  -- 优化前
  SELECT * FROM users 
  WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
  
  -- 优化后
  SELECT u.* FROM users u
  JOIN orders o ON u.id = o.user_id 
  WHERE o.amount > 1000;
  

• 深分页优化：

  -- 低效写法
  SELECT * FROM logs ORDER BY id LIMIT 1000000, 10;
  
  -- 高效写法（利用主键）
  SELECT * FROM logs 
  WHERE id > (SELECT id FROM logs ORDER BY id LIMIT 1000000, 1)
  ORDER BY id LIMIT 10;
  

3. 架构层优化

策略	适用场景
读写分离	读多写少，缓解主库压力
分库分表	单表数据量过大（如按 user_id 分片）
冷热分离	历史数据归档（如将3个月前订单转存到历史表）

4. 规避锁竞争

• 事务拆分为小操作
• 使用 SELECT ... FOR UPDATE SKIP LOCKED（MySQL 8.0+ 避开锁等待）
• 热点行更新改为队列异步处理

---

四、高级技巧：执行计划深度解读

1. 索引覆盖（Covering Index）

• 现象：EXPLAIN 的 Extra 列出现 Using index
• 原理：查询字段全部包含在索引中，无需回表
• 优化示例：

  -- 创建覆盖索引
  ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
  
  -- 查询时避免SELECT *
  SELECT user_id, status FROM orders WHERE user_id = 100;  -- 直接走索引
  

2. 索引下推（ICP）

• 现象：EXPLAIN 的 Extra 列出现 Using index condition
• 原理：在存储引擎层提前过滤数据，减少回表次数
• 生效条件：MySQL 5.6+ 且联合索引的部分字段作为 WHERE 条件

---

五、监控与预防体系

1. 实时告警配置

• 工具：Prometheus + Grafana + Alertmanager
• 监控指标：
  • mysql_global_status_slow_queries（慢查询计数）
  • mysql_info_schema_processlist_time（长事务阈值）

2. 自动化优化建议

• 使用工具：
  • pt-index-usage：分析未使用的索引
  • pt-online-schema-change：在线修改大表结构

3. 压测与预案

• 用 sysbench 模拟高峰流量
• 准备降级策略（如限流、关闭非核心查询）

---

六、经典案例：电商订单查询优化

问题 SQL

SELECT * FROM orders 
WHERE user_id = 100 
AND status = 'paid' 
ORDER BY create_time DESC 
LIMIT 10;

优化步骤：

1. 分析执行计划：发现 type=ALL（全表扫描）
2. 创建联合索引：

   ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);
   

3. 验证效果：
   • type=ref，key=idx_user_status_time
   • 执行时间从 1.2s → 15ms

---

总结：慢 SQL 优化闭环

graph LR
A[发现慢日志] --> B(EXPLAIN分析)
B --> C{索引问题？}
C -- YES --> D[优化索引/重写SQL]
C -- NO --> E{资源瓶颈？}
E -- YES --> F[扩容/分库分表]
E -- NO --> G[检查锁竞争]
D --> H[验证效果]
F --> H
G --> H
H --> I[监控迭代]

掌握慢 SQL 分析能力是高性能 MySQL 的基石。核心要点：

1. 80%的性能问题源于索引 → 精通 EXPLAIN
2. 避免在 SQL 中做计算 → 将逻辑移至应用层
3. 分治思想 → 大事务拆小，大表拆小
4. 监控驱动优化 → 建立持续跟踪机制