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=’?’

 

Write a Reply or Comment

电子邮件地址不会被公开。 必填项已用*标注