MySQL運(yùn)維實(shí)戰(zhàn)之PHP訪問MySQL，你使用對(duì)了嗎

作者：阿里云云棲社區(qū) 2019-03-08 16:30:44

大家都知道，slow query系統(tǒng)做的好不好，直接決定了解決slow query的效率問題，一個(gè)數(shù)據(jù)庫管理平臺(tái)，擁有一個(gè)好的slow query系統(tǒng)，基本上就擁有了解鎖性能問題的鑰匙，但是今天主要分享的并不是平臺(tái)，而是在平臺(tái)中看到的奇葩指數(shù)五顆星的slow issue。

[[259085]]

大家都知道，slow query系統(tǒng)做的好不好，直接決定了解決slow query的效率問題。

一個(gè)數(shù)據(jù)庫管理平臺(tái)，擁有一個(gè)好的slow query系統(tǒng)，基本上就擁有了解鎖性能問題的鑰匙。

但是今天主要分享的并不是平臺(tái)，而是在平臺(tái)中看到的奇葩指數(shù)五顆星的slow issue。

好了，關(guān)子賣完了，直接進(jìn)入正題：

一、癥狀

一堆如下慢查詢

# 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;

從我們的監(jiān)控圖上可以看到，每天不定時(shí)間段的slow query 總數(shù)在攀升，但是卻看不到任何query 語句。

這是我接觸到的slow query優(yōu)化案例中從來沒有過的情況，比較好奇，也比較興奮，至此決心要好好看看這個(gè)問題。

二、排查

要解決這個(gè)問題，首先想到的是，如何復(fù)現(xiàn)這個(gè)問題，如何模擬復(fù)現(xiàn)這個(gè)癥狀。

MySQL客戶端模擬prepare

* 模擬  
root:xx> prepare stmt1 from 'select * from xx_operation_log where id = ?';  
Query OK, 0 rows affected (0.00 sec)  
Statement prepared  
* 結(jié)果  
# 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 = ?';

結(jié)論是： 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}";#數(shù)據(jù)源  
#獲取數(shù)據(jù)庫句柄  
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();

結(jié)論是：跟MySQL客戶端一樣，同樣是看不到administrator command: Prepare。

php 模擬prepare

php模擬得到的slow 結(jié)果：

[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

結(jié)論：通過php代碼，我們成功模擬出了想要的結(jié)果。

那順藤摸瓜，抓取下這段時(shí)間有相同session id的整個(gè)sql執(zhí)行過程。

MySQL開啟slow=0的抓包模式

可以定位到同一個(gè)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

結(jié)論：我們發(fā)現(xiàn)，prepare時(shí)間的確很長(zhǎng)，但是sql語句卻執(zhí)行的很快，這就很尷尬了。

本來是想通過抓包，看看是否能夠驗(yàn)證我們的猜想： prepare的語句非常大，或者條件非常復(fù)雜，從而導(dǎo)致prepare在服務(wù)器端很慢。

結(jié)果發(fā)現(xiàn)query語句也都非常簡(jiǎn)單。

那么既然如此，我們就找了業(yè)務(wù)方，將對(duì)應(yīng)業(yè)務(wù)的prepare方法一起看看。

結(jié)果發(fā)現(xiàn)，業(yè)務(wù)使用的是php-pdo的方式，所以我們就又有了如下發(fā)現(xiàn)：

php-pdo 兩種prepare模式

http://php.net/manual/zh/pdo.prepare.php

1. 本地prepare $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);

不會(huì)發(fā)送給MySQL Server

2. 服務(wù)器端prepare $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

發(fā)送給MySQL Server

驗(yàn)證兩種prepare模式

服務(wù)端prepare模式( ATTR_EMULATE_PREPARES = false)

<?php  
$dbms='mysql'; //數(shù)據(jù)庫類型  
$host='xxx'; //數(shù)據(jù)庫主機(jī)名  
$dbName='test'; //使用的數(shù)據(jù)庫  
$user='xx'; //數(shù)據(jù)庫連接用戶名  
$pass='123456'; //對(duì)應(yīng)的密碼  
$dsn="$dbms:host=$host;dbname=$dbName";  
try {  
 $pdo = new PDO($dsn, $user, $pass); //初始化一個(gè)PDO對(duì)象  
 $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() . "<br/>");  
}

strace -s200 -f php mysql1.php 跟蹤

大家可以看到這個(gè)模式下，prepare的時(shí)候，是將query+占位符發(fā)送給服務(wù)端的：

本地prepare模式 (ATTR_EMULATE_PREPARES = true )

<?php  
$dbms='mysql'; //數(shù)據(jù)庫類型  
$host='xx'; //數(shù)據(jù)庫主機(jī)名  
$dbName='test'; //使用的數(shù)據(jù)庫  
$user='xx'; //數(shù)據(jù)庫連接用戶名  
$pass='123456'; //對(duì)應(yīng)的密碼  
$dsn="$dbms:host=$host;dbname=$dbName";  
try {  
 $pdo = new PDO($dsn, $user, $pass); //初始化一個(gè)PDO對(duì)象  
 $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() . "<br/>");  
}

strace -s200 -f php mysql1.php 跟蹤

大家可以看到這個(gè)模式下，prepare的時(shí)候，是不會(huì)將query發(fā)送給服務(wù)端的，只有execute的時(shí)候才會(huì)發(fā)送。

跟業(yè)務(wù)方確認(rèn)后，他們使用的是后者，也就是修改了默認(rèn)值，他們?cè)臼窍胩嵘龜?shù)據(jù)庫的性能，因?yàn)轭A(yù)處理后只需要傳參數(shù)就好了。

但是對(duì)于我們的業(yè)務(wù)場(chǎng)景并不適合，我們的場(chǎng)景是頻繁打開關(guān)閉連接，也就是預(yù)處理基本就用不到。

另外文檔上面也明確指出prepared statements 性能會(huì)不好。

調(diào)整和驗(yàn)證

如何驗(yàn)證業(yè)務(wù)方是否將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)

通過觀察，發(fā)現(xiàn)這個(gè)值沒有變化，說明調(diào)整已經(jīng)生效。

總結(jié)

prepare的優(yōu)點(diǎn)

1. 防止SQL注入  
2. 特定場(chǎng)景下提升性能  
 什么是特定場(chǎng)景： 就是先去服務(wù)端用占位符占位，后面可以直接發(fā)送請(qǐng)求來填空（參數(shù)值）  
 這樣理論上來說， 你填空的次數(shù)非常多，性能才能發(fā)揮出來

prepare的缺點(diǎn)

1. 在服務(wù)器端的prepare畢竟有消耗，當(dāng)并發(fā)量大，頻繁prepare的時(shí)候，就會(huì)有性能問題  
2. 服務(wù)端的prepare模式還會(huì)帶來的另外一個(gè)問題就是，排錯(cuò)和slow 優(yōu)化有困難，因?yàn)榇蟛糠智闆r下是看不到真實(shí)query的  
3. 盡量設(shè)置php-pdo為 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true) ，在本地prepare，不要給服務(wù)器造成額外壓力

建議

1. 默認(rèn)情況下，應(yīng)該使用php-pdo的默認(rèn)配置，采用本地prepare的方式，這樣可以做到防SQL注入的效果，性能差不到哪里去  
2. 除非真的是有上述說的特定場(chǎng)景，可以考慮配置成服務(wù)器prepare模式，前提是要做好測(cè)試

責(zé)任編輯：龐桂玉來源：今日頭條