MySQL運維實戰 之 PHP訪問MySQL你使用對了嗎

PHP技術大全 / 2019-03-15 14:59:35

大家都知道,slow query系統做的好不好,直接決定了解決slow query的效率問題

一個數據庫管理平臺,擁有一個好的slow query系統,基本上就擁有了解鎖性能問題的鑰匙

但是今天主要分享的并不是平臺,而是在平臺中看到的奇葩指數五顆星的slow issue

好了,關子賣完了,直接進入正題

一、癥狀

一堆如下慢查詢

# [email protected]: 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
# [email protected]: 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
# [email protected]: 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. 官方網址:

http://dev.mysql.com/doc/apis-php/en/apis-php-mysqli-stmt.prepare.html


$link = mysqli_connect("xx", "dba", "xx", "xx_db");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %sn", 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 %sn", $city, $district);

/* close statement */
mysqli_stmt_close($stmt);
}

/* close connection */
mysqli_close($link);
?>
  • php模擬得到的slow 結果

[[email protected] 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4  | grep '[email protected]' | grep 'xx_rx' | wc -l
7891

[[email protected] 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4 | grep '[email protected]' | wc -l
7908

結論: 通過php代碼,我們成功模擬出了想要的結果

那順藤摸瓜,抓取下這段時間有相同session id的整個sql執行過程

MySQL開啟slow=0的抓包模式

可以定位到同一個session id(3415357118) 的 prepare + execute + close stmt 


# [email protected]: 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
--
# [email protected]: 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
--
# [email protected]: 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)

$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() . "
");
}

strace -s200 -f php mysql1.php 跟蹤

大家可以看到這個模式下,prepare的時候,是將query+占位符 發送給服務端的

  • 本地prepare模式 (ATTR_EMULATE_PREPARES = true )

$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() . "
");
}

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模式,前提是要做好測試

干貨分享

敬請關注“PHP技術大全”微信公眾號


4399小游戏上海麻将连连看 山东时时11选5走势图 凤凰论坛两码中特 北京快车pk 重庆时时杀个位计划 特马预测软件 黑龙江36选7开奖查询 查云南快乐十分走势图 彩名堂客户端 广西快乐双彩开奖结果走势图 香港正版资料王中王开奖结果