MySQL 5.7的數(shù)據(jù)庫優(yōu)化器還是這么簡單？

1 問題現(xiàn)象

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

研發(fā)反饋應(yīng)用發(fā)生死鎖，收集如下診斷內(nèi)容：

------------------------
LATESTDETECTEDDEADLOCK
------------------------
2023-07-0406400x7fc07dd0e700
***(1)TRANSACTION:
TRANSACTION182396268,ACTIVE0secfetchingrows
mysqltablesinuse1,locked1
LOCKWAIT21lockstruct(s),heapsize3520,2rowlock(s),undologentries1
MySQLthreadid59269692,OSthreadhandle140471135803136,queryid3738514953192.168.0.215user1updating
deletefromltb2wherec='CCRSFD07E'andj='Y15'andb>='20230717'andd!='1'ande!='1'
***(1)WAITINGFORTHISLOCKTOBEGRANTED:
RECORDLOCKSspaceid603pageno86nbits248indexPRIMARYoftable`testdb`.`ltb2`trxid182396268lock_modeXlocksrecbutnotgapwaiting
***(2)TRANSACTION:
TRANSACTION182396266,ACTIVE0secfetchingrows,threaddeclaredinsideInnoDB1729
mysqltablesinuse1,locked1
28lockstruct(s),heapsize3520,2rowlock(s),undologentries1
MySQLthreadid59261188,OSthreadhandle140464721291008,queryid3738514964192.168.0.214user1updating
updateltb2setf='0',g='0',is_value_date='0',h='0',i='0'wherec='22115001B'andj='Y4'andb>='20230717'
***(2)HOLDSTHELOCK(S):
RECORDLOCKSspaceid603pageno86nbits248indexPRIMARYoftable`testdb`.`ltb2`trxid182396266lock_modeXlocksrecbutnotgap
***(2)WAITINGFORTHISLOCKTOBEGRANTED:
RECORDLOCKSspaceid603pageno86nbits248indexPRIMARYoftable`testdb`.`ltb2`trxid182396266lock_modeXlocksrecbutnotgapwaiting
***WEROLLBACKTRANSACTION(1)
------------

以上 space id 603 page no 86 n bits 248，其中 space id 表示表空間 ID，page no 表示記錄鎖在表空間內(nèi)的哪一頁，n bits 是鎖位圖中的位數(shù)，而不是頁面偏移量。記錄的頁偏移量一般以 heap no 的形式輸出，但此例并未輸出該信息。

基本環(huán)境信息

確認(rèn)如下問題相關(guān)信息：

數(shù)據(jù)庫版本：Percona MySQL 5.7

事務(wù)隔離級別：Read-Commited

表結(jié)構(gòu)和索引：

CREATETABLE`ltb2`(
`ID`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'ID',
`j`varchar(16)DEFAULTNULLCOMMENT'',
`c`varchar(32)NOTNULLDEFAULT''COMMENT'',
`b`dateNOTNULLDEFAULT'2019-01-01'COMMENT'',
`f`varchar(1)NOTNULLDEFAULT''COMMENT'',
`g`varchar(1)NOTNULLDEFAULT''COMMENT'',
`d`varchar(1)NOTNULLDEFAULT''COMMENT'',
`e`varchar(1)NOTNULLDEFAULT''COMMENT'',
`h`varchar(1)NOTNULLDEFAULT''COMMENT'',
`i`varchar(1)DEFAULTNULLCOMMENT'',
`LAST_UPDATE_TIME`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改時間',
PRIMARYKEY(`ID`),
UNIQUEKEY`uidx_1`(`b`,`c`)
)ENGINE=InnoDBAUTO_INCREMENT=270983DEFAULTCHARSET=utf8mb4COMMENT='';

關(guān)鍵信息梳理

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

那為什么會在主鍵列出現(xiàn)死鎖？

在分析死鎖根因問題前，需要先清楚 SQL 的執(zhí)行情況。

2 SQL 執(zhí)行情況

執(zhí)行計劃

以上兩個 SQL 發(fā)現(xiàn)都有列 b、c 作為條件，且該列構(gòu)成了索引唯一索引 uidx_1。簡化 SQL 改為查詢語句，并確認(rèn)執(zhí)行計劃：

mysql>descselect*fromltb2whereb>='20230717'andc='code001';

#部分結(jié)果
+------+-------------------+------+---------+
|type|possible_keys|key|Extra|
+------+-------------------+------+---------+
|ALL|uidx_1|NULL|Usingwhere|
+------+-------------------+------+---------+

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

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

根據(jù)經(jīng)驗，由于列 b 為索引的最左列。但查詢的條件為b>= '20230717'，即該條件不是等值查詢。因此數(shù)據(jù)庫可能只能“使用”到 b 列。為進(jìn)一步確認(rèn)不使用 b 列索引的原因，查詢數(shù)據(jù)分布：

mysql>selectcount(1)fromltb2;

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

mysql>selectcount(1)fromltb2whereb>='20230717';

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

計算滿足 b 列條件的數(shù)據(jù)占比為 1275/4509 = 28%，占比差不多達(dá)到了 1/3。此時也的確不應(yīng)使用該使用索引。

難道已經(jīng)是作為 MySQL 5.7 的數(shù)據(jù)庫，優(yōu)化器還是這么簡單？

ICP 特性

帶著問題，將條件設(shè)置一個更大的值（但小于該列的最大值），再次執(zhí)行驗證查詢語句：

mysql>descselect*fromltb2whereb>='20990717';

#部分結(jié)果
+----------+---------+---------+
|key_len|rows|Extra|
+----------+---------+---------+
|3|64|UsingIndexcondition|
+----------+---------+---------+

優(yōu)化器預(yù)估返回 64 行，數(shù)據(jù)占比 64/4509 = 1.4%，因此可以使用索引。但通過執(zhí)行計劃，從Extra列看到Using index condition提示。該提示則說明使用了索引條件下推（Index Condition Pushdown, ICP）。針對該特性，參考官方簡要說明如下：

使用 Index Condition Pushdown，掃描將像這樣進(jìn)行：

獲取下一行的索引元組（但不是完整的表行）。

測試 WHERE 條件中應(yīng)用于此表的部分，并且只能使用索引列的進(jìn)行檢查。如果不滿足條件，則繼續(xù)到下一行的索引元組。

如果滿足條件，則使用索引元組定位并讀取整個表行。

測試適用于此表的 WHERE 條件的其余部分。根據(jù)測試結(jié)果接受或拒絕該行。

既然可以使用到 ICP 特性，進(jìn)一步執(zhí)行如下驗證語句：

mysql>descselect*fromltb2whereb>='20990717'andc='code001';

#部分結(jié)果
+----------+---------+---------+
|key_len|rows|Extra|
+----------+---------+---------+
|133|64|UsingIndexcondition|
+----------+---------+---------+

發(fā)現(xiàn)當(dāng)新增 c 列作為條件后，并且根據(jù) key_len（索引里使用的字節(jié)數(shù)）可以判斷，的確使用到了 uidx_1 索引中的 c 列。但 rows 的結(jié)果與實際返回結(jié)果差異較大（實際執(zhí)行僅返回 0 行）。

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

mysql>select*fromltb2whereb>='20230717'andc='code001'

執(zhí)行計劃跟蹤

繼續(xù)帶著問題，通過 MySQL 提供的 OPTIMIZER TRACE，跟蹤執(zhí)行計劃生成過程。命令如下：

SETOPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SETOPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
--sql-1:
select*fromltb2whereb>='20990717'andc='code001';
--sql-2:
select*fromltb2whereb>='20990717';
--sql-3
select*fromltb2whereb>='20230717'andc='code001';

SELECT*FROMINFORMATION_SCHEMA.OPTIMIZER_TRACEG
SEToptimizer_trace="enabled=off";

由于分析結(jié)果較長，截取 SQL-1 和 SQL-2 的部分結(jié)果 (rows_estimation 和 considered_execution_plans)。具體內(nèi)容如下：

SQL-1

select*fromltb2whereb>='20990717'andc='code001'

#分析結(jié)果
"analyzing_range_alternatives":{
"range_scan_alternatives":[
{
"index":"uidx_1",
"ranges":[
"0xe76610<=?b"
????????]?/*?ranges?*/,
????????"index_dives_for_eq_ranges":?true,
????????"rowid_ordered":?false,
????????"using_mrr":?false,
????????"index_only":?false,
????????"rows":64,
????????"cost":?77.81,
????????"chosen":?true
?????}
??]?/*?range_scan?alternatives?*/
}

"best_access_path":{
????"considered?access_paths":[
??????"rows_to_scan":?64,
??????"access_type":"range",
??????"range_details":{
????????"used?index";"uidx?1"
????????}?/*?range_details?*/,
??????"resulting_rows":?64,
??????"cost":?90.61,
??????"chosen":?true
????}
??]?/*?considered?access_paths?*/
}?/*?best?access_path?*/,

SQL-2

select*fromltb2whereb>='20990717'

#分析結(jié)果
"analyzing_range_alternatives":{
"range_scan_alternatives":[
{
"index":"uidx_1",
"ranges":[
"0xe76610<=?b"
????????]?/*?ranges?*/,
????????"index_dives_for_eq_ranges":?true,
????????"rowid_ordered":?false,
????????"using_mrr":?false,
????????"index_only":?false,
????????"rows":64,
????????"cost":?77.81,
????????"chosen":?true
?????}
??]?/*?range_scan?alternatives?*/
}

"considered?access_paths":[
??{
????"rows_to_scan":?64,
????"access_type":"range",
????"range_details":{
??????"used?index":"uidx_1"
??????}?/*?range_details?*/,
??????"resulting_rows":?64,
??????"cost":?90.61,
??????"chosen":?true
???}
]?/*?considered?access_paths?*/,

根據(jù)以上信息：兩個 SQL 的 cost 部分是完全相同的，且在優(yōu)化器分析階段只能識別到 b 的條件。分析階段，只能根據(jù)優(yōu)化器認(rèn)為可用的列來計算 cost。ICP 特性，應(yīng)該是在執(zhí)行階段采用用到的特性。

同時，根據(jù) SQL-3 的執(zhí)行跟蹤結(jié)果，對比全表掃描和索引掃描的 cost，截取部分結(jié)果如下：

SQL-3

select*fromltb2whereb>='20230717'andc='code001';

#全表掃描結(jié)果
"range_analysis":{
"table_scan":{
"rows":4669，
"cost":1018.9
}/*table_scan*/，

#索引掃描評估結(jié)果
"analyzing_range_alternatives":{
"range_scan_alternatives":[
{
"index":"uidx_1",
"ranges":[
"@xe7ce0f]<=?b"
????????]?/*?ranges?*/,
????????"index?dives_for_eq_ranges":?true,
????????"rowid_ordered":?false,
????????"using_mrr":?false,
????????"index_only":?false,
????????"?rows":?1273,
????????"cost":?1528.6,
????????"chosen":?false,
????????"cause":"cost"
??????}
??]?/*?range?scan_alternatives?*/,
??
#?最優(yōu)執(zhí)行計劃
"best_access_path":?{
??"considered?access_paths":[
????{
??????"rows_to_scan":?4669,
??????"access_type":"scan",
??????"resulting_rows":?4669,
??????"cost":?1016.8,
??????"chosen":?true
????}
??]?/*?considered?access_paths?*//*?best?access_path?*/
}

由于優(yōu)化器階段使用使用列 b，使用索引的成本高于全表掃描。那最終數(shù)據(jù)庫就會選擇使用全表掃描。除非應(yīng)用使用 hint 強(qiáng)制索引：

mysql>descselect*fromltb2FORCEINDEX(uidx_1)whereb>='20230717'andc='code001';

#部分結(jié)果
+----------+---------+---------+
|key_len|rows|Extra|
+----------+---------+---------+
|133|1273|UsingIndexcondition|
+----------+---------+---------+

同時，根據(jù)執(zhí)行計劃的輸出結(jié)果，rows 列應(yīng)該是優(yōu)化器階段的輸出，key_len/Extra 則包括了執(zhí)行階段的輸出。

小結(jié)

綜上所述，對于問題 SQL 和索引結(jié)構(gòu)，由于列 b 為索引的最左列，且查詢時的條件為 b>= '20230717'（非等值條件），數(shù)據(jù)庫優(yōu)化器只能“使用”到 b 列。并給予“使用”的列，評估掃碼的行數(shù)和 cost。

如果優(yōu)化器評估后，使用索引的成本更低，則可以使用該索引，并利用 ICP 特性進(jìn)一步提高查詢性能；

如果優(yōu)化器評估后，使用全表掃描或的成本更低，那數(shù)據(jù)庫就會選擇使用全表掃描。

3 SQL 優(yōu)化方案

根據(jù)第 2 部分明確了問題的原因后，通過調(diào)整索引，解決最左列尾范圍查詢的問題即可解決該問題。具體如下：

altertableltb2dropindexuidx_1;
altertableltb2addindexuidx_1(c,b);
altertableltb2addindexidx_(b);

死鎖為何發(fā)生

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

在此，對死鎖過程進(jìn)行大膽猜想：

T1 時刻

trx-2 執(zhí)行了 UPDATE，在處理行時，在 row_search_mvcc 函數(shù)中，查詢到數(shù)據(jù)。獲取了對應(yīng)行的 LOCK_X,LOCK_REC_NOT_GAP 鎖；

T2 時刻

trx-1 執(zhí)行了 DELETE，在處理行時，在 row_search_mvcc 函數(shù)中，查詢到數(shù)據(jù)，嘗試獲取行的 LOCK_X,LOCK_REC_NOT_GAP。但由于 trx-1 已經(jīng)持有了該鎖，因此被堵塞。并會創(chuàng)建一個鎖（以指示鎖等待）；

T3 時刻

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

假如此時，此處的實現(xiàn)機(jī)制和 INSERT 死鎖案例一樣，也沒有先進(jìn)行沖突檢查。而只是看記錄上是否存在鎖的話，那么此時也會看到該記錄上有 trx-1 事務(wù)的鎖。從而導(dǎo)致 trx-2 第二次獲取鎖時，被堵塞。

死鎖發(fā)生！

審核編輯：劉清