MySql优化之key_buffer_size的设置

key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。
key_buffer_size只对MyISAM表起作用。但即使你不使用MyISAM表，内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）

---

使用命令

查询当前的设置值

mysql> SHOW VARIABLES LIKE '%key_buffer_size%';

查询全局的设置值

mysql> SHOW GLOBAL VARIABLES LIKE '%max_heap_table_size%';

设置当前连接的key_buffer_size

mysql> set key_buffer_size=xxx;

设置全局的key_buffer_size

mysql> set global key_buffer_size=xxx;

当然,在my.cnf里也需要把key_buffer_size改为xxM,这样在下次重启后才会生效，否则会使用之前的配置。

key_buffer_size优化问题

如果设置太大浪费内存空间，缓存太小缓存命中率低，通常会影响系统的性能与查询效率。
比如当你在执行查询的SQL语句报错，很有可能就是key_buffer_size在搞鬼：

SQLSTATE[HY000]: General error: 5 Out of memory (Needed 16777220 bytes)

通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得）。
Key_reads/Key_read_requests ≈ 0.1% 以下比较好。

下面查看key_buffer_size的使用情况：

mysql> show global status like 'key_read%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_read_requests | 27813678764 |    //从缓存读取索引的请求次数。
| Key_reads | 6798830 |         //从磁盘读取索引的请求次数。
+------------------------+-------------+
2 rows in set (0.00 sec)

一共有27813678764个索引读取请求，有6798830个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：

key_cache_miss_rate = Key_reads / Key_read_requests * 100%

比如上面的数据，key_cache_miss_rate为0.0244%，4000个索引读取请求才有一个直接读硬盘，key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘)，所以理论来上来说，这个比值越小越好，但过小的话，难免造成内存浪费。

---

by KingFer