Mysql索引介绍

这节讲述的是InnoDB使用的锁类型：

包含：

    Shared and Exclusive Locks(共享锁与独占锁)

    Intention Locks(意向锁)

    Record Locks(记录锁)

    Gap Locks(间隙锁)

    Nexy-Key Locks(下一个key锁？)

    Insert Intention Locks(插入意向锁)

    AUTO-INC Locks(自增锁)

    Predicate Locks for Spatial Indexes（空间索引的谓词锁）

1. Shared and Exclusive Locks(共享和独占锁)

InnoDB实现了标准的行级锁，包含：共享锁（S）和独占锁（X）。

        共享锁（S）允许持有该锁的事务去读取一行。

        独占锁（X）允许持有该锁的事务去更新和删除一行。

当事务T1持有行R的共享锁（S）时，另外一个事务T2想获取行R的锁时：

        T2能够立即获得行R的共享锁（S），所以，T1和T2同时持有行R的共享锁(S)。

        T2想获取行R的独占锁（X）时，必须等待。（mine:应该是要等待行R上所有的共享锁释放）

当事务T1持有行R的独占锁（X），另外一个事务T2想要获取共享锁和独占锁时，都必须等待事务T1释放行R的锁。

2. Intention Locks(意向锁)

InnoDB支持多种粒度的锁，允许行级锁和表级锁共存。比如，使用Lock TABLES ... WRITE就能获取某张表的独占锁（X）。InnoDB使用意向锁来实现多种粒度级别的锁。意向锁是表级锁，它表明需要在事务中获取表中的某行的独占锁或共享锁。

有下面两种类型的意向锁：

        意向共享锁（IS）：一个事务意图在表中的某几行设置共享锁。

        意向独占锁（IX）：一个事务意图在表中的某几行设置独占锁。

比如说：Select  ... LOCK IN SHARE MODE就是一种意向共享锁（IS）；而Select ... for update 就是一丈红意向排它锁（IX）。

意向锁的协议规则是这样的：

        一个事务想获取一张表中一行的共享锁，就必须先获取这张表的意向共享锁或更强的锁。

        一个事务想获取一张表中一行的独占锁，就必须先获取这张表的意向独占锁（IX）。

下面是表级别锁的兼容性：

XIXSIS

X冲突冲突冲突冲突

IX冲突兼容冲突兼容

S冲突冲突兼容兼容

IS冲突兼容兼容兼容

一个事务想获取与现有的锁的兼容的锁，而不能获取与现有锁冲突的锁。如果有冲突的锁，就必须等待冲突的锁被释放。

意向锁不会阻塞任何东西，除了全表请求，比如：Lock TABLES ... WRITE。意向锁的主要目的是表示某人正在锁某行或将要锁表中的某行。

意向锁可以通过SHOW ENGINE INNODB STATUS命令： 

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

3. 记录锁

记录锁是索引记录上的锁。比如：select c1 from t where c1 = 10 for update。可以防止其他事务插入，更新和删除t.c1值为10的行。

记录锁锁的是索引记录，甚至当表中没有索引时。没有索引的情况下，InnoDB会创建一个隐形的聚簇索引而且利用这个索引来使用记录锁。

当存在记录锁时，可以通过Show engine innodb status获取到下面的信息：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`

trx id 10078 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8000000a; asc    ;; 1: len 6; hex 00000000274f; asc    'O;; 2: len 7; hex b60000019d0110; asc        ;;

4. 间隙锁

间隙锁是会锁索引记录之间间隙的，或第一个索引之前或最后一个索引之后的索引记录。比如说：select c1 from t where c1 between 10 and 20 for update。防止其他事务插入一个t.c1值为15的记录，不管该列是否已经存在该值，因为这个区间所有现有的值都没锁住了。

一个间隙可能跨度单个索引值，多个索引值，甚至空的。

间隙锁是性能和并发性之间的权衡，使用在某些事务隔离级别中而不是全部。

间隙锁不会出现在使用一个唯一索引去查询一个唯一的行。而这并不包括搜索条件只包含多列唯一索引的一些列（就是说并不能完全走到唯一索引）。

比如：

select * from child where id = 100;

如果id有唯一索引，那么上面语句仅仅为id值为10的行使用索引记录的锁，其他事务也可以在它之前的间隙中插入行。（这里的间隙是啥？）

如果id没有索引或者不是一个唯一索引，上面的语句将会锁住前面的间隙（the preceding gap）。

值得一提的是，不同的事务能够对同一个间隙持有相冲突的锁。比如：事务A能够获取一个间隙的共享锁，而事务B能够获取此间隙的独占锁。之所以允许同时存在冲突的间隙锁是因为如果一个索引上的一条记录被删除（purged），那么这条记录关联的间隙锁必须被合并（merged）。

间隙锁在InnoDB是“纯粹禁止的”，意味着它仅仅阻止其他事务插入到这个间隙中。而并不能防止不同的事务持有相同的间隙锁，因此，间隙独占锁和间隙共享锁的作用一样。

间隙锁可以被禁止掉。比如说你把事务的隔离级别设置为Read Committed或者在系统变量中开启innodb_locks_unsafe_for_binlog（现在不推荐使用）。在以上禁止间隙锁的情况下，InnoDB在搜索数据和扫描索引的时候不会使用间隙索引，而仅在外键约束检查和重复键检查的时候会使用间隙锁。

当我们使用READ COMMITETED隔离级别或开启innodb_locks_unsafe_for_binlog时，会产生一些其他的影响。MySQL会释放where条件不满足的行的记录锁。对于Update语句，InnoDB做了一个“半一致性”的读取，这样它就把最新提交的版本返回给MySQL，这样MySQL就能判断行是否匹配更新的状态。

5. Next-Key Locks

Next-Key锁是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合。

InnoDB以这样一种方式执行行级锁：当它搜索或扫描一个表索引时，它会在它遇到的索引记录上设置共享或独占锁。因此，行级锁实际上是索引记录锁。Next-Key锁会将一个索引记录和这个索引记录之前的间隙加锁。因此，Next-Key锁是一个索引记录锁加索引记录之前的间隙的间隙锁。如果一个会话持有了记录R索引的共享锁或独占锁，另外一个会话不能够马上插入一条新的记录到R索引前面。

假设一个索引包含：10，11，13，20 这几个值。那么Next-Key锁可能会锁住一下几个区间：

（负无穷， 10]

（10， 11]

（11，13]

（13，20]

（20，正无穷）

在最后一个区间，Next-Key锁锁住了索引中最大的值和“至上”伪记录之间的差距，它的值比索引中的任何值都要高。至上者并不是一个真正的索引记录，因此，实际上，这个下键锁只锁住了最大索引值之后的差距。

默认情况下，InnoDB使用REPEATABLE READ（可重复读）。InnoDB使用Next-Key锁来保证出现幻读。

当存在Next-Key锁时，我们可以通过SHOW ENGINE INNODB STATUS查看到以下信息：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`

trx id 10080 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8000000a; asc    ;; 1: len 6; hex 00000000274f; asc    'O;; 2: len 7; hex b60000019d0110; asc        ;;

6. 插入意向锁

插入意向锁是在执行INSERT操作时使用的一种间隙锁。这种锁是为了插入不同gap的Insert操作不会相互阻塞。假设一个索引上有4和7两个索引值，两个事务意图想插入值为5和6的记录，两个事务先去获取4和7之间的插入意向锁，然后再去获取被插入行的独占锁，但是这两个事务并不会相互阻塞，因为插入的行是不冲突的。

下面的例子是验证事务先获取插入意向锁，然后再获取被插入记录的独占锁。例子包含两个客户端Ah和B。

客户端A创建一张包含两条索引记录的表（90和102），然后开启一个事务去获取id大于100的索引值的独占锁。这个独占锁包含102之前的间隙锁。

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;mysql> INSERT INTO child (id) values (90),(102);mysql> START TRANSACTION;mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;+-----+| id  |+-----+| 102 |+-----+

客户端B开启一个事务，插入一条记录到这个间隙中。该事务在等待获得独占锁时，需要获取一个插入意图锁。

mysql> START TRANSACTION;mysql> INSERT INTO child (id) VALUES (101);

当存在插入意向锁时，我们可以通过SHOW ENGINE INNODB STATUS查看到以下信息：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`trx id 8731 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000066; asc    f;; 1: len 6; hex 000000002215; asc    " ;; 2: len 7; hex 9000000172011c; asc    r  ;;...

7. 自增锁

自增锁是一个特殊的表级锁，当事务插入的列包含自增列的时候。当一个事务正在插入数据到表中，其他的事务必须等待自己的插入完成，所以第一个事务将会获取到连续的值。

8.空间索引的谓词锁  .....