MySQL优化之慢日志查询

MySQL慢查询日志的概念

慢查询日志是MySQL提供的一种记录执行时间超过指定阈值的SQL语句的机制。通过分析慢查询日志，可以找出执行效率低下的SQL语句，从而进行针对性的优化。

默认情况下，MySQL没有开启慢查询日志。需要手动配置参数来启用慢查询功能。

开启慢查询日志

在MySQL配置文件中（通常是my.cnf或my.ini）添加以下参数：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

• slow_query_log：1表示开启慢查询日志，0表示关闭
• slow_query_log_file：指定慢查询日志文件的存储路径
• long_query_time：设置慢查询的阈值，单位是秒
• log_queries_not_using_indexes：记录未使用索引的查询

配置完成后需要重启MySQL服务生效。

动态设置慢查询参数

也可以通过MySQL命令行动态设置这些参数：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

慢查询日志分析工具

使用mysqldumpslow工具

MySQL自带的mysqldumpslow工具可以分析慢查询日志：

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

参数说明：

• -s t：按总查询时间排序
• -t 10：只显示前10条记录

使用pt-query-digest工具

Percona Toolkit中的pt-query-digest功能更强大：

pt-query-digest /var/log/mysql/mysql-slow.log

常见慢查询优化示例

索引缺失导致的慢查询

SELECT * FROM orders WHERE customer_id = 100 AND status = 'completed';

优化方案是为customer_id和status字段添加联合索引：

ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);

大表分页查询慢

SELECT * FROM large_table LIMIT 100000, 20;

优化方案是使用延迟关联：

SELECT * FROM large_table t1
JOIN (SELECT id FROM large_table ORDER BY id LIMIT 100000, 20) t2
ON t1.id = t2.id;

复杂JOIN查询优化

SELECT o.*, c.name, p.product_name 
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.create_time > '2023-01-01';

优化方案是确保关联字段都有索引，并考虑使用覆盖索引：

ALTER TABLE orders ADD INDEX idx_customer_product_time (customer_id, product_id, create_time);
ALTER TABLE customers ADD INDEX idx_id_name (id, name);
ALTER TABLE products ADD INDEX idx_id_name (id, product_name);

使用EXPLAIN分析查询

EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND status = 'completed';

EXPLAIN结果可以帮助理解MySQL的执行计划，识别性能瓶颈。

慢查询日志轮转

为了防止慢查询日志文件过大，可以设置日志轮转：

mv /var/log/mysql/mysql-slow.log /var/log/mysql/mysql-slow.log.old
mysqladmin flush-logs

可以设置cron任务定期执行日志轮转。

慢查询监控与报警

结合监控工具如Prometheus+Grafana或Zabbix，可以设置慢查询的监控和报警机制，及时发现性能问题。

总结

MySQL慢查询日志是性能优化的重要工具。通过合理配置慢查询日志参数，定期分析日志内容，并结合EXPLAIN等工具，可以有效识别和解决数据库性能问题。优化慢查询需要综合运用索引优化、SQL重写、数据库参数调整等多种手段。