最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

从一条sql的执行流程来详细了解Buffer Pool

XAMPP相关 admin 16浏览 0评论

一条更新语句的执行流程

dqz073linux

Mysql更新语句执行过程.png

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL?

•因为来一个请求就直接对磁盘文件进行随机读写,然后更新磁盘文件里的数据性能可能相当差。•因为磁盘随机读写的性能是非常差的,所以直接更新磁盘文件是不能让数据库抗住很高并发的。•Mysql这套机制看起来复杂,但它可以保证每个更新请求都是更新内存BufferPool,然后顺序写日志文件,同时还能保证各种异常情况下的数据一致性。•更新内存的性能是极高的,然后顺序写磁盘上的日志文件的性能也是非常高的,要远高于随机读写磁盘文件。正是通过这套机制,才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

大幅提高性能的核心点

•buffer pool

buffer pool是什么?

•是一块内存区域,当数据库操作数据的时候,把硬盘上的数据加载到buffer pool,不直接和硬盘打交道,操作的是buffer pool里面的数据•数据库的增删改查都是在buffer pool上进行,和undo log/redo log/redo log buffer/binlog一起使用,后续会把数据刷到硬盘上•默认大小 128M

mysql的buffer pool使用的算法的空间大小配置:innodb_buffer_pool_size

•innodb_buffer_pool_size参数是控制buffer pool缓冲池的大小,一般建议大一点!;•查看当前的buffer pool大小:show global variables like ‘innodb_buffer_pool_size’;•修改当前的buffer pool大小(2G):SET GLOBAL innodb_buffer_pool_size= 2147483648;•数据库只要一启动,就会按照你设置的Buffer Pool大小,稍微再加大一点,去找操作系统申请一块内存区域,作为Buffer Pool的内存区域。•当内存区域申请完毕之后,数据库就会按照默认的缓存页的16KB的大小以及对应的800个字节左右的描述数据的大小,在Buffer Pool中划分出来一个一个的缓存页和一个一个的他们对应的描述数据

 

buffer pool的数据结构

dqz0073linux

buffer pool的数据结构.png

写数据不一致:脏缓存页(flush链表)

•被更新过的缓存页,数据和磁盘上的数据不一致,所以是脏缓存页•脏缓存页的数据是要刷到磁盘上的

大量的非常用页加入到buffer pool中:缓冲池污染

•触发条件:当一个表的数据量比加大,并且查询的结果集比较大,之后需要拿出来进行匹配的时候。会将大量的页加入到buffer pool中。

•解决条件:只有满足“被访问”并且“在老生代停留时间”大于T,才会被放入新生代头部。简单说就是修改innodb_old_blocks_pct、innodb_old_blocks_time配置。(下文详细介绍了这俩个配置)

最常用缓存算法:LRU

•页已经在缓冲池里,那就只做“移至”LRU头部的动作,而没有页被淘汰;

•页不在缓冲池里,除了做“放入”LRU头部的动作,还要做“淘汰”LRU尾部页的动作;

•mysql在LRU算法上做了优化

mysql的buffer pool使用的算法

•将LRU分为两个部分:新生代(new sublist)、老生代(old sublist)

•老生代默认占比37%,可以通过 innodb_old_blocks_pct 进行配置•新老生代收尾相连,即:新生代的尾(tail)连接着老生代的头(head);

•新页(例如被预读的页)加入缓冲池时,只加入到老生代头部:如果数据真正被读取(预读成功),才会加入到新生代的头部;如果数据没有被读取,则会比新生代里的“热数据页”更早被淘汰出缓冲池;

•数据页加载到缓存页后,在1s之后,访问该缓存页,该缓存页会被移动到热数据区头部。数据页刚加载到缓存页后,在1s之内,访问该缓存页,该缓存页是不会被移动到热数据区头部的。

•热数据区的前1/4的缓存页如果被访问,是不会移动到热数据区头部的;后3/4的缓存页被访问了,才会移动到热数据区头部

预读

•磁盘读写,并不是按需读取,而是按页读取,一次至少读一页数据(一般是16K),如果未来要读取的数据就在页中,就能够省去后续的磁盘IO,提高效率。

•数据访问,通常都遵循“集中读写”的原则,使用一些数据,大概率会使用附近的数据,这就是所谓的“局部性原理”,它表明提前加载是有效的,确实能够减少磁盘IO。

•预读失效:由于预读(Read-Ahead),提前把页放入了缓冲池,但最终MySQL并没有从页中读取数据,称为预读失效

mysql的buffer pool的淘汰策略

•当缓存页用完的时候,把冷数据区尾部的缓存页刷盘清空,缓存页对应的信息描述块从lru链表中移除,加入到free链表当中

•有一个后台线程,他会运行一个定时任务,这个定时任务每隔一段时间就会把LRU链表的冷数据区域的尾部的一些缓存页,刷入磁盘里去,清空这几个缓存页,把他们加入回free链表去;如果该缓存页也在flush链表中(该缓存页更新过),也需要把该缓存页从flush链表中移除

mysql的buffer pool的刷新机制

•当innodb中的脏页比例超过innodb_max_dirty_pages_pct_lwm(默认值为0,对于innodb_max_dirty_pages_pct_lwm表示不启动这个功能,也就是说innodb_buffer_pool中的脏页比例会操持在75%左右)的值时,这个时候innodb就会开始刷新脏页到磁盘。

•当innodb中的脏页比例超过innodb_max_dirty_pages_pct_lwm(默认是75)的值,而且还超过innodb_max_dirty_pages_pct时innodb就会进入勤快刷新模式(agressively flush)这个模式下innodb会把脏页更快的刷新到磁盘。

•sharp checkpoint:当innodb要重用它之前的redo文件时,就会把innodb_buffer_pool中所有与这个文件有关的页面都要刷新到磁盘;这样做就有可能引起磁盘的IO风暴了,轻者影响性能,重者影响可用性。

mysql的buffer pool使用的算法的核心配置:innodb_old_blocks_pct、innodb_old_blocks_time

•innodb缓存池有2个区域一个是sublist of old blocks存放不经常被访问到的数据,另外一个是sublist of new blocks存放经常被访问到的数据

•innodb_old_blocks_pct参数是控制进入到sublist of old blocks区域的数量,初始化默认是37.

•innodb_old_blocks_time参数是在访问到sublist of old blocks里面数据的时候控制数据不立即转移到sublist of new blocks区域,而是在多少微秒之后才会真正进入到new区域,这也是防止new区域里面的数据不会立即被踢出。

•如果在业务中做了大量的全表扫描,那么你就可以将innodb_old_blocks_pct设置减小,增到innodb_old_blocks_time的时间,不让这些无用的查询数据进入old区域,尽量不让缓存在new区域的有用的数据被立即刷掉。(这也是治标的方法,大量全表扫描就要优化sql和表索引结构了)•如果在业务中没有做大量的全表扫描,那么你就可以将innodb_old_blocks_pct增大,减小innodb_old_blocks_time的时间,让有用的查询缓存数据尽量缓存在innodb_buffer_pool_size中,减小磁盘io,提高性能。

 

来源:程序JAVA圈

转载请注明:XAMPP中文组官网 » 从一条sql的执行流程来详细了解Buffer Pool