MySQL key_buffer_size参数的设置（附带实例）

在 MySQL 数据库中，key_buffer_size 参数是对 MyISAM 表性能影响最大的一个参数。先来看看 mysqld 中是如何定义 key_buffer_size 参数的，如下所示：

[root@localhost ~]# mysqld --verbose --help|grep "\-\-key_buffer_size" -A 5
  --key_buffer_size=# The size of the buffer used for index blocks for MyISAM
                     tables. Increase this to get better index handling (for
                     all reads and multiple writes) to as much as you can
                     afford; 64M on a 256M machine that mainly runs MySQL is
                     quite common.

该参数用来设置索引块缓存的大小，只适用于 MyISAM 存储引擎。MySQL 5.1 版本以后提供了多个 key_buffer，可以将制定的表索引缓存到指定的 key_buffer，这样可以更好地降低线程之间的竞争。

【实例 1】设置 key_buffer_size 参数。首先查询 key_buffer_size 的值，SQL 语句如下：

mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+

然后修改该参数值为 10240000，SQL 语句如下：

mysql> SET GLOBAL key_buffer_size=10240000;

再次查看该值，发现修改成功，如下所示：

mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 10240000 |
+-----------------+----------+

上面介绍的是默认的 key_buffer，下面介绍如何设置多个 key_buffer。

【实例 2】设置多个key_buffer。首先建立一个索引缓存：

mysql> SET GLOBAL hot_cache2.key_buffer_size=128*1024;

然后将相关表的索引放到指定的索引缓存中，例如将表 t1 和表 t2 的索引存放到 hot_cache2 缓存中：

mysql> CREATE DATABASE mytest;
mysql> use mytest;
mysql> CREATE TABLE t1(id INT);
mysql> CREATE TABLE t2(id INT);
mysql> CACHE INDEX t1, t2 IN hot_cache2;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

注意，MySQL 的默认存储引擎为 InnoDB，不支持索引缓存，这里需要将存储引擎修改为 MyISAM。

如果想要把表 t1 的索引加载到默认的缓存（key_buffer）中，可以使用如下语句：

mysql> LOAD INDEX INTO CACHE t1;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

如果要删除索引缓存，则只需要设置该缓冲区大小为 0 即可：

mysql> SET GLOBAL hot_cache2.key_buffer_size=0;

值得注意的是，不能删除默认的索引缓冲区，下面看一下删除后的情况，如下所示：

mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 10240000 |
+-----------------+----------+
mysql> SET GLOBAL key_buffer_size = 0;
ERROR 1438 (HY000): Cannot drop default keycache

提示不能删除默认的索引缓存。接下来，查询 key_buffer_size 的大小，发现并没有删除成功，如下所示：

mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| key_buffer_size | 10240000   |
+-----------------+------------+

cache index 命令可以将多张表的索引加载到指定的索引缓冲区中，但每次数据库重启后，索引缓冲区中的数据会被清空，此时可以考虑在配置文件 /etc/my.cnf 中添加 init-file 选项，以便每次服务器启动的时候自动将指定表的索引加载到缓冲区中，如下所示：

[root@localhost ~]# more /etc/my.cnf
[mysqld]
port     = 3309
...
key_buffer_size = 1G
hot_cache.key_buffer_size = 512M
init_file = /root/initSQL/mysqld_init.sql
...

数据库每次启动的时候执行 /root/initSQL/mysqld_init.sql 脚本文件，该文件可以将多个表索引加载到缓冲区中。

【实例 3】将表 t1 和表 t2 的索引加载到 hot_cache 缓冲区中，SQL 语句如下：

cache index t1,t2 in hot_cache;

验证配置是否生效，SQL语句如下：

mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| key_buffer_size | 1073741824 |
+-----------------+------------+
　　　　　　　　
mysql> select @@global.hot_cache.key_buffer_size;
+------------------------------------+
| @@global.hot_cache.key_buffer_size |
+------------------------------------+
|                          536870912 |
+------------------------------------+

从结果中可以看出，表 t1 和表 t2 的索引已加载到 hot_cache 缓冲区中。