MySQL 慢查询-“等待查询缓存锁定”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5356975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL slow query - "Waiting for query cache lock"
提问by Rich C
I am running a simple query on a simple table on the same machine as the server running 5.5. It is taking 22sec to return ~7000 rows from a 20 million row table. Upon profiling most of the time is taken up by multiple "Waiting for query cache lock". What is "Waiting for query cache lock" and why is this query taking so long? Is it something with the way I set up the server?
我在运行 5.5 的服务器所在的同一台机器上的一个简单表上运行一个简单的查询。从 2000 万行表中返回约 7000 行需要 22 秒。在分析时大部分时间被多个“等待查询缓存锁定”占用。什么是“等待查询缓存锁定”，为什么这个查询需要这么长时间？这与我设置服务器的方式有关吗？
Here is the profile (note the time for the operation is actually from the row below as stated here):
这里是配置文件（注意，该操作的时间实际上是从陈述如下行这里）：
mysql> show profile for query 4;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000015 |
| Waiting for query cache lock | 0.000003 |
| checking query cache for query | 0.000045 |
| checking permissions | 0.000006 |
| Opening tables | 0.000027 |
| System lock | 0.000007 |
| Waiting for query cache lock | 0.000032 |
| init | 0.000018 |
| optimizing | 0.000008 |
| statistics | 0.033109 |
| preparing | 0.000019 |
| executing | 0.000002 |
| Sending data | 4.575480 |
| Waiting for query cache lock | 0.000005 |
| Sending data | 5.527728 |
| Waiting for query cache lock | 0.000005 |
| Sending data | 5.743041 |
| Waiting for query cache lock | 0.000004 |
| Sending data | 6.191706 |
| end | 0.000007 |
| query end | 0.000005 |
| closing tables | 0.000028 |
| freeing items | 0.000008 |
| Waiting for query cache lock | 0.000002 |
| freeing items | 0.000182 |
| Waiting for query cache lock | 0.000002 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000004 |
| logging slow query | 0.000001 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+--------------------------------+----------+
Here is the table:
这是表：
mysql> SHOW CREATE TABLE prvol;
"Table","Create Table"
"prvol","CREATE TABLE `prvol` (
`ticker` varchar(10) DEFAULT NULL,
`date` date DEFAULT NULL,
`close` float unsigned DEFAULT NULL,
KEY `Index 1` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"
Here is the query:
这是查询：
mysql> select close from prvol where date = '20100203';
EDIT:After running with SQL_NO_CACHE, all the time is now in the execution. Could this just be normal for a table this size on a 2.4GHz, 3GB ram machine?
编辑：使用 SQL_NO_CACHE 运行后，所有时间都在执行中。对于 2.4GHz、3GB ram 机器上这种大小的表，这是否正常？
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000052 |
| checking permissions | 0.000007 |
| Opening tables | 0.000027 |
| System lock | 0.000008 |
| init | 0.000019 |
| optimizing | 0.000008 |
| statistics | 0.034766 |
| preparing | 0.000011 |
| executing | 0.000002 |
| Sending data | 22.071324 |
| end | 0.000012 |
| query end | 0.000005 |
| closing tables | 0.000020 |
| freeing items | 0.000170 |
| logging slow query | 0.000001 |
| logging slow query | 0.000003 |
| cleaning up | 0.000004 |
+----------------------+-----------+
EDIT:Include results of explain.
编辑：包括解释的结果。
mysql> explain extended select cp from prvol where date = '20100208';
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | prvol | ref | Index 1 | Index 1 | 4 | const |6868 | 100.00 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.08 sec)
采纳答案by Rich C
I solved my slow query problem. To summarize the problem, it was taking 22sec to query 7000 rows from a 20mln row, 1.7GB indexed table. The problem was that the cache was too small and the query had to go to disk for every query. I would think the disk access would be faster than what I was seeing because I was going off an indexed column so the amount of data read off disk should have been small. But I'm guessing there is a lot of overhead with accessing the InnoDB storage on disk.
我解决了我的慢查询问题。总结一下这个问题，从一个 2000 万行、1.7GB 索引表中查询 7000 行需要 22 秒。问题是缓存太小，每次查询都必须访问磁盘。我认为磁盘访问会比我看到的要快，因为我要离开索引列，所以从磁盘读取的数据量应该很小。但我猜测访问磁盘上的 InnoDB 存储会产生很多开销。
Once I set innodb_buffer_pool_size=1024Min the my.ini file, the initial query would take a long time, but all subsequent queries would finish in under a second.
一旦我innodb_buffer_pool_size=1024M在 my.ini 文件中设置，初始查询将需要很长时间，但所有后续查询将在一秒钟内完成。
Unfortunately, the profiling didn't really help.
不幸的是，分析并没有真正帮助。
回答by Sasha
This is a known problem with MySQL. It's really well described here:
这是 MySQL 的一个已知问题。这里描述得很好：
Query cache can help you a lot but at the same time it can become a bottleneck.
查询缓存可以为您提供很多帮助，但同时它也可能成为瓶颈。
