php管理MySQLMySQL运维实战之 PHP拜访MySQL你应用对了吗

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

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

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

php管理MySQLMySQL运维实战之 PHP拜访MySQL你应用对了吗 Java

一、症状

一堆如下慢查询

# User@Host: cra[cra] @ [xx] Id: 3352884621# Query_time: 0.183673 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0use xx_db;SET timestamp=1549900927;# administrator command: Prepare;# Time: 2019-02-12T00:02:07.516803+08:00# User@Host: cra[cra] @ [xx] Id: 3351119968# Query_time: 0.294081 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0SET timestamp=1549900927;# administrator command: Prepare;

从我们的监控图上可以看到，每天禁绝光阴段的slow query 总数在攀升，但是却看不到任何query 语句

这是我打仗到的slow query优化案例中从来没有过的情形，比较好奇，也比较愉快，至此决心要好好看看这个问题

二、排查

要办理这个问题，首先想到的是，如何复现这个问题，如何仿照复现这个症状

MySQL客户端仿照prepare

仿照root:xx> prepare stmt1 from 'select from xx_operation_log where id = ?';Query OK, 0 rows affected (0.00 sec)Statement prepared 结果# Time: 2019-02-14T14:14:50.937462+08:00# User@Host: root[root] @ localhost [] Id: 369# Query_time: 0.000105 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0SET timestamp=1550124890;prepare stmt1 from 'select from xx_operation_log where id = ?';

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

perl 仿照prepare

#!/usr/bin/perluse DBI;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 from xx_operation_log where id in (?)};my $sth = $dbh->prepare($sql);$sth->bind_param (1, '100');sleep 3;$sth->execute();

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

php 仿照prepare

php仿照得到的slow 结果

结论：通过php代码，我们成功仿照出了想要的结果

那顺藤摸瓜，抓取下这段韶光有相同session id的全体sql实行过程

MySQL开启slow=0的抓包模式

可以定位到同一个session id（3415357118）的 prepare + execute + close stmt# User@Host: xx_rx[xx_rx] @ [xx.xxx.xxx.132] Id: 3415357118# Query_time: 0.401453 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0use xx_db;SET timestamp=1550017125;# administrator command: 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.001650 Lock_time: 0.000102 Rows_sent: 0 Rows_examined: 1use xx_db;SET timestamp=1550017125;update `xx` set `updated_at` = '2019-02-13 08:18:45', `has_sales_office_phone` = 1, `has_presale_permit` = 1 where `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.000029 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 1use xx_db;SET timestamp=1550017125;# administrator command: Close stmt;# 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 = new PDO($dsn, $user, $pass); //初始化一个PDO工具 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); echo \"大众----- prepare begin -----\n\公众; $stmt = $pdo->prepare(\公众select from test.chanpin where id = ?\"大众); echo \公众----- prepare after -----\n\"大众; $stmt->execute([333333]); echo \"大众----- execute after -----\n\公众; $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 = new PDO($dsn, $user, $pass); //初始化一个PDO工具 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); echo \"大众----- prepare begin -----\n\"大众; $stmt = $pdo->prepare(\"大众select from test.chanpin where id = ?\"大众); echo \"大众----- prepare after -----\n\"大众; $stmt->execute([333333]); echo \公众----- execute after -----\n\"大众; $rs = $stmt->fetchAll();} catch (PDOException $e) { die (\"大众Error!: \"大众 . $e->getMessage() . \"大众<br/>\"大众);}

strace -s200 -f php mysql1.php 跟踪

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

跟业务方确认后，他们利用的是后者，也便是修正了默认值，他们原来是想提升数据库的性能，由于预处理后只须要传参数就好了

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

其余文档上面也明确指出prepared statements 性能会不好

调度和验证

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

通过不雅观察，创造这个值没有变革，解释调度已经生效

总结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模式，条件是要做好测试

作者：兰春