MySQL原理篇

MySQL的四种隔离级别

读未提交（read uncommited）：可以读到没有提交的数据，也就是脏读，还可能会出现不可重复读和幻读问题。

读已提交（read commited）：只能读到已提交的数据也就避免了脏读，但是还是会出现不可重复读和幻读问题

可重复读（repeatable re）：确保了同一事务中，读取同一数据时，看到的是相同数据行。避免了脏读，不可重复读，但是还可能出现幻读。（MySQL默认隔离级别）

串型化（serializble）：通过强制事务排序，使其不可能相互冲突，避免了脏读，不可重复读、幻读。

（1）脏读：指事务A正在访问数据，并且对数据进行了修改（事务未提交），这时，事务B也使用这个数据，后来事务A撤销回滚，并把修改后的数据恢复原值，B读到的数据就与数据库中的数据不一致，即B读到的数据是脏数据。

（2）不可重复读：在一个事务内，多次读取同一个数据，但是由于另一个事务在此期间对这个数据做了修改并提交，导致前后读取到的数据不一致；

（3）幻读：在一个事务中，先后两次进行读取相同的数据（一般是范围查询），但由于另一个事务新增或者删除了数据，导致前后两次结果不一致。

不可重复读侧重于读取到其他事务修改的数据，幻读侧重于读取到其他事务新增或者删除的数据。

InnoDB中ACID的实现

原子性（Atomicity）

事务要么成功要么失败，不能做一半。

InnoDB在开启事务前会将要做的事记录到undo log日志文件中，如果失败了或主动rollback回滚，就可以通过undo log的内容，将事务回滚。

undo log属于逻辑日志，记录的是SQL执行相关的信息，当发生回滚时，InnoDB会根据undo log中的内容做相反的动作，是数据回到原始的状态

undo log (回滚日志)：是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。

一致性（Consistency） 

数据一致性是通过事务的原子性、持久性和隔离性来保证的

隔离性（Isolation） 

MySQL能支持Repeatable Read这种高隔离级别，主要是锁和MVCC一起努力的结果。

事务在读取某数据的瞬间，必须先对其加行级共享锁，直到事务结束才释放；事务在更新某数据的瞬间，必须先对其加行级排他锁，直到事务结束才释放；为了防止幻读，还会有间隙锁进行区间排它锁定。

MVCC，多版本并发控制，主要是为了实现可重复读，虽然锁也可以，但是为了更高性能考虑，使用了这种多版本快照的方式。因为是快照，所以一个事务针对同一条Sql查询语句的结果，不会受其它事务影响。

持久性（Durability）

持久性的实现关键在于redo log日志，在执行SQL时会保存已执行的SQL语句到一个指定的Log文件，当执行recovery时重新执行redo log记录的SQL操作。

3.1、redo log日志：

当向数据库写入数据时，执行过程会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程叫做刷盘），这整一过程称为redo log。redo log 分为：

• Buffer Pool内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；
• 磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

Buffer Pool的使用可以大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据在内存还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

为了确保事务的持久性，在当事务提交时，会调用fsync接口对redo log进行刷盘, （即redo log buffer写日志到磁盘的redo log file中 ），刷新频率由 innodb_flush_log_at_trx_commit变量来控制的：

• 0： 每秒刷新缓冲池中的数据写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据 ；
• 1： 事务每次提交的时候，就把缓冲池中的数据刷新到磁盘中；
• 2：提交事务的时候，把缓冲池中的数据写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件。可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

索引的底层实现

用的B+树，它是一个N叉排序树，每个节点通常有多个子节点。节点种类有普通节点和叶子节点。根节点可能是一个叶子节点， 也可能是个普通节点。

为什么选择用B+树作为索引的结构？

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

为什么是B+树，而不是平衡二叉树、红黑树或者B-树？

平衡二叉树追求绝对平衡，条件比较苛刻，实现起来比较麻烦，每次插入新节点之后需要旋转的次数不能预知。同时，B+树优势在于每个节点能存储多个信息，这样深度比平衡二叉树会浅很多，减少数据查找的次数。

红黑树放弃了追求完全平衡，只追求大致平衡，在与平衡二叉树的时间复杂度相差不大的情况下，保证每次插入最多只需要三次旋转就能达到平衡，实现起来也更为简单。但是红黑树多用于内部排序，即全放在内存中，而B+树多用于外存上时，B+也被称为一个磁盘友好的数据结构。同时，红黑树和平衡二叉树有相同缺点，即每个节点存储一个关键词，数据量大时，导致它们的深度很深，MySQL每次读取时都会消耗大量IO。

B+树非叶子节点只存储key值，而B-树存储key值和data值，这样B+树的层级更少，查询效率更高；MySQL进行区间访问时，由于B+树叶子节点之间用指针相连，只需要遍历所有的叶子节点即可，而B-树则需要中序遍历一遍。

聚簇索引和非聚簇索引（辅助索引、二级索引）

聚簇索引是主键上的索引，二级索引是非主键字段的索引。这两者相同点是都是基于B+树实现。

区别在于，二级索引的叶子结点只存储索引本身内容，以及主键ID，聚簇索引的叶子结点，会存储完整的行数据。在一定程度上，可以说二级索引就是主键索引的索引。

锁的分类

MySQL从锁粒度粒度上讲，有表级锁、行级锁。从强度上讲，又分为意向共享锁（IS）、共享锁(S)、意向排它锁(IX)和排它锁(x)。

select操作会加锁吗？

对于普通select语句，InnoDB 不会加任何锁。但是select语句，也可以显示指定加锁。有两种模式，一种是LOCK IN SHARE MODE是加共享锁，还有Select ... for updates是加排它锁。

什么情况下会发生死锁？

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

什么是间隙锁？

间隙锁就是对索引行进行加锁操作，不仅锁住其本身，还会锁住周围邻近的范围区间。间隙锁的目的是为了解决幻影读，但也因此带来了更大的死锁隐患。

比如，一个任务表里面有个状态字段，是一个非唯一索引，有一个任务id，是唯一索引。

一个sql将状态处于执行中的任务设置为等待中，另一个sql正好通过任务id更新在范围内的一条任务信息。那么因为是在不同索引加锁的，所以都能成功。但是最后去更新主键数据的时候，就会死锁。