MySQL資料庫的性能分析 ---圖書《軟體性能測試分析與調優實踐之路》-手稿節選

1  、MySQL資料庫的性能監控

1.1、如何查看MySQL資料庫的連接數

連接數是指用戶已經創建多少個連接，也就是MySQL中通過執行 SHOW  PROCESSLIST命令輸出結果中運行著的線程個數的詳情，如圖所示。

SHOW PROCESSLIST預設情況下只顯示前100條記錄的詳情，如果超過100條後需要顯示所有，可以通過執行SHOW  FULL  PROCESSLIST命令，如圖所示。

show variables like 'max_connections'可以查詢資料庫中可以支持的最大連接數，如圖所示。

本文作者：張永清， 轉載請註明： https://www.cnblogs.com/laoqing/p/16880718.html  來源於博客園 ,本文摘選自《軟體性能測試分析與調優實踐之路》

 show global status like 'max_used_connections'可以查詢當前已經使用過的最大連接數，如圖所示。

 1.2、如何查看MySQL資料庫當前運行的事務與鎖

事務是對資料庫執行一種帶有原子性、一致性、隔離性、持久性的數據操作，在MySQL中如果需要使用事務，那麼數據存儲時必須選用MySQL的innodb引擎，使用innodb引擎後，在MySQL系統資料庫information_schema的innodb_trx表中記錄了資料庫當前正在運行的事務。

innodb_trx表中包含的常用欄位說明如表所示。

表 innodb_trx表中包含的常用欄位說明

MySQL系統資料庫information_schema的 innodb_locks表中記錄了innodb資料庫引擎當前產生的鎖的情況， innodb_locks表中包含的常用欄位說明如表所示。

MySQL系統資料庫information_schema的innodb_lock_waits表中記錄了innodb資料庫引擎當前運行的資料庫事務等待鎖的情況，innodb_lock_waits表中包含的常用欄位說明如表所示。本文作者：張永清， 轉載請註明： https://www.cnblogs.com/laoqing/p/16880718.html  來源於博客園 ,本文摘選自《軟體性能測試分析與調優實踐之路》

表innodb_lock_waits表中包含的常用欄位說明

在資料庫中出現死鎖時，經常需要通過查詢innodb_trx、innodb_locks、innodb_lock_waits這三張表來找出在執行什麼事務操作時導致了死鎖，例如執行如下SQL可以列出資料庫中所有事務的等待和鎖定記錄。

--本文作者：張永清， 轉載請註明： https://www.cnblogs.com/laoqing/p/16880718.html  
--來源於博客園 ,本文摘選自《軟體性能測試分析與調優實踐之路》
SELECT 
  r.trx_isolation_level,/*事務隔離級別*/
  r.trx_id AS waiting_trx_id,/*正處於等待中的事務id*/
  r.trx_mysql_thread_id AS waiting_trx_thread, /*正處於等待中的資料庫線程id*/
  r.trx_state AS waiting_trx_state, /*正處於等待中的事務的狀態*/
  lr.lock_mode AS waiting_trx_lock_mode,/*正處於等待中的事務的鎖定模式*/
  lr.lock_type AS waiting_trx_lock_type,/*正處於等待中的事務的鎖定類型*/
  lr.lock_table AS waiting_trx_lock_table,/*正處於等待中的事務將鎖定的表*/
  lr.lock_index AS waiting_trx_lock_index,/*正處於等待中的事務將鎖定的索引*/
  r.trx_query AS waiting_trx_SQL,/*正處於等待中的事務將執行的SQL*/
  b.trx_id AS blocking_trx_id,/*正處於鎖定中的事務id*/
  b.trx_mysql_thread_id AS blocking_trx_thread,/*正處於鎖定中的線程id*/
  b.trx_state AS blocking_trx_state,/*正處於鎖定中的事務的狀態*/
  lb.lock_mode AS blocking_trx_lock_mode,/*正處於鎖定中的事務的鎖定模式*/
  lb.lock_type AS blocking_trx_lock_type,/*正處於鎖定中的事務的鎖定類型*/
  lb.lock_table AS blocking_trx_lock_table,/*正處於鎖定中的事務已經鎖定的表*/
  lb.lock_index AS blocking_trx_lock_index,/*正處於鎖定中的事務已經鎖定的索引*/
  b.trx_query AS blocking_sql /*正處於鎖定中的事務在執行的SQL*/
FROM
  information_schema.innodb_lock_waits wt 
  INNER JOIN information_schema.innodb_trx b 
    ON b.trx_id = wt.blocking_trx_id 
  INNER JOIN information_schema.innodb_trx r 
    ON r.trx_id = wt.requesting_trx_id 
  INNER JOIN information_schema.innodb_locks lb 
    ON lb.lock_trx_id = wt.blocking_trx_id 
  INNER JOIN information_schema.innodb_locks lr 
    ON lr.lock_trx_id = wt.requesting_trx_id;

1.3、MySQL中資料庫表的監控

•  1、查看資料庫中當前打開了哪些表: show OPEN TABLES ，如圖所示。另外還可以通過show OPEN TABLES where In_use > 0過濾出當前已經被鎖定的表。

•  2、查看資料庫中表的狀態：SHOW STATUS LIKE  '%table%'，如圖所示。需要特別註意的是Table_locks_waited  指的是不能立即獲取表級鎖而需要等待的次數，如果等待的次數非常大則說明可能存在鎖爭搶的情況，如果是頻繁的出現鎖爭搶則對應用程式的併發性能影響很大。

• 3、查看資料庫中鎖的信息：SHOW STATUS LIKE '%lock%'，如圖所示。

• 4、查看資料庫中的表被掃描的情況：show global status like 'handler_read%'，如圖所示，查詢的結果數據也可以用來評估資料庫中索引的使用情況。查詢的結果數據說明如表所示。

1.4、性能測試時MySQL中其他常用監控 

• 1、查看每秒事務的提交數：show global status like 'com_commit'，如圖所示

• 2、查看每秒事務的回滾數：show global status like 'com_rollback'，如圖所示

•  3、查看線程的運行情況：show global status like 'threads_%'，如圖所示。

查詢結果描述說明如表所示。

表查詢結果描述說明

• 4、查看資料庫建立過的連接總數（包括連接中以及已經斷開的連接）：show global status like 'Connections'，如圖所示。

•  5、查看innodb引擎緩存命中情況：show global status like 'innodb_buffer_pool_read%'，如圖所示。

• 6、 查看join操作時全表掃描的次數：show global status like 'select_full_join'，如圖所示，該值一般可以表示SQL語句中的join操作沒有走索引的次數，如果值非常大，那可能是SQL語句中的join操作存在性能問題。

• 7、 查看SQL中排序使用情況：show global status like 'sort%'，如圖所示

• 8、 查看SQL查詢緩存的命中情況：show global status like 'qcache%'，如圖所示。

如果需要查詢資料庫查詢緩存的設置，可以通過show variables like 'query_cache%'進行查看，如圖所示。

備註：MySQL資料庫中大部分的運行狀態都可以通過show status和show global status來查看，二者的區別在於前者是查詢當前的運行狀態，後者是查詢全局的運行狀態，也就是資料庫開始啟動運行到現在為止的狀態。

2、MySQL資料庫的性能定位

2.1、慢SQL

慢SQL 一般指查詢很慢的SQL語句，在MySQL資料庫中，可以通過慢查詢來查看所有執行超時的SQL語句，在預設情況下，一般慢SQL 是關閉的，可以通過執行show variables like 'slow_query%'來查看資料庫是否開啟了慢查詢，如圖所示。

從圖中看到slow_query_log的值為OFF表示未開啟，可以通過執行 set global slow_query_log=1; 或者 set global slow_query_log=ON;來臨時開啟慢查詢，如圖所示。

如果需要永久開啟，需要修改/etc/my.cnf配置文件，在[mysqld]處加入如下配置，然後重啟資料庫即可生效，如圖所示。

slow_query_log=ON

slow_query_log_file=/var/lib/mysql/localhost-slow.log

修改完成重啟資料庫後，再次執行show variables like 'slow_query%'，發現慢查詢已經被開啟，如圖所示。

通過執行show variables like 'long_query%';可以查詢慢查詢的記錄時間，如圖所示，預設是10秒，可以通過執行set long_query_time=需要修改的時長;來修改慢查詢的記錄時間。

通過執行show status like 'slow_queries';可以查看慢查詢發生的次數，如圖所示。

從慢查詢日誌中，我們也可以看到慢查詢發生的詳細信息，如圖所示，慢查詢日誌中會記錄每次慢查詢發生的時間、執行查詢時的資料庫用戶、線程id、查詢執行的SQL語句等信息。

本文作者：張永清， 轉載請註明： https://www.cnblogs.com/laoqing/p/16880718.html  來源於博客園 ,本文摘選自《軟體性能測試分析與調優實踐之路》

在獲取到慢查詢的SQL語句後，就可以藉助資料庫的執行計劃來對慢查詢的SQL語句做進一步分析了。

 2.2、執行計劃

在MySQL中使用explain關鍵字可以模擬查看資料庫是如何來執行SQL查詢語句，也就是常說的查看一條SQL語句在資料庫中的執行計劃，如圖6-2-8所示就是執行EXPLAIN

本文作者：張永清， 轉載請註明： https://www.cnblogs.com/laoqing/p/16880718.html  來源於博客園 ,本文摘選自《軟體性能測試分析與調優實踐之路》

SELECT * FROM  test.test 後返回的SELECT * FROM  test.test查詢的執行計劃。

查詢結果返回的欄位說明如表所示 

表查詢結果返回的欄位說明