我要评论1. 慢查询日志概述
1.1 什么是慢查询日志
mysql慢查询日志是mysql数据库提供的一种性能诊断工具，用于记录执行时间超过指定阈值的sql查询语句。通过分析慢查询日志，开发人员和dba可以：
- 识别执行效率低下的sql语句
- 发现数据库设计或索引设计的问题
- 监控数据库性能变化趋势
- 为sql优化提供数据依据
1.2 慢查询日志的重要性
在数据库性能优化中，慢查询日志是不可或缺的工具。据统计，80%的数据库性能问题都是由少数几个执行效率低下的sql语句引起的。通过慢查询日志，我们可以精准定位这些问题sql，实现"精准打击"。
2. 慢查询日志的配置与管理
2.1 检查当前状态
在配置慢查询日志之前，首先需要检查当前的相关配置状态：
-- 检查慢查询日志是否开启 show variables like 'slow_query_log'; -- 查看慢查询日志文件路径 show variables like 'slow_query_log_file'; -- 查看慢查询时间阈值 show variables like 'long_query_time'; -- 检查是否记录未使用索引的查询 show variables like 'log_queries_not_using_indexes'; -- 查看累计慢查询数量 show global status like 'slow_queries';
2.2 开启慢查询日志
临时开启（重启后失效）
-- 开启慢查询日志 set global slow_query_log = 'on'; -- 设置慢查询时间阈值为2秒 set global long_query_time = 2; -- 指定日志文件路径 set global slow_query_log_file = '/var/lib/mysql/mysql-slow.log'; -- 可选：记录未使用索引的查询（生产环境慎用） set global log_queries_not_using_indexes = 'on';
永久开启（修改配置文件）
修改mysql配置文件（my.cnf或my.ini），在[mysqld]部分添加以下配置：
[mysqld] # 开启慢查询日志 slow_query_log = 1 # 慢查询日志文件路径 slow_query_log_file = /var/lib/mysql/mysql-slow.log # 慢查询时间阈值，单位：秒 long_query_time = 2 # 记录未使用索引的查询（可选，生产环境慎用） log_queries_not_using_indexes = 0 # 慢查询日志记录时区（mysql 8.0+） log_timestamps = system
修改完成后需要重启mysql服务使配置生效。
2.3 配置参数详解
| 参数名 | 默认值 | 说明 | 建议 |
|---|---|---|---|
| slow_query_log | off | 慢查询日志开关 | 生产环境建议开启 |
| long_query_time | 10.0 | 慢查询阈值(秒) | 生产环境2-5秒，开发环境0.5-1秒 |
| slow_query_log_file | host_name-slow.log | 日志文件路径 | 建议指定绝对路径 |
| log_queries_not_using_indexes | off | 是否记录未使用索引的查询 | 性能测试时开启，生产环境关闭 |
| min_examined_row_limit | 0 | 记录检查行数下限 | 可根据业务特点调整 |
| log_slow_admin_statements | off | 是否记录管理语句 | 按需开启 |
| log_output | file | 日志输出方式 | 建议使用file |
3. 慢查询日志内容解析
3.1 日志格式详解
一条完整的慢查询日志记录通常包含以下部分：
# time: 2023-10-25t08:15:30.123456z
# user@host: user1[app_user] @ [192.168.1.100] id: 123456
# query_time: 5.123456 lock_time: 0.001234 rows_sent: 150 rows_examined: 500000
set timestamp=1698221730;
select * from orders o
join customers c on o.customer_id = c.id
where o.create_date > '2023-01-01'
and o.status = 'pending'
and c.country = 'us'
order by o.create_date desc;
3.2 关键字段说明
| 字段 | 含义 | 性能分析意义 |
|---|---|---|
| time | 查询发生的时间戳 | 用于分析慢查询的时间分布 |
| user@host | 执行查询的用户和主机 | 定位问题用户或应用 |
| query_time | 查询总执行时间 | 核心性能指标，直接反映sql性能 |
| lock_time | 表锁等待时间 | 锁竞争严重时需要关注 |
| rows_sent | 返回客户端的行数 | 查询结果集大小 |
| rows_examined | 服务器扫描的行数 | 关键指标，反映索引效率 |
| sql语句 | 完整的sql文本 | 优化分析的对象 |
3.3 执行时间分析
query_time字段是慢查询日志中最重要的指标，它精确记录了sql在数据库服务器上的实际执行时间。这个时间包括：
- sql解析和优化时间
- 数据读取时间（磁盘i/o）
- 数据处理时间（cpu）
- 锁等待时间
- 网络传输时间（在数据库服务器内）
4. 慢查询日志分析工具
4.1 使用mysqldumpslow分析
mysqldumpslow是mysql官方提供的慢查询日志分析工具：
# 按执行时间排序，显示最慢的10个查询 mysqldumpslow -s t -t 10 /path/to/slow.log # 按出现次数排序，显示最常见的10个慢查询 mysqldumpslow -s c -t 10 /path/to/slow.log # 按累计执行时间排序 mysqldumpslow -s at -t 10 /path/to/slow.log # 分析特定用户的慢查询 mysqldumpslow -a -g "user1" /path/to/slow.log # 详细模式，显示完整的sql语句 mysqldumpslow -a -s t -t 5 /path/to/slow.log
4.2 使用pt-query-digest分析
percona toolkit中的pt-query-digest是更强大的分析工具：
# 基本用法 pt-query-digest /path/to/slow.log # 输出到文件 pt-query-digest /path/to/slow.log > slow_report.txt # 分析最近12小时的慢查询 pt-query-digest --since=12h /path/to/slow.log # 按特定维度分析 pt-query-digest --group-by=fingerprint --order-by=query_time:sum /path/to/slow.log # 对比两个时间段的慢查询 pt-query-digest --since=2023-10-01 --until=2023-10-02 /path/to/slow.log
4.3 自定义sql分析
对于输出到表的慢查询日志，可以直接使用sql进行分析：
-- 查询最慢的10个sql语句
select db, query_time, lock_time, rows_sent, rows_examined, sql_text
from mysql.slow_log
order by query_time desc
limit 10;
-- 分析各数据库的慢查询分布
select db, count(*) as slow_count, avg(query_time) as avg_time
from mysql.slow_log
where start_time > now() - interval 1 day
group by db;
-- 查找扫描行数过多的查询
select sql_text, rows_examined, rows_sent,
round(rows_examined/rows_sent) as ratio
from mysql.slow_log
where rows_sent > 0 and rows_examined > 10000
order by ratio desc
limit 10;
5. 基于慢查询日志的性能优化实践
5.1 优化流程
- 识别问题sql：从慢查询日志中找出执行时间最长、频率最高的查询
- 分析执行计划：使用explain分析sql执行计划
- 定位瓶颈：确定是索引问题、join问题还是数据量问题
- 实施优化：添加索引、重写sql、调整schema等
- 验证效果：对比优化前后的执行时间和资源消耗
5.2 常见问题及解决方案
案例1：缺少合适的索引
-- 慢查询日志中的原始sql # query_time: 4.234567 lock_time: 0.001000 rows_sent: 1 rows_examined: 500000 select * from orders where customer_id = 100 and status = 'completed'; -- 优化方案：添加复合索引 alter table orders add index idx_customer_status (customer_id, status);
案例2：低效的like查询
-- 慢查询日志中的原始sql
# query_time: 3.123456 lock_time: 0.002000 rows_sent: 50 rows_examined: 1000000
select * from products where name like '%apple%';
-- 优化方案：使用全文索引或调整查询方式
alter table products add fulltext(name);
select * from products where match(name) against('apple' in natural language mode);
案例3：大表分页查询
-- 慢查询日志中的原始sql # query_time: 2.987654 lock_time: 0.003000 rows_sent: 20 rows_examined: 100020 select * from orders order by create_date desc limit 100000, 20; -- 优化方案：使用游标分页或子查询优化 select * from orders where id > (select id from orders order by create_date desc limit 100000, 1) order by create_date desc limit 20;
5.3 索引优化策略
基于慢查询日志的常见索引优化：
- 为where条件列添加索引
- 为join条件列添加索引
- 为order by和group by列添加索引
- 使用覆盖索引减少回表
- 注意索引选择性和区分度
6. 生产环境最佳实践
6.1 配置建议
- 阈值设置：生产环境建议2-5秒，开发测试环境可设置为0.5-1秒
- 日志轮转：配置日志轮转，避免日志文件过大
- 监控告警：对慢查询数量设置监控告警
- 定期分析：每周或每月定期分析慢查询趋势
6.2 性能考量
开启慢查询日志对数据库性能有一定影响，主要体现在：
- i/o开销：日志写入会增加磁盘i/o
- cpu开销：日志记录和分析需要cpu资源
- 存储空间：日志文件占用磁盘空间
建议在高并发业务高峰期适当调整阈值，或使用采样方式记录。
6.3 与其他工具结合
慢查询日志应与其他监控工具结合使用：
- performance schema：深入分析sql执行细节
- explain analyze：分析sql执行计划
- mysql enterprise monitor：企业级监控方案
- prometheus + grafana：可视化监控
7. 总结
mysql慢查询日志是数据库性能优化中不可或缺的工具，它提供了sql执行时间的精确记录。通过合理配置慢查询日志，结合专业的分析工具，我们可以：
- 精准定位性能瓶颈
- 识别优化机会
- 监控数据库性能趋势
- 预防潜在的性能问题
记住，慢查询日志只是起点，真正的价值在于基于日志分析结果采取有效的优化措施，持续提升数据库性能。建议将慢查询日志分析纳入日常的数据库维护流程，建立从监控、分析到优化的完整闭环。
以上就是从配置到性能优化全面解析mysql慢查询日志的详细内容，更多关于mysql慢查询日志的资料请关注代码网其它相关文章！
发表评论