SQL_NO_CACHE不起作用 [英] SQL_NO_CACHE does not work
问题描述
我第一次运行此sql，需要39秒，当我再次运行并增加SQL_NO_CACHE时，似乎没有生效:
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:
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)
我尝试了多种方法，此处
甚至重新启动mysql服务器或更改表名，但我仍然不能让39秒运行此SQL
and even restart the mysql server or change table name, but I still can not let 39 seconds run this SQL
我替换了另一个SQL，并在第一次在SQL_NO_CACHE上运行时增加了一个问题，
I replaced another SQL, and an increase in the first run on SQL_NO_CACHE, the problem is the same:
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)
原因是什么? 如何获得相同的SQL运行时?
What is the reason? How can I get the same SQL run-time?
我想找到一种方法来优化此SQL以执行39秒
I want to find a way to optimize this SQL to perform 39 seconds
顺便说一句:RESET QUERY CACHE FLUSH QUERY CACHE FLUSH TABLES SET SESSION query_cache_type=off不起作用
BTW: RESET QUERY CACHE FLUSH QUERY CACHE FLUSH TABLES SET SESSION query_cache_type=off does not work
mysql状态缓存已关闭:
mysql state cache has been closed:
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)
解释此SQL，使用的站点+结束时间复合索引(名为site_endtime):
explan this SQL,used site+endtime composite index(named 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)
推荐答案
如何获得相同的SQL运行时?"的答案.是-您不能. 如果查询读取某些行，则根据使用的存储引擎对其进行缓存，这些行将位于OS缓存(myisam)或缓冲池(innodb)中.如果缓存了行，则第二次运行相同的查询要快得多，因为MySQL不必从磁盘读取数据.
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_NO_CACHE不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
