php模拟得到的slow 结果
[root@xx 20190211]# cat xx-slow.log | grep "administrator command: Prepare" -B4 | grep "User@Host" | grep "xx_rx" | wc -l7891[root@xx 20190211]# cat xx-slow.log | grep "administrator command: Prepare" -B4 | grep "User@Host" | wc -l7908
结论: 通过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 -----
"; $stmt = $pdo->prepare("select * from test.chanpin where id = ?"); echo "----- prepare after -----
"; $stmt->execute([333333]); echo "----- execute after -----
"; $rs = $stmt->fetchAll();} catch (PDOException $e) { die ("Error!: " . $e->getMessage() . "
");}
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 -----
"; $stmt = $pdo->prepare("select * from test.chanpin where id = ?"); echo "----- prepare after -----
"; $stmt->execute([333333]); echo "----- execute after -----
"; $rs = $stmt->fetchAll();} catch (PDOException $e) { die ("Error!: " . $e->getMessage() . "
");}
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模式,前提是要做好测试
作者:兰春
本文仅代表作者个人观点,不代表巅云官方发声,对观点有疑义请先联系作者本人进行修改,若内容非法请联系平台管理员,邮箱qq2522407257。更多相关资讯,请到巅云www.yinxi.net学习互联网营销技术请到巅云学院www.yx10011.com。