MySQL: Admin: Slow Log Analysis

31st August 2020 at 3:22pm

MySQL 慢查询日志分析

MySQL 有一项纪录耗时长的查询的功能,叫 慢查询日志。可以在 MySQL Server 上设置 slow_query_log 参数为 1 打开,最终慢查询日志会被写到 slow_query_log_file 参数所指定的文件上。

慢查询的存在往往表示业务逻辑中有不合理的 SQL 查询存在,它们很可能会对数据库的性能、吞吐量产生很大影响。因此分析并消除慢查询是很有必要的。

Pernaco Toolkit 中提供了 pt-query-digest 工具,可以用来做 MySQL 慢查询的分析。它的基础能力是:

  1. 解析慢查询日志中的 SQL 语句、耗时等信息
  2. 对每条 SQL 语句,使用生成一个 fingerprint,用于对同类 SQL 语句做聚类,比如:SELECT * FROM my_table WHERE a = 3 AND b in (3, 1, 4) 会被转为 select * from my_table where a = ? and b in(?+)
  3. 按聚类后的结果,输出一份报告,指明哪几类 SQL 语句耗时大,它们的耗时分布如何,并给出一些实际例子
  4. 报告中还可以顺带帮你对语句做 explain,更方便地分析性能瓶颈
  5. 还提供了 review 跟 history 能力,可以把出现在慢查询中不同的 fingerprint 存在其他的一个数据库表中,供开发 / DBA 做 review。可以形成一个定时审核 SQL 语句合理性的工作流。同时也有开源的 Web 项目 Anemometer,可以对 review 跟 history 所产生的数据做操作(参考)。

总的来说, pt-query-digest 很大程度上把分析慢查询日志中的脏活累活给做了,减轻了开发或者 DBA 的负担。

同时 pt-query-digest 的 review 功能还可以用来辅助 发现 SQL 注入攻击