MySQL运维实战 之 PHP访问MySQL你使用对了吗

栏目: PHP · 发布时间: 5年前

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

大家都知道，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 faia_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`=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.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模式


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 跟踪

MySQL运维实战 之 PHP访问MySQL你使用对了吗

大家可以看到这个模式下，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 跟踪

MySQL运维实战 之 PHP访问MySQL你使用对了吗

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

跟业务方确认后，他们使用的是后者，也就是修改了默认值，他们原本是想提升数据库的性能，因为预处理后只需要传参数就好了

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

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

MySQL运维实战 之 PHP访问MySQL你使用对了吗

调整和验证

如何验证业务方是否将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模式，前提是要做好测试

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍
商战

商战

杰克•特劳特、阿尔•里斯 / 李正栓、李腾 / 机械工业出版社 / 2011-3 / 42.00元

本书重点阐述了商战中的四种常用战略形式，如防御战、进攻战、侧翼战和游击战，针对每一种形式又提出了三条应遵循的原则，以及如何在具体的商战中应用这些原则。本书分析了商战中的实际案例：可口可乐与百事可乐的战役，汉堡王与温迪斯对麦当劳的挑战以及DEC对阵IBM等。这些人们熟知品牌的案例在作者精心的组织下，使读者不仅加深了对本书中心思想的理解，而且学习了如何在实战中具体应用各种营销战略和策略的技巧。 ......一起来看看 《商战》 这本书的介绍吧!

码农工具
Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具

  • New
  • 文章
  • 话题
  • 教程
    • 关注 码农网 公众号
    版权所有，保留一切权利！© 2018-2024 码农网 粤ICP备17054400号-3