1. 慢查询日志数据库性能的“听诊器”如果你负责的线上应用突然变慢用户投诉页面加载时间从1秒变成了10秒你的第一反应是什么大多数人会去查代码、看服务器负载但经验告诉我数据库往往是那个“沉默的杀手”。而MySQL的慢查询日志就是定位这个杀手最直接、最有效的工具。它就像数据库的“听诊器”能清晰地记录下每一条执行缓慢的SQL语句告诉你到底是哪条查询在拖慢整个系统。很多团队在性能问题火烧眉毛时才想起来配置它其实它更应该作为数据库运维的常规体检项目。今天我就结合自己踩过的坑和实战经验把慢查询日志从查看、配置到深度分析的完整链路给你讲透让你不仅能快速上手更能理解背后的原理真正把它用起来。2. 核心参数配置为你的数据库装上“监控探头”慢查询日志不是默认开启的你需要手动告诉MySQL“请开始记录那些跑得慢的查询。” 这主要通过几个核心的系统变量来控制。理解并合理配置这些参数是有效利用慢查询日志的第一步。配置不当要么记录了一堆无关紧要的信息淹没重点要么漏掉了真正的“元凶”。2.1 开启与关闭slow_query_log这是总开关控制慢查询日志功能是否启用。-- 查看当前状态 SHOW VARIABLES LIKE slow_query_log; -- 临时开启重启后失效 SET GLOBAL slow_query_log ON; -- 临时关闭 SET GLOBAL slow_query_log OFF;为什么需要临时开关在生产环境当你进行一次性的大批量数据修复或迁移操作时可能会预期产生大量慢查询。此时临时关闭日志可以避免日志文件急速膨胀占用过多磁盘I/O影响正常业务。操作完成后切记立即开启。2.2 定义“慢”的标准long_query_time这是最关键的阈值参数单位是秒。它定义了什么样的查询才算“慢”。默认值是10秒但对于绝大多数OLTP在线事务处理系统来说10秒简直是灾难这个默认值过于宽松。-- 查看当前阈值 SHOW VARIABLES LIKE long_query_time; -- 设置为1秒常用 SET GLOBAL long_query_time 1; -- 设置为更严格的0.5秒 SET GLOBAL long_query_time 0.5;设置多少合适这没有黄金标准需要根据业务容忍度来定。我的经验是Web应用通常设置在0.5秒到1秒。用户对页面响应感知的临界点大约在1秒左右。内部后台系统可以适当放宽到2-3秒。报表或分析类查询这类查询本身耗时较长可能需要单独设置比如10秒避免日志被大量预期内的长查询淹没。注意SET GLOBAL命令对当前已建立的会话不生效只对新建立的连接有效。如果需要立即对所有连接生效需要在每个会话中执行SET SESSION long_query_time X或者直接重启MySQL服务。这是一个常见的“坑”你以为改好了但当前连接执行的查询还是按旧阈值记录。2.3 指定日志文件路径slow_query_log_file这个参数决定了慢查询日志写在哪里。默认路径和文件名取决于你的安装方式和操作系统。-- 查看当前日志文件位置 SHOW VARIABLES LIKE slow_query_log_file; -- 修改路径需要MySQL进程对该路径有写权限 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;最佳实践建议将日志文件放在独立的、容量较大的磁盘分区避免和数据库数据文件、系统日志争抢I/O资源。同时确保MySQL运行用户通常是mysql对该目录有读写权限。2.4 记录未使用索引的查询log_queries_not_using_indexes这是一个非常有用的辅助参数。当它设置为ON时即使查询执行时间没有超过long_query_time但只要它没有使用任何索引就会被记录到慢查询日志中。SET GLOBAL log_queries_not_using_indexes ON;为什么需要它有些查询可能因为数据量小全表扫描也很快比如0.01秒不会触发慢查询。但随着数据增长这类查询会迅速恶化。开启这个选项可以帮助你提前发现潜在的“性能炸弹”在问题爆发前进行优化。带来的副作用如果你的应用中有很多小的、没必要加索引的查询例如在配置表、状态表上的点查开启这个选项会导致日志里充满“噪音”。因此我通常会在定期巡检比如每周一次时临时开启它扫描一遍发现问题后即关闭。2.5 管理日志输出log_output这个参数控制慢查询日志的输出目的地。可选值为FILE输出到文件和TABLE输出到mysql.slow_log表。SHOW VARIABLES LIKE log_output; -- 输出到文件默认且最常用 SET GLOBAL log_output FILE; -- 输出到表 SET GLOBAL log_output TABLE;文件 vs. 表输出到文件性能开销小是生产环境的默认选择。可以使用mysqldumpslow、pt-query-digest等工具高效分析。输出到表记录在mysql.slow_log表中方便用SQL语句直接查询和分析更灵活。但所有查询的写入都会转化为对这张表的INSERT操作在高并发环境下可能对数据库本身造成额外压力并产生更多的慢查询记录慢查询这个动作本身成了慢查询。一般仅用于临时诊断或低负载环境。2.6 永久生效配置修改my.cnf或my.ini前面提到的SET GLOBAL命令只在MySQL服务运行期间有效重启后配置会丢失。要使配置永久生效必须修改MySQL的配置文件。找到配置文件通常是/etc/my.cnf、/etc/mysql/my.cnf或/usr/local/mysql/etc/my.cnf。Windows系统则在MySQL安装目录下的my.ini。在[mysqld]段落下添加或修改以下参数[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 0 # 默认关闭按需开启 log_output FILE重启MySQL服务使配置生效。# Linux systemd sudo systemctl restart mysqld # 或 sudo systemctl restart mysql # Linux SysVinit sudo service mysql restart # Windows (服务管理器) net stop MySQL net start MySQL3. 日志内容深度解读从一行记录到性能画像配置好并运行一段时间后你的慢查询日志文件里就会积累内容。原始日志看起来可能有点杂乱但每一行都包含着丰富的信息。我们来看一个典型的慢查询日志条目为了可读性做了换行和缩进# Time: 2023-10-27T08:15:42.123456Z # UserHost: app_user[app_user] [192.168.1.100] # Query_time: 3.451234 Lock_time: 0.001234 Rows_sent: 1 Rows_examined: 1000000 SET timestamp1698394542; SELECT * FROM order WHERE status pending AND create_time 2023-10-20 ORDER BY id DESC;我们来逐行拆解每个字段的含义和它揭示的性能问题Time: 查询执行完成的绝对时间UTC。用于定位问题发生的时间点结合业务监控可以看是否在促销、定时任务等特定时段高发。UserHost: 执行该查询的数据库账号和来源主机IP。这有助于区分是哪个应用服务、哪个后台任务发出的查询。如果发现来自某个非预期的IP或账号可能意味着有异常访问或权限问题。Query_time:最重要的指标查询执行的总耗时单位秒。本例中3.45秒远超我们设定的1秒阈值。Lock_time: 查询等待锁如表锁、行锁的时间。这个值如果很高说明不是SQL本身慢而是被其他事务阻塞了需要排查锁竞争。Rows_sent: 返回给客户端的数据行数。本例是1行。Rows_examined: 服务器层为了执行这条查询而检查的行数。本例是惊人的100万行。SET timestamp: 查询开始执行时的时间戳Unix时间戳。通过Query_time和这个时间戳可以推算出查询执行的起止时间。最后一行: 就是导致问题的原始SQL语句。核心矛盾点分析Rows_sent: 1对比Rows_examined: 1000000。这意味着数据库为了找到那1条符合条件的记录扫描了100万行数据。这就是典型的全表扫描或索引失效场景。理想情况下Rows_examined应该略大于或等于Rows_sent。这个比值Rows_examined/Rows_sent越大说明查询效率越低优化空间越大。Lock_time的警示如果一条SQL的Query_time是2秒但Lock_time就占了1.8秒那么真正的执行时间只有0.2秒。这时候优化SQL本身可能收效甚微你需要去查看是不是有未提交的长事务持有着锁表结构设计是否合理导致锁粒度太大业务逻辑是否在频繁更新同一行数据造成热点行锁竞争4. 高效分析工具从海量日志中提炼黄金信息慢查询日志文件会随时间增长动辄几个GB用肉眼逐行分析是不现实的。我们需要借助工具来聚合、排序、总结快速找到最需要被优化的“TOP N”慢查询。4.1 官方原生工具mysqldumpslowMySQL自带的Perl脚本适合快速进行简单的统计分析。它可以将类似的SQL语句即使参数值不同归类并统计执行次数、总耗时、平均耗时等。# 查看帮助 mysqldumpslow --help # 最常用的命令按总查询时间排序取出前10条 mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log # 按平均查询时间排序 mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log # 按出现次数锁时间排序 mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log # 解析结果示例 Count: 125 Time2.34s (292s) Lock0.00s (0s) Rows10.0 (1250), app_user[app_user][192.168.1.100] SELECT * FROM user WHERE age N AND city S;解读这表示同一种模式的查询SELECT * FROM user WHERE age ? AND city ?执行了125次总耗时292秒平均每次2.34秒总共返回1250行平均每次10行。这立刻让你知道优化这个模式的查询收益最大。mysqldumpslow的局限性分析能力相对基础无法提供更细致的等待时间分解如InnoDB行锁等待、IO等待。对SQL的规范化去除参数值算法比较简单有时会把不同模式的SQL误归为一类。输出信息不够直观对于复杂分析支持较弱。4.2 业界标杆pt-query-digest这是Percona Toolkit工具包中的明星组件是分析慢查询日志的事实标准。它功能强大能生成极其详细的报告。# 基本用法分析慢日志文件 pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt # 分析最近24小时的慢查询如果日志按天切割 pt-query-digest /var/log/mysql/mysql-slow.log --since 24h slow_report_last_24h.txt # 直接分析来自表的慢查询 pt-query-digest --processlist hlocalhost,uroot,ppassword --interval 0.01 current_queries.txtpt-query-digest生成的报告结构清晰通常包括总体概览总查询次数、唯一查询指纹数、时间范围、总耗时等。执行时间排名列出最耗时的查询模式。出现次数排名列出执行最频繁的查询模式。锁时间排名列出等待锁最久的查询。单条查询详情针对每一个查询指纹提供执行统计次数、总/平均/最小/最大耗时、95%分位耗时排除极端值更有代表性。表格显示时间分布直方图。示例SQL语句。通过EXPLAIN解析出的执行计划需额外参数支持。为什么推荐pt-query-digest深度聚合其SQL指纹算法更智能能准确识别同一模式的不同查询。丰富的指标提供分位数、标准差等统计指标帮你识别不稳定的查询。可扩展性支持多种输入源文件、表、tcpdump输出格式多样报告、Anemometer、JSON等。生态整合其输出结果可以方便地导入到监控系统如PrometheusGrafana进行长期趋势观察。4.3 实战分析案例定位并优化一个真实慢查询假设通过pt-query-digest我们定位到以下“罪魁祸首”# Rank 1: 总耗时占比 45% # Query: SELECT * FROM product WHERE category_id ? AND price BETWEEN ? AND ? ORDER BY sales DESC LIMIT 100 # 执行次数5000次 平均时间220ms 95%时间450ms第一步获取真实执行计划在测试环境或业务低峰期抓取一个具体的慢SQL实例使用EXPLAIN或EXPLAIN FORMATJSON查看其执行计划。EXPLAIN SELECT * FROM product WHERE category_id 10 AND price BETWEEN 100 AND 500 ORDER BY sales DESC LIMIT 100\G可能的输出显示type: ALL全表扫描key: NULL未使用索引rows: 500000预计扫描行数巨大。第二步分析索引情况检查product表结构SHOW CREATE TABLE product;发现现有索引是KEY idx_category (category_id)和KEY idx_price (price)。问题在于MySQL在单表查询中通常只能使用一个索引在5.6以后版本有索引合并优化但非所有场景生效。这里WHERE条件涉及两个字段ORDER BY又涉及第三个字段sales。第三步设计并创建更合适的索引根据“最左前缀原则”和查询模式创建一个复合索引来覆盖WHERE和ORDER BY子句。这里的选择有(category_id, price, sales)将等值查询的category_id放在最左范围查询的price放中间排序字段sales放最后。但范围查询price BETWEEN ... AND ...会导致其后面的索引列sales无法用于排序。(category_id, sales)利用category_id快速过滤但price条件无法用索引需要回表过滤。经过权衡如果category_id的过滤性很好每个类目下的商品数不多而price范围很宽那么(category_id, sales)可能更好因为它能利用索引完成排序避免昂贵的filesort。如果price范围很窄过滤性更强或许(category_id, price)更好但排序需要filesort。第四步测试验证创建索引后再次执行EXPLAIN观察执行计划是否变为type: range或refExtra字段中的Using filesort是否消失。然后在测试环境用真实数据量进行压力测试对比优化前后的平均响应时间和95分位响应时间。第五步上线与监控在业务低峰期上线新索引。上线后持续观察慢查询日志和监控确认该查询已从慢日志中消失且没有对写入性能造成明显影响因为索引会增加写操作的开销。5. 生产环境运维实践让慢查询日志健康运行仅仅会配置和分析还不够在生产环境中管理慢查询日志本身也是一门学问。处理不当它可能从帮手变成麻烦。5.1 日志轮转与清理慢查询日志会不断增长必须制定轮转策略防止撑爆磁盘。使用Linux的logrotate工具这是最推荐的方式。可以配置按天或按大小切割并压缩旧日志。# /etc/logrotate.d/mysql-slow /var/log/mysql/mysql-slow.log { daily rotate 30 compress delaycompress missingok notifempty create 640 mysql mysql postrotate # 向MySQL发送flush-logs信号让其开始写新的日志文件 /usr/bin/mysqladmin -uroot -p密码 flush-logs 2/dev/null || true endscript }手动清理如果日志文件过大可以先重命名原文件然后让MySQL重新创建。mv /var/log/mysql/mysql-slow.log /var/log/mysql/mysql-slow.log.old mysqladmin -uroot -p flush-logs # 或执行 SQL: FLUSH SLOW LOGS;重要提示在重命名日志文件后必须执行FLUSH LOGS或重启MySQL否则MySQL会继续向已重命名的文件描述符写入导致日志仍写入旧文件。这是另一个常见坑点。5.2 监控与告警慢查询日志不应该只是事后分析的“黑匣子”更应该接入实时监控。监控日志文件大小使用Zabbix、Prometheus等监控工具对慢查询日志所在分区的磁盘使用率设置告警。监控慢查询数量定期如每分钟解析慢查询日志统计新增的慢查询数量。如果单位时间内慢查询数量突增立即触发告警。这可以通过简单的Shell脚本配合crontab实现也可以使用更专业的日志收集工具如Filebeat将日志发送到ELK或Loki进行分析。关键指标监控将pt-query-digest分析出的TOP慢查询的执行时间平均、95分位、执行次数等指标通过脚本提取并上报到监控系统如Prometheus绘制趋势图。这样你能看到优化措施是否真的起了效果以及性能是否在逐渐劣化。5.3 性能开销考量与采样开启慢查询日志对数据库性能有轻微影响因为每条符合条件的查询都需要额外的I/O操作来写入日志。在极高并发的OLTP场景下这个开销需要关注。影响评估通常这个开销在1%-3%左右对于绝大多数系统是可以接受的。如果实在担心可以在业务低峰期开启高峰期间关闭。日志采样从MySQL 5.1.21版本开始引入了log_slow_rate_limit和log_slow_verbosity等更精细的控制但更常见的做法是在应用层或中间件层进行采样记录例如只记录1%的慢查询。不过这需要额外的架构支持。5.4 与全量日志(general_log)的区别新手有时会混淆慢查询日志和通用查询日志general_log。慢查询日志只记录超过阈值的查询。用于性能优化。通用查询日志记录所有连接到数据库的查询和语句包括连接、断开。用于审计、安全排查或复现问题。性能开销极大绝对不要在生产环境长期开启仅在排查特定问题时临时使用。6. 从日志到行动构建性能优化闭环查看和配置慢查询日志是手段不是目的。最终目的是形成一个持续的性能优化闭环。定期巡检建立制度每周或每两周固定时间运行pt-query-digest分析周期内的慢日志生成报告。不要等到用户投诉才行动。根本原因分析对于TOP慢查询不能只满足于“加个索引”。要深入分析业务逻辑是否合理这个查询是否真的需要能否通过业务改造避免例如用异步任务代替实时复杂统计。数据模型是否最优表结构设计能否改进字段类型是否合适例如用INT而不是VARCHAR存储IP。索引设计是否科学是否存在冗余索引索引选择性如何系统资源是否瓶颈慢查询是否因为当时CPU、内存、磁盘IO已达瓶颈需要结合系统监控如vmstat,iostat一起看。测试与上线任何索引变更或SQL重写必须在测试环境充分验证功能正确性和性能提升效果。上线时选择低峰期并准备好回滚方案。效果回溯优化上线后在下一个巡检周期确认该慢查询是否已从TOP列表中消失相关指标是否改善。这形成了闭环也积累了团队的优化经验。慢查询日志是MySQL给予DBA和开发者的宝贵礼物。它用最直接的方式将数据库内部那些拖慢系统的操作暴露出来。掌握它意味着你拥有了主动发现和解决性能问题的能力而不是在用户抱怨后被动救火。从我自己的经验来看坚持对慢查询日志进行例行分析和优化是保持一个数据库系统长期健康、稳定运行的最有效习惯之一。刚开始可能会觉得繁琐但当你通过优化一个关键查询将页面加载时间从数秒降到毫秒级时那种成就感以及业务方投来的赞许目光会让你觉得这一切都是值得的。