优化mysql之key_buffer_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也就是4096MB。

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进程的时间，提高业务繁忙高并发时的效率.

下面是其他网友的补充推荐阅读

key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）

提升性能的建议:

1.如果opened_tables太大,应该把my.cnf中的table_cache变大
2.如果Key_reads太大,则应该把my.cnf中key_buffer_size变大.可以用Key_reads/Key_read_requests计算出cache失败率
3.如果Handler_read_rnd太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥键的作用
4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率
5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基于内存的临时表代替基于磁盘的 

MySQL优化小案例：key_buffer_size
key_buffer_size是对MyISAM表性能影响最大的一个参数，下面一台以MyISAM为主要存储引擎服务器的配置：

mysql> SHOW VARIABLES LIKE '%key_buffer_size%';
　　
下面查看key_buffer_size的使用情况：

mysql> SHOW GLOBAL STATUS LIKE '%key_read%';
+-------------------+-----------------+
| Variable_name     | Value           |
+-------------------+-----------------+
| Key_read_requests | 2454354135490   |
| Key_reads         | 23490           |
+-------------------+-----------------+
2 rows in set (0.00 sec)
一共有Key_read_requests个索引请求，一共发生了Key_reads次物理IO

Key_reads/Key_read_requests ≈ 0.1%以下比较好。

key_buffer_size设置注意事项

1.单个key_buffer的大小不能超过4G，如果设置超过4G，就有可能遇到下面3个bug:

http://bugs.mysql.com/bug.php"新手"来说的，我们还可以更深入地优化key_buffer_size，使用"show status"来查看"Key_read_requests, Key_reads, Key_write_requests  以及Key_writes "，以调整到更适合你的应用的大小，Key_reads/Key_read_requests的大小正常情况下得小于0.01

参考资料：

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

根据上述情况小编把key_buffer_size设置为2048M解决了问题。