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

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

2022-11-11 18:02:51

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表中包含的常用欄位說明

欄位

描述

trx_id

事務ID

trx_state

事務的狀態，一般包括RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING著幾種不同的狀態

trx_started

事務開始執行的時間

trx_requested_lock_id

事務需要等待的但已經被別的程式鎖定的資源id，一般可以和INNODB_LOCKS表關聯在一起獲取更多的被鎖定的資源的詳細資訊

trx_wait_started

事務開始等待時間

trx_mysql_thread_id

事務對應的MySQL執行緒id

trx_query

事務正在執行的SQL語句

trx_operation_state

事務操作的狀態

trx_tables_in_use

事務使用到的資料庫表的數量

trx_tables_locked

事務鎖定的資料庫表的數量

trx_rows_locked

事務鎖定的資料記錄行數

trx_rows_modified

事務更改的資料記錄行數

trx_unique_checks

事務是否開啟唯一性檢查的標識

 

trx_foreign_key_checks

事務是否開啟外來鍵檢查的標識

 

trx_isolation_level

事務隔離級別，一般分為Read Uncommitted(未提交讀取)、Read Committed（已提交讀取）、Repeatable Read（可重複讀取）、Serializable（序列化）四種不同的級別

trx_weight

事務的權重

trx_lock_memory_bytes

事務鎖住的記憶體大小，單位為 位元組

trx_concurrency_tickets

事務並行票數

trx_last_foreign_key_error

 

事務最後一次的外來鍵檢查的錯誤資訊

 

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

欄位

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

lock_id

鎖的id

lock_trx_id

擁有鎖的事務 ID。可以和 INNODB_TRX 表關聯查詢得到事務的詳細資訊

lock_mode

鎖的模式，鎖的模式一般包含：

行級鎖：包括S（共用鎖）、X（排它鎖）、IS（意向共用鎖）、IX（意向排它鎖）。

表級鎖：包括S_GAP（共用間隙鎖）、X_GAP（排它間隙鎖）、IS_GAP（意向共用間隙鎖）、IX_GAP（意向排它間隙鎖） 和 AUTO_INC（自動遞增鎖）

lock_type

鎖的型別。包括RECORD（行級鎖）和TABLE（表級鎖）

lock_table

當前被鎖定的或者包含鎖定記錄的表的名稱

lock_index

當 LOCK_TYPE為RECORD 時，表示鎖定的索引的名稱，否則直接返回NULL

lock_space

當 LOCK_TYPE為RECORD 時，表示鎖定行的表空間 ID，否則直接返回NULL

lock_page

當 LOCK_TYPE為RECORD時，表示鎖定記錄行的頁數，否則直接返回NULL

lock_rec

當 LOCK_TYPE為RECORD時，表示鎖定的資料行的數量

lock_data

當 LOCK_TYPE為RECORD時，表示鎖定記錄行的主鍵

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

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

欄位

描述

requesting_trx_id

請求事務的 ID

Requested_lock_id

事務所等待的鎖定的 ID。可以和 INNODB_LOCKS 表關聯查詢

Blocking_trx_id

阻塞事務的 ID

Blocking_lock_id

阻塞了另一事務的執行的事務的鎖的 ID

在資料庫中出現死鎖時，經常需要通過查詢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%'，如圖所示，查詢的結果資料也可以用來評估資料庫中索引的使用情況。查詢的結果資料說明如表所示。

 

查詢結果項

描述

Handler_read_first

從索引中讀取第一項的次數，如果該值非常高，表明伺服器正在執行大量的全索引掃描，該值一般不宜太高

Handler_read_key

基於鍵讀取資料行的請求數，該值如果越高則表明大量的查詢都走了索引。如果越低，表示索引的利用很低，該值一般越高越好

Handler_read_last

讀取索引中最後一個鍵的請求數

Handler_read_next

按鍵順序讀取下一行的請求數，如果查詢都走了索引，那麼該值將不斷遞增

Handler_read_prev

按鍵順序讀取前一行的請求數（倒序讀取資料），一般用於評估執行ORDER BY … DESC的次數

Handler_read_rnd

基於固定位置讀取資料行的請求數，如果正在執行大量的需要對查詢結果進行排序的查詢，則此值很高。如果該值很高，則可能存在很多查詢需要做整表掃描或者查詢時一些表的關聯連線沒有正確使用主鍵或者索引

Handler_read_rnd_deleted

從資料庫資料檔案中讀取被刪除記錄行的請求數

Handler_read_rnd_next

從資料庫資料檔案中讀取下一行的請求數，如果SQL語句執行大量表掃描，則此值很高。如果該值很高，一般說明表沒有正確新增索引或者SQL語句沒有走索引來查詢

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

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

 

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

 

  •  3、檢視執行緒的執行情況：show global status like 'threads_%'，如圖所示。

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

表查詢結果描述說明

查詢結果項

描述

Threads_cached

執行緒快取中的執行緒數

Threads_connected

已經建立連線的執行緒數

Threads_created

已經建立的執行緒數

Threads_running

正在執行中的執行緒數
  • 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查詢的執行計劃。

 

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

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

欄位

說明

id

查詢的順序編號，表示查詢中執行的順序，id的值越大執行的優先順序越高，如果id相同，則從上往下執行

select_type

查詢型別，常見查詢型別如下：

SIMPLE：表示簡單查詢方式，SQL語句中一般不會不使用UNION和子查詢等

PRIMARY：表示當查詢中包含子查詢時，最外層的查詢語句則被標記為primary 。

UNION：查詢語句中如果在UNION關鍵字之後出現了第二個SELECT，則被標記為UNION。

UNION RESULT：表示查詢中有多個查詢結果集做UNION操作。

DEPENDENT UNION：表示子查詢中存在UNION操作時，從UNION之後的第二個及之後的SELECT語句都是DEPENDENT UNION。

DEPENDENT SUBQUERY：子查詢中UNION 中第一個SELECT查詢為DEPENDENT SUBQUERY。

SUBQUERY：子查詢內層查詢的第一個SELECT。

DERIVED：查詢語句中如果from子句的子查詢中出現了union關鍵字則外層select查詢將被標記為DERIVED。

MATERIALIZED：表示子查詢被物化。

UNCACHEABLE SUBQUERY：表示查詢結果集無法快取的子查詢，需要逐次查詢。

UNCACHEABLE UNION：表示子查詢不可被物化，需要逐次執行

Table

查詢涉及的表名或者表的別名

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

Type

表示表連線的型別，包括的型別如下所示，如下的這些型別的效能從高到低的順序是：null→system→const→eq-ref→ref→fulltext→ref_or_null→index_merge→unique_subquery→index_subquery→range→index→ALL

null：表示不存取任何的表

system：表示表中只有一條記錄，相當於系統表，一般可以認為是const型別的特例。

const：表示主鍵或者唯一索引的常數查詢，表中最多隻有1行記錄符合查詢要求，通常const使用到主鍵或者唯一索引進行定值查詢、常數查詢，查詢的速度非常快。

eq_ref：表示join 查詢過程中，關聯條件欄位走主鍵或者唯一索引，出來的行數不止一行。eq_ref是一種查詢效能很高的 join 操作。

ref：表示非聚集索引的常數查詢

fulltext：表示查詢的過程中，使用到了 fulltext 索引。

ref_or_null：跟ref查詢類似，在ref的查詢基礎上會多家一個null值的條件查詢

index merg：表示索引聯合查詢，

unique subquery：表示查詢走主鍵的子查詢

index subquery：表示查詢走非聚集索引的子查詢

range：表示查詢走索引範圍的查詢，一般包括：=、<>、>、>=、<、<=、IS NULL、BETWEEN、IN、<=> 等範圍。

index：表示通過索引做掃描查詢。

All：表示全表掃描，效能最差

possible_keys

查詢時預計可能會使用的索引，這裡說的索引只是可能會用到，實際查詢不一定會用到。

Key

實際查詢時真實使用的索引

key_len

使用的索引長度

Ref

關聯資訊

Rows

查詢時掃描的資料記錄行數

Extra

表示查詢特性的使用情況，常用的查詢特性如下所示。

Using index：表示使用了索引

Using index conditio：表示使用了索引做過濾

Using MRR：表示使用了索引做內部排序

Using where：表示使用了where條件

Using temporary：表示使用了臨時表。

Using filesort：表示使用檔案排序，一般指無法利用索引來完成的排序
本文作者：張永清， 轉載請註明： https://www.cnblogs.com/laoqing/p/16880718.html  來源於部落格園 ,本文摘選自《軟體效能測試分析與調優實踐之路》