MySQL45讲笔记（二）索引、锁、事务的可见性

最新推荐文章于 2024-09-02 11:10:50 发布

ycCoding

最新推荐文章于 2024-09-02 11:10:50 发布

阅读量709

点赞数

分类专栏：数据库文章标签： mvcc 行锁索引规则事务可见性死锁

本文链接：https://blog.youkuaiyun.com/m0_38060977/article/details/105745899

版权

数据库专栏收录该内容

2 篇文章

订阅专栏

一索引

索引是数据库中为了提高查询效率的一种数据结构。

1.1 常见模型

哈希表

一种以键-值（key-value）存储数据的结构，当哈希冲突时，会产生链表
在这里插入图片描述
适用于只用来等值查询的场景

有序数组

在等值查询和范围查询场景下都非常优秀
在这里插入图片描述

缺点

往中间插入数据时，必须挪动后面所有的记录。

所以适用于静态存储引擎，数据表里的数据不会再修改了

二叉搜索树

二叉树是搜索效率最高的，但是实际上大多数的数据库存**储却并不使用二叉树。**其原因是，索引不止存在内存中，还要写到磁盘上。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小

1.2 InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引(物理顺序)的形式存放的，这种存储方式的表称为索引组织表。

InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。

图示

R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下
在这里插入图片描述
根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

页分裂

而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。

页合并

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程

注意点

因为非主键索引存的是主键值，所以主键长度不宜太长。
有序主键可以减少页分裂
如果使用场景更多是k-v查询,则可以用业务字段做主键
由于InnoDB是索引组织表，一般情况下建议创建一个自增主键，这样非主键索引占用的空间最小。

1.3 覆盖索引

前面我们说到非主键索引在查询时可能会有回表。

但如果查询的数据已经在索引树上了(索引覆盖了查询需求)，那就没必要回表。
因此我们有时可以建立一些复合索引来满足覆盖索引

1.4 最左前缀原则

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录
在这里插入图片描述

符合索引如何安排顺序？

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的.比如建立a、b索引可以减少建立一个a索引
考虑空间。如果a、b都需要建立索引。那么将字段小的作为复合索引的前缀。

1.5 索引下推

而MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

如sql:select * from tuser where name like '张%' and age=10 and ismale=1;

以前每次都回表
在这里插入图片描述
现在如果能在当前索引中避免回表，就不回表了

1.6 重建索引

因为页分裂等问题，有时我们可以重建索引来提高页面利用率。

实现方式有

删除索引，再新增索引（不推荐。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了）
使用alter table T engine=InnoDB

二全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：

数据更新语句（数据的增删改）
数据定义语句（包括建表、修改表结构等）
更新类事务的提交语句。

2.1 使用场景

备份。因为有时候一个事务中的多个操作，可能不会同时保存到一个事务中，这样就不能保证数据的一致性。

事务状态下备份

可重复读，可以保证数据一致性,MySQL自动的mysqlduno也可以使用参数–single-transaction,在导数据前，开启一个事务，保证一致性试图。

但因为不是所有的引擎都支持事务，所以像MyISAM引擎下备份时，就需要用到FTWRT命令

为什么不使用set global readonly=true

readonly方式也可以让全库进入只读状态，但并不建议。因为

在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库
在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态

三表级锁

表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

3.1 表锁

表锁的语法是lock tables … read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象.即只能操作锁的哪些表。

3.2 MDL（metadata lock)

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

虽然MDL锁是系统默认会加的，但却是你不能忽略的一个机制。比如下面这个例子，我经常看到有人掉到这个坑里：给一个小表加个字段，导致整个库挂了。

小心下面这种场景的阻塞
在这里插入图片描述

3.3 注意DDL

ddl操作不支持事务(且会提交前面没有提交的操作)

四行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁

4.1 两段锁

在这里插入图片描述
在上面这个场景中，事务B将会被阻塞，知道事务A提交。

通过这个例子，我们可以得出结论：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放（因为往后放，阻塞的时间就越短）

update操作会对行上写锁，并在commit时才提交

4.2 死锁和死锁检测

并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。如下图
在这里插入图片描述
这时事务A和事务B互相等待。
处理死锁的两种策略

直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。(默认50s)
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。（默认开启）

死锁检测虽好，但是也有缺陷：当并发量很大时，死锁检测操作会耗费大量的CPU资源.这时CPU利用率很高，执行事务的效率却很慢。

建议解决办法

可以利用分段思想。就像java中的LongAdder.比如我们要修改某个账号的余额，因为该行的并发量过高。我们可以逻辑上将改行分成10条记录，该账号的实际金额时10个账号之和。这样我们的并发度就下来了（需要小写负数处理）

五事务的可见性

5.1 事务开始时间

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句（第一个快照读语句），事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot 这个命令。

当autocommit=1时，每一条update/inset语句本身就是一个事务，语句完成的时候会自动提交

5.2 视图

InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。
它没有物理结构，作用是事务执行期间用来定义我能看到什么数据

快照机制

可重复读隔离级别下，事务在启动的时候就拍了个快照(注意事务的起点时机)。注意，这个快照是基于整库的。

有时库很大，为什么MySQL的快照很快？
InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的row trx_id。
逻辑上，我们可以理解成下图
在这里插入图片描述
实际上，MySQL并不会保存V1、V2、V3这样的数据，而是通过undo log（回滚日志）实现的。
实际上，图中的三个虚线箭头，就是undo log；而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来

5.3 视图数组

InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在活跃的所有事务ID。
活跃指的就是，启动了但还没提交。

数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。
这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

这个视图数组把所有的row trx_id 分成了几种不同的情况
在这里插入图片描述
我们分析下上图，假设当前事务是100。视图数组中的元素是[96，98,100]。这时低水位是96，高水位就是101。我们可以发现低水位和高水之间的97和99是已提交的，96、98是未提交的。95包括之前是肯定提交的

可见性规则

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况
- a. 若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；
- b. 若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

小结

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。

5.4 更新操作是当前读（current read）

更新数据都是先读后写的，而这个读，只能读当前的值，称为当前读（current read）

前面几小节说的都是查询操作下的数据读取逻辑，但更新操作不同。想象一下，如果更新操作也是之前的读取规则，那么两个事务同时进行更新操作（如set a = a+1），那么一定会丢失修改的现象
所以MySQL对更新操作里面的读取时是采用当前度，即读取数据的最新值。这时又有一个问题，一个数据的最新值的row trx_id的事务可能还未提。，那么此时当前读读到的值是最新一个已提交版本，还是这个未提交的当前值呢？

答案是会阻塞!!!
因为当前读是会对行上读锁的，而前面说过行锁是两阶段锁协议的，所以只有事务结束时锁才能释放。总的来说：当前读只可能读到某一行已提交的最新值。
在这里插入图片描述