MySQL 5.7的数据库优化器还是这么简单？
1 问题现象
自发布了 insert 并发死锁问题的文章，收到了多次死锁问题的交流。一个具体案例如下：
研发反馈应用发生死锁，收集如下诊断内容：
------------------------latest detected deadlock------------------------2023-07-04 0640 0x7fc07dd0e700*** (1) transaction:transaction 182396268, active 0 sec fetching rowsmysql tables in use 1, locked 1lock wait 21 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1mysql thread id 59269692, os thread handle 140471135803136, query id 3738514953 192.168.0.215 user1 updatingdelete 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 1729mysql tables in use 1, locked 128 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1mysql thread id 59261188, os thread handle 140464721291008, query id 3738514964 192.168.0.214 user1 updatingupdate 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
表结构和索引：
create table `ltb2` ( `id` bigint(20) unsigned not null auto_increment comment 'id', `j` varchar(16) default null comment '', `c` varchar(32) not null default '' comment '', `b` date not null default '2019-01-01' comment '', `f` varchar(1) not null default '' comment '', `g` varchar(1) not null default '' comment '', `d` varchar(1) not null default '' comment '', `e` varchar(1) not null default '' comment '', `h` varchar(1) not null default '' comment '', `i` varchar(1) default null comment '', `last_update_time` timestamp not null default current_timestamp on update current_timestamp comment '修改时间', primary key (`id`), unique key `uidx_1` (`b`,`c`)) engine=innodb auto_increment=270983 default charset=utf8mb4 comment='';
关键信息梳理
事务 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 的执行情况。
2 sql 执行情况
执行计划
以上两个 sql 发现都有列 b、c 作为条件，且该列构成了索引唯一索引 uidx_1。简化 sql 改为查询语句，并确认执行计划：
mysql> desc select * from ltb2 where b >= '20230717' and c = 'code001';# 部分结果+----- -+-------------------+------+---------+| type | possible_keys | key | extra |+----- -+-------------------+------+---------+| all | uidx_1 | null | using where |+----- -+-------------------+------+---------+
注意：自 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，扫描将像这样进行：
获取下一行的索引元组（但不是完整的表行）。
测试 where 条件中应用于此表的部分，并且只能使用索引列的进行检查。如果不满足条件，则继续到下一行的索引元组。
如果满足条件，则使用索引元组定位并读取整个表行。
测试适用于此表的 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，跟踪执行计划生成过程。命令如下：
set optimizer_trace=enabled=on,end_markers_in_json=on;set optimizer_trace_max_mem_size=1000000;-- sql-1:select * from ltb2 where b >= '20990717' and c = 'code001';-- sql-2:select * from ltb2 where b >= '20990717';-- sql-3select * from ltb2 where b >= '20230717' and c = 'code001';select * from information_schema.optimizer_tracegset optimizer_trace=enabled=off;
由于分析结果较长，截取 sql-1 和 sql-2 的部分结果 (rows_estimation 和 considered_execution_plans)。具体内容如下：
sql-1
select * from ltb2 where b >= '20990717' and c = 'code001'# 分析结果analyzing_range_alternatives:{ range_scan_alternatives:[ { index:uidx_1, ranges:[ 0xe76610 = '20990717'# 分析结果analyzing_range_alternatives:{ range_scan_alternatives:[ { index:uidx_1, ranges:[ 0xe76610 = '20230717' and c = 'code001';# 全表扫描结果range_analysis: { table _scan: { rows: 4669， cost: 1018.9 } /* table_scan */，# 索引扫描评估结果analyzing_range_alternatives: { range_scan_alternatives: [ { index:uidx_1, ranges:[ @xe7ce0f] desc select * from ltb2 force index (uidx_1) where b >= '20230717' and c = 'code001';# 部分结果+----------+---------+---------+| key_len | rows | extra |+----------+---------+---------+| 133 | 1273 | using index condition |+----------+---------+---------+
同时，根据执行计划的输出结果，rows 列应该是优化器阶段的输出，key_len/extra 则包括了执行阶段的输出。
小结
综上所述，对于问题 sql 和索引结构，由于列 b 为索引的最左列，且查询时的条件为 b>= '20230717'（非等值条件），数据库优化器只能“使用”到 b 列。并给予“使用”的列，评估扫码的行数和 cost。
如果优化器评估后，使用索引的成本更低，则可以使用该索引，并利用 icp 特性进一步提高查询性能；
如果优化器评估后，使用全表扫描或的成本更低，那数据库就会选择使用全表扫描。
3 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 执行计划问题的分析和解决。但直接的问题是死锁，因查询语句无法使用索引，正常就应该使用全表扫描。但是全表扫描为什么会出现死锁呢？
在此，对死锁过程进行大胆猜想：
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 第二次获取锁时，被堵塞。
死锁发生！
可编程逻辑实现高性能抓捕罪犯系统
利用EHD喷印技术在MEMS器件上实现性能优异的无掩膜沉积WO3胶体量子点
2018年，JetBrains发起了其标准的年度调查项目
伟世通为全球汽车厂商提供增强现实技术的驾驶体验和最新抬头显示技术
锂锰扣式电池自动组装生产线方案设计
MySQL 5.7的数据库优化器还是这么简单？
Ku DiSEqC 1.2 极轴座知识讲座
荣耀的最新产品或将采用骁龙888处理器
三星SDI确定46直径系列电池规格
三星实现在华三级跳 天津是关键性一跃
无线充电模块？传富士康为iPhone8研发
摩托罗拉推出XIR MOTOTRBO XiR M3188车载台解决方案
高隔离度X波段RF MEMS电容式并联开关
OpenVINO™ 赋能 BLIP 实现视觉语言 AI 边缘部署
讯飞输入法×讯飞智能鼠标 感恩节享福利用好物
光电编码器原理及应用电路
导致硫酸罐渗漏的原因是什么
汽车领域为什么缺芯片
思必驰助力政企打造高效AI会议室
步进电机控制程序(c语言+51单片机)
自发布了 insert 并发死锁问题的文章，收到了多次死锁问题的交流。一个具体案例如下：
研发反馈应用发生死锁，收集如下诊断内容：
------------------------latest detected deadlock------------------------2023-07-04 0640 0x7fc07dd0e700*** (1) transaction:transaction 182396268, active 0 sec fetching rowsmysql tables in use 1, locked 1lock wait 21 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1mysql thread id 59269692, os thread handle 140471135803136, query id 3738514953 192.168.0.215 user1 updatingdelete 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 1729mysql tables in use 1, locked 128 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1mysql thread id 59261188, os thread handle 140464721291008, query id 3738514964 192.168.0.214 user1 updatingupdate 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
表结构和索引：
create table `ltb2` ( `id` bigint(20) unsigned not null auto_increment comment 'id', `j` varchar(16) default null comment '', `c` varchar(32) not null default '' comment '', `b` date not null default '2019-01-01' comment '', `f` varchar(1) not null default '' comment '', `g` varchar(1) not null default '' comment '', `d` varchar(1) not null default '' comment '', `e` varchar(1) not null default '' comment '', `h` varchar(1) not null default '' comment '', `i` varchar(1) default null comment '', `last_update_time` timestamp not null default current_timestamp on update current_timestamp comment '修改时间', primary key (`id`), unique key `uidx_1` (`b`,`c`)) engine=innodb auto_increment=270983 default charset=utf8mb4 comment='';
关键信息梳理
事务 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 的执行情况。
2 sql 执行情况
执行计划
以上两个 sql 发现都有列 b、c 作为条件，且该列构成了索引唯一索引 uidx_1。简化 sql 改为查询语句，并确认执行计划：
mysql> desc select * from ltb2 where b >= '20230717' and c = 'code001';# 部分结果+----- -+-------------------+------+---------+| type | possible_keys | key | extra |+----- -+-------------------+------+---------+| all | uidx_1 | null | using where |+----- -+-------------------+------+---------+
注意：自 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，扫描将像这样进行：
获取下一行的索引元组（但不是完整的表行）。
测试 where 条件中应用于此表的部分，并且只能使用索引列的进行检查。如果不满足条件，则继续到下一行的索引元组。
如果满足条件，则使用索引元组定位并读取整个表行。
测试适用于此表的 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，跟踪执行计划生成过程。命令如下：
set optimizer_trace=enabled=on,end_markers_in_json=on;set optimizer_trace_max_mem_size=1000000;-- sql-1:select * from ltb2 where b >= '20990717' and c = 'code001';-- sql-2:select * from ltb2 where b >= '20990717';-- sql-3select * from ltb2 where b >= '20230717' and c = 'code001';select * from information_schema.optimizer_tracegset optimizer_trace=enabled=off;
由于分析结果较长，截取 sql-1 和 sql-2 的部分结果 (rows_estimation 和 considered_execution_plans)。具体内容如下：
sql-1
select * from ltb2 where b >= '20990717' and c = 'code001'# 分析结果analyzing_range_alternatives:{ range_scan_alternatives:[ { index:uidx_1, ranges:[ 0xe76610 = '20990717'# 分析结果analyzing_range_alternatives:{ range_scan_alternatives:[ { index:uidx_1, ranges:[ 0xe76610 = '20230717' and c = 'code001';# 全表扫描结果range_analysis: { table _scan: { rows: 4669， cost: 1018.9 } /* table_scan */，# 索引扫描评估结果analyzing_range_alternatives: { range_scan_alternatives: [ { index:uidx_1, ranges:[ @xe7ce0f] desc select * from ltb2 force index (uidx_1) where b >= '20230717' and c = 'code001';# 部分结果+----------+---------+---------+| key_len | rows | extra |+----------+---------+---------+| 133 | 1273 | using index condition |+----------+---------+---------+
同时，根据执行计划的输出结果，rows 列应该是优化器阶段的输出，key_len/extra 则包括了执行阶段的输出。
小结
综上所述，对于问题 sql 和索引结构，由于列 b 为索引的最左列，且查询时的条件为 b>= '20230717'（非等值条件），数据库优化器只能“使用”到 b 列。并给予“使用”的列，评估扫码的行数和 cost。
如果优化器评估后，使用索引的成本更低，则可以使用该索引，并利用 icp 特性进一步提高查询性能；
如果优化器评估后，使用全表扫描或的成本更低，那数据库就会选择使用全表扫描。
3 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 执行计划问题的分析和解决。但直接的问题是死锁，因查询语句无法使用索引，正常就应该使用全表扫描。但是全表扫描为什么会出现死锁呢？
在此，对死锁过程进行大胆猜想：
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 第二次获取锁时，被堵塞。
死锁发生！
可编程逻辑实现高性能抓捕罪犯系统
利用EHD喷印技术在MEMS器件上实现性能优异的无掩膜沉积WO3胶体量子点
2018年，JetBrains发起了其标准的年度调查项目
伟世通为全球汽车厂商提供增强现实技术的驾驶体验和最新抬头显示技术
锂锰扣式电池自动组装生产线方案设计
MySQL 5.7的数据库优化器还是这么简单？
Ku DiSEqC 1.2 极轴座知识讲座
荣耀的最新产品或将采用骁龙888处理器
三星SDI确定46直径系列电池规格
三星实现在华三级跳 天津是关键性一跃
无线充电模块？传富士康为iPhone8研发
摩托罗拉推出XIR MOTOTRBO XiR M3188车载台解决方案
高隔离度X波段RF MEMS电容式并联开关
OpenVINO™ 赋能 BLIP 实现视觉语言 AI 边缘部署
讯飞输入法×讯飞智能鼠标 感恩节享福利用好物
光电编码器原理及应用电路
导致硫酸罐渗漏的原因是什么
汽车领域为什么缺芯片
思必驰助力政企打造高效AI会议室
步进电机控制程序(c语言+51单片机)