innodb的buffer pool

innodb是有buffer pool机制的,可以参考
http://dev.mysql.com/doc/refman/5.1/en/innodb-buffer-pool.html

buffer pool可以按page为单位,对记录进行缓存.

CREATE TABLE `20130122handler` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `content` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `20130122handler_idx_uid` (`uid`)
) ENGINE=InnoDB

这个表里有60w随机数据

先执行下

show status like 'Innodb_buffer_pool_read%';

结果为
Innodb_buffer_pool_read_requests 1204
Innodb_buffer_pool_reads         97


然后执行

select * from 20130122handler where uid=4349023;

用时为0.016s

show status like 'Innodb_buffer_pool_read%';

结果为
Innodb_buffer_pool_read_requests 1221
Innodb_buffer_pool_reads 98

观察下数据,发现Innodb_buffer_pool_reads增加了.
说明这条数据不在buffer pool中,需要从硬盘中读取

再执行一遍

select * from 20130122handler where uid=4349023;

用时为0s
观察下使用情况
Innodb_buffer_pool_read_requests 1237
Innodb_buffer_pool_reads 98
显然这次只需要读取buffer pool的数据就行了,省了一次磁盘IO

按照page来缓存,好处是连续读取同一个page上记录,只有第一个有磁盘IO.
也有人提出用按照row来缓存
http://rdc.taobao.com/team/jm/archives/1107


buffer pool有被污染的情况,即有些非热点数据把热点数据挤出了buffer pool
http://www.orczhou.com/index.php/2010/05/innodb-plugin-make-buffer-cache-scan-resistant/


buffer pool在启动的时候是空的,最好能预先warm up一下
http://www.mysqlperformanceblog.com/2008/05/01/quickly-preloading-innodb-tables-in-the-buffer-pool/


不过mysql 5.6有了新功能,可以关闭的时候dump出buffer pool镜像,然后启动的时候reload.
http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_warm_up