作者通过一个死锁案例结合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
  • 表结构和索引:

关键信息梳理

事务 T1
语句 delete from ltb2 where c = ‘code001’ and j = ‘Y15’ and b >= ‘20230717’ and d != ‘1’ and e != ‘1’
关联对象及记录 space id 603 page no 86 n bits 248 index PRIMARY of table testdb.ltb2
持有的锁 未知
等待的锁 lock_mode X locks rec but not gap waiting
事务 T2
语句 update ltb2 set f = ‘0’, g = ‘0’, is_value_date = ‘0’, h = ‘0’, i = ‘0’ where c = ‘22115001B’ and j = ‘Y4’ and b >= ‘20230717’
关联对象及记录 space id 603 page no 86 n bits 248 index PRIMARY of table testdb.ltb2
持有的锁 lock_mode X locks rec but not gap
等待的锁 lock_mode X locks rec but not gap waiting

可以看到在主键索引上发生了死锁,但是在查询的条件中,并未使用主键列。

那为什么会在主键列出现死锁? 在分析死锁根因问题前,需要先清楚 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. 除了调整索引,还能通过什么方式避免该问题发生?