MySQL慢查询日志优化与索引参数调整详解

MySQL慢查询日志优化：索引优化与参数调整实践指南

开启慢查询日志的正确方式：避免磁盘和性能被拖垮

新手DBA常犯的错误是直接开启 log_queries_not_using_indexes = ON。这个操作看似简单，实则风险极高：它会让每一条未走索引的SQL——哪怕只扫描一行、耗时零点几毫秒——都被完整记录。结果是慢查询日志可能在几小时内迅速膨胀，直至填满磁盘空间。在生产环境中，该参数必须与 min_examined_row_limit 配合使用，设置合理的扫描行数门槛。

临时排查问题时，建议采用以下组合配置：

• SET GLOBAL slow_query_log = 'ON'
• SET GLOBAL long_query_time = 1（核心业务可设为 0.5，以捕捉更细微的慢查询）
• SET GLOBAL min_examined_row_limit = 1000（仅记录扫描行数超过1000的语句，有效过滤噪音）
• SET GLOBAL log_queries_not_using_indexes = 'OFF'（关键步骤：先不开启此参数）

如需永久生效，请将配置写入 /etc/my.cnf 文件的 [mysqld] 配置段，然后重启服务。注意：修改后务必执行 SHOW VARIABLES LIKE '%slow%' 确认新值已成功加载。MySQL 8.0及以上版本中，变量命名风格可能使用下划线，例如 slow_query_log，而非旧版的 slow-query-log。

使用EXPLAIN快速定位慢查询根源：只需关注这三列

慢查询日志中的 Query_time 和 Rows_examined 只能提示“查询慢了”，却无法解释“为什么慢”。真正的诊断工具是 EXPLAIN 命令。面对输出结果，不必被所有字段吓倒，集中关注以下三列即可抓住关键：

• type：访问类型。若显示 ALL，则意味着全表扫描，索引大概率未生效。理想状态应为 range（范围扫描）或 ref（等值查询）。
• key：实际使用的索引名称。若此列为 NULL，表明MySQL优化器未选择任何索引。可能原因包括字段类型不匹配、查询条件中对字段使用函数、或未满足联合索引的“最左前缀”原则。
• rows：优化器预估的扫描行数。若此数字远大于实际返回行数（例如 SELECT COUNT(*) 返回1行，但 rows 显示500000），则需高度警惕。这通常指向索引失效或表的统计信息过时，误导了优化器。

进阶用法：使用 EXPLAIN FORMAT=JSON 可查看 filtered 字段，它表示经过条件过滤后剩余行数的预估百分比。若该值低于10%，则是一个明确的警告信号——当前索引的区分度太低，或WHERE条件的选择性太差，需要重新审视索引设计。

联合索引的正确构建方法：避免白建

给表添加联合索引时，不能简单地将字段堆砌在一起。顺序错误会导致索引失效，查询依然缓慢。

举例说明：假设有常见查询 WHERE status = 'paid' AND create_time > '2023-01-01' ORDER BY amount DESC。索引该如何构建？

• 错误示范：INDEX(create_time, status)。因为 create_time 是范围查询（>），根据最左前缀原则，其后的 status 字段无法用于索引过滤，实际失效。
• 正确做法：INDEX(status, create_time)。将等值查询字段 status 放在前面，先精准定位‘paid’状态的行，再对 create_time 进行范围扫描，完全符合最左前缀匹配规则。
• 更优方案：若查询只需返回 id, status, amount 等字段，可考虑 INDEX(status, create_time, amount)。这就是“覆盖索引”的妙用——所有需要的数据都在索引树中，无需回表查询，性能提升立竿见影。

最后需牢记两个原则：一是避免为使用 LIKE '%xxx' 这种前导通配符的字段单独建立索引，它无法生效；二是不要在WHERE条件中对索引字段使用函数，例如 WHERE DATE(create_time) = '2023-01-01'，这会让索引瞬间失效，强制全表扫描。

哪些参数调整反而让慢查询更难定位

许多管理员热衷于调整 innodb_buffer_pool_size 或调低 long_query_time，却忽略了一些干扰诊断过程的参数设置。

• log_output = TABLE：该设置将慢查询直接记录在 mysql.slow_log 系统表中，方便查询。但在高并发场景下，向此表写入日志会产生锁和I/O开销，可能拖慢正常业务查询。相比之下，使用文件模式（log_output = FILE）通常更轻量安全。
• long_query_time = 0：在测试环境用于捕捉所有查询无可厚非。但上线后若忘记调整回来，海量日志会迅速填满磁盘，甚至导致 mysqldumpslow 等日志分析工具因文件过大而卡死。
• slow_query_log_file 路径：若将慢查询日志文件放在系统盘，或与数据库数据目录（datadir）置于同一块物理磁盘，会产生激烈的I/O竞争。这可能导致慢查询记录延迟，甚至在极端情况下丢失最后几条关键日志。

另一个易被忽略的参数是 log_slow_admin_statements，它默认关闭。因此，像 ALTER TABLE、ANALYZE TABLE 这类管理语句即使执行很慢，也不会出现在常规慢查询日志中。然而，这类语句虽执行频率不高，但一旦变慢，会影响整个表的可用性。若在日志中始终找不到这类“元凶”，请检查并开启此开关。