我从MySQL.com下载运行MySQL 5.5.9 x86_64 RPM。 在CentOS 5.5 Xen DomU上运行。
我已经启用了Query_cache，但是MySQL从不使用它。 我所有的表都是InnoDB。 为什么Qcache从未打过？
更新2：我发现这是限于模式 – 在名称中。 创build一个新的模式例如新的数据库，查询caching失败。 不幸的是我有148个现有的Schama，全都用“ – ”来命名。
更新这似乎被限制到转储和从一个前面的MySQL版本导入（5.0.32）创build一个新的架构和查询表中的查询caching工作正常。
这里是我的设置和Qc工作，而不是工作的例子。
mysql> SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | have_query_cache | YES | | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 536870912 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+ 6 rows in set (0.00 sec) mysql> USE `existing-schema`; Database changed mysql> CREATE TABLE test ( -> `uid` INT AUTO_INCREMENT PRIMARY KEY, -> `str` VARCHAR(255) NOT NULL -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE test; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `uid` int(11) NOT NULL AUTO_INCREMENT, `str` varchar(255) NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO test (str) VALUES ('one'),('two'),('three'),('four'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SHOW GLOBAL STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 536852824 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 56725 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM test; +-----+-------+ | uid | str | +-----+-------+ | 1 | one | | 2 | two | | 3 | three | | 4 | four | +-----+-------+ 4 rows in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 536852824 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 89824 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> CREATE DATABASE new; Query OK, 1 row affected (0.00 sec) mysql> USE new; Database changed mysql> CREATE TABLE test ( -> `uid` INT AUTO_INCREMENT PRIMARY KEY, -> `str` VARCHAR(255) NOT NULL -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE test; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `uid` int(11) NOT NULL AUTO_INCREMENT, `str` varchar(255) NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO test (str) VALUES ('one'),('two'),('three'),('four'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SHOW GLOBAL STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 536852824 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 89824 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM test; +-----+-------+ | uid | str | +-----+-------+ | 1 | one | | 2 | two | | 3 | three | | 4 | four | +-----+-------+ 4 rows in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 536851288 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 109528 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM test; +-----+-------+ | uid | str | +-----+-------+ | 1 | one | | 2 | two | | 3 | three | | 4 | four | +-----+-------+ 4 rows in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 536851288 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 126100 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> SHOW CREATE DATABASE new; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | new | CREATE DATABASE `new` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> SHOW CREATE DATABASE `existing-schema`; +------------------+---------------------------------------------------------------------------+ | Database | Create Database | +------------------+---------------------------------------------------------------------------+ | ezlead-live-data | CREATE DATABASE `existing-schema` /*!40100 DEFAULT CHARACTER SET utf8 */ | +------------------+---------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR 'root'@'localhost'; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[REMOVED]' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) 这是MySQL中的一个错误 。 我猜目前唯一的解决scheme是重命名我的数据库架构，以便他们不包含连字符。
您的查询caching设置看起来不错。 请参阅查询caching如何操作以获取有关哪些types的SELECT查询不会被caching的细节，并查看是否有适用于您的情况。 同时检查MySQL错误日志中是否有任何相关的消息。
你也可以做的是发出一个你知道应该被caching的非常简单的SELECT查询，并且在查询之前和之后检查Qcache_hits 。 尝试创build一个小的testing数据库/表来排除现有表中的任何可能的问题。 如果这不起作用，你知道MySQL更加微妙的一点是错误的。