MySQL 实战宝典（六）：MySQL慢查询排查

一、基础排查：SQL 执行报错

当 SQL 语句无法执行时，首先要解决的是语法或逻辑错误。

常用工具： Navicat, DBeaver, MySQL Workbench, CLI

1. 错误码分析

当执行出错时，MySQL 会返回 Error Code 和 Message。

示例错误：
1064 - You have an error in your SQL syntax; ... near '* from admin' at line 1

• 定位方法：重点关注 near 关键字后面的内容，通常错误就发生在它的前一个单词或当前单词。
• 常见原因：
  • 关键字拼写错误（如 form 写成 from）。
  • 标点符号错误（中文逗号、缺失分号）。
  • 使用了 MySQL 保留字作为字段名且未加反引号（`）。

2. 善用搜索引擎

遇到生僻的错误码（如 ERROR 1205: Lock wait timeout exceeded），直接将 “MySQL + 错误码 + 报错信息” 放入搜索引擎。

---

二、进阶排查：慢查询日志 (Slow Query Log)

当系统变慢，但没有报错时，慢查询日志是捕获问题的“黑匣子”。

1. 核心参数解析

参数名	说明	推荐配置
slow_query_log	开关，ON 开启，OFF 关闭。	ON
slow_query_log_file	日志文件的存储路径。	/var/log/mysql/slow.log
long_query_time	阈值。超过该时间的 SQL 会被记录（单位：秒）。	1 或 0.5
log_queries_not_using_indexes	是否记录未使用索引的 SQL。	ON (前期排查时建议开启)

2. 配置实战

方式 A：命令行配置（临时生效，重启失效）

适合临时排查问题，无需重启数据库。

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

验证配置：

SHOW VARIABLES LIKE '%slow_query%';

方式 B：配置文件配置（永久生效）

修改 my.cnf (Linux) 或 my.ini (Windows)，添加以下内容并重启服务：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

3. 日志分析技巧

直接查看日志文件

# Time: 2023-03-12T09:32:24.818300Z
# User@Host: root[root] @ localhost [::1]  Id: 11
# Query_time: 2.003975  Lock_time: 0.000116 Rows_sent: 393  Rows_examined: 100000
SET timestamp=1678613544;
SELECT * FROM orders WHERE amount < 100;

• Query_time: 实际执行耗时（重点）。
• Lock_time: 等待锁的时间。如果该值很高，说明存在锁竞争或死锁。
• Rows_examined: 扫描行数。如果 Rows_sent 很小但 Rows_examined 很大（例如送回 10 行却扫描了 10 万行），说明索引效率极低。

使用 mysqldumpslow 工具

直接看日志眼花缭乱，MySQL 自带了分析工具。

# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log

# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

---

三、深度分析：EXPLAIN 执行计划

找到慢 SQL 后，使用 EXPLAIN 像做“CT扫描”一样分析它的执行逻辑。

1. 性能分析概览图 (Mermaid)

在分析 type 字段时，性能从最优到最差的排序如下：

优化目标：至少达到 range 级别，最好是 ref 或 const。

2. 核心字段详解

(1) type - 访问类型 (最重要)

类型	含义	场景示例
system/const	只有一行匹配，极快	主键或唯一索引查找 WHERE id = 1
eq_ref	唯一索引扫描	多表连接时，使用主键或唯一索引关联
ref	非唯一索引扫描	WHERE user_name = 'Tom' (普通索引)
range	索引范围扫描	WHERE id > 10 或 BETWEEN
index	全索引扫描	SELECT id FROM table (扫描整个索引树，不回表)
ALL	全表扫描	SELECT * FROM table (无索引，数据量大时必死)

(2) key_len - 索引使用的长度

• 作用：判断联合索引是否被完全使用。
• 示例：联合索引 (a, b, c)，如果 key_len 只显示了列 a 的长度，说明 b 和 c 没用到，索引失效中断了。

(3) Extra - 额外信息 (调优金钥匙)

值	评价	含义与建议
Using index	🟢 完美	覆盖索引。查询列全在索引中，无需回表，性能最高。
Using where	🟡 一般	存储引擎返回数据后，Server 层还在过滤。
Using index condition	🟡 不错	索引下推。MySQL 5.6+ 优化，减少回表次数。
Using temporary	🔴 警告	产生了临时表（常见于 GROUP BY 或 DISTINCT）。需优化索引或 SQL 逻辑。
Using filesort	🔴 严重	文件排序。无法利用索引顺序，需在内存/磁盘排序。需优化 ORDER BY 字段索引。

---

四、实战案例分析

假设表结构：

• users: id (PK), name (index), age, city
• orders: id (PK), user_id (index), create_time

案例 1：高效的关联查询

EXPLAIN SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.create_time > '2023-01-01';

分析结果：

• orders 表 type 为 range (利用 create_time 索引)。
• users 表 type 为 eq_ref (利用主键 id 关联)。
• 结论：性能良好。

案例 2：全表扫描灾难

EXPLAIN SELECT * FROM users WHERE age > 30;

分析结果：

• type: ALL (全表扫描)。
• key: NULL (无索引可用)。
• 优化：给 age 字段添加索引。

案例 3：文件排序 (Using filesort)

EXPLAIN SELECT * FROM users ORDER BY city;

分析结果：

• Extra: Using filesort。因为 city 字段没有索引，MySQL 必须将所有数据加载排序。
• 优化：给 city 建立索引，或者避免对无索引字段进行全表排序。

---

五、总结：MySQL 问题排查全流程

---

参考资料

• MySQL 8.0 Reference Manual - EXPLAIN
• MySQL Slow Query Log Configuration