MySQL高级讲座篇之：如何利用`Percona Toolkit`对`pt-query-digest`进行二次开发？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

大家好，今天咱们来聊聊MySQL优化的利器——pt-query-digest，以及如何用Percona Toolkit对它进行二次开发，让它更贴合你的业务需求。别害怕，这听起来很高大上，其实没那么难，我会用最接地气的方式，带你玩转这个工具。

开场白：pt-query-digest是个啥？

想象一下，你的MySQL服务器像一个繁忙的餐厅，每天接待成千上万的顾客（SQL请求）。时间长了，有些顾客（SQL）特别能吃（消耗资源），把餐厅搞得鸡飞狗跳。这时候，你就需要一个像大堂经理一样的工具，帮你找出这些“大胃王”SQL，然后想办法让他们吃得更优雅，或者干脆换一批更斯文的客人。

pt-query-digest就是这个大堂经理，它可以分析你的MySQL慢查询日志，告诉你哪些SQL语句执行时间最长，执行次数最多，消耗的资源最多等等。这样，你就可以有的放矢地进行优化，提升数据库性能。

第一幕：认识pt-query-digest的骨架

pt-query-digest的本质是一个Perl脚本，它读取慢查询日志、general log或者TCP dump文件，然后对SQL语句进行分析和聚合。它的核心功能可以大致分为以下几个部分：

日志读取器（Log Reader）： 负责从各种来源读取SQL语句，例如慢查询日志文件、general log文件，甚至直接从MySQL服务器读取。
SQL解析器（SQL Parser）： 将SQL语句解析成可处理的数据结构，例如提取表名、字段名、查询类型等等。
数据聚合器（Data Aggregator）： 根据一定的规则，将相似的SQL语句聚合在一起，计算它们的总执行时间、平均执行时间、执行次数等等。
报告生成器（Report Generator）： 将聚合后的数据生成报告，例如按照执行时间排序、按照执行次数排序等等。

第二幕：pt-query-digest的默认报告解读

在深入二次开发之前，先来看看pt-query-digest默认生成的报告长什么样，以及它都包含了哪些关键信息：

# pt-query-digest slow.log > report.txt

生成的report.txt文件中，你会看到类似这样的信息：

# Overall: 16 total, 2 unique, avg 0.00s, 0.01 QPS, 0.02 concurrency
# Time range: 2023-10-27 10:00:00 to 2023-10-27 10:00:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           0.01s   0.00s   0.01s   0.00s   0.01s   0.00s   0.00s
# Lock time           0.00s   0.00s   0.00s   0.00s   0.00s   0.00s   0.00s
# Rows sent               2       1       1       1       1       0.00       1
# Rows examined         346     173     173     173     173       0.00     173
# Query size           162     81     81     81     81       0.00      81

# Profile
# Rank Query ID           Response time   Calls R/Call V/M   Ite    QPS   Lock   Rows   Rows   DB
# ==== ================== ============== ===== ====== ===== ======= ====== ====== ====== ====== ===
#    1 0xAAAAAAAAAAAAAAAA 0.0002 100.0%     1 0.0002  0.0%  0.00   0.10 0.0000      1    173 test
#    2 0xBBBBBBBBBBBBBBBB 0.0000   0.0%     1 0.0000  0.0%  0.00   0.00 0.0000      1    173 test

# Query 1: 0.01 QPS(1.00/sec) 0.00 sec
# This query is executed 1 times, query ID is 0xAAAAAAAAAAAAAAAA
# Scores: V/M = 0.00
#   0.00 sec lock time
#   1 rows sent
#   173 rows examined
# EXPLAIN:
# Id  Select Type Table Type Possible Keys Key Key_len Ref Rows Extra
# 1 SIMPLE user ALL NULL NULL NULL NULL 173 Using where

# Query: SELECT * FROM `user` WHERE id=1;

# Query 2: 0.00 QPS(0.00/sec) 0.00 sec
# This query is executed 1 times, query ID is 0xBBBBBBBBBBBBBBBB
# Scores: V/M = 0.00
#   0.00 sec lock time
#   1 rows sent
#   173 rows examined
# EXPLAIN:
# Id  Select Type Table Type Possible Keys Key Key_len Ref Rows Extra
# 1 SIMPLE user ALL NULL NULL NULL NULL 173 Using where

# Query: SELECT * FROM `user` WHERE id=2;

简单解释一下：

Overall: 整个慢查询日志的统计信息，包括总的SQL语句数量、唯一SQL语句数量、平均执行时间、每秒查询次数（QPS）等等。
Profile: 按照执行时间排序的SQL语句列表，包括执行时间、执行次数、平均执行时间、QPS等等。
Query X: 每条SQL语句的详细信息，包括执行次数、执行时间、锁时间、发送的行数、检查的行数、EXPLAIN结果以及原始SQL语句。

第三幕：开始动刀子——二次开发的思路

pt-query-digest的强大之处在于它的可定制性。你可以通过修改它的源码，或者编写插件的方式，来扩展它的功能。常见的二次开发需求包括：

自定义报告格式： 修改报告的输出格式，例如添加自定义的统计指标、修改报告的排序方式等等。
自定义SQL解析规则： 扩展SQL解析器，支持更复杂的SQL语法，或者提取更多的SQL信息。
自定义数据聚合规则： 修改数据聚合器，按照不同的规则对SQL语句进行聚合，例如按照业务类型聚合、按照用户ID聚合等等。
自定义报警规则： 当某些SQL语句的执行时间超过阈值时，发送报警通知。

第四幕：操刀实战——修改报告格式

咱们先从最简单的开始，修改报告的输出格式。假设你希望在报告中添加一个“平均每行检查的行数”指标。

找到相关的代码： 首先，你需要找到pt-query-digest中生成报告的代码。这部分代码通常位于pt-query-digest脚本的print_query()或者类似的函数中。你可以使用grep命令来搜索相关的关键词，例如“Rows examined”、“Rows sent”等等。
```
grep "Rows examined" pt-query-digest
```

修改代码： 找到相关的代码后，就可以开始修改了。你需要计算“平均每行检查的行数”，并将其添加到报告中。

# 假设你已经获取了Rows sent和Rows examined的值
my $rows_sent = $query->{rows_sent};
my $rows_examined = $query->{rows_examined};

# 计算平均每行检查的行数
my $avg_rows_examined_per_row_sent = ($rows_sent > 0) ? ($rows_examined / $rows_sent) : 0;

# 将其添加到报告中
print "  $avg_rows_examined_per_row_sent avg rows examined per row sentn";

测试修改： 修改完成后，保存脚本，然后重新运行pt-query-digest，查看报告中是否包含了你添加的指标。

第五幕：进阶操作——自定义SQL解析规则

如果默认的SQL解析器无法满足你的需求，你可以自定义SQL解析规则。例如，你希望提取SQL语句中的业务类型信息，并将其添加到报告中。

了解SQL解析器的原理： pt-query-digest使用SQL::Statement模块来解析SQL语句。你需要了解这个模块的基本用法，以及如何提取SQL信息。

编写SQL解析代码： 根据你的需求，编写SQL解析代码。例如，你可以使用正则表达式来匹配SQL语句中的业务类型信息。

use SQL::Statement;

my $sql = "SELECT * FROM `order` WHERE order_id = 123 AND biz_type = 'A'";
my $sth = SQL::Statement->new( $sql );
my $parsed = $sth->parse;

if ($parsed) {
    # 假设业务类型信息位于WHERE子句中
    my $where_clause = $sth->where_clause;

    # 使用正则表达式提取业务类型信息
    if ($where_clause =~ /biz_type = '(w+)'/) {
        my $biz_type = $1;
        print "Business type: $biz_typen";
    }
} else {
    print "Failed to parse SQL statementn";
}

将代码集成到pt-query-digest中： 将你编写的SQL解析代码集成到pt-query-digest中。你需要找到pt-query-digest中解析SQL语句的代码，并将你的代码添加到其中。

第六幕：高阶技巧——自定义数据聚合规则

默认情况下，pt-query-digest会根据SQL语句的相似性进行聚合。如果你希望按照不同的规则进行聚合，例如按照业务类型聚合，你可以自定义数据聚合规则。

修改数据聚合器的代码： 找到pt-query-digest中数据聚合器的代码，然后修改它，使其按照你的规则进行聚合。

# 假设你已经提取了SQL语句中的业务类型信息
my $biz_type = ...;

# 使用业务类型作为聚合的key
my $key = $biz_type;

# 将SQL语句添加到对应的聚合组中
if (exists $query_groups{$key}) {
    # 已经存在对应的聚合组
    ...
} else {
    # 创建新的聚合组
    ...
}

修改报告生成器的代码： 修改报告生成器的代码，使其能够根据你的聚合规则生成报告。

第七幕：终极武器——编写插件

如果你的二次开发需求比较复杂，或者你希望将你的代码与其他pt-query-digest用户共享，你可以编写插件。

了解pt-query-digest的插件机制： pt-query-digest支持插件机制，你可以通过编写Perl模块的方式来扩展它的功能。
编写插件代码： 编写你的插件代码。插件代码需要实现一些特定的函数，例如plugin_init()、plugin_parse_query()、plugin_generate_report()等等。
安装插件： 将你的插件安装到pt-query-digest的插件目录中。
配置pt-query-digest： 配置pt-query-digest，使其能够加载你的插件。

一些实用技巧和注意事项

备份： 在修改pt-query-digest的源码之前，一定要先备份，以防万一。
版本控制： 使用版本控制系统（例如Git）来管理你的代码，方便回滚和协作。
测试： 在修改pt-query-digest的源码之后，一定要进行充分的测试，确保你的修改不会导致任何问题。
注释： 在你的代码中添加详细的注释，方便自己和其他人理解你的代码。
Perl基础： 二次开发pt-query-digest需要一定的Perl基础，建议先学习一些Perl的基本语法和常用模块。
善用Perl debugger： Perl debugger可以帮助你调试你的代码，找出错误。
阅读pt-query-digest的源码： 阅读pt-query-digest的源码可以帮助你更好地理解它的工作原理，从而更好地进行二次开发。

实例：添加自定义统计指标

假设我们需要统计每个SQL语句中包含的JOIN操作的数量。

修改SQL解析器： 找到解析SQL语句的代码部分，添加如下代码：

use SQL::Statement;

sub count_joins {
    my ($sql) = @_;
    my $count = 0;
    $count++ while $sql =~ /bJOINb/gi;
    return $count;
}

# 在解析SQL的地方，例如parse_query()函数中
my $sth = SQL::Statement->new( $query->{query} );
my $parsed = $sth->parse;

if ($parsed) {
    $query->{num_joins} = count_joins($query->{query});
} else {
    $query->{num_joins} = 0;
}

修改报告生成器： 找到生成报告的代码部分，添加如下代码：

# 在print_query()函数中，或者类似的函数中
print "  " . $query->{num_joins} . " JOIN operationsn";

测试： 重新运行pt-query-digest，查看报告中是否包含了“JOIN operations”指标。

表格总结：常用二次开发场景及示例代码

场景	描述	示例代码（Perl）
添加自定义统计指标	统计SQL语句中的特定关键词数量	`perl sub count_keywords { my ($sql, $keyword) = @_; my $count = 0; $count++ while $sql =~ /b$keywordb/gi; return $count; } # 假设你要统计SELECT语句中DISTINCT关键词的数量 my $num_distinct = count_keywords($query->{query}, "DISTINCT");`
自定义报告格式	修改报告的输出格式，例如添加颜色	`perl use Term::ANSIColor; # 在输出SQL语句时，添加颜色 print color("bold blue") . "Query: " . color("reset") . $query->{query} . "n";`
自定义报警规则	当SQL语句的执行时间超过阈值时，发送报警	`perl my $threshold = 1; # 阈值为1秒 if ($query->{sum_time} > $threshold) { # 发送报警邮件 my $email = "[email protected]"; my $subject = "Slow query alert"; my $body = "The following query exceeded the threshold: " . $query->{query}; system("mail -s '$subject' '$email' <<< '$body'"); }`

结束语：玩转pt-query-digest，成为MySQL优化大师

pt-query-digest是一个非常强大的工具，通过二次开发，你可以让它更好地服务于你的业务。希望今天的讲座能够帮助你入门pt-query-digest的二次开发，祝你在MySQL优化的道路上越走越远！记住，不要害怕尝试，勇敢地修改代码，你会发现一个全新的世界。有问题随时来找我，咱们一起探讨。

发表回复 取消回复

发表回复取消回复