MySQL慢查询分析与优化

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、慢查询日志配置

#

1.1 开启慢查询日志

[mysqld]
# 开启慢查询日志
slow_query_log = ON

# 日志文件路径
slow_query_log_file = /var/lib/mysql/slow.log

# 慢查询时间阈值（秒），默认10秒
long_query_time = 1

# 记录未使用索引的查询（可选）
log_queries_not_using_indexes = ON

# 控制每分钟记录多少条未使用索引的查询（防止日志过大）
log_throttle_queries_not_using_indexes = 10

动态开启（无需重启）：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

#

1.2 慢查询日志格式

# Time: 2024-07-18T09:00:01.234567Z
# User@Host: root[root] @ localhost []
# Query_time: 2.345678  Lock_time: 0.000123 Rows_sent: 10  Rows_examined: 1000000
SET timestamp=1721286001;
SELECT * FROM orders WHERE status = 0 ORDER BY create_time LIMIT 10;

关键字段说明：
| 字段 | 含义 |
|——|——|
| Query_time | SQL执行总时间 |
| Lock_time | 获取锁的时间 |
| Rows_sent | 返回给客户端的行数 |
| Rows_examined | 扫描的行数 |

Rows_examined远大于Rows_sent，说明索引可能有问题。

二、慢查询分析工具

#

2.1 mysqldumpslow（MySQL自带）

# 查看最慢的10条SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 参数说明
# -s: 排序方式（t:时间, l:锁时间, r:行数, c:次数）
# -t: 显示条数
# -g: 过滤SQL

# 查看出现次数最多的10条
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# 过滤包含SELECT的SQL
mysqldumpslow -s t -t 10 -g "SELECT" /var/lib/mysql/slow.log

#

2.2 pt-query-digest（Percona Toolkit）

功能最强大的慢查询分析工具：

# 安装
yum install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt

# 分析最近4小时的日志
pt-query-digest --since='4h' /var/lib/mysql/slow.log

# 只分析特定数据库
pt-query-digest --filter '$event->{db} eq "mydb"' /var/lib/mysql/slow.log

# 分析processlist（实时分析）
pt-query-digest --processlist h=localhost,u=root,p=password

报告核心内容：

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== =============
#    1 0xABC123...        1200.0000 60%  1000  1.2000 0.01 SELECT orders
#    2 0xDEF456...         400.0000 20%   500  0.8000 0.02 SELECT user

字段	含义
Rank	排名
Response time	总响应时间及占比
Calls	执行次数
R/Call	平均每次响应时间
V/M	响应时间方差均值比（越大越不稳定）

#

2.3 慢查询表（MySQL 5.6+）

将慢查询日志存入表中，便于SQL分析：

-- 设置输出到表
SET GLOBAL log_output = 'TABLE';

-- 查询慢查询表
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 清空慢查询表
TRUNCATE TABLE mysql.slow_log;

#

2.4 性能模式（Performance Schema）

MySQL 5.6+ 推荐的分析方式：

-- 开启语句事件采集
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%statements%';

-- 查看执行时间最长的SQL
SELECT 
    SQL_TEXT,
    COUNT_STAR AS exec_count,
    AVG_TIMER_WAIT / 1000000000000 AS avg_time_sec,
    MAX_TIMER_WAIT / 1000000000000 AS max_time_sec,
    SUM_ROWS_EXAMINED / COUNT_STAR AS avg_rows_examined,
    SUM_CREATED_TEMP_TABLES AS temp_tables
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

三、慢查询优化流程

#

3.1 分析步骤

发现慢查询
    │
    ├── 查看执行计划（EXPLAIN）
    │       ├── 全表扫描？→ 添加索引
    │       ├── 索引未使用？→ 分析原因
    │       ├── 文件排序？→ 优化ORDER BY
    │       └── 临时表？→ 优化GROUP BY
    │
    ├── 分析SQL写法
    │       ├── 避免SELECT *
    │       ├── 避免大OFFSET分页
    │       ├── 避免隐式转换
    │       └── 优化子查询
    │
    ├── 检查表结构
    │       ├── 字段类型是否合理
    │       ├── 是否有过大字段
    │       └── 分区是否合适
    │
    └── 检查服务器状态
            ├── CPU/IO是否瓶颈
            ├── 连接数是否过多
            └── 缓存命中率

#

3.2 常见慢查询场景

场景一：分页深翻页

-- 深翻页，越来越慢
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 100000, 10;
-- 扫描100010行，返回10行

优化方案：

-- 方案一：延迟关联（推荐）
SELECT o.* 
FROM orders o
JOIN (
    SELECT id 
    FROM orders 
    ORDER BY create_time DESC 
    LIMIT 100000, 10
) tmp ON o.id = tmp.id;

-- 方案二：记录上一页最大值
SELECT * FROM orders 
WHERE create_time < '2024-06-01 00:00:00'
ORDER BY create_time DESC 
LIMIT 10;

-- 方案三：限制翻页深度
-- 业务上限制只能翻到100页

场景二：大表JOIN

-- 未优化的大表JOIN
SELECT u.*, o.* 
FROM user u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

优化方案：

-- 确保关联列有索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- 只查需要的列
SELECT u.id, u.name, o.order_no, o.amount
FROM user u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

场景三：IN大列表

-- IN列表过大
SELECT * FROM user WHERE id IN (1, 2, 3, ..., 10000);

优化方案：

-- 方案一：改为JOIN临时表
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1), (2), (3), ...;

SELECT u.* FROM user u
JOIN tmp_ids t ON u.id = t.id;

-- 方案二：分批查询（程序中循环）
-- 每次IN 500-1000个

场景四：统计查询

-- 全表统计
SELECT COUNT(*) FROM orders WHERE status = 0;

优化方案：

-- 方案一：汇总表（实时性要求不高）
CREATE TABLE order_stats (
    status TINYINT PRIMARY KEY,
    total_count INT,
    last_updated DATETIME
);

-- 方案二：缓存（Redis）
-- 方案三：近似值（SHOW TABLE STATUS）
SHOW TABLE STATUS LIKE 'orders';
-- Rows列是近似值

四、索引优化策略

#

4.1 索引优化案例

案例：订单查询优化

原始SQL：

SELECT * FROM orders 
WHERE user_id = 100 
  AND status IN (1, 2)
  AND create_time > '2024-01-01'
ORDER BY create_time DESC 
LIMIT 10;

EXPLAIN分析：

type: ALL（全表扫描）
Extra: Using where; Using filesort

优化过程：

-- 步骤一：添加联合索引（错误示范）
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- 问题：IN条件导致create_time无法用于排序

-- 步骤二：调整索引顺序
CREATE INDEX idx_user_time_status ON orders(user_id, create_time, status);
-- 分析：user_id等值 → create_time范围+排序 → status过滤

-- 步骤三：覆盖索引（可选）
CREATE INDEX idx_user_time_status_cover ON orders(
    user_id, create_time, status, 
    order_no, amount  -- 包含查询列
);

#

4.2 索引优化检查清单

• WHERE条件列是否有索引？
• 联合索引顺序是否符合最左前缀？
• 排序列是否在索引中？
• 是否可以设计覆盖索引？
• 索引选择性是否足够高？
• 是否存在冗余索引？

五、SQL改写优化

#

5.1 UNION替代OR

-- 优化前（索引可能失效）
SELECT * FROM user WHERE name = 'Alice' OR phone = '13800138000';

-- 优化后
SELECT * FROM user WHERE name = 'Alice'
UNION ALL
SELECT * FROM user WHERE phone = '13800138000';

#

5.2 EXISTS替代IN

-- 优化前（子查询可能全表扫描）
SELECT * FROM user WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 100
);

-- 优化后
SELECT * FROM user u WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 100
);

#

5.3 批量操作替代循环

-- 优化前（程序循环执行）
INSERT INTO logs (msg) VALUES ('msg1');
INSERT INTO logs (msg) VALUES ('msg2');
...

-- 优化后
INSERT INTO logs (msg) VALUES ('msg1'), ('msg2'), ...;

六、服务器参数优化

#

6.1 内存相关

# InnoDB缓冲池（通常设为物理内存的50%-75%）
innodb_buffer_pool_size = 4G

# 缓冲池实例数（高并发时提高）
innodb_buffer_pool_instances = 4

# 日志缓冲区
innodb_log_buffer_size = 64M

# 连接缓冲区
join_buffer_size = 256K
sort_buffer_size = 256K
read_buffer_size = 128K

#

6.2 连接相关

# 最大连接数
max_connections = 500

# 连接缓存
thread_cache_size = 100

# 等待超时
wait_timeout = 600
interactive_timeout = 600

#

6.3 InnoDB相关

# 事务日志大小（大事务优化）
innodb_log_file_size = 512M
innodb_log_files_in_group = 3

# 刷新策略（性能vs持久性）
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

七、监控与预警

#

7.1 慢查询监控脚本

#!/bin/bash
# slow_query_monitor.sh

SLOW_LOG="/var/lib/mysql/slow.log"
THRESHOLD=100  # 慢查询条数阈值

COUNT=$(wc -l < $SLOW_LOG)

if [ $COUNT -gt $THRESHOLD ]; then
    echo "警告：过去1小时慢查询超过${THRESHOLD}条，当前${COUNT}条" | \
    mail -s "MySQL慢查询告警" dba@company.com
fi

#

7.2 使用Prometheus + Grafana监控

-- 创建监控视图
CREATE VIEW slow_query_stats AS
SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    AVG_TIMER_WAIT / 1000000000000 AS avg_time,
    MAX_TIMER_WAIT / 1000000000000 AS max_time,
    SUM_ROWS_EXAMINED / COUNT_STAR AS avg_rows
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 1000000000000  -- 超过1秒
ORDER BY AVG_TIMER_WAIT DESC;

八、总结

阶段	工具/方法	目标
发现	slow_query_log, Performance Schema	找到慢查询
分析	EXPLAIN, pt-query-digest	定位原因
优化	索引优化, SQL改写	提升性能
验证	EXPLAIN ANALYZE	确认效果
监控	告警脚本, Grafana	持续跟踪

慢查询优化是持续的过程，需要：

1. 建立慢查询发现和监控机制
2. 掌握EXPLAIN分析技能
3. 积累常见场景的优化方案
4. 定期进行全量SQL审核

核心要点

1. 索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型

2. SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN

3. 事务隔离级别的选择：根据业务需求选择合适的级别

4. 常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。