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查詢的執行計劃，

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

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

標籤：其他

上一篇：Mysql資料庫函式-函式的用法

下一篇：TDSQL-C 真·秒級啟停：連接斷了，又沒斷