InnoDB原理以及索引优化

数据库事务设计遵循ACID原则 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

mysql默认的4种隔离级别

读未提交(read-uncommitted)、读已提交(或不可重复读)(read-committed)、可重复读(repeatable-read)、串行化(serializable)。

MySQL的默认隔离级别是RR。

一 共享锁 独占锁

mysql提供两种行锁 共享锁(shared locks，S锁)   独占锁(exclusive locks，X锁)

共享锁运行当前事务读取行 独占锁运行当前事务更新和删除行

共享锁  事务T1获取行x的共享锁，其他事务同时也可获取行x的共享锁

独占锁  事务T1获取行x的独占锁，其他事务必须等到T1释放锁才能获得该锁

如果事务T1在行r上保持S锁，则另一个事务T2对行r的锁的请求按如下方式处理：

         T2可以同时持有S锁；

         T2如果想在行r上获取X锁，必须等待其他事务对该行添加的S锁或X锁的释放。

二  意向锁-Intention Locks

InnoDB支持多种粒度的锁，允许行级锁和表级锁的共存。

InnoBD使用意向锁来实现多个粒度级别的锁定。意向锁是表级锁，表示table中的row所需要的锁(S锁或X锁)的类型。

意向锁分为意向共享锁(IS锁)和意向排它锁(IX锁)。IS锁表示当前事务意图在表中的行上设置共享锁，下面语句执行时会首先获取IS锁，因为这个操作在获取S锁：

SELECT ... LOCK IN SHARE MODE

IX锁表示当前事务意图在表中的行上设置排它锁。下面语句执行时会首先获取IX锁，因为这个操作在获取X锁：

SELECT ... FOR UPDATE

事务要获取某个表上的S锁和X锁之前，必须先分别获取对应的IS锁和IX锁。

InnoDB中的锁

记录锁Record

Record Lock是对索引记录的锁定。

记录锁有两种模式：S模式和X模式。例如：SELECT id FROM test WHERE id = 10 FOR UPDATE; 表示防止任何其他事务插入、更新或者删除id =10的行。

记录锁始终只锁定索引。即使表没有建立索引，InnoDB也会创建一个隐藏的聚簇索引(隐藏的递增主键索引)，并使用此索引进行记录锁定。

间隙锁Gap Locks

间隙锁作用在索引记录之间的间隔，又或者作用在第一个索引之前，最后一个索引之后的间隙。不包括索引本身。

例如：

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

这条语句阻止其他事务插入10和20之间的数字，无论这个数字是否存在。

Next-key锁

Next-key锁实际上是Record锁和gap锁的组合。Next-key锁是在下一个索引记录本身和索引之前的gap加上S锁或是X锁(如果是读就加上S锁，如果是写就加X锁)。

默认情况下，InnoDB的事务隔离级别为RR，系统参数innodb_locks_unsafe_for_binlog的值为false。InnoDB使用next-key锁对索引进行扫描和搜索，这样就读取不到幻象行，避免了幻读的发生。

插入意向锁

插入意向锁在行插入之前由INSERT设置一种间隙锁，是意向排它锁的一种。在多事务同时写入不同数据至同一索引间隙的时，不会发生锁等待，事务之间互相不影响其他事务的完成，这和间隙锁的定义是一致的。

假设一个记录索引包含4和7，其他不同的事务分别插入5和6，此时只要行不冲突，插入意向锁不会互相等待，可以直接获取。

自增锁

自增锁(AUTO-INC Locks)是事务插入时自增列上特殊的表级别的锁。最简单的一种情况：如果一个事务正在向表中插入值，则任何其他事务必须等待，以便第一个事务插入的行接收连续的主键值。

三 幻读

在同一个事务中，执行同一个sql，得到不同的结果

幻读在RR条件下是不会出现的。

RC(Read Commit)隔离级别可以避免脏读，事务内无法获取其他事务未提交的变更，但是由于能够读到已经提交的事务，因此会出现幻读和不重复读。也就是说，RC的快照读是读取最新版本数据，而RR的快照读是读取被next-key锁作用区域的副本。

乐观锁与悲观锁

乐观锁：在UPDATE的WHERE子句中加入版本号信息来确定修改是否生效；

悲观锁：在UPDATE执行前，SELECT后面加上FOR UPDATE来给记录加锁，保证记录在UPDATE前不被修改。SELECT ... FOR UPDATE是加上了X锁，也可以通过SELECT ... LOCK IN SHARE MODE加上S锁，来防止其他事务对该行的修改。

死锁

   无论是哪种场景，万变不离其宗，都是由于某个区间上或者某一个记录上可以同时持有锁，例如不同事务在同一个间隙gap上的锁不冲突；不同事务中，S锁可以阻塞X锁的获取，但是不会阻塞另一个事务获取该S锁。这样才会出现两个事务同时持有锁，并互相等待，最终导致死锁。

其中需要注意的点是，增、删、改的操作都会进行一次当前读操作，以此获取最新版本的数据，并检测是否有重复的索引。这个过程除了会导致RR隔离级别下出现死锁之外还会导致其他两个问题：

第一个是可重复读可能会因为这次的当前读操作而中断，(同样，幻读可能也会因此产生)；

第二个是其他事务的更新可能会丢失(解决方式：悲观锁、乐观锁)。

记录锁Record记录锁Record