MySQL运维实战PHP访问MySQL，你会正确使用吗？一个具备良好慢查询系统的数据库管理平台，基本上就掌握了解开性能问题的钥匙。不过我今天主要分享的不是平台，而是我在平台上看到的精彩指数中五颗星的慢问题。好了，答题已满，直接进入正题：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-12T00:02:07.516803+08:00#User@Host:cra[cra]@[xx]Id:3351119968#Query_time:0.294081Lock_time:0.000000ex_amine:0Redows:0SETtimestamp=1549900927;#administratorcommand:准备;从我们的监控图中可以看到每天不同时间段的慢查询总数都在增加，但是看不到任何查询语句。这是我在慢查询优化的情况下从来没有遇到过的情况。我很好奇，也很兴奋，所以我决心好好看看这个问题。2.Troubleshooting要解决这个问题，首先想到的就是如何重现这个问题，如何模拟重现这个现象。MySQL客户端模拟prepare*模拟root: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#Query_time:0.000105Lock_time:0.000000Rows_sent:0Rows_examined:0SETtimestamp=1550124890;preparemt1from'select*fromxx_operation_logwhereid=?';结论是：MySQL客户端模拟的prepare不是我们所期望的，并没有得到administratorcommand:Preparewewanted。perl模拟prepare#!/usr/bin/perluseDBI;my$dsn="dbi:mysql:database=${db_name};hostname=${db_host};port=${db_port}";#datasource#Getdatabasehandlemy$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客户端，也看不到管理员命令：准备。php模拟preparephp模拟得到的慢结果：[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代码，我们成功模拟出了想要的结果。然后顺藤摸瓜，抓取这段时间同一个sessionid的整个sql执行过程。mysql启用slow=0的抓包方式定位prepare+execute+closestmt#User@Host:xx_rx[xx_rx]@[xx.xxx.xxx.132]Id:3415357118#Query_time:0.401453同一个sessionid(3415357118)Lock_time:0.000000Rows_sent:0Rows_examined:0usexx_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:1usexx_db;SETtimestamp=1550017125;update`xx`set`updated_at`='2019-02-1308:18:45',``off_sale,``off_sales1,`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:1usexx_db;SETtimestamp=1550017125;#administratorcommand:Closestmt;#Time:2019-02-13T08:18:45p.62643确实是我们发现的结论好长，但是sql语句执行的很快，很尴尬。本来想抓包看看能不能验证一下猜想：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);发送到MySQLServer验证两种准备模式server-sidepreparemode(ATTR_EMULATE_PREPARES=false)execute([333333]);echo"-----executeafter-----";$rs=$stmt->fetchAll();}catch(PDOException$e){die("Error!:".$e->getMessage()."
");}strace-s200-fphpmysql1.php和可以看到在这种模式下，prepare时，query+placeholder被发送到服务器：Localpreparemode(ATTR_EMULATE_PREPARES=true)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("错误！:".$e->getMessage()."
");}strace-s200-fphpmysql1.phptrace可以看到在这种模式下，在prepare的时候，query是不会发送到service的在客户端，只有在执行并与业务方确认后才会发送。他们使用的是后者，即修改默认值。他们本来是想提高数据库的性能，因为经过预处理，只需要传递参数即可。但是不适合我们的业务场景。我们的场景是频繁打开和关闭连接，也就是基本不用预处理。此外，文档中还明确指出，preparedstatements的性能会很差。调整验证如何验证业务方是否修改了prepare到本地？dba:(none)>showglobalstatuslike'Com_stmt_prepare';+----------------+----------+|Variable_name|Value|+---------------+----------+|Com_stmt_prepare|716836596|+----------------+------------+1rowinset(0.00sec)通过观察，发现这个值没有变化，说明调整生效了。总结一下prepare的优点1.防止SQL注入2.提高特定场景下的性能具体场景是什么：先去服务器用placeholder填空格，然后直接发送请求填入即可空白（参数值）。理论上，你填空的次数是非常多的，表现才能发挥出来。prepare的缺点1.毕竟server端的prepare是比较消耗的。当并发量大，prepare频繁的时候，就会出现性能问题。2.服务器端的prepare模式还会带来其他的东西。一个问题是故障排除和缓慢优化很困难，因为在大多数情况下，无法看到真正的查询。3.尝试设置php-pdo为$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true)，本地准备。不要给服务器增加额外的压力建议1、默认情况下，应该使用php-pdo的默认配置，使用本地的prepare方法，这样可以达到防止SQL注入的效果，性能也不错.2.除非真的有上面提到的特定场景，可以考虑配置成serverprepare模式，前提是你做好了测试
