如何打开慢查询日志？long_query_time、log_queries_not_using_indexes 的作用？

考察要点

这道题面试官想看你是否掌握 MySQL 性能优化的基础工具——慢查询日志。它不仅考察你是否知道如何开启和配置，更深层次是考察你对性能问题定位的系统性方法论，以及在生产环境中权衡功能开关带来的性能开销的工程意识。

核心概念

慢查询日志（Slow Query Log）是 MySQL 提供的一种日志记录，用于记录执行时间超过指定阈值的 SQL 语句。它是 DBA 和开发人员定位性能瓶颈、优化 SQL 的首要工具。

1. 核心参数与作用

2. 如何开启和配置

有两种方式：动态配置（服务器运行时修改）和静态配置（修改配置文件）。

动态配置（临时生效，重启后失效）

适合临时性的诊断和调试。

 1-- 1. 查看当前配置
 2SHOW VARIABLES LIKE '%slow%';
 3SHOW VARIABLES LIKE 'long_query_time';
 4
 5-- 2. 开启慢查询日志
 6SET GLOBAL slow_query_log = 'ON';
 7
 8-- 3. 设置慢查询时间阈值为 0.5 秒
 9SET GLOBAL long_query_time = 0.5;
10
11-- 4. 开启“未走索引”的查询记录
12SET GLOBAL log_queries_not_using_indexes = 'ON';
13
14-- 5. 修改日志输出到 TABLE (可选)
15SET GLOBAL log_output = 'TABLE,FILE'; -- 可以同时输出到两种格式

注意：SET GLOBAL 只对新建立的连接生效。对于当前会话，可以使用 SET SESSION long_query_time = 0.5;。

静态配置（永久生效）

修改 MySQL 配置文件（Linux 通常是 /etc/my.cnf，Windows 是 my.ini），在 [mysqld] 节下添加或修改配置。

 1[mysqld]
 2# 开启慢查询日志
 3slow_query_log = 1
 4
 5# 慢查询日志文件路径
 6slow_query_log_file = /var/lib/mysql/mysql-slow.log
 7
 8# 慢查询时间阈值（秒）
 9long_query_time = 0.5
10
11# 记录没有使用索引的查询
12log_queries_not_using_indexes = 1
13
14# 检查行数超过1000才记录未使用索引的查询
15min_examined_row_limit = 1000

修改后需要重启 MySQL 服务才能生效。

常见低分回答

1. 只知道 long_query_time：回答时只提 long_query_time，不知道 log_queries_not_using_indexes，说明对慢查询的理解只停留在“慢”，而没有意识到“不高效”也是关键问题。
2. long_query_time 设置不合理：当被问到生产环境设为多少时，回答“默认的 10 秒就行”。这在任何一个有一定访问量的线上系统都是完全不可接受的，10 秒的查询足以拖垮整个应用。
3. 滥用 log_queries_not_using_indexes：不假思索地建议在所有生产环境永久开启 log_queries_not_using_indexes。这会产生大量日志，在高并发下对磁盘 I/O 造成显著压力，属于没有考虑性能开销的回答。
4. 混淆 GLOBAL 和 SESSION：在 SET 配置时，不清楚 GLOBAL 和 SESSION 的作用域区别，误以为 SET SESSION 会对所有连接生效。
5. 不知道如何分析日志：只知道如何开启，但当被问到“开启后怎么用”时，回答“用 cat 或 less 去看”，而不知道使用 mysqldumpslow 或 pt-query-digest 等专业工具进行聚合分析。

工程实践与优化

1. 日志分析工具：

   • mysqldumpslow：MySQL 自带的工具，功能简单，主要用于对慢查询日志进行分类、汇总。
   • pt-query-digest：Percona Toolkit 中的王牌工具，强烈推荐。它能对日志进行“指纹”分析（将 id=1 和 id=2 的同类 SQL 归为一类），并按总耗时、平均耗时、出现频率等多个维度进行排序输出，生成详尽的分析报告。这是定位核心慢 SQL 的不二之选。
   bash

   ⧉⛶

    1# 示例：分析最近一小时的慢查询，并输出报告
    2pt-query-digest /var/lib/mysql/mysql-slow.log > slow_query_report.txt

2. 性能开销与权衡：

   • 开启慢查询日志（尤其是 log_output=FILE）对性能的影响通常在 1%-5% 之间，对于绝大多数系统是可以接受的。
   • log_queries_not_using_indexes 开启时，如果系统中存在大量全表扫描操作，会导致日志文件迅速膨胀，I/O 压力增大。建议在性能诊断期间短期开启，或配合 min_examined_row_limit 使用。
   • log_output=TABLE 的写入开销比 FILE 大，因为它涉及到 InnoDB 的事务、锁、MVCC 等机制。在高并发写入场景下，mysql.slow_log 表本身可能成为瓶颈。

3. 日志轮转（Log Rotation）： 生产环境必须配置日志轮转，防止单个日志文件无限增长撑爆磁盘。可以通过 logrotate 工具配置，或者手动执行 mv 重命名旧日志文件后，执行 FLUSH SLOW LOGS; 或 mysqladmin flush-logs 来让 MySQL 创建新的日志文件。

4. 集中式日志平台： 在大规模分布式系统中，通常会将慢查询日志通过 filebeat 等工具采集，发送到 ELK (Elasticsearch, Logstash, Kibana) 或 Loki 等集中式日志平台，进行统一的存储、查询、监控和告警。

常见追问与延伸题目

1. 追问：你找到了一个慢查询，接下来你的优化步骤是什么？

   • 回答要点 1 (分析)：使用 EXPLAIN 或 EXPLAIN ANALYZE (MySQL 8.0+) 查看其执行计划。重点关注 type (是否为 ALL 全表扫描), key (是否用上了正确的索引), rows (预估扫描行数), Extra (是否出现 Using filesort, Using temporary)。
   • 回答要点 2 (诊断)：结合业务场景，分析 SQL 逻辑。是否存在多余的 JOIN？WHERE 条件是否可索引？GROUP BY 或 ORDER BY 的字段是否能通过索引来优化？
   • 回答要点 3 (优化)：根据分析结果进行优化。可能是创建或修改索引（如添加联合索引，调整索引顺序），也可能是改写 SQL（如拆分复杂查询、避免在索引列上使用函数），甚至是调整表结构或业务逻辑。
   • 回答要点 4 (验证)：在测试环境验证优化效果，再次 EXPLAIN 确认执行计划符合预期，并进行压测，确保性能提升且结果正确。

2. 追问：如果 log_queries_not_using_indexes 产生的日志太多，有没有更精细化的方法找到那些没有走索引的查询？

   • 回答要点 1 (Performance Schema)：使用 Performance Schema。这是比慢查询日志更现代、更底层的性能监控工具。可以查询 events_statements_summary_by_digest 表，通过 SUM_NO_INDEX_USED 和 SUM_NO_GOOD_INDEX_USED 字段来统计和排序那些全表扫描或未使用最佳索引的 SQL 类型。这种方式开销更可控，数据更结构化。
   • 回答要点 2 (第三方监控)：利用 Prometheus + mysqld_exporter 监控体系。mysqld_exporter 会从 Performance Schema 中采集相关指标，可以通过 Grafana 仪表盘直观地监控 Select_scan (全表扫描次数) 等关键指标的增长率，并设置告警。

3. 追问：日志输出到 FILE 和 TABLE 各有什么优缺点？你在什么场景下会选择哪一种？

   • 回答要点 1 (FILE)：
     • 优点：性能开销低，对数据库本身影响小；是业界标准工具（如 pt-query-digest）的默认输入源；日志独立于数据库实例，即使数据库崩溃，日志文件通常依然存在。
     • 缺点：需要服务器文件系统访问权限才能查看和分析；非结构化文本，不方便直接用 SQL 查询。
     • 场景：首选于所有生产环境，特别是高并发、高负载的系统。
   • 回答要点 2 (TABLE)：
     • 优点：可以直接用 SQL 查询分析，非常方便；可以远程查询，无需登录服务器；便于和其他数据库表做关联分析。
     • 缺点：对数据库有写入开销，mysql.slow_log 表本身可能成为性能瓶颈；如果对 slow_log 表的查询本身很慢，可能会造成循环记录；表大小需要管理，否则会占用过多存储空间。
     • 场景：适用于开发环境、测试环境或负载非常低的内部系统，此时便利性优先于极致性能。