MySQL日志详解,主要讲解bin log,redo log,undo log日志,还有buffer pool缓冲池原理,包括一条SQL语句怎么执行的,执行经过了哪些操作。
第一章、MySQL基础架构
第二章、buffer pool缓冲池详解
第三章、MySQL日志详解
第四章、Hash表和B+Tree详解
第五章、全面解析MySQL索引
第六章、InnoDB引擎详解
第七章、MySQL事务的脏读,不可重复读,幻读
第八章、关于MySQL各种锁的详解
前言
MySQL遵循WAL原则,先写日志后写磁盘,贯彻能使用内存,就尽量使用内存。数据先存储在内存中,内存里面使用缓冲池(buffer pool
)加速数据访问和数据更新。
脑图
buffer pool 缓冲池详解
什么是缓冲池?
在操作系统中,CPU与磁盘的执行速度不是一个量级的,因此CPU在磁盘中间,有一个高速缓存区,意思是,CPU将需要操作的数据存储在告诉缓存区中,并不直接操作磁盘。而操作系统另起一个线程去定期的将数据更新到磁盘中。
同样,缓冲池也是这个道理,你可以简单把缓冲池理解为缓存。
数据存储在内存中,加速数据访问,加速数据更新,避免每次查询数据都进行磁盘IO,这就是缓冲池的作用
缓冲池缓存什么数据?
缓存表数据和索引数据,将磁盘上最热最近的数据存储到缓冲池上,避免每次查询都到磁盘查询。
缓冲池的缺点
容量小,由innodb_buffer_pool_size
决定,默认是128M。可以根据需求设置。
读缓存概念
什么是预读?
操作系统读取磁盘数据到缓冲池上,并不是需要什么读取什么,而是按页读取,而一页是4K,磁盘访问按页读取能够提高性能,所以缓冲池一般也是按页缓存数据。如果需要查询的值刚好在缓冲池中,那么就直接查缓冲池,不需要再到磁盘IO查询。
正如上面提的,缓冲池设置一定大小的容量,如果缓冲池满了之后,怎么处理这些数据呢?
LRU算法,最热最近的数据,但是MySQL的LRU算法是经过优化的,加入了新生代老生代概念,还有大于或者等于老生代停留时间才进入新生代逻辑。
为什么MySQL要对LRU算法进行优化?
涉及到两个问题,预读失效和缓冲池污染
传统的LRU算法,头部存储最新的数据,尾部存储需要淘汰的数据。而如果有一个查询条件,全表查询,那么数据都会缓存到缓存池里面,而LRU算法会直接把原先的数据全淘汰再存储。
也就是说,传统的LRU算法,查询到的数据页放到头部,淘汰的页放在后面,如果查询的数据够多,则会将原先存储的热点数据全部淘汰,这就是缓冲池污染
预读失效
由于预读机制,提前读取页数据到缓冲池中,但是查询的时候,并没有从页中读取到数据,称为预读失效
优化预读失效
- 让预读失效的页,停留在缓冲池的时间够短
- 让真正被读取的页,才挪到缓冲池LRU头部,保证真正被读取的热点数据停在缓冲池的时间够长
具体操作,LRU优化,添加了新生代和老年代概念
新生代:存储热点的数据,新生代的尾部连接着老生代的头部
老年代:存储被预读的页,加入老生代的头部上,只有真正被读取(预读成功)的时候,才挪到新生代的头部上。如果没有被读取,那么比 新生代更早被淘汰
新生代和老生代的空间容量一般为:5 : 3比例
缓冲池污染
当某一个SQL语句,要批量扫描大量数据时,可能导致把缓冲池的所有页都替换出去,导致大量热数据被换出,MySQL性能急剧下降,这种情况叫缓冲池污染。
解决思路
MySQL缓冲池加了一个老生代停留时间的概念。凡是查询大量数据,没有超过老生代停留时间的情况,并不会立即放到新生代的头部。这样就避免了一旦查询大量数据,热点数据被淘汰的情况。
具体操作
老生代的头部引入了一个老生代停留时间窗口,查询的数据先存储在此,即使被立即访问,也不会马上被挪至头部,只有超过了老生代停留时间,才会被放到新生代头部。
所以如果查询了之后,没有被立即访问,那么淘汰的时间就快了。比传统的LRU,进来就到头部好很多。
设置InnoDB_buffer_pool的常用参数
# 配置缓冲池的大小,在内存允许的情况下,DBA往往会建议调大这个参数,越多数据和索引放到内存里,数据库的性能会越好。
innodb_buffer_pool_size:
# 老生代占整个LRU链长度的比例,默认是37,即整个LRU中新生代与老生代长度比例是63:37。如果设置100,就是传统LRU
innodb_old_blocks_pct
# 老生代停留时间窗口,单位是毫秒,默认是1000,即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件,才会被插入到新生代头部。
innodb_old_blocks_time
# 查询语句。自己修改
show variables like %innodb_buffer_pool_size%;
写缓冲概念
写缓存概念,先要理解WAL机制,先写日志,再写磁盘。尽可能的减少磁盘IO的次数,MySQL5.5之前,只对insert操作做了优化,叫做插入缓冲(insert buffer)
,MySQL5.5之后,对delete和update页做了优化,叫做写缓存(change buffer)
MySQL5.5之前
-- 执行更新操作
update T set account = 1 where id = 3;
-
更新的数据存在缓冲池的情况
- 直接修改缓冲池中的页,一次内存操作
- 写入redo log buffer中,等待checkpint追上write pos,然后再进行刷脏页操作,而刷脏页操作,是一次顺序写磁盘操作。
-
更新的数据不存在缓冲池的情况
- 在缓冲池中查询不到数据,到磁盘中查询数据并且放到缓冲池中,一次磁盘IO操作
- 修改缓冲池中的页,一次内存操作
- 写入redo log buffer中,等待checkpint追上write pos,然后再进行刷脏页操作,而刷脏页操作,是一次顺序写磁盘操作。
MySQL 5.5 之前,没有命中缓冲池的情况,至少产生一次磁盘IO的操作,有没有优化的空间呢?
有,加入change buffer
,除却建立唯一索引的字段之外,其他字段的更新均会被存储在change buffer
中,等待查询语句查询到该字段,再进行marge
操作,恢复到缓冲池上。
MySQL 5.5 之后
-
更新的数据不存在缓冲池的情况
- 将更新的数据存储到
change buffer
上,一次内存操作 - 写入redo log中
- 将更新的数据存储到
-
查询更新的数据的情况
- 读缓存页的时候,获取不到该更新数据,此时磁盘IO不可避免
- 从磁盘IO读取数据读入内存,再应用
change buffer
的操作日志,做marge
合并操作,生成一个正确的版本并返回内容。 - 将恢复后的数据存储到缓冲池上。
可以明显的看出,MySQL5.5之后,做了一个重大的改变,就是读取磁盘IO操作,不再是更新数据的时候,从缓冲池中获取不到数据就直接读磁盘了。而是将更新数据的操作先记录到change buffer
上。
为什么这样做呢?
因为有些操作,仅仅只是更新操作,并不需要马上读取,也就是在写多读少的场景下,这种操作能够减少磁盘IO,提高性能。等到需要读取到该数据的最新版本的情况,再读取磁盘io,然后再内存中做合并操作,存储到缓冲池中。
注意change buffer使用的场景
-
如果是写多读多的场景,则达不到优化效果,因为查询多,每次都要做
marge
操作,不可避免需要操作磁盘。 -
唯一索引也是一样的道理,因为唯一索引每次都要从磁盘中读取数据校验是否唯一这个操作,所以唯一索引使用
change buffer
没有必要。
marge操作除了查询触发,还有会哪些动作会触发
- 后台认为数据库空闲的时候
- 数据库缓冲池不够用的时候
- 正常关闭数据库的时间
change buffer对应设置的参数
# 配置写缓冲的大小,占整个缓冲池的比例,默认值是25%,最大值是50%。
innodb_change_buffer_max_size
# 配置哪些写操作启用写缓冲,可以设置成all/none/inserts/deletes等。默认是all
innodb_change_buffering
总结
change buffer
出现的愿景,减少操作磁盘IO的情况。