從一個死鎖問題分析最佳化器特性

作者透過一個死鎖案例結合OPTIMIZER TRACE，對 MySQL 5.7 的索引成本計算、索引選擇以及 ICP 特性進行了分析。

作者：李錫超，一個愛笑的江蘇蘇寧銀行 資料庫工程師，主要負責資料庫日常運維、自動化建設、DMP 平臺運維。擅長 MySQL、Python、Oracle，愛好騎行、研究技術。

愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

本文約 2100 字，預計閱讀需要 7 分鐘。

問題現象

自發布了 INSERT 併發死鎖問題的文章，收到了多次死鎖問題的交流。一個具體案例如下：

研發反饋應用發生死鎖，收集如下診斷內容：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-07-04 06:02:40 0x7fc07dd0e700
*** (1) TRANSACTION:
TRANSACTION 182396268, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 21 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1
MySQL thread id 59269692, OS thread handle 140471135803136, query id 3738514953 192.168.0.215 user1 updating
delete from ltb2 where c = 'CCRSFD07E' and j = 'Y15' and b >= '20230717' and d != '1' and e != '1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2` trx id 182396268 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 182396266, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 1729
mysql tables in use 1, locked 1
28 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1
MySQL thread id 59261188, OS thread handle 140464721291008, query id 3738514964 192.168.0.214 user1 updating
update ltb2 set f = '0', g = '0', is_value_date = '0', h = '0', i = '0' where c = '22115001B' and j = 'Y4' and b >= '20230717'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2` trx id 182396266 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2` trx id 182396266 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

以上 space id 603 page no 86 n bits 248，其中 space id 表示表空間 ID，page no 表示記錄鎖在表空間內的哪一頁，n bits 是鎖點陣圖中的位數，而不是頁面偏移量。記錄的頁偏移量一般以 heap no 的形式輸出，但此例並未輸出該資訊。

基本環境資訊

確認如下問題相關資訊：

• 資料庫版本：Percona MySQL 5.7
• 事務隔離級別：Read-Commited
• 表結構和索引：

關鍵資訊梳理

可以看到在主鍵索引上發生了死鎖，但是在查詢的條件中，並未使用主鍵列。

那為什麼會在主鍵列出現死鎖？ 在分析死鎖根因問題前，需要先清楚 SQL 的執行情況。

SQL 執行情況

執行計劃

以上兩個 SQL 發現都有列 b、c 作為條件，且該列構成了索引唯一索引 uidx_1。簡化 SQL 改為查詢語句，並確認執行計劃：

注意：自 MySQL 5.6 開始可以直接檢視 UPDATE/DELETE/INSERT 等語句的執行計劃。因個人習慣、避免誤操作等原因，還是習慣改為 SELECT 檢視執行計劃。

執行計劃中可能的索引有 uidx_1(b,c)，但實際並未使用該索引，而是採用全表掃描方式執行。

根據經驗，由於列 b 為索引的最左列。但查詢的條件為 b>= '20230717'，即該條件不是等值查詢。因此資料庫可能只能“使用”到 b 列。為進一步確認不使用 b 列索引的原因，查詢資料分佈：

mysql> select count(1) from ltb2;

+------------+
| count(1) | 
+------------+
|     4509 |
+------------+

mysql> select count(1) from ltb2 where b >= '20230717' ;

+------------+
| count(1) | 
+------------+
|     1275 |
+------------+

計算滿足 b 列條件的資料佔比為 1275/4509 = 28%，佔比差不多達到了 1/3。此時也的確不應使用該使用索引。

難道已經是作為 MySQL 5.7 的資料庫，最佳化器還是這麼簡單？

ICP 特性

帶著問題，將條件設定一個更大的值（但小於該列的最大值），再次執行驗證查詢語句：

mysql> desc select * from ltb2 where b >= '20990717';

# 部分結果
+----------+---------+---------+
| key_len | rows | Extra |
+----------+---------+---------+
| 3      | 64   | Using Index condition |
+----------+---------+---------+

最佳化器預估返回 64 行，資料佔比 64/4509 = 1.4%，因此可以使用索引。但透過執行計劃，從 Extra 列看到 Using index condition 提示。該提示則說明使用了索引條件下推（Index Condition Pushdown, ICP）。針對該特性，參考官方簡要說明如下：

使用 Index Condition Pushdown，掃描將像這樣進行：

1. 獲取下一行的索引元組（但不是完整的錶行）。
2. 測試 WHERE 條件中應用於此表的部分，並且只能使用索引列的進行檢查。如果不滿足條件，則繼續到下一行的索引元組。
3. 如果滿足條件，則使用索引元組定位並讀取整個錶行。
4. 測試適用於此表的 WHERE 條件的其餘部分。根據測試結果接受或拒絕該行。

既然可以使用到 ICP 特性，進一步執行如下驗證語句：

mysql> desc select * from ltb2 where b >= '20990717' and c = 'code001';

# 部分結果
+----------+---------+---------+
| key_len | rows | Extra |
+----------+---------+---------+
| 133     | 64   | Using Index condition |
+----------+---------+---------+

發現當新增 c 列作為條件後，並且根據 key_len（索引裡使用的位元組數）可以判斷，的確使用到了 uidx_1 索引中的 c 列。但 rows 的結果與實際返回結果差異較大（實際執行僅返回 0 行）。

更重要的是，既然具有 ICP 特性，針對原始的 SQL 為什麼不能助於 ICP 特性使用到索引呢？

mysql> select * from ltb2 where b >= '20230717' and c = 'code001'

執行計劃跟蹤

繼續帶著問題，透過 MySQL 提供的 OPTIMIZER TRACE，跟蹤執行計劃生成過程。命令如下：

由於分析結果較長，擷取 SQL-1 和 SQL-2 的部分結果 (rows_estimation 和 considered_execution_plans)。具體內容如下：

SQL-1

根據以上資訊：兩個 SQL 的 cost 部分是完全相同的，且在最佳化器分析階段只能識別到 b 的條件。分析階段，只能根據最佳化器認為可用的列來計算 cost。ICP 特性，應該是在執行階段採用用到的特性。

同時，根據 SQL-3 的執行跟蹤結果，對比全表掃描和索引掃描的 cost，擷取部分結果如下：

SQL-3

同時，根據執行計劃的輸出結果，rows 列應該是最佳化器階段的輸出，key_len/Extra 則包括了執行階段的輸出。

小結

綜上所述，對於問題 SQL 和索引結構，由於列 b 為索引的最左列，且查詢時的條件為 b>= '20230717'（非等值條件），資料庫最佳化器只能“使用”到 b 列。並給予“使用”的列，評估掃碼的行數和 cost。

如果最佳化器評估後，使用索引的成本更低，則可以使用該索引，並利用 ICP 特性進一步提高查詢效能；

如果最佳化器評估後，使用全表掃描或的成本更低，那資料庫就會選擇使用全表掃描。

SQL 最佳化方案

根據第 2 部分明確了問題的原因後，透過調整索引，解決最左列尾範圍查詢的問題即可解決該問題。具體如下：

alter table ltb2 drop index uidx_1;
alter table ltb2 add index uidx_1(c,b);
alter table ltb2 add index idx_(b);

死鎖為何發生

自此，完成了 SQL 執行計劃問題的分析和解決。但直接的問題是死鎖，因查詢語句無法使用索引，正常就應該使用全表掃描。但是全表掃描為什麼會出現死鎖呢？

在此，參考《故障分析 | 從 Insert 併發死鎖分析 Insert 加鎖原始碼邏輯》的經驗，對死鎖過程進行大膽猜想：

T1 時刻

trx-2 執行了 UPDATE，在處理行時，在 row_search_mvcc 函式中，查詢到資料。獲取了對應行的 LOCK_X,LOCK_REC_NOT_GAP 鎖；

T2 時刻

trx-1 執行了 DELETE，在處理行時，在 row_search_mvcc 函式中，查詢到資料，嘗試獲取行的 LOCK_X,LOCK_REC_NOT_GAP。但由於 trx-1 已經持有了該鎖，因此被堵塞。並會建立一個鎖（以指示鎖等待）；

T3 時刻

trx-2 繼續執行 UPDATE 操作。由於是該操作除了在 T1 時刻的操作外，在其它位置，還需要獲取鎖（lock_mode X locks rec but not gap）。但由於 T2 時刻，trx-1 嘗試獲取該鎖而被堵塞，並且也增加了一個鎖。

假如此時，此處的實現機制和 INSERT 死鎖案例一樣，也沒有先進行衝突檢查。而只是看記錄上是否存在鎖的話，那麼此時也會看到該記錄上有 trx-1 事務的鎖。從而導致 trx-2 第二次獲取鎖時，被堵塞。

死鎖發生！

以上僅根據經驗進行的猜想，真正的原因還需要進一步分析和驗證。有興趣的讀者結合如下幾個問題，進一步研究。

1. 以上各步驟獲取鎖的位置，是否正確？
2. T3 時刻，update操作在其它的什麼位置再次獲取了鎖？
3. T3 時刻，發起的假設是否成立？如成立，具體邏輯是什麼？不成立，那正確的邏輯是什麼？
4. T3 時刻，如果假設不成立，那死鎖的原因又是什麼？
5. 以上都是針對於唯一索引/主鍵索引的執行邏輯分析的。那結合該案例，全表掃描和索引查詢的執行邏輯是否存在差異？差異的地方在哪裡？
6. 除了調整索引，還能透過什麼方式避免該問題發生？