710-MySQL原理篇

清疚 2022-09-16 15:21 173阅读 0赞

## MySQL原理篇 ##

**MySQL的四种隔离级别**  
SQL标准定义了4类隔离级别，包括一些具体规则，用来限定事务之间的隔离性。  
这四种级别分别是读未提交、读已提交、可重复读、串型化。  
读未提交，顾名思义，就是可以读到还没有提交的数据；读已提交会读到其它事务已经提交的数据；可重复读确保了同一事务中，读取同一条数据时，会看到同样的数据行；串型化通过强制事务排序，使其不可能相互冲突。

**重点介绍下Repeatable Read**  
Repeatable Read就是可重复读。它确保了在同一事务中，读取同一条数据时，会看到同样的数据行。  
它也是MyQL的默认事务隔离级别，这种级别事务之间影响很小，通常已经能够满足日常需要了。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16]

**聊聊InnoDB中ACID的实现吧，先说一下原子性是怎么实现的**  
事务要么失败，要么成功，不能做一半。聪明的InnoDB，在干活儿之前，先将要做的事情记录到一个叫undo log的日志文件中，如果失败了或者主动rollback，就可以通过undo log的内容，将事务回滚。

**那undo log里面具体记录了什么信息呢？**  
undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作，使数据回到之前的状态。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
**那持久性又是怎么实现的？**  
持久性是用来保证一旦给客户返回成功，数据就不会消失，持久存在。最简单的做法，是每次写完磁盘落地之后，再给客户返回成功。但如果每次读写数据都需要磁盘IO，效率就会很低。  
为此，追求极致的InnoDB提供了缓冲。当向数据库写入数据时，会首先写入缓冲池，缓冲池中修改的数据会定期刷新到磁盘中，这一过程称为刷脏。

**如果MySQL宕机，那此时Buffer Pool中修改的数据不是丢失了吗？**  
Innodb引入了redo log来解决这个问题。当数据修改时，会先在redo log记录这次操作，然后再修改缓冲池中的数据，当事务提交时，会调用fsync接口对redo log进行刷盘。  
如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。由于redo log是WAL日志，也就是预写式日志，所有修改先写入日志，所以保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 2]  
**redo log 也需要写磁盘，为什么不直接将数据写磁盘呢？**  
1.对Buffer Pool进行刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO；

2.刷脏是以数据页为单位，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入，所以积累一些数据一并写入会大大提升性能；而redo log中只包含真正需要写入的部分，无效IO比较少。

redo log是持久性的核心，WAL的思路也是持久化的常见解决方式，只有先落地了，才能应对后续的各种异常。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 3]  
**那隔离性怎么实现呢？**

MySQL能支持Repeatable Read这种高隔离级别，主要是锁和MVCC一起努力的结果。  
我先说锁吧。事务在读取某数据的瞬间，必须先对其加行级共享锁，直到事务结束才释放；事务在更新某数据的瞬间，必须先对其加行级排他锁，直到事务结束才释放；  
为了防止幻读，还会有间隙锁进行区间排它锁定。  
然后是MVCC，多版本并发控制，主要是为了实现可重复读，虽然锁也可以，但是为了更高性能考虑，使用了这种多版本快照的方式。  
因为是快照，所以一个事务针对同一条Sql查询语句的结果，不会受其它事务影响。

**索引的底层实现是什么？**  
用的B+树，它是一个N叉排序树，每个节点通常有多个子节点。节点种类有普通节点和叶子节点。根节点可能是一个叶子节点， 也可能是个普通节点。

**那MySQL为什么用树做索引**  
一般而言，能做索引的，要么Hash，要么树，要么就是比较特殊的跳表。Hash不支持范围查询，跳表不适合这种磁盘场景，而树支持范围查询，且多种多样，很多树适合磁盘存储。所以MySQL选择了树来做索引。

**那你能说说为什么是B+树，而不是平衡二叉树、红黑树或者B-树吗？**  
平衡二叉树追求绝对平衡，条件比较苛刻，实现起来比较麻烦，每次插入新节点之后需要旋转的次数不能预知。  
同时，B+树优势在于每个节点能存储多个信息，这样深度比平衡二叉树会浅很多，减少数据查找的次数。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 4]  
红黑树放弃了追求完全平衡，只追求大致平衡，在与平衡二叉树的时间复杂度相差不大的情况下，保证每次插入最多只需要三次旋转就能达到平衡，实现起来也更为简单。  
但是红黑树多用于内部排序，即全放在内存中，而B+树多用于外存上时，B+也被称为一个磁盘友好的数据结构。  
同时，红黑树和平衡二叉树有相同缺点，即每个节点存储一个关键词，数据量大时，导致它们的深度很深，MySQL每次读取时都会消耗大量IO。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 5]

**那B+树相比B-树有什么优点呢？**

B+树非叶子节点只存储key值，而B-树存储key值和data值，这样B+树的层级更少，查询效率更高；  
MySQL进行区间访问时，由于B+树叶子节点之间用指针相连，只需要遍历所有的叶子节点即可，而B-树则需要中序遍历一遍。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 6]  
**接下来讲讲聚簇索引和二级索引吧。**  
聚簇索引是主键上的索引，二级索引是非主键字段的索引。这两者相同点是都是基于B+树实现。  
区别在于，二级索引的叶子结点只存储索引本身内容，以及主键ID，聚簇索引的叶子结点，会存储完整的行数据。在一定程度上，可以说二级索引就是主键索引的索引。

**二级索引是主键索引的索引**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 7]  
**下面讲讲MySQL锁的分类吧。**  
MySQL从锁粒度粒度上讲，有表级锁、行级锁。从强度上讲，又分为意向共享锁、共享锁、意向排它锁和排它锁。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 8]  
**那select操作会加锁吗？**  
对于普通select语句，InnoDB 不会加任何锁。但是select语句，也可以显示指定加锁。有两种模式，一种是LOCK IN SHARE MODE是加共享锁，还有Select … for updates是加排它锁。

**什么情况下会发生死锁？**  
比如事务A锁住了资源1，然后去申请资源2，但事务B已经占据了资源2，需要资源1，谁都不退让，就死锁了。对于MySQL，最常见的情况，就是资源1、资源2分别对应一个排它锁。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 9]  
**那间隙锁你有了解么？**  
间隙锁就是对索引行进行加锁操作，不仅锁住其本身，还会锁住周围邻近的范围区间。间隙锁的目的是为了解决幻影读，但也因此带来了更大的死锁隐患。  
比如，一个任务表里面有个状态字段，是一个非唯一索引，有一个任务id，是唯一索引。  
一个sql将状态处于执行中的任务设置为等待中，另一个sql正好通过任务id更新在范围内的一条任务信息。那么因为是在不同索引加锁的，所以都能成功。但是最后去更新主键数据的时候，就会死锁。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 10]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/d510f96352f44f30a9a79bfe60f843ba.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/416d252c636347c7ac847450f7164096.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/0b34700545ec44709562dea2c0483170.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220828/9a6f9e2700a84a7ebfda492c95f5bd43.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 4]: /images/20220828/4b849561a4634364a2f7ec82debdc1f3.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 5]: /images/20220828/b1e2892faade4185b284af3adc5a9d87.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 6]: /images/20220828/61a4c58748864a29ac6212153b819a32.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 7]: /images/20220828/bbf65f40afbf41f681fc4f3c56b62c57.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 8]: /images/20220828/eea1b394e3b6438aabea087f7ca4d5a3.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 9]: /images/20220828/c7345f6f151843d38d619420e5bbfb5e.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALeael-azveWuhw_size_20_color_FFFFFF_t_70_g_se_x_16 10]: /images/20220828/b1ecfe6bee8e4b3097443881b6de84ce.png