MySQL 复习总结（二）

felicia_m18

已于 2024-07-28 23:56:13 修改

阅读量714

点赞数 23

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-07-28 23:50:07 首次发布

本文链接：https://blog.csdn.net/felicia_m18/article/details/140758392

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言

本篇内容承接上文，主要集中于对MySQL底层的理解。

ps：本文内容均由书籍、网络等方式整理而来，会加入一些个人见解，如有侵权可以联系我删除。如有错误也欢迎批评指正。

事务

对于事务的理解

事务是一组操作的集合，它是一个不可分割的工作单位，

事务会把所有的操作 作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务的ACID特性

原子性（Atomicity）——undo log（回滚日志）保证；

一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样

一致性（Consistency）——持久性+原子性+隔离性保证

是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。

隔离性（Isolation）——MVCC或锁保证

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

持久性（Durability）——redo log（重做日志）保证

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

并发事务会引发的问题

脏读

一个事务「读到」了另一个「未提交事务修改过的数据」

不可重复读

在一个事务内多次读取同一个数据，出现前后两次读到的数据不一样的情况

幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，出现前后两次查询到的记录数量不一样的情况

ps:脏读的严重性最高，幻读严重性最低

事务的隔离级别

隔离级别的类型

SQL 标准提出了四种隔离级别来规避以上现象，越往下隔离级别越高，性能效率就越低

读未提交（read uncommitted）

指一个事务还没提交时，它做的变更就能被其他事务看到；

读提交（read committed）

指一个事务提交之后，它做的变更才能被其他事务看到；

可重复读（repeatable read）——MySQL InnoDB 引擎的默认隔离级别

指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的

串行化（serializable）

会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

四种隔离级别对于并发事务产生的问题的解决方案

要解决脏读现象，就要将隔离级别升级到读提交以上的隔离级别，
要解决不可重复读现象，就要将隔离级别升级到可重复读以上的隔离级别。
而对于幻读现象，不建议将隔离级别升级为串行化，因为这会导致数据库并发时性能很差。

可重复读对于幻读现象的解决方案

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象，解决的方案有两种：

针对快照读（普通 select 语句，读写不冲突），是通过 MVCC 方式解决了幻读，（不加锁）

因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

针对当前读（update、select ... for update 等语句，读写互斥），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，

因为当执行 select ... for update 语句的时候，会加上 next-key lock，

如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

四种隔离级别的具体实现

对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View （或者说MVCC）来实现的，它们的区别在于创建 Read View 的时机不同：

「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View（在每次读取数据时，都会生成一个新的 Read View。）

「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

（Read View是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)）

对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；

MVCC

对于MVCC的理解

通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制），用来实现读提交和可重复读两个隔离级别。

（版本链：对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成的一个链表。版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id）

MVCC的具体实现

比对「事务的 Read View 里的字段」 和 「聚簇索引记录中的两个隐藏列」

图源@小林coding

如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：

图源@小林coding

如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

拓展：可重复读隔离级别下还会发生幻读吗？

MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

锁

锁的类型

全局锁

执行命令：flush tables with read lock

执行后，整个数据库就处于只读状态了

表级锁

会限制别的线程的读写外，也会限制本线程接下来的读写操作

共享锁是读锁，独占锁是写锁

元数据锁（MDL）

我们不需要显式的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；

对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

意向锁

分两种：

意向共享锁（IS）——事务有意向对表中的某些行加共享锁（S锁）

意向独占锁（IX）——事务有意向对表中的某些行加排他锁（X锁）

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。
表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。
那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁

AUTO-INC 锁

特殊的表锁机制，锁不是再一个事务提交后才释放，而是在执行完插入语句后就会立即释放。

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此，在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁

行级锁

Record Lock，记录锁——仅仅把一条记录锁上

Gap Lock，间隙锁——锁定一个范围，但是不包含记录本身；

只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

Next-Key Lock：临键锁，Record Lock + Gap Lock 的组合——锁定一个范围，并且锁定记录本身

其中有个 插入意向锁（名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁）

MySQL的乐观锁和悲观锁

这些都是锁的设计思想，不是具体的锁

悲观锁指的是采用一种持悲观消极的态度，默认数据被外界访问时，必然会产生冲突，所以在数据处理的整个过程中都采用加锁的状态，保证同一时间，只有一个线程可以访问到数据，实现数据的排他性；

通常情况下，数据库的悲观锁就是利用数据库本身提供的锁去实现的。

乐观锁是假设认为即使在并发环境中，外界对数据的操作一般是不会造成冲突，所以并不会去加锁(所以乐观锁不是一把锁)，而是在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回冲突信息，让用户决定如何去做下一步，比如说重试，直至成功为止；

数据库乐观锁的具体实现几乎就跟Java中乐观锁采用的CAS算法思想是一致的

MySQL的死锁问题

MySQL在并发环境下可能会出现死锁问题。

死锁是指两个或多个事务互相等待对方释放资源，导致无法继续执行的情况。

解决死锁问题的方法通常有以下几种：

调整事务隔离级别：通过将事务隔离级别降低为读未提交（或读已提交，可以减少死锁的发生概率。但是要注意隔离级别的降低可能引发脏读、不可重复读等数据一致性问题，在选择时需要权衡利弊。
优化查询和事务逻辑：分析造成死锁的原因，优化查询语句和事务逻辑，尽量缩短事务持有锁的时间，减少死锁的可能性。比如按照相同的顺序获取锁，避免跨事务的循环依赖等。
使用行级锁：行级锁可以较小地限制锁的范围，从而减少死锁的可能性。将表的锁粒度调整为行级别，可以减少事务之间的冲突。
设置合理的超时时间和重试机制：当发生死锁时，可以设置适当的超时时间，在一定时间内尝试解锁操作。如果超过设定的时间仍未成功，则进行死锁处理，如终止较早请求的事务或进行回滚等。