大家都知道慢查询系统好不好直接决定了慢查询的效率。一个数据库管理平台，有了好的慢查询系统，基本上就掌握了破解性能问题的钥匙。不过我今天要分享的不是平台，而是我在平台上看到的奇葩指数5颗星的慢问题。完成。卖完了。直接进入正题1，一堆症状如下慢查询#User@Host:cra[cra]@[xx]id:3352884621#Query_time:0.183673Lock_time:0.000000Rows_sent:0Rows_examined:0usexx_db;SETtimestamp=1549900927;#administratorcommand:Prepare;#Time:2019-02-12T07+07#User:08User:08:cra[cra]@[xx]Id:3351119968#Query_time:0.294081Lock_time:0.000000Rows_sent:0Rows_examined:0SETtimestamp=1549900927;#管理员命令：准备；从我们的监控图中可以看出，慢查询总数在增加，但是我看不到任何查询语句。这是我在慢查询优化案例中从未遇到过的情况。我很好奇也很兴奋。至此，我下定决心好好看看这个问题。2.Troubleshoottosolve这个问题，首先想到的是如何重现这个问题，如何模拟重现这个症状MySQLclientsimulationprepare*simulationroot:xx>preparestmt1from'select*fromxx_operation_logwhereid=?';QueryOK,0rowsaffected(0.00sec)Statementprepared*Result#Time:2019-02-14T14:14:50.937462+08:00#User@Host:root[root]@localhost[]Id:369#渠ery_time:0.000105Lock_time:0.000000Rows_sent:0Rows_examined:0SETtimestamp=1550124890;从'select*fromxx_operation_logwhereid=?'准备stmt1;结论是：MySQL客户端模拟的prepare不是我们所期望的，并没有得到我们想要的需要管理员命令：prepareperl模拟prepare#!/usr/bin/perluseDBI;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');睡眠3;$sth->execute();结论是：和MySQL客户端一样，看不到管理员命令：Preparephp模拟prepare1\。官网：https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli-stmt.prepare.htmlphp模拟得到的慢结果[root@xx20190211]#catxx-slow.log|grep'administratorcommand:Prepare'-B4|grep'User@Host'|grep'xx_rx'|wc-l7891[root@xx20190211]#catxx-slow.log|grep'administratorcommand:Prepare'-B4|grep'User@Host'|wc-l7908结论：通过php代码，我们成功模拟出了想要的结果。在整个sql执行过程中下一期sessionid相同，mysql开启slow=0抓包方式定位相同sessionid的prepare+execute+closest(3415357118)mt#User@Host:xx_rx[xx_rx]@[xx.xxx.xxx.132]Id:3415357118#Query_time:0.401453Lock_time:0.000000Rows_sent:0Rows_examined:0usexx_db;SETtimestamp=1550017125;时间：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:0aminedRows_:1usexx_db;SETtimestamp=1550017125;update`xx`set`updated_at`='2019-02-1308:18:45',`has_sales_office_phone`=1,`has_presale_permit`=1where`id`=28886;时间：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:0aminedrows_:1usexx_db;SETtimestamp=1550017125;#administratorcommand:closestmt;#Time:2019-02-13T08:18:45.626430+08:00结论：我们发现prepare时间确实很长，但是sql语句执行的很快Quick，这就很尴尬了。本来想抓包看看能不能验证一下猜想：prepare语句很大，或者条件很复杂，导致server端prepare很慢。原来查询语句也很简单然后两个但是，我们找到了业务端，查看业务对应的prepare方法。结果发现业务使用的是php-pdo方式，于是我们找到了php-pdo的两种prepare模式如下http://php.net/manual/zh/pdo.prepare.php1。本地准备$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);不会发送到MySQLServer2。服务器端准备$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);发送到MySQL服务器以验证两种准备模式服务器准备模式(ATTR_EMULATE_PREPARES=false)setAttribute(PDO::ATTR_EMULATE_PREPARES,false);echo"-----准备开始-----\n";$stmt=$pdo->prepare("select*fromtest.chanpinwhereid=?");echo"-----在-----之后准备\n";$stmt->执行([333333]);echo"-----在-----之后执行\n";$rs=$stmt->fetchAll();}catch(PDOException$e){die("Error!:".$e->getMessage()。"
");}strace-s200-fphpmysql1.phpTracing可以看到在这种模式下，prepare时，query+placeholder发送到serverLocalpreparemode(ATTR_EMULATE_PREPARES=true)setAttribute(PDO::ATTR_EMULATE_PREPARES,true);echo"-----准备开始-----\n";$stmt=$pdo->prepare("select*fromtest.chanpinwhereid=?");echo"-----在-----之后准备\n";$stmt->执行([333333]);echo"-----在-----\n之后执行";$rs=$stmt->fetchAll();}catch(PDOException$e){die("Error!:".$e->getMessage()."
");}strace-s200-fphpmysql1.phptrace可以看到，在这种模式下，preparing的时候，query是不会发给server的，只有executing的时候才会发给业务方确认后，他们用的是后者，也就是修改了default价值。他们本来是想提高数据库的性能，因为经过预处理，只需要传递参数，但是不适合我们的业务场景。我们的场景连接频繁打开和关闭，也就是预处理基本用不到。此外，该文件明确指出准备好的报表的性能将不容易调整和验证。如何验证业务方是否将prepare改为local？dba:(none)>showglobalstatuslike'Com_stmt_prepare';+----------------+------------+|变量名|值|+----------------+------------+|Com_stmt_准备|716836596|+------------------+------------+1rowinset(0.00sec)通过观察，发现这个value没有变化，说明调整生效了总结一下prepare1\的优点。防止SQL注入2\.提升特定场景下的性能什么是特定场景：先去服务器端用占位符填空，然后可以直接发送请求填空（参数值）。理论上，你填空很多，而性能是为了衬托出prepare1\的缺点。毕竟server端的prepare是比较耗时的。当并发量大，准备频繁时，就会出现性能问题。2.server端prepare模式会带来的另一个问题是，troubleshootingslow很难优化，因为大多数情况下是看不到真正的query的。3、尝试将php-pdo设置为$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true)，在本地准备，以免对服务器造成额外压力建议1\。默认情况下应该使用php-pdo的默认配置，使用本地的prepare方法，这样就可以达到防止SQL注入的效果，性能也差不了多少。2.除非真的有上面提到的特定场景，可以考虑配置serverprepare模式，前提是做好测试本文作者：兰春阅读云栖原文内容社区，未经许可不得转载。
