MySQL InnoDB的缓冲池之预读失效和缓存池污染

InnoDB的缓存池作用:

• 缓存表数据和索引数据,把磁盘上的数据加载到缓冲池中,避免每次都进行磁盘IO,起到加速访问的效果.

LRU算法(Least recently used):

• 把入缓存池的页放在LRU的头部,作为最近访问的元素
  • 页在缓冲池中的数据,把它放在队列的前面(情景一)
  • 页不在缓冲池中的数据,把它放在队列的前面,同时淘汰队列后面的数据(情景二)
    

情景一

情景二

MySQL不用LRU算法原因:

• 预读失效
  • 提前把页放入了缓冲池，但最终MySQL并没有从页中读取数据，称为预读失效。
• 缓冲池污染
  • 当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。

解决预读失效的方式:

• 将LRU分为两部分:
  • 新生代
  • 老生带
• 新生代,老生代首尾相连
• 新页加入缓冲池前,先加入到老生带头部
  • 如果数据真正的被读取,才会加入到老生带中
  • 如果数据没有被读取,则会比新生代的"热数据"更早的淘汰出缓冲池
    
    

解决MySQL缓冲池污染:

缓冲池污染案例:

	有一个数据量较大的用户表，当执行：
	select * from user where name like "%shenjian%";
	虽然结果集可能只有少量数据，但这类like不能命中索引，必须全表扫描，就需要访问大量的页：
	（1）把页加到缓冲池（插入老生代头部）；
	（2）从页里读出相关的row（插入新生代头部）；
	（3）row里的name字段和字符串shenjian进行比较，如果符合条件，加入到结果集中；
	（4）…直到扫描完所有页中的所有row…
	 
	如此一来，所有的数据页都会被加载到新生代的头部，但只会访问一次，真正的热数据被大量换出。

    有一个数据量较大的用户表，当执行：
    select * from user where name like "%shenjian%";
    虽然结果集可能只有少量数据，但这类like不能命中索引，必须全表扫描，就需要访问大量的页：
    （1）把页加到缓冲池（插入老生代头部）；
    （2）从页里读出相关的row（插入新生代头部）；
    （3）row里的name字段和字符串shenjian进行比较，如果符合条件，加入到结果集中；
    （4）…直到扫描完所有页中的所有row…
     
    如此一来，所有的数据页都会被加载到新生代的头部，但只会访问一次，真正的热数据被大量换出。

解决方案:

MySQL InnoDB数据参数设置:

参数：innodb_buffer_pool_size
介绍：配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。

参数：innodb_old_blocks_pct
介绍：老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。
画外音：如果把这个参数设为100，就退化为普通LRU了。

参数：innodb_old_blocks_time
介绍：老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。

总结:

• 缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；
• 缓冲池通常以页(page)为单位缓存数据；
• 缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；
• InnoDB对普通LRU进行了优化：
  • 将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题
  • 页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题