1. 慢查询SQL的识别价值与核心原理当数据库开始出现性能瓶颈时慢查询往往是首要怀疑对象。我经历过一个电商系统在促销期间崩溃的惨痛教训——事后分析发现一条未被及时发现的商品分类查询SQL在流量激增时拖垮了整个数据库集群。这个经历让我深刻认识到慢查询监控不是可选项而是数据库运维的生命线。MySQL的慢查询识别机制本质上是个执行时间过滤器。通过设定阈值默认10秒系统会自动记录所有执行时间超过该阈值的SQL语句。但这里有个关键细节这个时间计算的是实际执行时间execution time而非响应时间response time这意味着网络延迟等因素不会被计入。在MySQL 5.7及以上版本中时间精度可以达到微秒级这对于高性能应用尤为重要。慢查询日志的核心参数包括slow_query_log开关1开启/0关闭slow_query_log_file日志文件路径long_query_time阈值秒log_queries_not_using_indexes记录未使用索引的查询log_throttle_queries_not_using_indexes限制每分钟记录的未使用索引查询数量关键提示生产环境建议将long_query_time设置为1-3秒电商等高并发场景可能需要0.5秒。但要注意设置过低会导致日志量暴增。2. 慢查询日志的配置与启用实战2.1 动态参数设置与持久化最快的方式是通过SET GLOBAL命令立即生效SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;但这种方式重启后会失效。我建议同时修改配置文件my.cnf或my.ini[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 log_throttle_queries_not_using_indexes 10配置后需要重启MySQL服务或执行FLUSH LOGS命令。有个容易忽略的细节确保日志目录的权限设置正确chown mysql:mysql /var/log/mysql/ chmod 755 /var/log/mysql/2.2 日志轮转与维护策略慢查询日志会持续增长需要定期维护。我推荐以下方案使用Linux的logrotate工具创建/etc/logrotate.d/mysql-slow配置文件/var/log/mysql/mysql-slow.log { daily rotate 30 missingok compress delaycompress notifempty create 640 mysql mysql postrotate /usr/bin/mysqladmin flush-logs endscript }对于大型系统考虑将日志写入单独的分区避免占满系统空间高负载环境下可以启用log_throttle_queries_not_using_indexes防止日志爆炸3. 慢查询日志的分析方法与工具链3.1 原生分析工具mysqldumpslowMySQL自带的mysqldumpslow工具能快速统计慢查询模式mysqldumpslow -s t /var/log/mysql/mysql-slow.log常用参数组合-s t按总时间排序-s l按锁定时间排序-s at按平均时间排序-t 10只显示前10条输出示例Count: 5 Time12.34s (61s) Lock0.00s (0s) Rows1000.0 (5000), user1[user1][10.0.0.1] SELECT * FROM orders WHERE create_time N这个输出告诉我们这个查询执行了5次平均耗时12.34秒每次返回1000行。其中的N表示这是个变化的参数值。3.2 可视化分析工具pt-query-digestPercona Toolkit中的pt-query-digest提供了更强大的分析能力pt-query-digest /var/log/mysql/mysql-slow.log --output slow_report.txt它生成的报告包含总体统计总查询量、唯一查询模式、时间分布查询排名按影响排序执行时间×次数每个查询的详细分析执行计划EXPLAIN时间分布直方图表扫描统计我特别推荐使用--filter参数聚焦关键问题pt-query-digest --filter $event-{arg} ~ /WHERE.*id\d/ slow.log3.3 实时监控与报警方案对于关键业务系统建议建立实时监控使用Prometheus mysqld_exporter采集slow_queries指标配置Grafana仪表盘监控慢查询趋势设置Alertmanager规则当慢查询突增时触发报警示例PromQL查询rate(mysql_global_status_slow_queries[5m]) 54. 慢查询SQL的优化实战指南4.1 索引缺失型慢查询典型特征执行计划显示ALL类型扫描rows_examined远大于rows_sent日志中显示Using where; Using filesort优化案例-- 原查询耗时3.8秒 SELECT user_name FROM orders WHERE create_time 2023-01-01; -- 优化方案 ALTER TABLE orders ADD INDEX idx_create_time (create_time);注意点避免过度索引每个索引会增加写操作开销多列索引要遵循最左前缀原则使用EXPLAIN FORMATJSON获取更详细的执行计划4.2 复杂连接查询优化典型问题场景SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status pending ORDER BY o.create_time DESC LIMIT 100;优化策略确保连接字段有索引o.user_id和u.id对于排序字段添加索引o.create_time考虑使用覆盖索引ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);4.3 分页查询深度优化深度分页是常见性能杀手-- 低效写法偏移量越大越慢 SELECT * FROM products ORDER BY id LIMIT 100000, 20; -- 优化方案1基于游标的分页 SELECT * FROM products WHERE id 100000 ORDER BY id LIMIT 20; -- 优化方案2延迟关联 SELECT p.* FROM products p JOIN (SELECT id FROM products ORDER BY id LIMIT 100000, 20) AS tmp ON p.id tmp.id;4.4 临时表与文件排序处理当看到Using temporary; Using filesort警告时检查GROUP BY和ORDER BY子句是否使用相同字段增大sort_buffer_size参数建议2-4MB考虑使用SQL_BIG_RESULT提示5. 生产环境慢查询治理体系5.1 分级处理机制根据影响程度建立四级响应紧急单次10s立即处理可能kill查询严重平均3s当天优化一般平均1s本周优化观察偶尔阈值记录跟踪5.2 预防性检查清单每次上线前检查[ ] 所有WHERE条件字段是否有索引[ ] 分页查询是否使用游标方式[ ] JOIN操作是否使用小表驱动大表[ ] 事务是否保持简短[ ] 是否避免使用SELECT *5.3 性能测试验证方案优化后必须验证sysbench oltp_read_write \ --db-drivermysql \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-usertest \ --mysql-passwordtest \ --mysql-dbsbtest \ --tables10 \ --table-size100000 \ --threads32 \ --time300 \ --report-interval10 \ run监控指标95%延迟QPS变化错误率5.4 长期监控策略推荐部署Percona PMM全量性能监控VividCortexSQL指纹分析自定义脚本定期分析慢日志并生成报告我在实际运维中总结出一个黄金法则慢查询治理不是一次性任务而是需要持续优化的过程。建议每周固定时间分析慢日志建立性能基线当指标偏离基线超过20%时立即调查原因。