更多博客请见 我的语雀知识库

user表,id是主键,有a,b,c三个字段，都是int,a有索引，b有索引
问：**update user set a = 1 where c = 2**;这条语句怎么执行？
思考：先干什么？后干什么？全表扫描？扫的对象是什么？数据页？索引页？
哪个素引？为什么扫这个素引？扫的过程要上锁，上的什么锁？为什么上这个锁？不上行不行？
拿到锁后要干什么？更新？怎么更新？更新完后还有什么操作吗？直接结束了？
非聚簇索引上修改了字段值会怎样？有了解吗？为什么这样设计？
如果此时有其它事务来并发，如何保证事务隔离的？
select id from user where a = 1 lock in share mode;怎么办？
如果第一条sql变成：update user set id = id+1 where a = 1;又如何执行？

update user set a = 1 where c = 2;

先执行update user set a = 1 where c = 2;
再执行select id from user where a = 1 lock in share mode;

索引结构

image.png

image.png image.png

除了默认的主键索引之外，还有建立在a,b字段上的普通索引。每个索引一颗B+树。

执行过程

“**update user set a = 1 where c = 2；**” where子句里的条件能用什么索引优化查询吗？没有，c字段没有与之相关联的索引，这条语句的执行只能在主键索引的B+树中逐行扫描（也就是说，加的锁是组合锁，而不是表锁，并不是全表扫描）, 这里参考up的解释：image.png
简而言之就是，表锁开销太大了没必要，不如用组合锁。虽然效果上是锁住了全表，但不是表锁。

从主键索引B+树中找到对应的行数据后，将a的值设置为1。（索引页节点二分查找，叶子节点遍历查找）
结束了吗？
没有，还要维护其它索引的B+树。

维护索引B+树

b索引因为只包含主键和b字段，和a字段无关，不用维护。
在维护a索引的过程中，根据主键索引查出来的修改之前a字段所有值去在a索引B+树中查询出对应的叶子节点中的数据。
然后修改a字段的值就结束了？ 没有。 a索引的B+树是根据a字段的值有序排列节点的。修改了a字段的值，有序性被打破，就要重建有序性。
如何重建？
先删后插。
image.png 如上图，将涉及到的节点全删了，再把更新后的新节点根据有序性插入到对应的位置上。
这样就完成了对索引B+树的维护。

并发问题

假设原来a = 1的数据有3行，现在由于update语句，又增加了3行，但是update语句因为维护B+树的原因还没提交。
在维护B+树的过程中，如果有新的语句被执行：
**select id from user where a = 1 lock in share mode;**（“in share mode”使得这条sql成为当前读）
会怎么样？查出来3条还是6条？
和事务隔离性有关吗？
无关。事务隔离性只针对普通的select这样的走MVCC的快照读，顺着版本链读下去。
lock in share mode、update、delete、select ..... for update等等关键字都是当前读，强制读取最新数据。那么这样的语句在并发环境中就涉及到了锁的问题。 维护a索引B+树期间，是先删后插的形式，那么插入就需要意向锁。此时上文的select语句被执行，且走a索引。需要访问被删除和新增的节点。这样的并发情况下，意向锁是怎么被加上的？

为了便于表述，执行完**update user set a = 1 where c = 2**;并维护a索引B+树的事务称为A，后续并发执行**select id from user where a = 1 lock in share mode;**的事务称为B。 A不会上意向锁，锁是B替A加的。

插入意向锁的上锁过程很有懒加载的思想。有并发情况才上锁，没有就不加。
也就是说，后续并发执行的B事务通过ReadView得知对最新数据做出修改地事务还没提交，于是为A事务加了插入意向锁，锁不是A事务加的！
B在替A上锁后，就阻塞等待了，真是个好人我哭死

update user set id = id+1 where a = 1;

先执行**update user set id = id + 1 where a = 1;** 再执行**select id from user where a = 1 lock in share mode;**
大致情况与前一种情况相同。但是有差异：
执行第一条sql时
先走a索引查询,显式地锁住a = 1 的记录，拿到主键id之后再去主键索引上回表找到主键索引的数据，也是显式地锁住。再根据主键索引里的b字段数据去b索引里回表查询找到对应的主键id，隐式地锁住。总共涉及到了两次回表操作，一个隐式锁，两个显式锁。
由于本次的update语句涉及到了修改主键，那么需要维护的B+树就不止a索引的了。三个B+树都要维护。
修改后如果不影响有序性，则直接修改即可，但如果影响到了B+树的有序性，就需要先删后插。

如何区分显式锁和隐式锁？

显式锁是由开发者手动控制的锁，需要在代码中显式地指定锁的类型和范围。在MySQL中，显式锁通常是指使用LOCK TABLES语句或者SELECT ... FOR UPDATE语句来加锁，用于实现对数据的精确控制。

隐式锁是由数据库系统自动管理的锁，不需要开发者手动介入。在MySQL中，隐式锁通常是指数据库系统在执行SQL语句时自动加上的锁，用于保证数据的一致性和事务的原子性。
image.png

up的总结

image.png

优雅_0v0. (2024, June 07). 一条SQL带你理清页、索引、锁的关系，95%的人回答不上来！. Retrieved from一条SQL带你理清页、索引、锁的关系，95%的人回答不上来！_哔哩哔哩_bilibili