Mysql是如何处理间隙锁的加锁规则

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是如何刷脏页

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讲》

MySql – 给字符串建立索引

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

前缀索引

假如用户的邮箱长度平均在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讲》

Nginx – proxy_module处理响应缓存流程

nginx作为反向代理时,经常会将上游服务器的响应缓存,以提升用户体验和服务并发能力。proxy_module提供了很多指令来完成这一项工作,下面简单分析下nginx在缓存上游响应时,大致的一个流程。

判断是否开启缓存

客户端的请求到来后,如果是HEAD请求会转成GET,这和cdn的做法是一样的,接着nginx会先根据proxy_cache指令来判断cache是否可用,如果指令结果为on,那么会接着判断proxy_cache_methods指令指定的请求方法是否与http_method匹配,如果没有匹配上则不使用缓存直接向上游发送请求并且不对响应缓存。

检查缓存

1 . 如果上一步判定此次请求使用缓存,那么nginx会先判断proxy_cache_bypass指令值决定哪些条件下即使缓存存在也不使用缓存,该指令可以指定多个变量,其中有一个不为空字符串或者0,都表示满足条件。

2. 接着nginx会根据proxy_cache_key生成对应的hash值,去共享内存中查找hash是否存在(其实每个key对应的hash都保存在二叉树中,而不是存在hash表中,可能数据经常删除新增,性能比hash表要强),如果不存在则直接向上游发送请求,然后对响应缓存(这里又涉及到一些判断,下面的步骤会说明)

3. 如果缓存存在,nginx会更新LRU链表,将hash对应在链表中的位置向链表头部移动。这里补充下,nginx在启动后一般会有额外的两个进程cache-loader和cache-manager,它们负责cache的载入和管理,举几个例子,在proxy_cache_path指令中可以指定cache-loader每次加载的cache文件数量和加载频率以及cache-manager淘汰旧缓存的频率;上面提到的LRU链表会交由cache-manager进程管理,它负责自动淘汰缓存;缓存都有有效期,cache-manager会定时去清除这些缓存。

判断缓存是否可用

上一步中如果缓存存在,nginx会接着判断缓存是否过期,如果已过期,则直接向上游发送新的请求,然后会判断proxy_cache_min_uses指令值(该指令表示对于同一个key,只有请求次数达到这个值后,才可以使用缓存,默认为1。使用缓存和缓存响应是不一样的)大于该值即将缓存响应给客户端,否则则会向上游发送请求。

接受上游响应

如果上面几步确定了使用缓存,但是由于某些条件没有使用缓存,而是直接向上游发送请求,那么在接收到上游响应后,nginx会根据一些条件决定如何更新或缓存响应。

响应头部

上游响应的头部,会影响nginx的缓存,比如以下几种:

X-Accel-Expires

值为正数则上游服务器明确表示,nginx可以缓存且指定了缓存的时间,负数为不缓存。

Set-Cookie

一般有Set-Cookie头部,nginx是不会缓存响应的。

Vary

一般有Vary头部,nginx也是不会缓存响应的。

Cache-Control

类似与cdn遵守的源站缓存策略,如果Cache-Control的值为private,max-age=0,no-cache,no-store,nginx都不会进行缓存。

以上头部都可以用proxy_ignore_headers指令忽略。

proxy_no_cache指令

如果配置了proxy_no_cache指令,在收到上游响应后,nginx不会缓存响应或者更新缓存。

proxy_cache_valid指令

如果响应码不匹配那么也不会缓存响应。

缓存上游响应

如果上一步中几个条件都满足,那么nginx就会决定缓存,nginx会将上游的响应先保存在临时文件中,然后做一次rename操作将临时文件移动到proxy_cahce_path指定的目录中,如果在用proxy_cache_path指令时指定了use_temp_path,那么要注意临时文件目录要和cache_path目录在同一个文件系统下,这样才可以直接使用rename,否则就是一次copy和删除了。

总结

其实nginx类似与cdn,可以用来做一个静态资源缓存服务器,cdn的回源相当于nginx向上游发送请求,cdn遵守源站的缓存策略,这和nginx对于上游响应头优先级最高的规则是相似的。不同的是proxy_module则提供了更多的指令来帮助用户完成更细粒度的缓存控制。

善用http缓存 – 杂记

善用http缓存可以提升用户体验和服务器性能,而在使用过程中常常会有一些迷惑的地方,做一个杂记,总结一下。

启用缓存后,客户端的请求流程

如上图所示,涵盖了一个请求在使用缓存时的大致流程,不过有一些细节需要补充:

  1. 不仅仅是服务器可以在response里添加Cache-Control头部,request的头部也可以出现Cache-Control,比如当我们强制刷新浏览器,会有Cache-Control: no-store。所以请求头里的Cache-Control会影响上图的流程,作以下补充:在判断是否有本地缓存前,如果Cache-Control的值为no-store表示,不管有没有缓存都去重新请求资源;在判断是否过期之前,如果Cache-Control的值为no-cache,表示不管缓存是否过期都去服务器验证,即带上If-None-Match或者If-Modified-Since头部;在缓存没过期取本地缓存之前,如果Cache-Control的值为max-age=0,那么仍然会去服务器验证。
  2. 当我们使用本地缓存时,status code会显示200(from cache),区别于304(from cache)(协商缓存),前者并没有真正的发送请求。

Cache-Control & Expires

这两个响应头部都可以表示资源缓存的过期时间,当仅存在Expires时,浏览器会根据请求的Date和Expires比较判断缓存是否过期,当二者同时存在时,后者会被忽略。

Etag & Last Modified

这两个响应头部都可以作为资源变更的校验信息,不同的是,Etag是一种强校验器,以apache为例,由资源内容的hash,资源最后修改的时间戳,以及资源对应文件系统的inode生成,格式为x-x-x,但这不是绝对的生成方法,比如nginx是以最后修改的时间戳和资源内容长度生成。使用Etag不但可以高效的利用缓存还可以配合If-None-Match头部防止“空中碰撞”。而Last Modified头部可以理解成弱校验器,因为它只验证了最后修改时间这一项条件,通常情况下,最后修改时间是一种很有效的验证方式,但是有一些例外的场景:1. 客户端时间和服务器时间标准不统一。2. 如果同一秒内某个资源被修改了两次,那么可能最后一次修改不会返回给浏览器(通常我们都会将etag的选项打开)。

If-None-Match & If-Modified-Since

这两个请求头都是要求服务器做资源检验使用,后者表示如果请求的资源在对应的时间之后为修改过,那么将返回304,只能用于GET/HEAD请求,前者表示如果请求的资源的Etag与给定Etag相同时返回304,可以在更新资源时使用比如PUT/POST。

当这两个请求头部同时存在时,以nginx为例,(可能还有If-Match,If-Unmodified-Since头部,同时nginx还会有一个if-modified-since指令)会依次比较If-Unmodified-Since -> If-Match -> If-Modified-Since -> If-Not-Match。满足一个200条件都会重新获取资源。