MySQL SQL_NO_CACHE 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6666631/
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
SQL_NO_CACHE does not work
提问by Koerr
The first time I run this sql, needs 39 seconds,when I run again and increase SQL_NO_CACHE,does not seem to take effect:
第一次运行这个sql，需要39秒，当我再次运行并增加SQL_NO_CACHE时，似乎没有生效：
mysql> select count(*) from `deal_expired` where `site`=8&&`area`=122 &&
endtime<1310444996056;
+----------+
| count(*) |
+----------+
| 497 |
+----------+
1 row in set (39.55 sec)
mysql> select SQL_NO_CACHE count(*) from `deal_expired` where `site`=8&&`area`=
122 && endtime<1310444996056;
+----------+
| count(*) |
+----------+
| 497 |
+----------+
1 row in set (0.16 sec)
I tried a variety of methods, here
我尝试了多种方法， 这里
and even restart the mysql server or change table name, but I still can not let 39 seconds run this SQL
甚至重新启动mysql服务器或更改表名，但我仍然不能让39秒运行此SQL
I replaced another SQL, and an increase in the first run on SQL_NO_CACHE, the problem is the same:
我换了另一个SQL，并在第一次运行时增加了SQL_NO_CACHE，问题是一样的：
mysql> select SQL_NO_CACHE count(*) from `deal_expired` where `site`=25&&`area`=
134 && endtime<1310483196227;
+----------+
| count(*) |
+----------+
| 315 |
+----------+
1 row in set (2.17 sec)
mysql> select SQL_NO_CACHE count(*) from `deal_expired` where `site`=25&&`area`=
134 && endtime<1310483196227;
+----------+
| count(*) |
+----------+
| 315 |
+----------+
1 row in set (0.01 sec)
What is the reason? How can I get the same SQL run-time?
是什么原因？如何获得相同的 SQL 运行时？
I want to find a way to optimize this SQL to perform 39 seconds
我想找到一种方法来优化这个 SQL 以执行 39 秒
BTW: RESET QUERY CACHEFLUSH QUERY CACHEFLUSH TABLESSET SESSION query_cache_type=offdoes not work
顺便说一句：RESET QUERY CACHEFLUSH QUERY CACHEFLUSH TABLESSET SESSION query_cache_type=off不起作用
mysql state cache has been closed:
mysql 状态缓存已关闭：
mysql> SHOW STATUS LIKE "Qcache%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.04 sec)
mysql> select count(*) from `deal_expired` where `site`=25&&`area`=134 && endtime<1310
483196227;
+----------+
| count(*) |
+----------+
| 315 |
+----------+
1 row in set (0.01 sec)
mysql> SHOW STATUS LIKE "Qcache%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.00 sec)
explan this SQL,used site+endtime composite index(named site_endtime):
解释此 SQL，使用站点 + 结束时间复合索引（名为 site_endtime）：
mysql> explain select count(*) from `deal_expired` where `site`=8&&`area`=122 && endti
me<1310444996056;
+--------+------+-------------------------------+--------------+---------+------
-+------+-------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+--------+------+-------------------------------+--------------+---------+------
-+------+-------------+
| deal_expired | ref | name,url,endtime,site_endtime | site_endtime | 4 | const
| 353 | Using where |
+--------+------+-------------------------------+--------------+---------+------
-+------+-------------+
1 row in set (0.00 sec)
采纳答案by Vladislav Vaintroub
The answer to "How can I get the same SQL run-time?" is - you cannot. If your query reads some rows, they are cached, dependent on the storage engine in use, those rows are either in OS cache (myisam), or in buffer pool (innodb). If rows are cached, running the same query second time is much faster, because MySQL does not have to read from the disk.
“如何获得相同的 SQL 运行时？”的答案 是 - 你不能。如果您的查询读取某些行，它们会被缓存，具体取决于正在使用的存储引擎，这些行要么位于操作系统缓存 (myisam) 中，要么位于缓冲池 (innodb) 中。如果行被缓存，第二次运行相同的查询会快得多，因为 MySQL 不必从磁盘读取。
回答by Will Demaine
The first query should use SQL_NO_CACHE to tell MySQL not to put the resultinto the cache. The second query uses the cache and the tells MySQL not to cache the result of that query, which does nothing.
第一个查询应该使用 SQL_NO_CACHE 来告诉 MySQL 不要将结果放入缓存中。第二个查询使用缓存并告诉 MySQL 不要缓存该查询的结果，它什么也不做。
tl;dr - Reverse your queries.
tl;dr - 反转您的查询。
回答by Steve Gricci
I was under the impression that including any sort of SQL function that is calculated in the current runtime would not cache. Have you tried doing something like the following?
我的印象是，包括在当前运行时计算的任何类型的 SQL 函数都不会缓存。您是否尝试过执行以下操作？
select count(*), now() from `deal_expired` where `site`=8&&`area`=122 && endtime<1310444996056;
回答by Kevin Burton
- see: http://forums.mysql.com/read.php?24,225286,225468#msg-225468
- you could try
RESET QUERY CACHE(you need the RELOAD privilege) although having just read the link above this will probably not work either :(
- 见：http: //forums.mysql.com/read.php?24,225286,225468#msg-225468
- 您可以尝试
RESET QUERY CACHE（您需要 RELOAD 权限）尽管刚刚阅读了上面的链接，但这可能也不起作用:(
