自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

MySQL運維實戰(zhàn)之PHP訪問MySQL，你使用對了嗎

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

[[259085]]

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

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

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

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

一、癥狀   

  1. 一堆如下慢查詢  
  1. # User@Host: cra[cra] @ [xx] Id: 3352884621  
  2. # Query_time: 0.183673 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0  
  3. use xx_db;  
  4. SET timestamp=1549900927;  
  5. # administrator command: Prepare;  
  6. # Time: 2019-02-12T00:02:07.516803+08:00  
  7. # User@Host: cra[cra] @ [xx] Id: 3351119968  
  8. # Query_time: 0.294081 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0  
  9. SET timestamp=1549900927;  
  10. # administrator command: Prepare; 

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

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

二、排查

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

  •  MySQL客戶端 模擬prepare 
  1. * 模擬  
  2. root:xx> prepare stmt1 from 'select * from xx_operation_log where id = ?';  
  3. Query OK, 0 rows affected (0.00 sec)  
  4. Statement prepared  
  5. * 結(jié)果  
  6. # Time: 2019-02-14T14:14:50.937462+08:00  
  7. # User@Host: root[root] @ localhost [] Id: 369  
  8. # Query_time: 0.000105 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0  
  9. SET timestamp=1550124890;  
  10. prepare stmt1 from 'select * from xx_operation_log where id = ?'; 

結(jié)論是： MySQL client 模擬出來的prepare 并不是我們期待的，并沒有得到我們想要的 administrator command: Prepare。

  •  perl 模擬prepare 
  1. #!/usr/bin/perl  
  2. use DBI;  
  3. my $dsn = "dbi:mysql:database=${db_name};hostname=${db_host};port=${db_port}";#數(shù)據(jù)源  
  4. #獲取數(shù)據(jù)庫句柄  
  5. my $dbh = DBI->connect("DBI:mysql:database=xx;host=xx", "xx", "xx", {'RaiseError' => 1});  
  6. my $sql = qq{select * from xx_operation_log where id in (?)};  
  7. my $sth = $dbh->prepare($sql);  
  8. $sth->bind_param (1, '100');  
  9. sleep 3;  
  10. $sth->execute(); 

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

  •  php 模擬prepare

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

  1. [root@xx 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4 | grep 'User@Host' | grep 'xx_rx' | wc -l  
  2. 7891  
  3. [root@xx 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4 | grep 'User@Host' | wc -l  
  4. 7908 

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

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

MySQL開啟slow=0的抓包模式 

  1. 可以定位到同一個session id（3415357118） 的 prepare + execute + close stmt  
  2. # User@Host: xx_rx[xx_rx] @ [xx.xxx.xxx.132] Id: 3415357118  
  3. # Query_time: 0.401453 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0  
  4. use xx_db;  
  5. SET timestamp=1550017125;  
  6. # administrator command: Prepare;  
  7. # Time: 2019-02-13T08:18:45.624671+08:00  
  8. --  
  9. # User@Host: xx_rx[xx_rx] @ [xx.xxx.xxx.132] Id: 3415357118  
  10. # Query_time: 0.001650 Lock_time: 0.000102 Rows_sent: 0 Rows_examined: 1  
  11. use xx_db;  
  12. SET timestamp=1550017125;  
  13. update `xx` set `updated_at` = '2019-02-13 08:18:45', `has_sales_office_phone` = 1, `has_presale_permit` = 1 where `id` = 28886;  
  14. # Time: 2019-02-13T08:18:45.626138+08:00  
  15. --  
  16. # User@Host: xx_rx[xx_rx] @ [xx.xxx.xxx.132] Id: 3415357118  
  17. # Query_time: 0.000029 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 1  
  18. use xx_db;  
  19. SET timestamp=1550017125;  
  20. # administrator command: Close stmt;  
  21. # Time: 2019-02-13T08:18:45.626430+08:00 

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

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

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

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

結(jié)果發(fā)現(xiàn)，業(yè)務使用的是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);

不會發(fā)送給MySQL Server

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

發(fā)送給MySQL Server

驗證兩種prepare模式

  •  服務端prepare模式( ATTR_EMULATE_PREPARES = false) 
  1. <?php  
  2. $dbms='mysql'; //數(shù)據(jù)庫類型  
  3. $host='xxx'; //數(shù)據(jù)庫主機名  
  4. $dbName='test'; //使用的數(shù)據(jù)庫  
  5. $user='xx'; //數(shù)據(jù)庫連接用戶名  
  6. $pass='123456'; //對應的密碼  
  7. $dsn="$dbms:host=$host;dbname=$dbName";  
  8. try {  
  9.  $pdo = new PDO($dsn, $user, $pass); //初始化一個PDO對象  
  10.  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);  
  11.  echo "----- prepare begin -----  
  12. ";  
  13.  $stmt = $pdo->prepare("select * from test.chanpin where id = ?");  
  14.  echo "----- prepare after -----  
  15. ";  
  16.  $stmt->execute([333333]);  
  17.  echo "----- execute after -----  
  18. ";  
  19.  $rs = $stmt->fetchAll();  
  20. } catch (PDOException $e) {  
  21.  die ("Error!: " . $e->getMessage() . "<br/>");  
  22. } 

strace -s200 -f php mysql1.php 跟蹤

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

  •  本地prepare模式 (ATTR_EMULATE_PREPARES = true ) 
  1. <?php  
  2. $dbms='mysql'; //數(shù)據(jù)庫類型  
  3. $host='xx'; //數(shù)據(jù)庫主機名  
  4. $dbName='test'; //使用的數(shù)據(jù)庫  
  5. $user='xx'; //數(shù)據(jù)庫連接用戶名  
  6. $pass='123456'; //對應的密碼  
  7. $dsn="$dbms:host=$host;dbname=$dbName";  
  8. try {  
  9.  $pdo = new PDO($dsn, $user, $pass); //初始化一個PDO對象  
  10.  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);  
  11.  echo "----- prepare begin -----  
  12. ";  
  13.  $stmt = $pdo->prepare("select * from test.chanpin where id = ?");  
  14.  echo "----- prepare after -----  
  15. ";  
  16.  $stmt->execute([333333]);  
  17.  echo "----- execute after -----  
  18. ";  
  19.  $rs = $stmt->fetchAll();  
  20. } catch (PDOException $e) {  
  21.  die ("Error!: " . $e->getMessage() . "<br/>");  
  22. } 

strace -s200 -f php mysql1.php 跟蹤

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

跟業(yè)務方確認后，他們使用的是后者，也就是修改了默認值，他們原本是想提升數(shù)據(jù)庫的性能，因為預處理后只需要傳參數(shù)就好了。

但是對于我們的業(yè)務場景并不適合，我們的場景是頻繁打開關閉連接，也就是預處理基本就用不到。

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

調(diào)整和驗證

如何驗證業(yè)務方是否將prepare修改為local了呢？ 

  1. dba:(none)> show global status like 'Com_stmt_prepare';  
  2. +------------------+-----------+  
  3. | Variable_name | Value |  
  4. +------------------+-----------+  
  5. | Com_stmt_prepare | 716836596 |  
  6. +------------------+-----------+  
  7. 1 row in set (0.00 sec) 

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

總結(jié)

  •  prepare的優(yōu)點 
  1. 1. 防止SQL注入  
  2. 2. 特定場景下提升性能  
  3.  什么是特定場景： 就是先去服務端用占位符占位，后面可以直接發(fā)送請求來填空（參數(shù)值）  
  4.  這樣理論上來說， 你填空的次數(shù)非常多，性能才能發(fā)揮出來 
  •  prepare的缺點 
  1. 1. 在服務器端的prepare畢竟有消耗，當并發(fā)量大，頻繁prepare的時候，就會有性能問題  
  2. 2. 服務端的prepare模式還會帶來的另外一個問題就是，排錯和slow 優(yōu)化有困難，因為大部分情況下是看不到真實query的  
  3. 3. 盡量設置php-pdo為 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true) ，在本地prepare，不要給服務器造成額外壓力 
  •  建議 
  1. 1. 默認情況下，應該使用php-pdo的默認配置，采用本地prepare的方式，這樣可以做到防SQL注入的效果，性能差不到哪里去  
  2. 2. 除非真的是有上述說的特定場景，可以考慮配置成服務器prepare模式，前提是要做好測試  
責任編輯：龐桂玉 來源： 今日頭條
MySQPHP數(shù)據(jù)庫
相關推薦

2020-09-18 06:39:18

hashMap循環(huán)數(shù)據(jù)

2022-05-09 07:27:50

ThreadLocaJava

2015-05-26 09:35:29

運維運維危機云計算應用

2020-07-21 15:53:18

戴爾

2024-12-17 15:00:00

字符串Java

2017-11-09 13:56:46

數(shù)據(jù)庫MongoDB水平擴展

2013-04-16 14:55:21

自動化運維Puppet實戰(zhàn)

2021-03-16 06:47:47

Python

2019-05-28 11:52:43

可視化圖表數(shù)據(jù)

2024-09-18 10:08:37

2023-12-08 13:23:00

大數(shù)據(jù)MySQL存儲

2013-04-11 17:31:28

運維自動化Cobbler

2011-06-29 09:27:10

2019-03-15 10:13:10

運維云計算運營

2013-03-29 09:15:08

IT運維運維人員運維工程師

2023-06-30 08:54:39

2015-01-26 10:55:56

云服務器PowerEdge C

2022-01-12 18:35:54

MongoDB數(shù)據(jù)查詢

2019-12-26 14:07:19

隨機數(shù)偽隨機多線程
點贊
收藏

51CTO技術棧公眾號