大家都知道，slow query 系统做的好不好，直接决定了解决 slow query 的效率问题
一个数据库管理平台，拥有一个好的 slow query 系统，基本上就拥有了解锁性能问题的钥匙
但是今天主要分享的并不是平台，而是在平台中看到的奇葩指数五颗星的 slow issue
好了，关子卖完了，直接进入正题
一、症状
一堆如下慢查询
# User@Host: cra[cra] @ [xx] Id: 3352884621
# Query_time: 0.183673 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use 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: 0
SET 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: 0
SET timestamp=1550124890;
prepare stmt1 from ‘select * from xx_operation_log where id = ?’;
结论是：MySQL client 模拟出来的 prepare 并不是我们期待的，并没有得到我们想要的 administrator command: Prepare
perl 模拟 prepare
#!/usr/bin/perl
use 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
1\. 官方网址：
https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli-stmt.prepare.html
<?php
$link = mysqli_connect(“xx”, “dba”, “xx”, “xx_db”);
/* check connection */
if (mysqli_connect_errno()) {
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
}
$city = ‘1’;
/* create a prepared statement */
$stmt = mysqli_stmt_init($link);
if (mysqli_stmt_prepare($stmt, ‘select * from xx_operation_log where id in (1,2,3)’){
/* bind parameters for markers */
/* mysqli_stmt_bind_param($stmt, “s”, $city);
/* execute query */
mysqli_stmt_execute($stmt);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $district);
/* fetch value */
mysqli_stmt_fetch($stmt);
printf(“%s is in district %s\n”, $city, $district);
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
php 模拟得到的 slow 结果
[root@xx 20190211]# cat xx-slow.log | grep ‘administrator command: Prepare’ -B4 | grep ‘User@Host’ | grep ‘xx_rx’ | wc -l
7891
[root@xx 20190211]# cat xx-slow.log | grep ‘administrator command: Prepare’ -B4 | grep ‘User@Host’ | wc -l
7908
结论：通过 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: 0
use 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: 1
use 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: 1
use 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 了呢？
dba:(none)> show global status like ‘Com_stmt_prepare’;
+——————+———–+
| Variable_name | Value |
+——————+———–+
| Com_stmt_prepare | 716836596 |
+——————+———–+
1 row in set (0.00 sec)
通过观察，发现这个值没有变化，说明调整已经生效
总结
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 模式，前提是要做好测试
本文作者：兰春阅读原文
本文为云栖社区原创内容，未经允许不得转载。