优化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_Us
age = 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%
mysql下载32位
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太⼤，应该把myf中的table_cache变⼤
2.如果Key_reads太⼤，则应该把myf中key_buffer_size变⼤.可以⽤Key_reads/Key_read_requests计算出cache失败率
3.如果Handler_read_rnd太⼤，则你写的SQL语句⾥很多查询都是要扫描整个表,⽽没有发挥键的作⽤
4.如果Threads_created太⼤，就要增加myf中thread_cache_size的值.可以⽤Threads_created/Connections计算cache命中率
5.如果Created_tmp_disk_tables太⼤，就要增加myf中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:
2.建议key_buffer设置为物理内存的1/4(针对MyISAM引擎)，甚⾄是物理内存的30%~40%，如果key_buffer_size设置太⼤，系统就会频繁的换页，降低系统性能。因为MySQL使⽤操作系统的缓存来缓存数据，所以我们得为系统留够⾜够的内存；在很多情况下数据要⽐索引⼤得多。
3.如果机器性能优越，可以设置多个key_buffer，分别让不同的key_buffer来缓存专门的索引
上⾯只是对"新⼿"来说的，我们还可以更深⼊地优化key_buffer_size，使⽤"show status"来查看", ,  以及 "，以调整到更适合你的应⽤的⼤
⼩，Key_reads/Key_read_requests的⼤⼩正常情况下得⼩于0.01
参考资料：
根据上述情况⼩编把key_buffer_size设置为2048M解决了问题。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。