MySQL慢查询日志：配置、分析与优化实战

1. 慢查询日志数据库性能优化的第一道防线作为一名常年与数据库打交道的开发者我见过太多因为忽视慢查询而导致系统崩溃的案例。慢查询日志Slow Query Log就像是数据库系统的黑匣子它记录下所有执行时间超过预设阈值的SQL语句为我们提供了优化数据库性能的第一手资料。想象一下当你的应用突然变慢用户开始抱怨这时候慢查询日志就是你的破案线索。MySQL的慢查询日志功能自5.1版本开始就作为核心功能存在它能够精确记录执行时间超过long_query_time值的查询语句包括查询时间、锁定时间、返回行数等关键信息。不同于普通的错误日志或二进制日志慢查询日志专门针对性能问题设计是DBA和开发人员优化数据库性能的必备工具。重要提示在生产环境中开启慢查询日志会带来约5%-15%的性能开销建议在问题排查期间开启日常监控可选择抽样记录方式。2. 慢查询日志的核心配置参数详解2.1 基础配置参数在my.cnfLinux或my.iniWindows配置文件中以下参数控制着慢查询日志的行为[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 log_queries_not_using_indexes 1 min_examined_row_limit 100slow_query_log启用(1)或禁用(0)慢查询日志slow_query_log_file日志文件路径确保MySQL用户有写入权限long_query_time阈值秒执行时间超过此值的查询会被记录log_queries_not_using_indexes记录未使用索引的查询即使执行时间未超阈值min_examined_row_limit仅记录检查行数超过此值的查询2.2 动态调整参数在不重启MySQL服务的情况下可以通过SET命令动态调整SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;实战经验long_query_time的初始设置建议为2秒优化过程中逐步下调至0.5秒。对于高并发OLTP系统甚至可设为0.1秒。2.3 日志轮换策略慢查询日志会不断增长需要定期清理或轮换# 日志轮换示例配合Linux的logrotate /var/log/mysql/mysql-slow.log { daily rotate 7 missingok compress delaycompress notifempty create 640 mysql mysql postrotate /usr/bin/mysqladmin flush-logs endscript }3. 慢查询日志分析实战3.1 原始日志解读一条典型的慢查询日志记录如下# Time: 2023-08-20T15:23:45.123456Z # UserHost: app_user[app_user] [192.168.1.100] # Query_time: 5.123456 Lock_time: 0.001234 Rows_sent: 1 Rows_examined: 1000000 SET timestamp1692545025; SELECT * FROM orders WHERE customer_id 100 AND status pending ORDER BY create_time DESC;各字段含义Query_time查询执行总时间Lock_time等待表锁的时间Rows_sent返回给客户端的行数Rows_examined服务器层检查的行数3.2 使用mysqldumpslow工具MySQL自带的mysqldumpslow工具可以汇总慢查询日志mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log常用参数-s排序方式t按时间c按次数l按锁定时间-t显示前N条记录-g使用正则过滤输出示例Count: 5 Time3.12s (15s) Lock0.00s (0s) Rows1.0 (5), app_user[app_user][192.168.1.100] SELECT * FROM orders WHERE customer_idN AND statuspending ORDER BY create_time DESC3.3 使用pt-query-digest进行高级分析Percona Toolkit中的pt-query-digest提供更强大的分析功能pt-query-digest /var/log/mysql/mysql-slow.log输出包括查询响应时间占比执行次数统计单个查询的详细性能分析建议的优化方向4. 常见慢查询模式与优化策略4.1 全表扫描问题症状Rows_examined远大于Rows_sent日志中出现Using filesort或Using temporary解决方案-- 添加复合索引 ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status); -- 优化后EXPLAIN验证 EXPLAIN SELECT * FROM orders WHERE customer_id 100 AND status pending;4.2 分页查询性能低下典型慢查询SELECT * FROM large_table ORDER BY create_time LIMIT 1000000, 10;优化方案-- 方案1使用覆盖索引 SELECT id FROM large_table ORDER BY create_time LIMIT 1000000, 10; SELECT * FROM large_table WHERE id IN (...); -- 方案2记住上次查询位置 SELECT * FROM large_table WHERE create_time 2023-08-01 ORDER BY create_time LIMIT 10;4.3 大表JOIN操作问题查询SELECT * FROM users u JOIN orders o ON u.id o.user_id WHERE u.register_time 2023-01-01;优化步骤确保JOIN字段有索引减少SELECT * 只查询必要字段考虑分批次处理ALTER TABLE orders ADD INDEX idx_user_id (user_id); SELECT u.name, o.order_id, o.amount FROM users u FORCE INDEX(PRIMARY) JOIN orders o FORCE INDEX(idx_user_id) ON u.id o.user_id WHERE u.register_time 2023-01-01;5. 生产环境慢查询监控体系5.1 监控架构设计------------------- ---------------- ------------------- | MySQL Slow Log | - | Log Shipper | - | Centralized | | (本地文件) | | (Filebeat/Fluentd)| | Storage (ES) | ------------------- ---------------- ------------------- | --------------- | Visualization | | (Grafana/Kibana)| ---------------5.2 基于Prometheus的监控方案配置mysqld_exporter收集慢查询指标- name: mysql_slow_queries metrics_path: /metrics static_configs: - targets: [localhost:9104] params: slow_log: [ON]Grafana面板关键指标慢查询次数/分钟平均慢查询时长按模式分组的慢查询统计索引命中率5.3 慢查询告警规则groups: - name: mysql-slow-alert rules: - alert: MySQLSlowQueryIncrease expr: rate(mysql_global_status_slow_queries[5m]) 5 for: 10m labels: severity: warning annotations: summary: MySQL慢查询激增 (instance {{ $labels.instance }}) description: 慢查询率当前为 {{ $value }} 次/分钟6. 高级优化技巧与实战案例6.1 查询重写技巧案例1IN子查询优化-- 优化前 SELECT * FROM products WHERE category_id IN ( SELECT id FROM categories WHERE name LIKE %电子% ); -- 优化后 SELECT p.* FROM products p JOIN categories c ON p.category_id c.id WHERE c.name LIKE %电子%;案例2OR条件优化-- 优化前 SELECT * FROM users WHERE phone 13800138000 OR email testexample.com; -- 优化后 SELECT * FROM users WHERE phone 13800138000 UNION SELECT * FROM users WHERE email testexample.com;6.2 索引优化策略多列索引顺序原则等值查询字段优先高区分度字段靠前范围查询字段靠后排序字段放在最后示例-- 查询模式 SELECT * FROM orders WHERE shop_id 100 AND status completed AND create_time BETWEEN 2023-01-01 AND 2023-08-20 ORDER BY amount DESC; -- 最优索引 ALTER TABLE orders ADD INDEX idx_shop_status_time_amount (shop_id, status, create_time, amount);6.3 临时表与内存优化对于复杂查询适当调整内存参数-- 增加排序缓冲区 SET SESSION sort_buffer_size 4*1024*1024; -- 临时表大小 SET SESSION tmp_table_size 64*1024*1024; SET SESSION max_heap_table_size 64*1024*1024;7. 慢查询分析的常见误区7.1 仅关注执行时间不要只看Query_time还需关注Lock_time表锁等待时间Rows_examined检查的行数Rows_sent实际返回的行数是否使用临时表或文件排序7.2 忽视事务的影响一个在测试环境很快的查询在生产环境可能变慢因为长事务持有锁事务隔离级别不同并发连接数影响7.3 过度依赖工具输出自动化工具给出的建议并非总是最优索引建议可能忽略写操作成本查询重写可能改变语义需要结合业务场景判断8. 从慢查询到性能优化的完整闭环8.1 优化流程方法论收集持续记录慢查询日志分析识别高频/高消耗查询诊断EXPLAIN分析执行计划优化索引/查询重写/架构调整验证对比优化前后性能监控建立持续监控机制8.2 性能基准测试优化前后应进行基准测试sysbench oltp_read_write \ --db-drivermysql \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-usertest \ --mysql-passwordtest \ --mysql-dbsbtest \ --tables10 \ --table-size100000 \ --time300 \ --threads32 \ --report-interval10 \ run8.3 文档化与知识沉淀建立团队内部的慢查询知识库常见慢查询模式已验证的优化方案禁止使用的查询模式索引使用规范我在实际工作中发现许多性能问题都是重复出现的。建立一个共享的优化案例库可以显著提高团队处理慢查询的效率。比如我们团队维护的十大禁忌查询文档新成员入职第一天就必须学习这防止了很多潜在的性能问题。