关于Mysql——索引，事务，锁

索引

在MySQL中常用的有两种引擎，InnoDB和MyISAM。

MyISAM的特点

1. 不支持事务,只支持表级锁(这是大部分情况下不使用MyISAM的原因)
2. B+树叶子节点存储的是数据指针，所以因此可知MyISAM的索引文件和数据文件是分开存储的，对于MyISAM，主键索引和二级索引没有区别，它们的叶子节点都是存储的是数据指针
3. MyISAM适合写少读多的场景，在某些场景下MyISAM速度会比InnoDB快

InnoDB的特点

1. 支持事务，支持行级锁
2. 主键索引使用聚簇索引，所谓聚簇索引，就是在B+树中非叶子节点只存放主键值和指向树节点的指针，在叶子节点存放完整的数据行，“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储，在InnoDB中，磁盘管理最小单位为page（默认大小16kb），一个页上的数据都是物理存放在一起的，读取一页就是进行了一次磁盘IO
3. 使用MVCC(版本并发控制)，MVCC是为了在数据被锁锁住时，为了不堵塞的查询，增加查询速度
4. 支持RU-读未提交、RC-读已提交、RR-可重复读（默认）、SERIALIZABLE-串行四种隔离级别

为什么在某些场景下，MyISAM速度会比InnoDB快

1. InnoDB只有在主键索引的叶子节点才存放数据行，在二级索引的叶子节点只存放对应的主键。所以当使用二级索引来查找时，首先在二级索引中找到对应的主键值，然后还需要回到主键索引的B+树中来查找数据行，此过程被称为回表。而MyISAM叶子节点存储的是数据指针，所以无需回表。
2. InnoDB需要维护MVCC，在RC的隔离级别下，每次SELECT都需创建一个ReadView，关于MVCC和ReadView在事务中会详细写
3. InnoDB增加了行锁，而MyISAM只有表锁，所以增加了开销

B树与B+树

• B树：多路平衡查找树

• B+树：改进了B树
  
  区别：
  B树在非叶子节点和叶子节点都存储了数据，而B+树只在叶子节点存储数据，非叶子节点只存储索引值和指针。所以这会导致B树的非叶子节点无法存储更多的索引和指针，这也会导致一次IO将读取更多的数据；并且会使B+树的查询时间复杂度更为稳定，而B树查询时间复杂度不稳定。B+树叶子节点都是用链表链接起来的，所以在范围查找时，会利用磁盘预读取机制把将要访问的数据提前读入内存，减少磁盘IO次数。

注意点

1. 由于InnoDB的聚簇索引的非叶子节点只存储key的副本和指针，每一个节点都存储在page中，page的大小是固定的（默认16kb），所以主键的大小应该尽可能的小，这样一个page才有可能存储更大的数据量
2. 由于InnoDB的聚簇索引非叶子节点存储的key都是按照左小右大的顺序存储的，所以主键的值应该是趋势递增的，如果不是趋势递增，插入一条新数据时会造成磁盘的随机IO，开销很大，并且如果插入的是一个满的page中会造成页分裂和碎片的产生

锁

表锁

使用表锁会锁定整张表，效率较低

行锁

行锁只会锁定某行，行锁又分为共享锁（读锁）和排他锁（写锁）

• 共享锁（读锁）
  共享锁是相互之间可以共存的，也就是说可以有多个共享锁一起操作某个行
• 排他锁（写锁）
  排他锁不能与共享锁和排他锁共存，也就是说同一时刻只能有一个排他锁操作某个行

是否冲突	共享锁	排他锁
共享锁	不冲突	冲突
排他锁	冲突	冲突

意向锁

意向锁分为共享意向锁和排他意向锁。获得共享锁之前会先获得共享意向锁，获得排他锁之前会先获得排他意向锁，意向锁的作用是告知表锁此时有行锁正在锁定或者即将锁定某个数据行，使得表锁和行锁之间能够共存。举个简单的例子，
如果此时事务A有一个排他锁正在操作表中某一行，此时事务B要获取表锁，事务B在申请表锁时先判断该表是否存在表锁，该表并无表锁，然后再判断该表是否存在意向锁，该表存在一个排他意向锁，所以事务B会被阻塞。

是否冲突	共享锁	排他锁	共享意向锁	排他意向锁
共享锁	不冲突	冲突	不冲突	冲突
排他锁	冲突	冲突	冲突	冲突
共享意向锁	不冲突	冲突	不冲突	不冲突
排他意向锁	冲突	冲突	不冲突	不冲突

通过这个表格，可以看出意向锁之间是可以兼容的，这也很好理解，这使得两个操作不同数据行的行锁是可以同时存在的，符合行锁的目的。

插入意向锁

插入意向锁是一种间隙锁，插入间隙锁表示一种插入的意图，如果多个事务插入相同间隙的不同位置，不会阻塞等待。举个例子，此时有数据2和9，如果事务A插入3，事务B插入7，虽然事务A和B都是操作的相同的间隙2，9，但是A,B插入的不是相同的位置，所以A，B之间不必相互等待。

行锁的实现

行锁由间隙锁（Gap Key）、记录锁（Record Key）和next-key这三种锁实现

• 记录锁
  记录锁是锁定索引记录的锁，例如SELECT * FROM x WHERE id = 1 FOR UPDATE(id为唯一索引)，这条语句会锁定id=1的数据行，其他事务无法对其进行增删改。记录锁只会锁定索引记录，锁定非聚簇索引会先锁定聚簇索引。
• 间隙锁
  间隙锁是锁定索引之间的范围，例如SELECT * FROM x WHERE id BETWEEN 1 AND 10 FOR UPDATE（id为唯一索引）,这条语句会锁定id值为1-10的范围，其他事务无法对1-10这个区间内插入数值，
  如果id为非唯一索引，会锁定整张表，需要注意。
• next-key锁
  记录锁+间隙锁实现了next-key锁，因此next-key锁定了锁定记录以及这条索引记录的间隙，还是SELECT * FROM x WHERE id BETWEEN 1 AND 10 FOR UPDATE（id为唯一索引）这个例子并且x这个表上有id=1、2、3，详细一点分析，它锁定了（negative infinity,1]、(1,2]、(2,3]、(3,positive infinity)这四个next-key锁。单拿（1，2]，这个next-key锁来看，这是由记录锁+间隙锁实现的，记录锁为id=2，间隙锁为（1，2）
  在RR的隔离级别下，next-key锁用于防止幻读
  如果索引是主键或者唯一索引，next-key锁会降级为记录锁，如上述记录锁的例子一样

事务

我们都知道，事务需满足ACID四个原则，mysql中的事务也不例外，但是往往这四种原则都不能非常完美的实现，在实际的实现中需要做一些取舍

• A（atomicity）原子性
  原子性是指事务要么全部成功，要么全部失败（回滚），这也是事务最重要的一项原则。
• C（consistency）一致性
  举个例子，A给B转账50元，如果A的账户少了50元而B的账户并没有多了50元，那么这就是不一致的
• I（isolation）隔离性
  隔离性指的是在一个事务中，不会被别的事务而干扰，这有点抽象，我会在下面详细介绍InnoDB关于隔离性的机制
• D（durability）持久性
  持久性指的是事务提交成功后会持久性的保存在磁盘中，不会因为断电等因素丢失数据

InnoDB实现事务依靠了在表中的三个隐藏字段以及undo log和redo log，下面介绍一下这些概念。

undo log

undo log是保存旧的数据，新的保存在数据表中的数据行当DB_ROLL_PTR回滚指针就会指向该条数据的旧的版本，当事务需要回滚时，就需找到回滚指针指向的数据行，把旧的数据替换上去，实现了原子性。undo log除了回滚时需要用到，快照读时也需要,关于快照读,下面会介绍。

需要注意的是，如果是insert操作，当事务提交后，undo log就会丢弃这条insert的记录，因为insert只需要在回滚时需要用到，快照读时并不需要。

redo log

redo log一种物理日志，所谓物理日志，指的是日志中存储的是修改的值（逻辑日志指的是存储的是修改数据的sql语句），当mysql因断电或者其他原因重启后，mysql就能够利用redo log实现快速的数据还原，实现了持久性。redo log分为在缓存上的redo log buffer和在磁盘中的redo log file，如果每次事务的执行都会写入在磁盘中的redo log file，会造成大量IO，影响性能，所以事务的提交并不一定会直接写入磁盘，而是先写入内存中的redo log buffer，再经过master thread写入磁盘（可以通过设置innoDB_flush_log_at_trx_commit来更改写入磁盘时机）

隐藏字段

InnoDB的数据表中有三个隐藏的字段，分别为：

1. DB_TRX_ID(6字节)：事务ID，表示最近一次操作该数据行当事务ID（update，insert），对于delete操作，InnoDB会把update操作视为update操作，只是修改了删除状态标志位。
2. DB_ROLL_PTR(7字节)：回滚指针，指向undo log中该数据行当旧的数据
3. DB_ROW_ID(6字节)：隐藏主键ID，当表没有显示设置主键ID时，InnoDB会指定第一个非null的唯一索引为主键，如果没有非null的唯一索引，就会利用这个字段隐式的设置一个主键

关于InnoDB中隔离性的介绍

InnoDB中有四种隔离级别：

1. Read Uncommitted：读未提交，指的是事务A可以读到事务B还未提交的事务，这种情况也叫做脏读
   
2. Read Committed：读已提交，指的是事务A只能读到事务B提交的事务，避免了脏读，但是如果下图所示情况，就被称为不可重复读，即同一个事务两次读取的数据是不同的
   
3. Repeatable Read: 可重复读，可重复读利用了MVCC解决了不可重复读的问题，但是在当前读中会有幻读的问题，在快照读中不会产生幻读，幻读情况如下，mysql利用next-key解决了当前读时的幻读问题
   
4. SERIALIZABLE：串行化，读时加共享锁，写时加排他锁

MVCC多版本控制

RC和RR都是利用MVCC来实现的，在RU中读取的都是最新版本的数据，在串行化中，读时加共享锁，写时加排他锁，所以无需MVCC。
MVCC的实现主要依靠undo log、ReadView以及隐藏字段

ReadView

• low_limit_id:目前出现过的最大事务ID+1
• up_limit_id:活跃事务列表id_trxs中最小事务id
• id_trxs：当前活跃事务列表，即当创建当前事务时，以开始但还未提交的事务id列表
• creator_trx_id:当前创建事务的id

可见性比较算法

假设当前事务ID为trx_id，当trx_id>=low_limit_id时，表示在ReadView创建之后，这个事务才提交，所以不可见；当up_limit_id=<trx_id<low_limit_id时,表示在这个ReadView创建时,这个事务已经开始但还未提交,所以也是不可见的;当trx_id<up_limit_id时，表示这个ReadView开始时这个事务已经提交，所以是可见的。

ReadView在RC和RR间的差异

在RC的隔离级别下,每次select都会创建一个新的readview,而RR中，只有在事务的第一次select才会创建一个readview，并且在提交前都会使用这个readview。
注意：在RR中，快照读不会造成幻读，而当前读会引起幻读，mysql是利用了next-key解决了RR中当前读的幻读问题。
参考博客
通过各种简单案例，让你彻底搞懂 MySQL 中的锁机制与 MVCC
MySQL中MVCC的正确打开方式（源码佐证）
图片来源
一文理解Mysql MVCC
mysql事务隔离级别
MySQL中MVCC的正确打开方式（源码佐证）
一文彻底搞懂MySQL基础：B树和B+树的区别