MyISAM的key_buffer_size和InnoDB的innodb_buffer_pool_size

一.MyISAM的key_buffer_size

MyISAM的索引方式是非聚集索引,主索引和其他索引没有本质区别,在data域都是存储了具体记录行的地址.key_buffer_size规定了系统将多少内存用作MyISAM的索引缓存.如果内存足够大,又不想去计算,一个简单的计算办法就是将所有的索引文件加起来作为key_buffer_size的大小(当然这会存在大量的浪费),但确实一种简便的办法.

为了最小化磁盘I/O，MyISAM将最频繁访问的索引块（“indexblock”）都放在内存中，这样的内存缓冲区我们称之为Key Cache，它的大小可以通过参数key_buffer_size来控制。在MyISAM的索引文件中（MYI），连续的单元（contiguous unit）组成一个Block，Index block的大小等于该BTree索引节点的大小。Key Cache就是以Block为单位的。

1.key cache只存放索引,对于数据,是读取操作系统缓存的数据文件(如果操作系统能缓存),如果你将key_buffer_size设置为0,对于索引,将和数据文件的读取方式一致.如果一个读请求到达,能从key cache中找到数据,那么就不再访问myi文件,直接根据data域去找对应的数据.当然这个前提是查询能有效用到索引才可以.可以根据linux的stat查看myi和myd文件,发现myd被访问,而myi没有被访问.如果在key cache中找不到,则读取myi中的对应block放入key cache的LRU链的头部.

ps:目前大部分的linux下默认使用relatime,#cat /proc/mounts查看,看是否能看到realtime,如果能看到,需要重新挂载该目录.#mount -o remount,strictatime  /

ps:mysql每次只能用到一个索引.

示意图:

 

 

我们先来分析一下与MyISAM 索引缓存相关的几个系统参数和状态参数：
key_buffer_size，索引缓存大小； 
这个参数用来设置整个MySQL 中的常规Key Cache 大小。一般来说，如果我们的MySQL 是运行在32 位平台纸上，此值建议不要超过2GB 大小。如果是运行在64 位平台纸上则不用考虑此限制，但也最好不要超过4GB。

key_buffer_block_size，索引缓存中的Cache Block Size；
在前面我们已经介绍了，在Key Cache 中的所有数据都是以Cache Block 的形式存在，而 key_buffer_block_size 就是设置每个Cache Block 的大小，实际上也同时限定了我们将 “.MYI”文件中的Index Block 被读入时候的File Block 的大小。

key_cache_division_limit，LRU 链表中的Hot Area 和Warm Area 分界值；
实际上，在MySQL 的Key Cache 中所使用的LRU 算法并不像传统的算法一样仅仅只是通过访问频率以及最后访问时间来通过一个唯一的链表实现，而是将其分成了两部分。一部分用来存放使用比较频繁的Hot Cacke Lock（Hot Chain），被成为Hot Area，另外一部分则用来存放使用不是太频繁的Warm Cache Block（Warm Chain），被成为Warm Area。这样做的目的主要是为了保护使用比较频繁的Cache Block 更不容易被换出。而key_cache_division_limit 参数则是告诉MySQL该如何划分整个Cache Chain划分为Hot Chain和Warm Chain 两部分，参数值为WarmChain 占整个Chain 的百分比值。设置范围1～100，系统默认为100，也就是只有Warm Chain。

key_cache_age_threshold，控制Cache Block 从Hot Area 降到Warm Area 的限制；
key_cache_age_threshold参数控制Hot Area 中的Cache Block 何时该被降级到Warm Area 中。系统默认值为300，最小可以设置为100。值越小，被降级的可能性越大。

 

对于key_buffer_size 的设置我们一般需要通过三个指标来计算，第一个是系统索引的总大小，第二个是系统可用物理内存，第三个是根据系统当前的Key Cache 命中率。对于一个完全从零开始的全新系统的话，可能出了第二点可以拿到很清楚的数据之外，其他的两个数据都比较难获取，第三点是完全没有。当然，我们可以通过MySQL 官方手册中给出的一个计算公式粗略的估算一下我们系统将来的索引大小，不过前提是要知道我们会创建哪些索引，然后通过各索引估算出索引键的长度，以及表中存放数据的条数，公式如下： Key_Size = key_number * (key_length+4)/0.67 
Max_key_buffer_size < Max_RAM - QCache_Usage - Threads_Usage - System_Usage Threads_Usage = max_connections * (sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + thread_stack)
当然，考虑到活跃数据的问题，我们并不需要将key_buffer_size 设置到可以将所有的索引都放下的大小，这时候我们就需要Key Cache 的命中率数据来帮忙了。下面我们再来看一下系统中记录的与KeyCache 相关的性能状态参数变量。

◆ Key_blocks_not_flushed，已经更改但还未刷新到磁盘的Dirty Cache Block；(如果设置了delay_key_write,更新索引的时候,该值会增加)
◆ Key_blocks_unused，目前未被使用的Cache Block 数目； 
◆ Key_blocks_used，已经使用了的Cache Block 数目； 
◆ Key_read_requests，Cache Block 被请求读取的总次数； 
◆ Key_reads，在Cache Block 中找不到需要读取的Key 信息后到“.MYI”文件中读取的次数； 
◆ Key_write_requests，Cache Block 被请求修改的总次数； 
◆ Key_writes，在Cache Block 中找不到需要修改的Key 信息后到“.MYI”文件中读入再修改的次数；
 
由于上面各个状态参数在MySQL 官方文档中都有较为详细的描述，所以上面仅做基本的说明。当我们的系统上线之后，我们就可以通过上面这些状态参数的状态值得到系统当前的Key Cache 使用的详细情况和性能状态

Key_buffer_UsageRatio = (1 - Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *100%
Key_Buffer_Read_HitRatio = (1 - Key_reads/Key_read_requests) * 100% 
Key_Buffer_Write_HitRatio = (1 - Key_writes/Key_Write_requests) * 100%  

通过上面的这三个比率数据，就可以很清楚的知道我们的Key Cache 设置是否合理，尤其是Key_Buffer_Read_HitRatio 参数和Key_buffer_UsageRatio 这两个比率。一般来说 Key_buffer_UsageRatio 应该在99%以上甚至100%，如果该值过低，则说明我们的key_buffer_size 设置过大，MySQL 根本使用不完。Key_Buffer_Read_HitRatio 也应该尽可能的高。如果该值较低，则很有可能是我们的key_buffer_size 设置过小， 需要适当增加key_buffer_size 值， 也有可key_cache_age_threshold和key_cache_division_limit的设置不当，造成Key Cache失效太快。 
一般来说，在实际应用场景中，很少有人调整key_cache_age_threshold 和key_cache_division_limit这两个参数的值，大都是使用系统的默认值。

 

二.InnoDB的innodb_buffer_pool_size

1.InnoDB主索引是聚簇索引，索引与数据共用表空间.InnoDB缓存机制和MyISAM缓存机制的最大区别就是在于，InnoDB不仅仅是缓存索引，还会缓存数据.InnoDB将数据和索引等信息缓存在innodb_buffer_pool中.

下面的参数规定了innodb_buffer_pool的大小和数量以及其他特性等:
innodb_buffer_pool_instances:几个innodb_buffer_pool,默认是1个
innodb_buffer_pool_size:每个innodb_buffer_pool_size大小
innodb_additional_mem_pool_size:指定InnoDB用来存储数据字典和其他内部数据结构的缓存大小，默认值是2MB

 

2.InnoDB何时将数据加载到innodb_buffer_pool中

InnoDB在MySQL启动一段时间后,将经常访问的innodb引擎表的数据放入innodb_buffer_pool.即innodb_buffer_pool保存的是热数据.然后根据一定算法淘汰不常访问的数据.

当停止MySQL服务时，所有存储在InnoDB缓冲池中的热数据将被全部清空.重新启动后,再次缓存数据.

从5.6版本开始，MySQL支持关闭MySQL服务时将内存中的热数据保存到硬盘，MySQL重启后首先将硬盘中的如数据加载到InnoDB缓冲池中，以便缩短warmup进程的时间，提高业务繁忙高并发时的效率.