如何排查mysql慢查询？

lanbing

于 2025-07-01 00:18:34 发布

阅读量1.2k

点赞数 32

CC 4.0 BY-SA版权

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/wamp0001/article/details/149034328

Mysql 专栏收录该内容

23 篇文章

订阅专栏

排查线上慢查询是数据库性能优化的关键步骤。以下是系统的排查方法和优化策略：

一、通用排查思路

确定慢查询范围
- 定义“慢”：根据业务需求设定阈值（如执行时间 > 1s、CPU 时间 > 500ms）。
- 分类问题：
  - 运行时间长（CPU 绑定）：高 CPU 使用率。
  - 等待时间长（资源瓶颈）：锁竞争、I/O 延迟、网络延迟等。
监控与日志收集
- 开启慢查询日志：记录所有超阈值的查询。
- 实时监控工具：使用数据库自带或第三方工具（如 Prometheus + Grafana）监控资源使用率（CPU、内存、I/O）。

二、具体操作步骤

1. 开启慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询时间阈值（单位：秒）
SET GLOBAL long_query_time = 2;
-- 设置日志文件路径（可选）
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 记录未使用索引的查询（可选）
SET GLOBAL log_queries_not_using_indexes = 1;

验证配置：

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

2. 分析慢查询日志

工具：
- mysqldumpslow（MySQL 自带）：
```
mysqldumpslow /var/log/mysql/slow.log
```
- 第三方工具：pt-query-digest（Percona Toolkit）。
关注点：
- 频繁出现的 SQL。
- 高耗时的查询（如 Query_time > 阈值）。

3. 使用 `EXPLAIN` 分析执行计划

对可疑 SQL 执行 EXPLAIN：

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

关键字段：

type：连接类型（ALL 表示全表扫描，需优化）。
key：使用的索引（NULL 表示未使用索引）。
rows：预估扫描行数（越小越好）。
Extra：额外信息（如 Using filesort、Using temporary 表示排序/临时表）。

4. 监控实时查询状态

-- 查看当前运行中的查询
SELECT * FROM information_schema.processlist WHERE Command != 'Sleep';
-- 查看慢查询统计
SHOW STATUS LIKE 'Slow_queries';

5. 检查等待事件（Wait Stats）

(1)使用 SHOW ENGINE INNODB STATUS 查看等待事件。

关键信息：

LATEST DETECTED DEADLOCK：显示最近检测到的死锁信息，包括涉及的事务、SQL 语句和锁类型。
TRANSACTIONS：列出当前活跃的事务，包含事务 ID、状态、持有的锁和等待的锁。
SEMAPHORES：查看资源竞争（如锁争用）的情况。

示例输出：

---TRANSACTION 123456, ACTIVE 10 sec
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 12, OS thread handle 0x7f8c3c0a0700, query id 9876543 localhost user updating
UPDATE table1 SET column1 = 'new value' WHERE id = 1;

(2)使用 performance_schema 分析等待事件
performance_schema 提供了更细粒度的等待事件监控，适合分析实时和历史等待事件。

实时等待事件：

SELECT * FROM performance_schema.events_waits_current;

历史等待事件：

SELECT * FROM performance_schema.events_waits_history;
SELECT * FROM performance_schema.events_waits_history_long;

按等待事件类型统计：

SELECT event_name, COUNT(*) AS count, SUM(timer_wait) AS total_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/io/%' OR event_name LIKE 'wait/lock/%'
GROUP BY event_name
ORDER BY total_time DESC;

(3)使用 information_schema 查询锁等待
通过 information_schema.INNODB_TRX 和 sys.innodb_lock_waits 视图查看锁等待详情。

查看当前事务的等待时间：

SELECT 
    trx_id,
    TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.INNODB_TRX
WHERE trx_wait_started IS NOT NULL;

查看锁等待链：

SELECT 
    waiting_trx_id, 
    time_to_sec(timediff(NOW(), r.trx_wait_started)) AS seconds_in_queue,
    waiting_pid, 
    waiting_query, 
    blocking_trx_id, 
    blocking_pid, 
    blocking_query
FROM sys.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

(4) 使用 SHOW PROCESSLIST 查看进程状态
SHOW PROCESSLIST 可以查看当前所有连接的状态，包括等待中的查询。

操作步骤：

SHOW PROCESSLIST;

关键列：

Command：连接当前执行的命令（如 Sleep、Query）。
Time：连接等待或执行的时长（秒）。
State：连接的状态（如 Locked、Waiting for table metadata lock）。
Info：当前执行的 SQL 语句（如果有的话）。

6. 分析执行计划中的资源消耗

在 SSMS（SQL Server Management Studio）中查看实际执行计划的 QueryTimeStats：
- Elapsed Time：总耗时。
- CPU Time：CPU 使用时间。
- Logical Reads：逻辑读取次数（高值表示扫描大量数据）。

三、常见问题定位与优化

1. 索引缺失或不合理

问题表现：
- EXPLAIN 显示 type=ALL 或 key=NULL。
- Logical Reads 高。
解决方法：
- 添加索引：
```
CREATE INDEX idx_customer_id ON orders(customer_id);
```
- 复合索引：对多条件查询字段创建复合索引（如 (customer_id, order_date)）。
- 避免冗余索引：删除低效或重复索引。

2. 查询语句设计问题

问题表现：
- SELECT * 查询全部字段（增加网络传输和 CPU 解析）。
- 未使用分页（LIMIT）导致结果集过大。

优化方法：

只查询必要字段：

SELECT id, customer_id FROM orders WHERE customer_id = 1001;

分页处理：

SELECT * FROM orders WHERE customer_id = 1001 LIMIT 1000, 20;

3. 锁竞争或事务过长

问题表现：
- LCK_M_* 等待类型频繁出现。
- 事务中包含大量写操作（如批量更新）。
优化方法：
- 减少事务范围：将长事务拆分为多个短事务。
- 避免热点数据更新：分散更新操作到不同时间段。

4. I/O 或存储瓶颈

问题表现：
- PAGEIOLATCH_* 等待时间高。
- 磁盘读写速度慢（如机械硬盘 vs SSD）。
优化方法：
- 升级存储介质：使用 SSD 替代 HDD。
- 优化查询逻辑：减少全表扫描，增加索引。

5. 客户端或网络问题

问题表现：
- ASYNC_NETWORK_IO 等待时间高。
- 大型结果集传输（如返回百万行数据）。
优化方法：
- 减少结果集大小：
```
-- 在数据库侧聚合数据
SELECT COUNT(*) FROM orders WHERE customer_id = 1001;
```
- 优化客户端代码：
  - 使用流式读取（如 SqlDataReader）替代一次性加载全部数据。
  - 避免在客户端做复杂计算。

四、预防与长期优化

定期检查慢查询日志
- 将慢查询日志接入监控系统（如 ELK、Prometheus），设置告警规则。
SQL 审计与规范
- 制定开发规范（如禁止 SELECT *、强制使用索引）。
- 使用 ORM 工具时配置查询优化策略。
数据库配置调优
- 调整缓存参数：增大 innodb_buffer_pool_size（MySQL）。
架构优化
- 读写分离：将查询压力分散到从库。
- 分库分表：对超大规模表进行水平拆分。

五、示例：优化一个慢查询

原始查询：

SELECT * FROM orders WHERE customer_id = 1001 AND status = 'paid' ORDER BY order_date DESC;

问题：

未使用索引（EXPLAIN 显示 Using filesort）。
返回全部字段（部分字段冗余）。

优化步骤：

添加复合索引：

CREATE INDEX idx_customer_status_date ON orders(customer_id, status, order_date);

精简字段：

SELECT id, order_date, total_amount FROM orders 
WHERE customer_id = 1001 AND status = 'paid' 
ORDER BY order_date DESC;