mysql创建新的键缓存
时间:2013-10-05 01:10:12 作者:admin 标签: key_buffer_key mysql 索引 键缓存 分类: Mysql
键缓存就是将数据表的索引提前加载到缓存当中，如果有一个使用频率非常高的数据表和足够的内存空间，就应该把数据表的索引全部加载到一个足够大的键缓存里，这种能力使我们可以避免来自同一个数据表的键发生竞争，也可以避免来自其它数据表的键竞争这个缓存里的空间：创建一个足够大的键缓存来容纳某个数据表的所有索引，并把该缓存设定公供该数据表使用。在把键加载到键缓存里之后，就不再需要进行I/O操作了，当然，也不再需要从缓存丢弃键值，与这个数据表有关的键检索操作在内存里就可全部完成。但一定要注意设置这个键缓存能在MyISAM、MEMEORY，但不能在Innodb引擎中。
1、创建一个新的键缓存，让它大到足以容纳来自newpet数据表的索引
mysql> SET GLOBAL pet_cache.key_buffer_size = 1024*1024; Query OK, 0 rows affected (0.01 sec) mysql> CACHE INDEX pet IN pet_cache; ----把pet数据表加入到这个键缓存，但是pet表的引擎是innodb，无法加载 +----------+--------------------+----------+---------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+--------------------+----------+---------------------------------------------------------------------+ | test.pet | assign_to_keycache | note | The storage engine for the table doesn't support assign_to_keycache | +----------+--------------------+----------+---------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table pet; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | pet | CREATE TABLE `pet` ( `name` varchar(20) DEFAULT NULL, `owner` varchar(20) DEFAULT NULL, `species` varchar(20) DEFAULT NULL, `sex` char(1) DEFAULT NULL, `birth` date DEFAULT NULL, `death` date DEFAULT NULL, `Id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'pet ID', PRIMARY KEY (`Id`), KEY `testINDEX` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) 1 row in set (0.00 sec)
2、那么把之前复制的newpet的引擎修改成MyISAM,然后再加入到pet_cache中
mysql> ALTER TABLE newpet engine = MyISAM; Query OK, 10 rows affected (0.06 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> show create table newpet; +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | newpet | CREATE TABLE `newpet` ( `name` varchar(20) DEFAULT NULL, `owner` varchar(20) DEFAULT NULL, `species` varchar(20) DEFAULT NULL, `sex` char(1) DEFAULT NULL, `birth` date DEFAULT NULL, `death` date DEFAULT NULL, `Id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'pet ID', PRIMARY KEY (`Id`), KEY `testINDEX` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> CACHE INDEX newpet IN pet_cache; ----重新将newpet表加载到pet_cache中，OK +-------------+--------------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+--------------------+----------+----------+ | test.newpet | assign_to_keycache | status | OK | +-------------+--------------------+----------+----------+1 row in set (0.00 sec) 1 row in set (0.00 sec)
3、把newpet数据表的索引提前加载到它的键缓存中去：
mysql> LOAD INDEX INTO CACHE newpet; +-------------+--------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+--------------+----------+----------+ | test.newpet | preload_keys | status | OK | +-------------+--------------+----------+----------+1 row in set (0.00 sec) 1 row in set (0.00 sec)
如果你想把其它的数据表加载到同一个缓存或才是为其它的数据表创建其它的键缓存，在用以上的步骤即可，上面的步骤和语句创建专用键缓存在服务器重新启动之后将会消失。如果想一直保留需要在每次启动之后自动得到执行。比如放到一个文件里并用--init-file服务器选项来命名那个文件。也可以在my.cnf中的[server] 或者 [mysqld] 或者 [mysqld_safe]中添加init-file=/usr/local/mysql/init.file。另需要注意这个文件的需要mysql能够访问。
[root@localhost mysql]# cat init.file use test; SET GLOBAL pet_cache.key_buffer_size = 1024*1024; CACHE INDEX newpet IN pet_cache; LOAD INDEX INTO CACHE newpet; LOAD INDEX INTO CACHE newpet;
通过如下命令可以查看设置情况
mysql> select @@global.pet_cache.key_buffer_size; +------------------------------------+ | @@global.pet_cache.key_buffer_size | +------------------------------------+ | 1048576 | +------------------------------------+1 row in set (0.00 sec) 1 row in set (0.00 sec)