MySQL范围查询中next-key lock优化问题

背景

《MySQL45讲》里提到间隙锁(gap lock)与next-key lock的区别,并提到了索引上发生等值查询时,向右遍历时且最后⼀个值不满⾜等值条件的时候, next-key lock退化为间隙锁。另外也阐述了在不等号查询(范围查询)中会存在等值查询所引起的退化。
然而在实测中发现相关实验出现了相互矛盾的结果。

实验(MySQL v8.0.11)

表结构

# 创建表
create table `table21` (
`id` int(11) not null,
`c` int(11) default null,
`d` int(11) default null,
primary key (`id`),
key `c` (`c`)
) engine=InnoDB;

# 插入数据
insert into table21 values (0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

由此可见这批初始化的数据将表分成了(-∞,0]、 (0,5]、 (5,10]、 (10,15]、 (15,20]、 (20, 25]、 (25, +supremum]几个区间

无优化的情况

会话A会话B
begin;
sleect * from table21 where id<12 for update;
update table21 set d=d+1 where id=15;
(被阻塞)

由修改id=15被阻塞的现象可见,在(12,15]加了next-key lock,即既锁定(12,15)这个间隙,同时为id=15添加行锁。

有优化的情况

会话A会话B
begin;
sleect * from table21 where id<12 order by id desc for update;
update table21 set d=d+1 where id=15;
(更新成功)

由更新成功可见next-key lock(12,15]退化成了间隙锁(12,15),即id=15未被锁定。
按照原文的说法是找id=12时发生了等值查询,向右遍历的下一个值是id=15,但由于不符合等值条件,所以退化成间隙锁。
我也曾经用非唯一索引c做同样的实验,结果也是一样有矛盾。

问题

  1. 如何才算是等值查询?
  2. 为什么加入(order by id desc)字段后就可以发生优化?

实验结论

通过实验结果尝试回答以上两个问题。

即使是“等值查询”也没有优化

为此补做实验:

会话A会话B
begin;
sleect * from table21 where id<15 for update;
update table21 set d=d+1 where id=15;
(阻塞)

如果按原文的说法,执行引擎会先进行id=15的等值查询,然后开始向左搜索,则id=15的记录不应该被上行锁。然而经过测试,行锁还是被加到该记录上了,说明优化跟访问查询里是不是“等值查询”关系不大。

倒序查询带来的优化

对本文开始的两个重要语句用explain解释一次,发现使用order by XX desc后,Extra字段中会出现backward index scan,即发生从后向前的倒序查询。
在这里插入图片描述
为了验证倒序查询是否有优化,做了以下两个实验:

  • 实验一
会话A会话B
begin;
sleect * from table21 where id<15 order by id desc for update;
update table21 set d=d+1 where id=15;
(成功)
  • 实验二:MySQL45讲里面提到有个bug,即唯⼀索引上的范围查询会访问到不满⾜条件的第⼀个值为⽌。比如当查询范围是(10,15]时,被锁的范围会扩展成(10,20]。这里尝试通过倒序查询来优化成(10,20)。
会话A会话B
begin;
sleect * from table21 where id>10 and id<=15 order by id desc for update;
update table21 set d=d+1 where id=20;
(成功)
insert into table21 values(16,16,16);
(阻塞)

另外也曾尝试不加desc,此时查询方向为正向,id=20的记录会被上行锁。由此说明倒序查询在范围查询中会去掉不必要的行锁。从而推测上面出现的现象不是由等值查询引起优化。不过(15,20)区间还是会被上间隙锁。

总结

通过这次实验,仍未能准确认识next-key lock退化成间隙锁的条件,这一点我觉得需要等以后翻源代码才能够解决。但是当使用倒序查询的话会对边界有一定优化。
另外,比起next-key lock会不会退化成间隙锁,我认为next-key lock会不会退化成行锁更重要,因为前者影响的是作为边界的一行会不会被上行锁,影响的是更新操作,而后者则锁住了一个区间,影响插入操作。

  • 3
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值