数据库慢查询如何定位？全套排查步骤手把手教

二月龙
3 阅读8分钟

数据库慢查询如何定位？全套排查步骤手把手教

生产环境突然变卡，CPU 飙到 100%，用户投诉页面打不开——你的第一反应是什么？别慌，跟着这篇文章，5 分钟找到那条拖垮整个系统的慢 SQL。

第一步：确认"慢查询日志"是否开启

慢查询日志是定位慢 SQL 的最核心依据，它会自动记录执行时间超过阈值的所有 SQL 语句。但问题是——MySQL 默认是关闭的。

🔍 检查当前状态

sql
-- 查看慢查询日志是否开启（ON=开启，OFF=关闭）
SHOW VARIABLES LIKE '%slow_query_log%';

-- 查看慢查询阈值（默认10秒，这太大了！）
SHOW VARIABLES LIKE 'long_query_time';

-- 查看日志存放路径
SHOW VARIABLES LIKE 'slow_query_log_file';

典型结果：

参数默认值建议值
slow_query_logOFFON
long_query_time10秒0.5~1秒
log_queries_not_using_indexesOFFON

⚠️ 默认 10 秒才记录，意味着一条执行了 8 秒的 SQL 不会被记录！生产环境建议设为 0.5~1 秒，才能尽早发现隐患。

⚡ 临时开启（应急排查用）

sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;  -- 没走索引的SQL也记录
SET GLOBAL log_output = 'FILE';
FLUSH LOGS;

临时方式重启 MySQL 后失效，适合线上应急。

💾 永久开启（生产环境必须做）

修改配置文件 my.cnf（Linux）或 my.ini（Windows）：

ini
[mysqld]
# 开启慢查询日志
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log

# 超过1秒就算慢查询
long_query_time = 1

# 记录未走索引的SQL（提前发现无索引查询）
log_queries_not_using_indexes = 1

# 扫描行数超过1000才记录（减轻日志压力）
min_examined_row_limit = 1000

# 日志输出方式
log_output = FILE

然后重启 MySQL：

bash
# Linux
systemctl restart mysqld

# Windows
# 在服务管理器中找到 MySQL，右键重启

第二步：三种方法找到慢 SQL

🎯 方法一：直接看慢查询日志（最准）

日志文件位置通过上面的命令查到后，直接读取：

bash
# 查看最新的100条慢SQL
tail -n 100 /var/lib/mysql/slow.log

日志格式长这样：

# Time: 2026-05-10T14:30:45.123456Z
# User@Host: app_user[app_user] @ 192.168.1.100
# Query_time: 3.800000  Lock_time: 0.000200  Rows_sent: 150  Rows_examined: 120000
SET timestamp=1684606245;
SELECT * FROM order_info WHERE user_id = 567 AND create_time BETWEEN '2024-01-01' AND '2024-06-01';

解读关键字段：

字段含义告警信号
Query_timeSQL 执行时间> 1秒就是慢查询
Lock_time等待锁的时间> 0 说明有锁竞争
Rows_sent返回给客户端的行数150 行
Rows_examinedMySQL 扫描的行数12万行！ 扫描12万行只返回150行，100%没走索引

核心判断：Rows_examined 远大于 Rows_sent → 索引大概率失效或缺失。

🎯 方法二：用 mysqldumpslow 工具（MySQL 自带）

直接读日志太累，用内置工具一键分析：

bash
# 按总耗时排序，取前10条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 按执行次数排序（高频慢SQL最该先优化）
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# 按平均耗时排序
mysqldumpslow -s a -t 10 /var/lib/mysql/slow.log

输出示例：

Count: 200  Time=5.12s (1024s)  Lock=0.00s (0s)  Rows=100.0 (20000)
  SELECT * FROM orders WHERE status='pending' AND create_time > '2023-01-01';

翻译：这条 SQL 执行了 200 次，总耗时 1024 秒，平均每次 5 秒，扫描了 2 万行。

🎯 方法三：实时抓正在卡住的 SQL（最快）

慢查询日志只记录"已经跑完的慢 SQL"，如果有一条 SQL 正在执行中卡住了，日志里还没有，怎么办？

sql
-- 查看所有正在运行的线程
SHOW FULL PROCESSLIST;

-- 或者用这个更精准的查询（MySQL 5.7+/8.0）
SELECT * FROM information_schema.PROCESSLIST 
WHERE TIME > 1 AND INFO IS NOT NULL
ORDER BY TIME DESC;

重点看这两列：

  • Time：执行了多少秒，越大越危险
  • State：如果显示 Sending data，说明在疯狂扫描表，99% 是慢查询

🚨 如果看到 Time > 60 秒的线程，立刻用 pt-kill 终止：

bash
pt-kill --busy-time 10 --kill --victims all --print h=127.0.0.1

🎯 彩蛋：MySQL 8.0+ 性能视图（不用开日志也能看）

sql
-- 查看耗时最高的 SQL
SELECT * FROM sys.schema_table_statistics ORDER BY total_latency DESC LIMIT 10;

-- 查看全表扫描最多的 SQL（没走索引的）
SELECT * FROM sys.statement_analysis WHERE full_scan = 'YES' LIMIT 10;

第三步：找到慢 SQL 后，用 EXPLAIN 看透本质

找到慢 SQL 只是第一步，关键是搞清楚它为什么慢。一条 EXPLAIN 命令，5 秒出结果：

sql
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 'pending';

📊 重点看这 4 列

列名理想值最差值含义
typeref / range / constALL访问类型，ALL = 全表扫描
key有索引名NULL实际用了哪个索引，NULL = 没走索引
rows< 100> 100000预估扫描行数
ExtraUsing indexUsing filesort / Using temporary额外信息

🔥 常见 EXTRA 危险信号

Extra 值含义严重程度
Using filesortORDER BY 没走索引，需要额外排序⭐⭐⭐
Using temporaryGROUP BY / DISTINCT 用了临时表⭐⭐⭐
Using index condition索引下推，还算正常⭐
Using index覆盖索引，不用回表，最优✅

一句话总结：type = ALL 或 key = NULL 或 Extra 出现 filesort/temporary → 这条 SQL 90% 有问题。

第四步：90% 的慢查询，就这 4 个原因

根据实战经验，慢查询的根因高度集中：

❌ 原因一：没加索引 / 索引失效（占比最高）

典型场景：

sql
-- 场景1：WHERE 里对索引列做运算
SELECT * FROM article WHERE YEAR(publish_time) < 2019;
-- 改为：
SELECT * FROM article WHERE publish_time < '2019-01-01';

-- 场景2：隐式类型转换
SELECT * FROM user WHERE phone = 13800138000;  -- phone是varchar，传了int
-- 改为：
SELECT * FROM user WHERE phone = '13800138000';

-- 场景3：LIKE 左模糊
SELECT * FROM user WHERE name LIKE '%三';  -- 全表扫描
-- 改为：
SELECT * FROM user WHERE name LIKE '张%';  -- 走索引

-- 场景4：联合索引不遵循最左前缀
-- 索引是 (a, b, c)，但查询 WHERE b = 1 → 索引失效

解决方案：

sql
-- 添加复合索引（覆盖 WHERE + ORDER BY 字段）
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);

-- 用前缀索引节省空间（长文本字段）
ALTER TABLE article ADD INDEX idx_title (title(20));

❌ 原因二：SELECT * 查太多字段

sql
-- 烂SQL：查了20个字段，实际只用3个
SELECT * FROM orders WHERE user_id = 1001;

-- 优化：只查需要的
SELECT id, status, created_at FROM orders WHERE user_id = 1001;

少查一个字段，IO 少一分，网络少传一分，内存少占一分。

❌ 原因三：LIMIT 偏移量太大

sql
-- 烂SQL：跳过10万行取20行
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20;

-- 优化：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20
) tmp ON o.id = tmp.id;

或者记住上一页的最大 ID：WHERE id < 上次最大ID LIMIT 20

❌ 原因四：JOIN 太多表 / 子查询太深

sql
-- 烂SQL：3层嵌套子查询
SELECT * FROM orders WHERE status IN (
    SELECT status FROM config WHERE type = 'order'
);

-- 优化：改成 JOIN
SELECT o.* FROM orders o
JOIN config c ON o.status = c.status
WHERE c.type = 'order';

铁律：JOIN 表不超过 3 个，能用 EXISTS 替代 IN，能用 JOIN 替代子查询。

第五步：实战排查流程（背下来）

┌─────────────────────────────────────────────────┐
│           慢查询排查 5 步速查法                    │
├─────────────────────────────────────────────────┤
│                                                 │
│  ① 开启慢查询日志                                │
│     set global slow_query_log=1;                 │
│     set global long_query_time=1;                 │
│                                                 │
│  ② 找慢 SQL                                      │
│     mysqldumpslow -s t -t 10 slow.log           │
│     或 show full processlist 看正在卡的           │
│                                                 │
│  ③ EXPLAIN 看执行计划                            │
│     EXPLAIN 你的慢SQL;                           │
│     看 type / key / rows / Extra                 │
│                                                 │
│  ④ 定位根因                                      │
│     type=ALL? → 没索引，加！                     │
│     key=NULL? → 索引失效，修SQL！                │
│     Using filesort? → ORDER BY优化！             │
│                                                 │
│  ⑤ 优化验证                                      │
│     再 EXPLAIN 一次，对比前后差异                 │
│     rows 从 100000 降到 100 → 成功！            │
│                                                 │
└─────────────────────────────────────────────────┘

第六步：架构级优化（当索引优化到极限时）

如果单条 SQL 已经优化到头了，但系统还是慢，就要考虑架构层面：

手段适用场景效果
读写分离读多写少查询压力分摊到从库
分库分表单表超 2000 万行扫描行数从百万降到万级
Redis 缓存热点数据反复查QPS 提升 10 倍+
Elasticsearch复杂搜索/多条件筛选替代 MySQL 模糊查询
物化视图大量只读聚合查询预计算结果，查表即得

附：一键排查脚本

把这段保存为 check_slow.sh，排查时直接跑：

bash
#!/bin/bash
echo "=== 1. 慢查询日志状态 ==="
mysql -e "SHOW VARIABLES LIKE '%slow_query_log%';"

echo -e "\n=== 2. 当前正在跑的慢SQL ==="
mysql -e "SELECT ID, USER, TIME, STATE, INFO 
          FROM information_schema.PROCESSLIST 
          WHERE TIME > 1 AND INFO IS NOT NULL 
          ORDER BY TIME DESC LIMIT 10;"

echo -e "\n=== 3. 历史慢SQL Top 10 ==="
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log 2>/dev/null || echo "暂无慢日志"

echo -e "\n=== 4. 全表扫描最多的SQL ==="
mysql -e "SELECT * FROM sys.statement_analysis 
          WHERE full_scan = 'YES' LIMIT 10;" 2>/dev/null

最后一句话

慢查询排查的本质就三个字：看日志、跑 EXPLAIN、修索引。 90% 的性能问题，一条 ALTER TABLE ADD INDEX 就能解决。剩下 10%，才需要分库分表、读写分离这些大动作。

别一上来就想着架构升级，先把那条 SELECT * 的慢 SQL 找到——系统飞起来，往往就差这一步。 🚀

avatar
文章
阅读
粉丝