外观
MySQL 慢查询故障处理
慢查询是 MySQL 数据库性能问题的主要原因之一，会导致数据库响应变慢、资源占用增加，甚至影响整个应用的可用性。本文将详细介绍 MySQL 慢查询的定义、常见原因、排查步骤、优化方法和最佳实践，帮助 DBA 快速定位和解决慢查询问题，兼顾不同 MySQL 版本的特性差异。
慢查询概述
什么是慢查询
慢查询是指执行时间超过预设阈值的 SQL 查询语句。MySQL 通过慢查询日志（slow query log）记录这些语句，便于 DBA 分析和优化。
慢查询的影响
- 数据库性能下降：慢查询会占用大量 CPU、内存和 I/O 资源，导致其他查询响应变慢
- 应用响应延迟：慢查询会导致应用请求超时，影响用户体验
- 资源耗尽风险：大量慢查询可能导致数据库服务器资源耗尽，甚至崩溃
- 主从复制延迟：慢查询会导致主从复制延迟，影响数据一致性
慢查询的常见表现
- 应用响应时间延长
- 数据库 CPU 使用率持续偏高
- 数据库连接数增加
- 主从复制延迟增大
- 慢查询日志中记录了大量查询语句
慢查询的常见原因
| 类别 | 常见原因 |
|---|---|
| 索引问题 | 缺少索引、索引失效、索引设计不合理 |
| SQL 语句问题 | 复杂查询、全表扫描、多表关联效率低、子查询性能差 |
| 表结构问题 | 表数据量过大、表结构设计不合理、字段类型选择不当 |
| 服务器资源问题 | CPU 不足、内存不足、I/O 瓶颈、磁盘性能差 |
| 配置问题 | MySQL 配置不合理、缓存配置不当、连接数设置不合理 |
| 并发问题 | 锁竞争激烈、事务处理不当、连接池配置不合理 |
慢查询的排查步骤
开启和配置慢查询日志
临时开启慢查询日志
sql
-- 查看当前慢查询日志配置
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 1;
-- 记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 1;
-- 设置慢查询日志文件路径
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';永久配置慢查询日志
在 MySQL 配置文件（如 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf）中添加以下配置：
ini
[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 设置慢查询阈值（秒）
long_query_time = 1
# 记录没有使用索引的查询
log_queries_not_using_indexes = 1
# 设置慢查询日志文件路径
slow_query_log_file = /var/lib/mysql/slow.log
# 日志格式，5.6.14+ 支持 JSON 格式
log_output = FILE分析慢查询日志
使用 mysqldumpslow 工具
mysqldumpslow 是 MySQL 自带的慢查询日志分析工具，可以汇总相似的慢查询，便于分析。
bash
# 查看帮助信息
mysqldumpslow --help
# 按查询时间排序，显示前 10 条记录
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# 按锁定时间排序，显示前 10 条记录
mysqldumpslow -s l -t 10 /var/lib/mysql/slow.log
# 按查询次数排序，显示前 10 条记录
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
# 只显示 SELECT 语句
mysqldumpslow -s t -t 10 -g "SELECT" /var/lib/mysql/slow.log使用 pt-query-digest 工具
pt-query-digest 是 Percona Toolkit 中的工具，功能更强大，可以生成更详细的慢查询分析报告。
bash
# 安装 Percona Toolkit（CentOS/RHEL）
yum install percona-toolkit
# 安装 Percona Toolkit（Ubuntu/Debian）
apt-get install percona-toolkit
# 分析慢查询日志并生成报告
pt-query-digest /var/lib/mysql/slow.log > slow_query_report.txt
# 查看报告
cat slow_query_report.txt使用 Performance Schema
MySQL 5.6+ 引入了 Performance Schema，可以实时监控慢查询。
sql
-- 查看慢查询语句
SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;
-- 查看慢查询的详细信息
SELECT * FROM performance_schema.events_statements_current WHERE digest_text LIKE '%SELECT%';使用 EXPLAIN 分析查询计划
EXPLAIN 命令可以查看 MySQL 执行 SQL 查询的计划，帮助分析查询的性能瓶颈。
sql
-- 分析 SELECT 查询
EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2023-01-01';
-- 分析 UPDATE 查询
EXPLAIN UPDATE orders SET status = 'completed' WHERE id = 12345;
-- 分析 DELETE 查询
EXPLAIN DELETE FROM orders WHERE id = 12345;
-- 查看执行计划的扩展信息
EXPLAIN EXTENDED SELECT * FROM orders WHERE customer_id = 100;
SHOW WARNINGS;
-- 查看查询的执行成本
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 100;定位性能瓶颈
根据分析结果，定位慢查询的性能瓶颈：
索引问题：
- 检查是否缺少必要的索引
- 检查索引是否被正确使用
- 检查索引设计是否合理
SQL 语句问题：
- 检查是否存在全表扫描
- 检查是否存在复杂的多表关联
- 检查是否存在低效的子查询
- 检查是否存在不必要的字段查询
表结构问题：
- 检查表数据量是否过大
- 检查表结构设计是否合理
- 检查字段类型选择是否不当
- 检查是否需要分区表
服务器资源问题：
- 检查 CPU 使用率
- 检查内存使用情况
- 检查 I/O 性能
- 检查磁盘空间
验证优化效果
优化后，需要验证优化效果：
- 使用
EXPLAIN检查优化后的查询计划 - 使用
SHOW PROFILE查看查询的执行时间 - 监控慢查询日志，确认优化后的查询不再被记录
- 监控数据库性能指标，确认性能有所提升
慢查询自动分析脚本
bash
#!/bin/bash
# MySQL 慢查询自动分析脚本
MYSQL_HOST="localhost"
MYSQL_USER="root"
MYSQL_PASS="password"
SLOW_LOG_FILE="/var/lib/mysql/slow.log"
REPORT_DIR="/var/reports/mysql/slow_queries"
DATE=$(date +"%Y-%m-%d_%H-%M-%S")
REPORT_FILE="$REPORT_DIR/slow_query_report_$DATE.txt"
# 确保报告目录存在
mkdir -p $REPORT_DIR
# 初始化报告
echo "MySQL 慢查询分析报告" > $REPORT_FILE
echo "生成时间: $DATE" >> $REPORT_FILE
echo "=====================" >> $REPORT_FILE
echo "" >> $REPORT_FILE
# 1. 慢查询日志基本信息
echo "1. 慢查询日志基本信息" >> $REPORT_FILE
echo "-------------------" >> $REPORT_FILE
echo "慢查询日志文件: $SLOW_LOG_FILE" >> $REPORT_FILE
echo "日志大小: $(du -h $SLOW_LOG_FILE | awk '{print $1}')" >> $REPORT_FILE
echo "日志行数: $(wc -l $SLOW_LOG_FILE | awk '{print $1}')" >> $REPORT_FILE
echo "" >> $REPORT_FILE
# 2. 慢查询配置信息
echo "2. 慢查询配置信息" >> $REPORT_FILE
echo "-------------------" >> $REPORT_FILE
mysql -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST -e "SHOW VARIABLES LIKE '%slow_query%'; SHOW VARIABLES LIKE 'long_query_time';" >> $REPORT_FILE
echo "" >> $REPORT_FILE
# 3. 慢查询统计
echo "3. 慢查询统计" >> $REPORT_FILE
echo "-------------------" >> $REPORT_FILE
# 使用 mysqldumpslow 统计
echo "慢查询TOP 10（按执行时间）:" >> $REPORT_FILE
mysqldumpslow -s t -t 10 $SLOW_LOG_FILE >> $REPORT_FILE
echo "" >> $REPORT_FILE
echo "慢查询TOP 10（按查询次数）:" >> $REPORT_FILE
mysqldumpslow -s c -t 10 $SLOW_LOG_FILE >> $REPORT_FILE
echo "" >> $REPORT_FILE
# 4. 慢查询详细分析（使用 pt-query-digest）
echo "4. 慢查询详细分析" >> $REPORT_FILE
echo "-------------------" >> $REPORT_FILE
pt-query-digest --limit 10 $SLOW_LOG_FILE >> $REPORT_FILE
echo "" >> $REPORT_FILE
# 5. 系统性能信息
echo "5. 系统性能信息" >> $REPORT_FILE
echo "-------------------" >> $REPORT_FILE
echo "CPU 使用率:" >> $REPORT_FILE
top -bn1 | grep "Cpu(s)" >> $REPORT_FILE
echo "内存使用:" >> $REPORT_FILE
free -h >> $REPORT_FILE
echo "磁盘 I/O:" >> $REPORT_FILE
iostat -x 1 5 | tail -n 5 >> $REPORT_FILE
echo "" >> $REPORT_FILE
# 6. MySQL 性能信息
echo "6. MySQL 性能信息" >> $REPORT_FILE
echo "-------------------" >> $REPORT_FILE
mysql -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST -e "SHOW GLOBAL STATUS LIKE 'Slow_queries'; SHOW GLOBAL STATUS LIKE 'Queries'; SHOW GLOBAL STATUS LIKE 'Threads_running';" >> $REPORT_FILE
echo "" >> $REPORT_FILE
# 发送报告邮件（可选）
# echo "MySQL 慢查询分析报告已生成，请查收附件" | mail -s "MySQL 慢查询报告 $DATE" -a $REPORT_FILE admin@example.com
echo "慢查询分析报告已生成: $REPORT_FILE"慢查询的优化方法
索引优化
添加必要的索引
根据查询条件添加合适的索引：
sql
-- 为经常用于查询条件的列添加索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- 为多列查询条件添加复合索引
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- 为排序和分组的列添加索引
CREATE INDEX idx_orders_order_date ON orders(order_date);优化索引设计
- 选择合适的索引类型：根据查询场景选择 B-tree、Hash、Full-text 等索引类型
- 控制索引数量：避免为每个列都添加索引，索引过多会影响写入性能
- 选择合适的索引列顺序：将选择性高的列放在复合索引的前面
- 避免冗余索引：删除不必要的重复索引
修复和优化索引
sql
-- 查看表的索引信息
SHOW INDEX FROM orders;
-- 优化表，重建索引
OPTIMIZE TABLE orders;
-- 删除不必要的索引
DROP INDEX idx_orders_old ON orders;SQL 语句优化
优化 SELECT 语句
只查询必要的字段：避免使用
SELECT *sql-- 不推荐 SELECT * FROM orders WHERE customer_id = 100; -- 推荐 SELECT id, order_date, total_amount FROM orders WHERE customer_id = 100;避免使用子查询：尽量使用 JOIN 替代子查询
sql-- 不推荐 SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active'); -- 推荐 SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.status = 'active';优化 JOIN 操作：
- 确保 JOIN 条件上有索引
- 小表驱动大表
- 避免复杂的多表 JOIN
优化排序和分组：
- 确保排序和分组的列上有索引
- 避免在排序和分组中使用函数
优化 UPDATE 和 DELETE 语句
添加 WHERE 条件：避免全表更新或删除
sql-- 不推荐 UPDATE orders SET status = 'completed'; -- 推荐 UPDATE orders SET status = 'completed' WHERE id = 12345;使用 LIMIT 限制更新和删除的数量：
sqlDELETE FROM orders WHERE status = 'cancelled' LIMIT 1000;避免在 WHERE 条件中使用函数：
sql-- 不推荐 SELECT * FROM orders WHERE DATE(order_date) = '2023-01-01'; -- 推荐 SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-01-02';
表结构优化
优化表设计
选择合适的字段类型：
- 尽量使用小的字段类型
- 选择合适的整数类型（INT、SMALLINT、TINYINT 等）
- 选择合适的字符串类型（VARCHAR、CHAR 等）
- 避免使用 TEXT 和 BLOB 类型存储频繁查询的数据
合理设计表结构：
- 遵循范式设计，避免数据冗余
- 适当反范式设计，提高查询性能
- 拆分大表，将不常用的字段拆分到单独的表
使用分区表
对于超大型表，可以使用分区表提高查询性能：
sql
-- 按时间范围分区
CREATE TABLE logs (
id INT AUTO_INCREMENT,
log_date DATETIME NOT NULL,
message TEXT,
PRIMARY KEY (id, log_date)
) PARTITION BY RANGE (YEAR(log_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024)
);使用分表和分库
对于超大规模的数据，可以考虑使用分表和分库：
- 水平分表：将同一表的数据分散到多个表中，按行分割
- 垂直分表：将同一表的不同字段分散到多个表中，按列分割
- 分库：将不同的表分散到不同的数据库中
服务器配置优化
根据服务器资源和业务需求，调整 MySQL 配置：
ini
[mysqld]
# 内存配置
innodb_buffer_pool_size = 8G
key_buffer_size = 256M
table_open_cache = 2000
thread_cache_size = 128
# 查询优化
query_cache_size = 0
query_cache_type = 0
# 连接配置
max_connections = 1000
wait_timeout = 3600
# 日志配置
slow_query_log = 1
long_query_time = 1
# InnoDB 配置
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1000
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M应用层优化
- 使用连接池：减少连接建立和关闭的开销
- 添加缓存：将频繁查询的结果缓存到 Redis、Memcached 等缓存系统中
- 优化应用逻辑：减少不必要的数据库查询
- 批量操作：将多个小查询合并为一个大查询
- 异步操作：将非关键操作改为异步执行
版本差异与特性
MySQL 5.6
- 基础的慢查询日志功能
- 支持
log_output配置（FILE、TABLE、NONE） - 支持
long_query_time配置，精度为秒 - 不支持 sys schema
- 有限的 Performance Schema 支持
- 不支持 JSON 格式的慢查询日志
MySQL 5.7
- 增强的慢查询日志功能
- 支持更精确的
long_query_time配置（微秒级） - 引入 sys schema，提供更简单的慢查询分析视图
- 增强的 Performance Schema，支持更多慢查询监控指标
- 支持
log_slow_admin_statements配置，记录管理语句 - 支持
log_slow_slave_statements配置，记录从库慢查询
MySQL 8.0
- 支持 JSON 格式的慢查询日志
- 增强的 Performance Schema，提供更详细的慢查询监控
- 增强的 sys schema，提供更多慢查询诊断视图
- 支持
slow_query_log_use_global_control配置，动态控制慢查询日志 - 支持
log_throttle_queries_not_using_indexes配置，限制无索引查询日志的生成频率 - 支持
log_slow_extra配置，记录更多慢查询信息
慢查询的监控和管理
慢查询日志的管理
- 定期归档慢查询日志：避免日志文件过大
- 设置合理的慢查询阈值：根据业务需求调整
long_query_time - 清理过期的慢查询日志：定期删除不再需要的日志文件
- 使用日志轮换工具：如
logrotate管理日志文件
监控工具的使用
- 内置监控：Performance Schema、Sys Schema
- 第三方监控工具：
- Prometheus + Grafana：提供丰富的监控指标和可视化仪表盘
- Zabbix：支持慢查询监控和告警
- Nagios：通过插件监控慢查询
- MySQL Enterprise Monitor：企业级监控解决方案
告警机制的建立
- 设置慢查询数量告警阈值
- 设置慢查询执行时间告警阈值
- 配置告警通知方式（邮件、短信、微信等）
- 建立告警处理流程
慢查询优化的最佳实践
索引设计原则
- 为常用查询条件添加索引
- 选择选择性高的列作为索引
- 控制索引数量，避免过多索引
- 使用复合索引时，将选择性高的列放在前面
- 为排序和分组的列添加索引
- 定期检查和优化索引
SQL 编写规范
- 只查询必要的字段，避免使用
SELECT * - 使用 JOIN 替代子查询
- 避免在 WHERE 条件中使用函数
- 使用 LIMIT 限制查询结果数量
- 避免全表扫描
- 优化 JOIN 操作，确保 JOIN 条件上有索引
定期优化和维护
- 定期分析慢查询日志：至少每周分析一次慢查询日志
- 定期优化数据库：使用
OPTIMIZE TABLE优化表 - 定期检查索引使用情况：删除不使用的索引
- 定期监控数据库性能：关注 CPU、内存、I/O 等指标
- 定期更新 MySQL 版本：使用最新的稳定版本，享受性能优化
与开发团队协作
- 建立 SQL 审查机制：所有 SQL 语句在上线前必须经过审查
- 提供开发规范和培训：指导开发人员编写高效的 SQL
- 建立性能测试环境：在上线前测试 SQL 性能
- 及时沟通性能问题：与开发团队共同解决慢查询问题
案例分析
缺少索引导致的慢查询
问题描述： 电商平台的订单查询页面响应缓慢，慢查询日志中记录了大量如下查询：
sql
SELECT * FROM orders WHERE customer_id = 1000 AND order_date > '2023-01-01';排查过程：
使用
EXPLAIN分析查询计划：id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders ALL NULL NULL NULL NULL 1000000 Using where发现查询使用了全表扫描，没有使用任何索引。
查看表的索引信息：
sqlSHOW INDEX FROM orders;发现
orders表只有主键索引，没有为customer_id和order_date字段添加索引。
解决方案： 为 customer_id 和 order_date 字段添加复合索引：
sql
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);优化效果：
- 查询执行时间从 5.2 秒降低到 0.01 秒
- 不再出现在慢查询日志中
- 订单查询页面响应时间显著提高
SQL 语句优化
问题描述： 某报表查询语句执行缓慢，耗时超过 10 秒：
sql
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total_amount) AS total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active'
AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY c.id, c.name
ORDER BY total_amount DESC;排查过程：
- 使用
EXPLAIN分析查询计划，发现orders表的order_date字段没有索引 - 检查
orders表的索引信息，确认缺少order_date索引 - 分析 SQL 语句，发现可以优化 JOIN 顺序和条件
解决方案：
为
orders表的order_date字段添加索引：sqlCREATE INDEX idx_orders_order_date ON orders(order_date);优化 SQL 语句，先过滤
orders表，再 JOIN：sqlSELECT c.name, COUNT(o.id) AS order_count, SUM(o.total_amount) AS total_amount FROM customers c LEFT JOIN ( SELECT customer_id, id, total_amount FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' ) o ON c.id = o.customer_id WHERE c.status = 'active' GROUP BY c.id, c.name ORDER BY total_amount DESC;
优化效果：
- 查询执行时间从 10.5 秒降低到 0.8 秒
- 资源使用率显著降低
- 报表生成时间大幅缩短
总结
慢查询是 MySQL 数据库性能问题的主要原因之一，解决慢查询问题需要系统性的方法和丰富的经验。通过本文的介绍，DBA 可以掌握慢查询的常见原因、排查步骤、优化方法和最佳实践。
在实际运维中，DBA 应该：
- 建立完善的慢查询监控机制：及时发现和处理慢查询
- 定期分析慢查询日志：找出性能瓶颈并优化
- 优化索引和 SQL 语句：提高查询效率
- 与开发团队协作：共同优化应用和数据库
- 持续学习和实践：不断提高慢查询优化的能力
通过不断优化和改进，可以显著提高 MySQL 数据库的性能，确保应用的高可用性和良好的用户体验。不同 MySQL 版本在慢查询处理方面有不同的特性和工具，DBA 需要根据实际使用的版本选择合适的慢查询处理策略。