PHP访问MySQL你用对了么?

前言

大家都知道,slow query系统做的好不好,直接决定了解决slow query问题的效率。 一个数据库管理平台,拥有一个好的slow query系统,基本上就拥有了解锁性能问题的钥匙。但是今天主要分享的并不是平台,而是在平台中看到的奇葩指数五颗星的slow issue。

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

症状

数据库的慢SQL日志中发现了一堆如下慢查询:

PHP访问MySQL你用对了么?

从我们的监控图上可以看到,每天不定时间段的slow query 总数在攀升,但是却看不到任何query 语句,这是我接触到的slow query优化案例中从来没有过的情况,比较好奇,也比较兴奋,至此决心要好好看看这个问题。

排查

要解决这个问题,首先想到的是,如何复现这个问题,如何模拟复现这个症状。

  • MySQL客户端模拟prepare

PHP访问MySQL你用对了么?

  • perl 模拟prepare

PHP访问MySQL你用对了么?

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

  • PHP 模拟 prepare

PHP访问MySQL你用对了么?

  • PHP 模拟得到的slow结果

PHP访问MySQL你用对了么?

结论: 通过php代码,我们成功模拟出了想要的结果

那顺藤摸瓜,抓取下这段时间有相同session id的整个sql执行过程。

  • MySQL开启slow=0的抓包模式

PHP访问MySQL你用对了么?

结论:我们发现,prepare时间的确很长,但是sql语句却执行的很快,这就很尴尬了

本来是想通过抓包,看看是否能够验证我们的猜想: prepare的语句非常大,或者条件非常复杂,从而导致prepare在服务器端很慢,结果发现query语句也都非常简单。那么既然如此,我们就找了业务方,将对应业务的prepare方法一起看看,结果发现,业务使用的是php-pdo的方式,所以我们就又有了如下发现:

  • php-pdo 两种prepare模式
  1. 本地prepare $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);不会发送给MySQL Server

  2. 服务器端prepare $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false;发送给MySQL Server

官网说明: http://php.net/manual/zh/pdo.prepare.php

  • 验证两种prepare模式

服务端prepare模式( ATTR_EMULATE_PREPARES = false)

PHP访问MySQL你用对了么?

strace -s200 -f php mysql1.php 跟踪如下图

PHP访问MySQL你用对了么?

这个模式下,prepare的时候是将query+占位符发送给服务端的。

本地prepare模式 (ATTR_EMULATE_PREPARES=true )

PHP访问MySQL你用对了么?

strace -s200 -f php mysql1.php 跟踪如下图

PHP访问MySQL你用对了么?

这个模式下,prepare的时候是不会将query发送给服务端的,只有execute的时候才会发送。跟业务方确认后,他们使用的是后者,也就是修改了默认值,他们原本是想提升数据库的性能,因为预处理后只需要传参数就好了,但是对于我们的业务场景并不适合,我们的场景是频繁打开关闭连接,也就是预处理基本就用不到。

另外文档上面也明确指出prepared statements 性能会不好。

PHP访问MySQL你用对了么?

调整和验证

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

PHP访问MySQL你用对了么?

通过观察,发现这个值没有变化,说明调整已经生效。

总结

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模式,前提是要做好测试。

点关注,不迷路

好了各位,以上就是这篇文章的全部内容了,能看到这里的人呀,都是人才。之前说过,PHP方面的技术点很多,也是因为太多了,实在是写不过来,写过来了大家也不会看的太多,所以我这里把它整理成了PDF和文档,如果有需要的可以

点击进入暗号: PHP+「平台」

PHP访问MySQL你用对了么?

PHP访问MySQL你用对了么?


更多学习内容可以访问【对标大厂】精品PHP架构师教程目录大全,只要你能看完保证薪资上升一个台阶(持续更新)

以上内容希望帮助到大家,很多PHPer在进阶的时候总会遇到一些问题和瓶颈,业务代码写多了没有方向感,不知道该从那里入手去提升,对此我整理了一些资料,包括但不限于:分布式架构、高可扩展、高性能、高并发、服务器性能调优、TP6,laravel,YII2,Redis,Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点高级进阶干货需要的可以免费分享给大家,需要的可以加入我的 PHP技术交流群

上一篇:1587: 【例 3】Windy 数


下一篇:Mysql预编译