乐趣区

关于mysql:从一个死锁问题分析优化器特性

jiezi

作者通过一个死锁案例联合 OPTIMIZER TRACE，对 MySQL 5.7 的索引成本计算、索引抉择以及 ICP 个性进行了剖析。

作者：李锡超，一个爱笑的江苏苏宁银行 数据库工程师，次要负责数据库日常运维、自动化建设、DMP 平台运维。善于 MySQL、Python、Oracle，喜好骑行、钻研技术。

爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

本文约 2100 字，预计浏览须要 7 分钟。

问题景象

自公布了 INSERT 并发死锁问题的文章，收到了屡次死锁问题的交换。一个具体案例如下：

研发反馈利用产生死锁，收集如下诊断内容：


> 以上 `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. 除了调整索引，还能通过什么形式防止该问题产生？
退出移动版