LaoKiea

InnoDB的间隙锁是避免在RR隔离级别下出现”幻读“，间隙相对于行锁来说更加复杂，且加锁范围更大，如果业务可以保证不会出现幻读，可以考虑关闭。但是如果业务不能保证，那么间隙锁确实能为业务省去不少烦恼，那么mysql针对间隙锁是怎么处理加锁规则的，下面根据我的理解简单总结一下，以备以后翻阅。

开始前先建以下表：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

然后上几个规则：

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

针对上面几个规则，先说说我觉得比较绕的地方，然后再用例子辅助理解：

1. ”next-key lock 是前开后闭区间“：间隙锁可以理解成都是开区间，加上行锁，才组成前开后闭的情况。

2. “给唯一索引加锁的时候，next-key lock 退化为行锁” ：退化意思就是，在唯一索引上做等值查询，那么原本要加上的间隙锁就被去掉了，只留下行锁。

3. “索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候”：这个意思就是，在遍历索引时，如果遍历的最后一个值不是等值查询的那个值，那么next-key lock 里的行锁就去掉了，只留下间隙锁。这里强调的是最后一个节点。

下面我举个几个例子来理解上面的规则：

非唯一索引的加锁

select id from t where c = 5 lock in share mode；

这个sql的查询过程大致 如下，通过c索引，找到5这个节点，那么会加行锁和间隙锁，范围是(0,5]，但是c索引不是唯一索引，索引innodb会继续往下遍历，找到c索引的下一个值，就是10，满足了规则2，即访问到的节点都需要加锁，所以会加(5,10]，但是这个是最后一个节点，且10不等于等值查询条件（c = 5）,所以这个next-key lock就退化成间隙锁(5,10)，所以这个语句一共会加以下两个间隙锁和一个行锁，那么我另外一个事务里，有

update t set c = c +1 where id = 5 lock in share mode;

第一个直觉会想到，id对应的那条记录已经加锁里，所以会等待，但结果是这个sql是可以执行成功的，原因是，锁是加在索引上的，也就是说上面的锁都是在索引c上，而select语句用到里索引覆盖，并没有回表去访问主键索引上的值，所以主键上没有加锁，所以update语句可以执行成功，因为是用主键进行树搜索的。但是lock in share mode改成for update是会给主键对应的节点加锁的。

如果上面的sql改成

select id from t where c = 5 limit 1 lock in share mode;

那么innodb在找到5这个节点后，并不会往后遍历到10，索引这个语句加锁是间隙锁和行锁(0,5]

唯一索引范围锁

select * from t where id >= 10 and id < 15 lock in share mode;

对于上面这个语句，加锁过程大致如下：

先在主键索引上找到值为10的节点，加锁(5,10]，但由于优化1，间隙锁会去掉，留下行锁，然后遍历下一个值，找到15，访问到的节点都需要加锁，于是加next-key lock，所以整个sql加锁是id=10的行锁和(10,15]间隙锁

非唯一索引范围锁

select * from t where c >= 10 and c < 15  in lock share mode;

过程和上面的例子类似，先访问c索引值为10的节点，加锁(5,10]，而由于c是非唯一索引，所以不会退化成行锁，再访问15，加锁(10, 15]，因为这个查询不是等值查询，所以锁不会退化成间隙锁。

如果上面的例子改成c <= 15，向后遍历c索引时，还会找20那个值，那么间隙锁的范围会更大。

 

参考：《mysql实战45讲》

mysql的大部分操作都是在内存中执行，以一个update为例，如果要更新的数据所在page已经被加载到内存中了，那么整个update过程其实就是更改内存该页中的值，外加写redo log。

当一个sql突然变慢，可能由于myql正在刷脏页，如果一次操作刷的脏页很多，那么是会影响性能的。比如select一个很大的结果集，那么mysql可能要在buffer pool中申请好几个page，那么如果根据LRU淘汰的页是脏页，那么必须先刷脏变成干净页，然后才能复用。

那么什么情况下会，mysql会进行刷脏呢？

内存不够用

如果内存不够用了，这其实是常态，一个长时间使用的mysql，buffer pool中很少有干净页，每次操作都需要从磁盘加载数据进buffer pool的话，都会根据LRU算法淘汰旧数据，如上所说，如果淘汰的是脏页必须先刷脏。而查询结果集很大是会导致查询变慢的。

redo log写满了

redo log在mysql中，是非常重要的存在，WAL，奔溃恢复等都离不开redo log，所以当redo log写满了，mysql必须停止所有的更新操作，然后把checkpoint往前推进，那么推进的这一部分对应的脏页都需要刷到磁盘上。

mysql正常关闭

mysql正常关闭时后，会将内存中的脏页全部刷到磁盘上。

mysql空闲时

mysql在服务器负载较小时会主动刷脏页。

以上四种，后面两种其实不需要去关心，而可能会有性能影响的是前两种，因为mysql可能会一下刷很多脏页，那么怎么去优化呢？

刷脏页主要就是磁盘的写操作，那么mysql首先要知道，磁盘的写能力。

mysql有两个参数来设置磁盘的io能力，分别是innodb_io_capacity和innodb_io_capacity_max，分别表示磁盘io能力和极限。前者可以设置成磁盘的IOPS，即每秒io次数（可以用fio命令测试，可以多测几次）。

想一下如果你用的是ssd那么这个值可以设置到10000到20000，而你却设置成200，那么mysql在刷脏页时肯定是刷的“小心翼翼”。

当然，mysql并不会每次刷脏页时都按照这个读写能力去控制刷脏页的速度，他会根据当前mysql的脏页比例以及当前redolog的LSN和checkpoint的LSN的差值来计算出一个刷脏速率。

脏页比例是通过Innodb_buffer_pool_pages_dirty和Innodb_buffer_pool_pages_total计算

show status like "%pages_dirty%";
show status like "%pages_total%";

 总结

以一个问题开始，mysql怎么判断某页是否是脏页？

说下个人理解，系统会维护一个LSN，每次在记录日志时该值都会增加，每个数据页的头部都会记录这个值，checkpoint也会对应一个LSN，这个LSN是内存中最早的脏页的LSN，以一个update举例，过程如下：

记录日志到内存中的redo log buffer，内存中该页的LSN增加，为旧LSN + 日志大小

修改内存中的数据页，该页的LSN增加同样的值

根据innodb_flush_log_at_trx_commit = 1（通常会这么设置），将redo log buffer刷到redo log file，文件中保存该日志的page对应的LSN增加同样的值。

而mysql刷脏时，idb文件中保存该数据的page也会增加

回到问题，如何判断一个页是否是脏页？只要判断该页的LSN如果大于checkpoint的LSN，那么该页就是脏页，同样的，mysql在崩溃恢复时，会从redo log的checkpoint处开始进行重放操作，那么redo log对应磁盘上的页大于LSN时，表示需要重放，否则跳过该log。

关于checkpoint和lsn的一些杂记

每个数据页都有LSN
redo log有两个LSN，一个是in buffer，一个是on disk，show innodb engine status可以查看
数据库重启时，检查redo log file，对比log对应的page的LSN和redo log file的LSN值，如果小于

更新时先写内存page（page有一个LSN），然后写log进log buffer（LSN），commit时innodb_flush_log_at_trx_commit写日志进磁盘file（磁盘pageLSN）

在 Innodb 每次都取最老的 modified page 对应的 LSN，并将此脏页的 LSN 作为 Checkpoint 点记录到日志文件，意思就是 “此 LSN 之前对应的日志和数据都已经刷新到磁盘” 。

当 MySQL 启动做崩溃恢复时，会从 last checkpoint 对应的 LSN 开始扫描 redo log ，并将其应用到 buffer pool，直到 last checkpoint 对应的 LSN 等于 log flushed up to 对应的 LSN，则恢复完成。
（加入有两个脏页产生了
LSN = 第二个脏页的LSN
checkpointLSN = 第一个脏页的LSN，重启时从checkpoint（last checkpoint at LSN）开始遍历日志，第一条日志对应磁盘page的LSN < redo log LSN,开始重放至buffer pool，递增last checkpoint at LSN，第二条日志对应磁盘的pageLSN <= redo log LSN, 递增last checkpoint at LSN直到last checkpoint at LSN = redo log LSN = log flush up to = lsn）

当数据库宕机时，数据库不需要重做所有日志，因为CheckPoint之前的页都已经刷新回磁盘。只需对CheckPoint后的重做日志进行恢复，从而缩短恢复时间。

 

参考： 《mysql实战45讲》

平常开发中，字符串字段使用的场景应该是非常多了，用户名、邮箱等等，针对字符串的长短，使用场景，如何合理的建立索引是非常重要的，下面来简单探讨下，以邮箱登陆这个场景为例：

前缀索引

假如用户的邮箱长度平均在20个字节，直接给email字段建立索引，索引占据的空间会变大，每页所能保存的索引值就减少，那么在查询时读取进内存的页变多，增加了磁盘io，影响效率。

mysql是支持前缀索引的，也就是可以给字段的前N个字节创建索引，比如取前4个字节为索引值：

alter table user add index email_index(email(4))

那么前缀索引和包含整个字符串的索引除了索引文件大小不一样，还有一些查询效率上的区别。

假如我们的邮箱有xxxx1@gmail.com，xxxx2@gmail.com，yyyy1@gmail.com三个值，那么在做查询

select * from user where email = "xxxx1@gmail.com"

 前缀索引

1. 先在索引树中找到xxxx（left email 4）这个值，拿到主键id，再回表查询行数据，判断email是xxxx1@gmail.com，然后将结果加入到结果集中

2. 接着从刚刚找到的位置上找到下一条记录，结果还是xxxx，同样的拿到id回表查询，发现email值不符合，丢弃

3. 接着从刚刚的位置找下一条，结果是yyyy不符合，查询结束

整个过程回表两次

整个字符串作为索引

1. 先从索引树中找到xxxx1@gmail.com这个值，拿到id，回表取行数据，将该行加入到结果集

2. 从刚刚的位置取下一条判断索引值不符合，查询结束

整个过程只回表了一次

由此可见，选好前缀索引的长度非常重要，一个合适的值N，不但可以减少索引占据的空间，还能减少回表次数。通常选取N值是按照区分度来做，比如先查询该字段的非重复值的次数

select count(distinct(email)) from user

再依次选取不同的长度做测试：

select count(distinct(left(email, 4))) from user
select count(distinct(left(email, 5))) from user
select count(distinct(left(email, 6))) from user

选差距最小的长度。

前缀索引虽然减少了索引数据占据的空间，但是某些场景下会增加查询的回表次数，而且前缀索引无法用到索引覆盖这个优点，还是刚刚的例子：

如果查询语句改成

select id,email from user where email = “xxxx1@gmail.com”

那么对于前缀索引，必须要回表检查email的值是否匹配，也就是说前缀索引还是需要回表两次，但是第二种直接从索引中取值即可，不需要回表。同样的like查询即使前缀索引满足最左前缀原则，同样需要回表判断

 

其他创建索引的方式

如果对于某些字符串很长，但是前缀区分度不高，比如一共20个字节长度，主要区分是在最后几个字符，这时候有以下两种做法：

1. 可以使用倒序存储+前缀索引

可以在存储的时候倒序存储，然后创建前缀索引，查询的时候

select * from user where `field` = reverse("your search key");

 2. 给字段创建hash值字段

可以再创建一个字段，保存该字符串的hash值，然后给hash值创建索引，hash值一般int类型保存即可，查询时

select * from user where `field_crc` = crc32("your search key");

hash值可能会出现冲突，所以查询语句改一下：

select * from user where `field_crc` = crc32("your search key") and field = "your search key"

这两种方式都能解决字符串很长但是前缀区分度不高的情况，二者都不能利用索引进行范围查询和排序且二者在空间和时间上效率相当。

 总结

1. 前缀索引可以针对字符串特别长且区分度足够的情况下，减少索引文件占据的空间，但是可能会增加回表次数，影响查询效率，并且不能使用覆盖索引

2. 倒序存储+前缀索引 / hash 的方式可以解决字符串很长但是前缀区分度不够的问题。但是查询有限制，比如排序和范围查询，且有额外的性能和存储消耗，比如函数计算，多一个字段和增加主键索引空间

 

参考：《mysql实战45讲》