InnoDB索引机制
MySQL－ InnoDB锁机制
InnoDB与MyISAM的最大不同有两点：
一是支持事务（TRANSACTION）；二是采用了行级锁。
MyISAM不支持事务、行锁和外键，访问速度快（表级锁，可同时读，不可写），多使用于多读写少或对事务完整性没有要求的情况；
MEMORY：将所有的数据保存在RAM中，对表的大小有限制；
MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们；
一、事务
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。
- 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
- 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
- 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
- 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
二、并发事务处理带来的问题
- 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务提交前，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
- 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
- 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
三、事务隔离级别
4种隔离级别比较
|
隔离级别 |
读数据一致性 |
脏读 |
不可重复读 |
幻读 |
|
未提交读（Read uncommitted） |
最低级别，只能保证不读取物理上损坏的数据 |
是 |
是 |
是 |
|
已提交度（Read committed） |
语句级 |
否 |
是 |
是 |
|
可重复读（Repeatable read） |
事务级 |
否 |
否 |
是 |
|
可序列化（Serializable） |
最高级别，事务级 |
否 |
否 |
否 |
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
四、事务传播行为
1、PROPAGATION_REQUIRES_NEW：创建新事务，无论当前存不存在事务，都创建新事务。
2、PROPAGATION_REQUIRED：支持当前事务，如果当前存在事务，就加入该事务，如果当前没有事务，就创建一个新事务。
3、PROPAGATION_SUPPORTS：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。‘
4、PROPAGATION_MANDATORY：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。
5、PROPAGATION_NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
6、PROPAGATION_NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。
7、PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。
五、InnoDB中的锁
InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。
InnoDB的锁类型有：
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
InnoDB行锁模式兼容性列表
|
锁 |
X |
IX |
S |
IS |
|
X |
冲突 |
冲突 |
冲突 |
冲突 |
|
IX |
冲突 |
兼容 |
冲突 |
兼容 |
|
S |
冲突 |
冲突 |
兼容 |
兼容 |
|
IS |
冲突 |
兼容 |
兼容 |
兼容 |
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；
若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。如下图，当我们要对记录加上排他锁时，那么我们先要对数据库A、表、页都加上意向排他锁，最后再对记录加上排他锁，若其中任何一部分导致等待，那么该操作需要等待粗粒度锁的完成。
六、InnoDB中锁的实现算法
InnoDB存储引擎有3种行锁的算法设计，分别是：
- Record Lock：单个行记录上的锁。
- Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了幻读的问题。
- Next-Key Lock：Gap Lock + Record Lock。锁定一个范围，并且锁定记录本身。
Record Lock总是会去锁住索引记录。如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。
InnoDB中对于行的查询都是采用Next-Key Lock锁定算法。对于不同SQL查询语句，可能设置共享的（Share） Next-Key Lock和排他的（exlusive） Next-Key Lock，其主要目的也是为了解决幻行问题。
但当查询的索引含有全部唯一属性（主键或唯一索引）时InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围，从而提高应用的并发性。
但是如果查询的条件中包含的是辅助索引，则情况会完全不同，会产生如下锁情况：
- 首先会对辅助索引对应的主键索引加上Record Lock。
- 对辅助索引加上Next-Key Lock，锁定辅助索引的前一个区间。
- 对辅助索引的下一个区间加上gap Lock。
如果查询条件没有索引，则全表扫描，且每条记录之间加上了gap锁，为了防止幻读。
下面是一个示例：
我们知道InnoDB默认的事务隔离级别为REPEATABLE READ模式，而REPEATABLE READ模式下，Next-Key Lock算法又是默认的行记录锁定算法，所以就可以避免幻读的现象。
七、InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
InnoDB存储引擎的表在不使用索引时使用表锁例子
|
session_1 |
session_2 |
|
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id = 1 ; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) |
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id = 2 ; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) |
|
mysql> select * from tab_no_index where id = 1 for update; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) |
|
|
mysql> select * from tab_no_index where id = 2 for update; 等待 |
session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行。
InnoDB存储引擎的表在使用索引时使用行锁例子
|
session_1 |
session_2 |
|
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 1 ; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) |
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 2 ; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) |
|
mysql> select * from tab_with_index where id = 1 for update; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) |
|
|
mysql> select * from tab_with_index where id = 2 for update; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) |
2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
InnoDB存储引擎使用相同索引键的阻塞例子
|
session_1 |
session_2 |
|
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) |
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) |
|
mysql> select * from tab_with_index where id = 1 and name = '1' for update; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) |
|
|
虽然session_2访问的是和session_1不同的记录，但是因为使用了相同的索引，所以需要等待锁： mysql> select * from tab_with_index where id = 1 and name = '4' for update; 等待 |
3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
InnoDB存储引擎的表使用不同索引的阻塞例子
|
session_1 |
session_2 |
|
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) |
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) |
|
mysql> select * from tab_with_index where id = 1 for update; +------+------+ | id | name | +------+------+ | 1 | 1 | | 1 | 4 | +------+------+ 2 rows in set (0.00 sec) |
|
|
Session_2使用name的索引访问记录，因为记录没有被索引，所以可以获得锁： mysql> select * from tab_with_index where name = '2' for update; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) |
|
|
由于访问的记录已经被session_1锁定，所以等待获得锁。： mysql> select * from tab_with_index where name = '4' for update; |
4） 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
八、面试题汇总
2、数据库索引为什么会失效？
- 全局扫描的效率高于建立索引
- 索引涉及强制的类型转换
- 索引上做相关的运算操作
