MySQL锁、事务和索引

并发事务的控制方式是MVCC和行锁

按范围分：表级锁、页锁、行级锁（锁一行或者多行）（记录索、间隙锁、临键锁（可重复读默认用这个做行锁，除非是主键和唯一索引会使用记录索））

按功能分：读锁（S）、写锁（X）

意向锁是表级锁，分为意向共享锁、意向排他锁，用于协调表锁和行锁的关系，事务想要在某些行上加共享锁/排它锁，需要先取得意向共享/排他锁，这个是由数据库引擎自己维护的

插入意向锁是特殊的间隙锁，同种锁之间只要索引主键不冲突就不会阻塞

自增锁是用于自增主键的

数据库的隔离级别：

读未提交（READ-UNCOMMITTED）:最低的隔离级别，能读取到并发事务未提交的数据，会产生脏读、不可重复读、幻读

读已提交（READ-COMMITTED）: 事务内，普通读能读到并发事务已经提交的数据，可以阻止脏读，会发生不可重复度和幻读

可重复读（REPEATABLE-READ）：事务内，对同一行数据的普通读（非当前读），读到的数据是不变的

串行化（SERIALIZABLE）：最高的隔离级别，完全服从ACID，事务依次诸葛执行，没有并发事务，能防止脏读、不可重复读、幻读

脏读：可以读取到别的事务还未commit的数据

不可重复读：在一个事务内，对同一数据的多次读取（普通读），结果不一致

幻读：在一个事务内，对同一范围的数据进行多次读取（普通读），会受到并发事务的插入和删除的影响，从而导致结果集不一致，可能多了几条数据，可能少了几条数据

普通读：select * from table

当前读（更新读）：select * from table for update

MySQL默认是可重复读，PostgreSQL默认读已提交

MySQL的InnoDB的可重复读通过MVCC和临键锁可以解决大部分的幻读，为什么是大部分呢，如下是一个例子

背景：InnoDB+可重复读

1. A开启事务，并执行sql：select * from table where id > 101，结果为0条数据

2. B开启事务，insert into table (id, name) values (101, 'aa'), 并提交事务

3. A执行sql: update table set name = 'bb' where id = '101', 可以修改

4. A执行sql: select * from table where id > 101，能查到101这条数据，并提交事务

虽然事务A一开始普通读查到的记录数为0，但是后续经过update以后，普通读能查到1条数据，就产生了幻读

MySQL的InnoDB+可重复读

插入：插入意向锁，特殊的间隙锁，同种锁之间只要索引主键不冲突就不会阻塞

更新：可能表锁（不走索引）、临键锁（普通索引）、记录锁（唯一索引/主键索引）

删除：可能表锁（不走索引）、临键锁（普通索引）、记录锁（唯一索引/主键索引）

更新读：可能表锁（不走索引）、临键锁（普通索引）、记录锁（唯一索引/主键索引）

InnoDB在可重复读的隔离级别下，因为使用了临键锁，把间隙给锁起来了，扩大了锁的范围，就容易发生死锁

死锁的例子

覆盖索引：就是select字段包含在索引内了，一般使用联合索引，将需要select的字段和一个需要做where条件的字段组成联合索引，就不会回表（因为普通索引会存主键和自身的索引值，所以select的字段都包含了，就不会回表了，本来会进行回表查询，先根据普通索引查到主键，然后根据主键再去找到select字段）

索引下推：一般是发生在联合索引中，where有多个条件，提前判断第二第三个条件，把不符合的记录直接过滤掉

在5.6之前的版本（没有索引下推），先判断第一个条件，满足就回表取回整行数据，然后再server层，server层再进行判断第二、第三个条件

例子：name 和age组成联合索引，select * from table where name like 'zh%' and age=10;  存储引擎会在取回数据前，就根据name和age进行了过滤，因为这里用的是select * ，所以是减少了回表的次数，如果是select name, age  就不会回表