MySQL 慢查询配置与线程排查指南

MySQL 慢查询配置与线程排查指南

本文档旨在帮助开发与运维人员快速配置 MySQL 慢查询日志（Slow Query Log），分析慢SQL，并排查数据库运行时的线程阻塞或负载问题。

---

1. 开启慢查询日志

开启慢查询主要有两种方式：临时生效（无需重启，重启后失效）和 永久生效（修改配置文件，需重启）。

1.1 临时开启 (Runtime)

适用于生产环境临时排查问题，不希望重启数据库的场景。

SQL

-- 1. 开启慢查询日志开关
SET GLOBAL slow_query_log = 'ON';

-- 2. 设置慢查询阈值（单位：秒）
-- 例如：超过 1 秒的查询记录下来
SET GLOBAL long_query_time = 1;

-- 3. (可选) 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

注意： 修改 long_query_time 后，仅对新建立的连接生效。当前会话若想生效，需执行 SET long_query_time = 1;。

1.2 永久开启 (Configuration File)

适用于长期监控。修改 my.cnf (Linux) 或 my.ini (Windows) 文件。

在 [mysqld] 模块下添加或修改：

Ini, TOML

[mysqld]
# 开启慢查询
slow_query_log = 1

# 慢查询日志文件路径 (建议指定绝对路径)
slow_query_log_file = /var/lib/mysql/mysql-slow.log

# 慢查询阈值 (秒)
long_query_time = 1

# (可选) 记录没有使用索引的查询
log_queries_not_using_indexes = 1

修改后需要重启 MySQL 服务： systemctl restart mysqld

1.3 验证配置

执行以下 SQL 确认配置是否生效：

SQL

SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

---

2. 分析与展示慢查询

慢查询日志不仅可以直接查看文本文件，官方还提供了 mysqldumpslow 工具进行聚合分析，这是最常用的方法。

2.1 常用参数说明 (mysqldumpslow)

• -s: 排序方式 (Sort)
  • c: 访问次数 (Count)
  • t: 查询时间 (Time)
  • l: 锁定时间 (Lock time)
  • r: 返回记录 (Rows)
  • at: 平均查询时间
• -t: 返回前面多少条数据 (Top)
• -g: 正则匹配 (Grep)

2.2 常用分析命令示例

在终端（Shell）中执行:

mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log	

2. 按照“访问次数”排序，找出执行最频繁的慢 SQL

Bash

mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log

3. 按照“平均时间”排序，并包含特定表名（如 user）的 SQL

Bash

mysqldumpslow -s at -t 10 -g "user" /var/lib/mysql/mysql-slow.log

---

3. 排查线程与负载问题

当数据库 CPU 飙升或响应卡顿，但慢查询日志还未生成（因为查询还未结束）时，需要实时查看线程状态。

3.1 查看当前运行的线程

使用 SHOW PROCESSLIST 命令。

SQL

-- 查看当前连接的前 100 条线程
SHOW PROCESSLIST;

-- 查看所有线程 (完整 SQL)
SHOW FULL PROCESSLIST;

3.2 重点关注的字段

字段	说明	异常状态 (State)
Id	线程 ID	用于 Kill 操作
User	执行用户	-
Time	耗时 (秒)	若数值很大，需警惕
Command	命令类型	Query (正在查询), Sleep (空闲)
State	当前状态 (核心)	Locked (被锁), Sending data (读取大量数据), Copying to tmp table (临时表太大)
Info	SQL 语句	具体的执行语句

3.3 使用 Information Schema 进行高级筛选

如果线程非常多，SHOW PROCESSLIST 不好观察，可以直接查询系统表：

SQL

-- 查询运行时间超过 30 秒的非 Sleep 线程
SELECT * FROM information_schema.processlist 
WHERE command != 'Sleep' 
AND time > 30 
ORDER BY time DESC;

3.4 杀掉卡死的线程

如果发现某个 SQL (例如 ID 为 1234) 导致了死锁或耗尽了资源，可以强制终止：

SQL

KILL 1234;

---

4. 常见问题排查速查表

现象	可能原因	排查手段
CPU 100%	某个复杂 SQL 正在计算或死循环	top -c 确认 mysql 进程 -> SHOW FULL PROCESSLIST 找 State 为 Sending data 或 Statistics 的高耗时 SQL。
IO wait 高	全表扫描、临时表写入磁盘	查看慢查询日志，关注 Rows_examined 远大于 Rows_sent 的语句；检查 State 为 Copying to tmp table on disk。
连接数满	慢 SQL 堆积导致连接不释放	SHOW VARIABLES LIKE 'max_connections'; 对比当前连接数。