MySQL Performance Schema

1.  Performance Schema Lock Tables

MySQL安裝以後，我們會看到有這麼兩個資料庫：information_schema 和 performance_schema ，它們對於排查問題是非常有用的。

Performance Schema 是一種存儲引擎，預設情況下，它是啟用的。

performance_schema資料庫的名稱是小寫的，其中的表的名稱也是小寫的。查詢應該用小寫字母指定名稱。

SHOW VARIABLES LIKE 'performance_schema';

SHOW ENGINES;

USE performance_schema;

SHOW TABLES;

SHOW TABLES FROM performance_schema;

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema';

show variables like 'autocommit';

Performance Schema 通過下麵這些表來公開鎖的信息：

• data_locks ：持有和請求的數據鎖
• data_lock_waits ：數據鎖的擁有者與被這些擁有者阻塞的請求者之間的關係
• metadata_locks ：持有和請求的元數據鎖
• table_handles ：持有和請求的表鎖

1.1.  data_locks表

data_locks表顯示持有和請求的數據鎖。它包含以下欄位：

information_schema.INNODB_LOCKS 與 performance_schema.data_locks 表的區別：

• 如果一個事務持有一個鎖，INNODB_LOCKS只在另一個事務正在等待它的時候顯示這個鎖。而data_locks不管是否有任何事務正在等待它都顯示這個鎖。
• 與INNODB_LOCKS相比，data_locks表沒有LOCK_SPACE、LOCK_PAGE、LOCK_REC這幾列。
• INNODB_LOCKS表需要全局的PROCESS許可權。data_locks表需要在要選擇的表上使用通常的SELECT許可權。

INNODB_LOCKS到data_locks列的映射：

1.2.  data_lock_waits表

data_lock_waits表實現了一種多對多關係，顯示了data_locks表中的哪些數據鎖請求被data_locks表中持有的哪些數據鎖阻塞。data_locks中的持有鎖只有在阻塞了某些鎖請求時才會出現在data_lock_waits中。

這些信息使得我們能夠瞭解會話之間的數據鎖依賴關係。該表不僅顯示會話或事務正在等待的鎖，還顯示當前持有該鎖的會話或事務。

INNODB_LOCK_WAITS表需要全局的PROCESS許可權，data_lock_waits表只需要在選擇的表上擁有SELECT許可權即可。

2.  InnoDB 事務和鎖信息

一個 INFORMATION_SCHEMA 表和兩個 Performance Schema 表可以監視InnoDB事務並診斷潛在的鎖問題。

• INNODB_TRX ：這個INFORMATION_SCHEMA表提供了當前在InnoDB內執行的每個事務的信息，包括事務狀態(例如，它是正在運行還是正在等待鎖)，事務何時啟動，以及事務正在執行的特定SQL語句。
• data_locks ：這個Performance Schema表為每個持有鎖和每個等待持有鎖被釋放的鎖請求包含一行：
  • 無論持有鎖的事務的狀態（RUNNING, LOCK WAIT, ROLLING BACK, COMMITTING）如何，每個持有的鎖都有一行
  • InnoDB中的每個事務都在等待另一個事務釋放鎖(INNODB_TRX.TRX_STATE 是 LOCK WAIT) 
• data_lock_waits ：此Performance Schema表指示哪些事務正在等待給定的鎖，或者給定事務正在等待哪個鎖

2.1.  INNODB_TRX表

INNODB_TRX表提供了當前在InnoDB內部執行的每個事務的信息，包括事務是否正在等待鎖，事務何時啟動，以及事務正在執行的SQL語句(如果有的話)。

2.2.  識別阻塞事務

使用以下查詢查看哪些事務正在等待，哪些事務正在阻塞它們

SELECT 
  r.trx_id waiting_trx_id, 
  r.trx_mysql_thread_id waiting_thread, 
  r.trx_query waiting_query, 
  b.trx_id blocking_trx_id, 
  b.trx_mysql_thread_id blocking_thread, 
  b.trx_query blocking_query 
FROM 
  performance_schema.data_lock_waits w 
  INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id 
  INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

或者，更簡單的方式，直接看sys資料庫中的 innodb_lock_waits 視圖

SELECT 
    waiting_trx_id,
    waiting_pid,
    waiting_query,
    blocking_trx_id,
    blocking_pid,
    blocking_query
FROM
    sys.innodb_lock_waits;

如果阻塞查詢被報告是NULL值，即查詢sys.innodb_lock_waits的結果集中blocking_query欄位值為NULL，在這種情況下，使用以下步驟來確定阻塞查詢：

第1步、確定阻塞事務的processlist ID。在sys.innodb_lock_waits表中，阻塞事務的processlist ID就是blocking_pid欄位的值

第2步、用blocking_pid去performance_schema.threads表中查阻塞事務的線程ID

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 9;

第3步、用THREAD_ID去查performance_schema.events_statements_current表，以確定線程執行的最後一次查詢

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 50;

第4步、如果線程執行的最後一個查詢沒有足夠的信息來確定為什麼持有鎖，我們還可以查詢performance_schema.events_statements_history表來查看線程執行的最後10條語句

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID = 50 ORDER BY EVENT_ID;

2.3.  InnoDB鎖和鎖等待信息

當一個事務更新表中的一行，或者用SELECT FOR UPDATE鎖定該行時，InnoDB會在該行上建立一個鎖列表或鎖隊列。類似地，InnoDB在表上維護一個表級鎖的鎖列表。如果第二個事務想以不相容的方式更新一個被前一個事務鎖住的行或鎖住一個表，InnoDB就會向相應的隊列中添加一個鎖請求。為了讓事務獲得鎖，必須刪除先前進入該行或表的鎖隊列的所有不相容的鎖請求（當持有或請求這些鎖的事務提交或回滾時發生）。

一個事務可以對不同的行或表有任意數量的鎖請求。在任何給定的時間，一個事務可能請求另一個事務持有的鎖，在這種情況下，它被另一個事務阻塞。請求事務必須等待持有鎖的事務提交或回滾。如果事務沒有等待鎖，則處於RUNNING狀態。如果事務正在等待鎖，則處於LOCK WAIT狀態。

Performance Schema的data_locks表為每個LOCK WAIT事務保存一個或多個行，表示任何阻止其進行的鎖請求。這個表還包含一行，描述了為給定行或表掛起的鎖隊列中的每個鎖。data_lock_waits表顯示了某個事務已經持有的哪些鎖正在阻塞其他事務請求的鎖。

3.  排序索引構建

3.1.  B-tree

在資料庫索引中常用的一種樹狀數據結構。該結構始終保持排序，支持精確匹配(等於操作符)和範圍(例如大於、小於和BETWEEN操作符)的快速查找。這種類型的索引適用於大多數存儲引擎，如InnoDB和MyISAM。

因為B-tree節點可以有很多子節點，所以B-tree與二叉樹不同，二叉樹每個節點只能有2個子節點。

MySQL存儲引擎使用的B-tree結構可能被視為變體，因為在經典的B-tree設計中不存在複雜的結構。

3.2.  索引構建

InnoDB在創建或重建索引時執行批量載入，而不是一次插入一條索引記錄。這種創建索引的方法也稱為排序索引構建。

在引入排序索引構建之前，使用插入api將索引條目插入b樹，每次插入一條記錄。該方法涉及打開b樹游標以查找插入位置，然後使用樂觀插入將條目插入到b樹頁面中。如果由於頁已滿而導致插入失敗，則執行悲觀插入，這涉及打開b -樹游標，並根據需要拆分和合併b -樹節點，以便為條目找到空間。這種“自頂向下”構建索引的方法的缺點是搜索插入位置的成本，以及b樹節點的不斷拆分和合併。

排序索引構建使用“自底向上”的方法來構建索引。使用這種方法，對最右邊的葉子頁的引用將保存在b樹的所有級別上。在必要的b樹深度處分配最右邊的葉子頁，並根據它們的排序順序插入條目。一旦一個葉頁填滿，就會向父頁追加一個節點指針，併為下一次插入分配一個同級葉頁。這個過程一直持續到所有條目都被插入，這可能導致插入到根級別。當分配兄弟頁時，對先前固定的葉頁的引用將被釋放，新分配的葉頁將成為最右邊的葉頁和新的預設插入位置。

4. 文檔

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-lock-waits-table.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-indexes.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html