Percona-Toolkit系列之pt-fingerprint查询语句转换利器

1. pt-fingerprint

相信接触过oracle的朋友一定听说过绑定变量，硬解析，软解析，软软解析等等概念，绑定变量有什么用呢？很显然就是为了减少数据库解析次数，将一类SQL语句绑定变量，那么该类sql重复执行，就可以直接沿用以前的执行计划，不用多次解析SQL生成执行计划。该方式大大缓解CPU负载，在高并发场景下大有用处。MySQL中也有绑定变量，绑定变量的SQL，使用问号标记可以接收参数的位置当真正需要只需的时候，使用具体值替换这些问号。 pt-fingerprint就是这么个工具将一类SQL语句转换成绑定变量的模式，比如

SELECT name, password FROM user WHERE id=’12823’;
select name, password from user
where id=5;

都应该转换为

select name, password from user where id=?

2.使用

假如我们将一下文件中的所有SQL都计算一下

[mysql@hpc02 ~]$ cat file.txt 
SELECT name, password FROM user WHERE id=’12823’;
select name, password from user where id=5;
select name, password from user where id=6;
select name, password from user where id=7;
select name, password from user where id=8;
select name, password from user where id=9;
select name, password from user where id=10;
select name, password from user where id=11;
select name, password from user where id=12;
select name, password from user where id=13;
select name, password from user where id=11;
[mysql@hpc02 ~]$ pt-fingerprint --match-embedded-numbers file.txt |sort|uniq
select name, password from user where id=?
select name, password from user where id=’?’