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表顯示持有和請求的資料鎖。它包含以下欄位：
| ENGINE | 持有或請求鎖的儲存引擎 |
| ENGINE_LOCK_ID | 儲存引擎持有或請求的鎖的ID。元組(ENGINE_LOCK_ID, ENGINE)的值是唯一的 |
| ENGINE_TRANSACTION_ID | 事務在儲存引擎裡面的內部ID。對於InnoDB，為了獲取詳細資訊，還需要關聯INFORMATION_SCHEMA.INNODB_TRX表的TRX_ID列。 |
| THREAD_ID | 建立鎖的對談的執行緒ID。為了獲取執行緒的詳細資訊，需要關聯performance_schema.threads表的THREAD_ID列。 |
| EVENT_ID | 造成鎖的Performance Schema事件。元組(THREAD_ID, EVENT_ID)的值標識了Performance Schema表中的父事件。 |
| OBJECT_SCHEMA | 包含鎖的表 |
| OBJECT_NAME | 被鎖定表的名稱 |
| PARTITION_NAME | 被鎖定分割區的名稱 |
| SUBPARTITION_NAME | 被鎖定的子分割區的名稱 |
| INDEX_NAME | 被鎖定的索引的名稱 |
| OBJECT_INSTANCE_BEGIN | 鎖在記憶體中的地址 |
| LOCK_TYPE | 鎖的型別（該值取決於儲存引擎） |
| LOCK_MODE | 如何請求鎖（該值取決於儲存引擎） |
| LOCK_STATUS | 鎖請求的狀態（該值取決於儲存引擎） |
| LOCK_DATA | 與鎖相關的資料(如果有的話)。該值依賴於儲存引擎。對於InnoDB，如果LOCK_TYPE為RECORD，則顯示一個值，否則為NULL。對於放置在主鍵索引上的鎖，顯示鎖定記錄的主鍵值。鎖定記錄的二級索引值顯示為附加在二級索引上的鎖的主鍵值。如果沒有主鍵，LOCK_DATA將根據InnoDB叢集索引的使用規則顯示所選唯一索引的鍵值或唯一的InnoDB內部行ID號。 |
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列的對映：
| INNODB_LOCKS 列 | data_locks 列 |
|---|---|
| LOCK_ID | ENGINE_LOCK_ID |
| LOCK_TRX_ID | ENGINE_TRANSACTION_ID |
| LOCK_MODE | LOCK_MODE |
| LOCK_TYPE | LOCK_TYPE |
| LOCK_TABLE | OBJECT_SCHEMA , OBJECT_NAME |
| LOCK_INDEX | INDEX_NAME |
| LOCK_SPACE | None |
| LOCK_PAGE | None |
| LOCK_REC | None |
| LOCK_DATA | LOCK_DATA |
1.2. data_lock_waits表
data_lock_waits表實現了一種多對多關係，顯示了data_locks表中的哪些資料鎖請求被data_locks表中持有的哪些資料鎖阻塞。data_locks中的持有鎖只有在阻塞了某些鎖請求時才會出現在data_lock_waits中。
這些資訊使得我們能夠了解對談之間的資料鎖依賴關係。該表不僅顯示對談或事務正在等待的鎖，還顯示當前持有該鎖的對談或事務。
| ENGINE | 請求鎖的儲存引擎 |
| REQUESTING_ENGINE_LOCK_ID | 儲存引擎請求的鎖ID。為了獲得鎖的詳細資訊，需要關聯data_locks表的ENGINE_LOCK_ID列。 |
| REQUESTING_ENGINE_TRANSACTION_ID | 請求鎖的事務的儲存引擎內部ID |
| REQUESTING_THREAD_ID | 請求鎖的對談的執行緒ID |
| REQUESTING_EVENT_ID | 請求鎖的對談中引起鎖請求的事件 |
| REQUESTING_OBJECT_INSTANCE_BEGIN | 請求的鎖在記憶體中的地址 |
| BLOCKING_ENGINE_LOCK_ID | 阻塞鎖的ID。為了獲取鎖的詳細資訊，需要關聯data_locks表的ENGINE_LOCK_ID列。 |
| BLOCKING_ENGINE_TRANSACTION_ID | 持有阻塞鎖的事務的儲存引擎內部ID |
| BLOCKING_THREAD_ID | 持有阻塞鎖的對談的執行緒ID |
| BLOCKING_EVENT_ID | 造成阻塞鎖的事件 |
| BLOCKING_OBJECT_INSTANCE_BEGIN | 阻塞鎖在記憶體中的地址 |
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語句(如果有的話)。
| TRX_ID | InnoDB內部唯一的事務ID號 |
| TRX_WEIGHT | 事務的權重，反映(但不一定是確切的計數)被更改的行數和被事務鎖定的行數。為了解決死鎖，InnoDB選擇權值最小的事務作為回滾的「受害者」。 |
| TRX_STATE | 事務執行狀態。允許的值有：RUNNING, LOCK WAIT, ROLLING BACK, COMMITTING |
| TRX_STARTED | 事務開始時間 |
| TRX_REQUESTED_LOCK_ID | 如果TRX_STATE為 LOCK WAIT，則表示事務當前正在等待的鎖的ID，否則無效。 |
| TRX_WAIT_STARTED | 如果TRX_STATE為 LOCK WAIT，則表示事務開始等待鎖的時間，否則無效。 |
| TRX_MYSQL_THREAD_ID | MySQL執行緒ID |
| TRX_QUERY | 事務正在執行的SQL語句 |
| TRX_OPERATION_STATE | 事務的當前操作(如果有的話)，否則無效 |
| TRX_TABLES_IN_USE | 在處理該事務SQL語句使用的InnoDB表的數量 |
| TRX_TABLES_LOCKED | 當前SQL語句加行鎖的表的個數 |
| TRX_LOCK_STRUCTS | 事務保留的鎖的數量 |
| TRX_LOCK_MEMORY_BYTES | 事務的鎖結構在記憶體中佔用的總大小 |
| TRX_ROWS_LOCKED | 事務鎖定的大致行數 |
| TRX_ROWS_MODIFIED | 事務中修改和插入的行數 |
| TRX_CONCURRENCY_TICKETS | 一個值，指示當前事務在被換出之前可以完成多少工作，由innodb_concurrency_tickets系統變數指定 |
| TRX_ISOLATION_LEVEL | 當前事務的隔離級別 |
| TRX_UNIQUE_CHECKS | 是否為當前事務開啟或關閉惟一檢查 |
| TRX_FOREIGN_KEY_CHECKS | 當前事務的外來鍵檢查是否開啟或關閉 |
| TRX_LAST_FOREIGN_KEY_ERROR | 最後一個外來鍵錯誤的詳細錯誤訊息 |
| TRX_IS_READ_ONLY | 1表示事務為唯讀 |
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