我正在运行从 mysql.com 下载的 MySQL 5.5.9 x86_64 RPM。在 CentOS 5.5 Xen DomU 上运行。
我已启用 Query_cache，但 MySQL 从来不使用它。我的所有表都是 InnoDB。为什么 Qcache 从来不命中？
更新 2：我发现这仅限于名称中带有 - 的架构。创建新架构（例如 new-db），查询缓存失败。不幸的是，我有 148 个现有的 Schama，它们的名称中都带有“-”。
更新这似乎仅限于从以前版本的 MySQL（5.0.32）转储和导入的模式。创建新模式并查询此查询缓存中的表可按预期工作。
这是我的设置以及 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)
答案1
这是一个漏洞在 MySQL 中。我猜目前唯一的解决方案是重命名我的数据库架构，以便它们不包含连字符。
答案2
您的查询缓存设置看起来不错。请参阅查询缓存如何运作了解哪些类型的 SELECT 查询不会被缓存，并查看是否有任何内容适用于您的情况。另外，检查 MySQL 错误日志中是否有任何相关消息。
您还可以执行一个非常简单的 SELECT 查询，您知道该查询应该被缓存，Qcache_hits然后检查查询前后的情况。尝试创建一个小型测试数据库/表，以排除现有表可能存在的问题。如果这不起作用，您就知道 MySQL 存在一些更微妙的问题。