大家都知道，slow query系统做的好不好，直接决定了解决slow query的效率问题。

一个数据库管理平台，拥有一个好的slow query系统，基本上就拥有了解锁性能问题的钥匙。

但是今天主要分享的并不是平台，而是在平台中看到的奇葩指数五颗星的slow issue。

好了，关子卖完了，直接进入正题：

一、症状

一堆如下慢查询

#User@Host:cra[cra]@[xx]Id:3352884621
#Query_time:0.183673Lock_time:0.000000Rows_sent:0Rows_examined:0
usexx_db;
SETtimestamp=1549900927;
#administratorcommand:Prepare;
#Time:2019-02-12T00:02:07.516803+08:00
#User@Host:cra[cra]@[xx]Id:3351119968
#Query_time:0.294081Lock_time:0.000000Rows_sent:0Rows_examined:0
SETtimestamp=1549900927;
#administratorcommand:Prepare;

从我们的监控图上可以看到，每天不定时间段的slow query 总数在攀升，但是却看不到任何query 语句。

这是我接触到的slow query优化案例中从来没有过的情况，比较好奇，也比较兴奋，至此决心要好好看看这个问题。

二、排查

要解决这个问题，首先想到的是，如何复现这个问题，如何模拟复现这个症状。

• MySQL客户端 模拟prepare

*模拟
root:xx>preparestmt1from'select*fromxx_operation_logwhereid=?';
QueryOK,0rowsaffected(0.00sec)
Statementprepared
*结果
#Time:2019-02-14T14:14:50.937462+08:00
#User@Host:root[root]@localhost[]Id:369
#Query_time:0.000105Lock_time:0.000000Rows_sent:0Rows_examined:0
SETtimestamp=1550124890;
preparestmt1from'select*fromxx_operation_logwhereid=?';

结论是： MySQL client 模拟出来的prepare 并不是我们期待的，并没有得到我们想要的 administrator command: Prepare。

• perl 模拟prepare

#!/usr/bin/perl
useDBI;
my$dsn="dbi:mysql:database=${db_name};hostname=${db_host};port=${db_port}";#数据源
#获取数据库句柄
my$dbh=DBI->connect("DBI:mysql:database=xx;host=xx","xx","xx",{'RaiseError'=>1});
my$sql=qq{select*fromxx_operation_logwhereidin(?)};
my$sth=$dbh->prepare($sql);
$sth->bind_param(1,'100');
sleep3;
$sth->execute();

结论是：跟MySQL客户端一样，同样是看不到administrator command: Prepare。

• php 模拟prepare

php模拟得到的slow 结果：

[root@xx20190211]#catxx-slow.log|grep'administratorcommand:Prepare'-B4|grep'User@Host'|grep'xx_rx'|wc-l
7891
[root@xx20190211]#catxx-slow.log|grep'administratorcommand:Prepare'-B4|grep'User@Host'|wc-l
7908

结论： 通过php代码，我们成功模拟出了想要的结果。

那顺藤摸瓜，抓取下这段时间有相同session id的整个sql执行过程。

MySQL开启slow=0的抓包模式

可以定位到同一个sessionid（3415357118）的prepare+execute+closestmt
#User@Host:xx_rx[xx_rx]@[xx.xxx.xxx.132]Id:3415357118
#Query_time:0.401453Lock_time:0.000000Rows_sent:0Rows_examined:0
usexx_db;
SETtimestamp=1550017125;
#administratorcommand:Prepare;
#Time:2019-02-13T08:18:45.624671+08:00
--
#User@Host:xx_rx[xx_rx]@[xx.xxx.xxx.132]Id:3415357118
#Query_time:0.001650Lock_time:0.000102Rows_sent:0Rows_examined:1
usexx_db;
SETtimestamp=1550017125;
update`xx`set`updated_at`='2019-02-1308:18:45',`has_sales_office_phone`=1,`has_presale_permit`=1where`id`=28886;
#Time:2019-02-13T08:18:45.626138+08:00
--
#User@Host:xx_rx[xx_rx]@[xx.xxx.xxx.132]Id:3415357118
#Query_time:0.000029Lock_time:0.000000Rows_sent:0Rows_examined:1
usexx_db;
SETtimestamp=1550017125;
#administratorcommand:Closestmt;
#Time:2019-02-13T08:18:45.626430+08:00

结论：我们发现，prepare时间的确很长，但是sql语句却执行的很快，这就很尴尬了。

本来是想通过抓包，看看是否能够验证我们的猜想： prepare的语句非常大，或者条件非常复杂，从而导致prepare在服务器端很慢。

结果发现query语句也都非常简单。

那么既然如此，我们就找了业务方，将对应业务的prepare方法一起看看。

结果发现，业务使用的是php-pdo的方式，所以我们就又有了如下发现：

php-pdo 两种prepare模式

• http://php.net/manual/zh/pdo.prepare.php

1. 本地prepare $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);

不会发送给MySQL Server

2. 服务器端prepare $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

发送给MySQL Server

验证两种prepare模式

• 服务端prepare模式( ATTR_EMULATE_PREPARES = false)

<?php
$dbms='mysql';//数据库类型
$host='xxx';//数据库主机名
$dbName='test';//使用的数据库
$user='xx';//数据库连接用户名
$pass='123456';//对应的密码
$dsn="$dbms:host=$host;dbname=$dbName";
try{
$pdo=newPDO($dsn,$user,$pass);//初始化一个PDO对象
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
echo"-----preparebegin-----
";
$stmt=$pdo->prepare("select*fromtest.chanpinwhereid=?");
echo"-----prepareafter-----
";
$stmt->execute([333333]);
echo"-----executeafter-----
";
$rs=$stmt->fetchAll();
}catch(PDOException$e){
die("Error!:".$e->getMessage()."<br/>");
}

strace -s200 -f php mysql1.php 跟踪

大家可以看到这个模式下，prepare的时候，是将query+占位符 发送给服务端的：

• 本地prepare模式 (ATTR_EMULATE_PREPARES = true )

<?php
$dbms='mysql';//数据库类型
$host='xx';//数据库主机名
$dbName='test';//使用的数据库
$user='xx';//数据库连接用户名
$pass='123456';//对应的密码
$dsn="$dbms:host=$host;dbname=$dbName";
try{
$pdo=newPDO($dsn,$user,$pass);//初始化一个PDO对象
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);
echo"-----preparebegin-----
";
$stmt=$pdo->prepare("select*fromtest.chanpinwhereid=?");
echo"-----prepareafter-----
";
$stmt->execute([333333]);
echo"-----executeafter-----
";
$rs=$stmt->fetchAll();
}catch(PDOException$e){
die("Error!:".$e->getMessage()."<br/>");
}

strace -s200 -f php mysql1.php 跟踪

大家可以看到这个模式下，prepare的时候，是不会将query发送给服务端的，只有execute的时候才会发送。

跟业务方确认后，他们使用的是后者，也就是修改了默认值，他们原本是想提升数据库的性能，因为预处理后只需要传参数就好了。

但是对于我们的业务场景并不适合，我们的场景是频繁打开关闭连接，也就是预处理基本就用不到。

另外文档上面也明确指出prepared statements 性能会不好。

调整和验证

如何验证业务方是否将prepare修改为local了呢？

dba:(none)>showglobalstatuslike'Com_stmt_prepare';
+------------------+-----------+
|Variable_name|Value|
+------------------+-----------+
|Com_stmt_prepare|716836596|
+------------------+-----------+
1rowinset(0.00sec)

通过观察，发现这个值没有变化，说明调整已经生效。

总结

• prepare的优点

1.防止SQL注入
2.特定场景下提升性能
什么是特定场景：就是先去服务端用占位符占位，后面可以直接发送请求来填空（参数值）
这样理论上来说，你填空的次数非常多，性能才能发挥出来

• prepare的缺点

1.在服务器端的prepare毕竟有消耗，当并发量大，频繁prepare的时候，就会有性能问题
2.服务端的prepare模式还会带来的另外一个问题就是，排错和slow优化有困难，因为大部分情况下是看不到真实query的
3.尽量设置php-pdo为$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true)，在本地prepare，不要给服务器造成额外压力

• 建议

1.默认情况下，应该使用php-pdo的默认配置，采用本地prepare的方式，这样可以做到防SQL注入的效果，性能差不到哪里去
2.除非真的是有上述说的特定场景，可以考虑配置成服务器prepare模式，前提是要做好测试