求助：MySQLrownum无法缓存查询问题
近期查看数据库慢查询时，发现有不少开头select (@row:=@row+1)取rownum的语句，而且都是连续的，from后面的表也没有任何改变，由此引发了为何每次查询不进缓存的疑问。哪位大神能告知rownum如何处理可以使每次的查询进入查询缓存。数据库版本5.5.27 OS：CentOS release 5.4
mysql>show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| have_query_cache | YES |
| query_cache_limit | 5242880 |
| query_cache_min_res_unit | 10240 |
| query_cache_size | 314572800 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
6 rows in set (0.00 sec)
mysql>show status like '%Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 308165784 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 24 |
| Qcache_queries_in_cache | 5 |
| Qcache_total_blocks | 13 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> select (@row:=@row+1) NUM_ROW_ZZZ,ZX.* from (select @row:=0) z,(select * from compressedlimit 0 ,20) zx;
+-------------+---------+---+--------------------------------------------------------------------------------------------------+----------------------------------------------------+
| NUM_ROW_ZZZ | id | k | c | pad |
+-------------+---------+---+--------------------------------------------------------------------------------------------------+----------------------------------------------------+
| 1 | 2234138 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 2 | 2419987 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 3 | 2632523 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 4 | 2680732 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 5 | 2735615 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 6 | 2773732 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 7 | 2777291 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 8 | 2792860 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 9 | 2819811 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 10 | 2837150 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 11 | 2867204 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 12 | 2875548 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 13 | 2887289 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 14 | 2915592 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 15 | 2917012 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 16 | 2930515 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 17 | 2967873 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 18 | 2983291 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 19 | 3000625 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 20 | 3007026 | 0 | 935646469-72629954-146305612-562514080-912847893-196202510-372791273-88733593-89207671-332844895 | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
+-------------+---------+---+--------------------------------------------------------------------------------------------------+----------------------------------------------------+
20 rows in set (0.01 sec)
mysql> select (@row:=@row+1) NUM_ROW_ZZZ,ZX.* from (select @row:=0) z,(select * from compressedlimit 0 ,20) zx;
+-------------+---------+---+--------------------------------------------------------------------------------------------------+----------------------------------------------------+
| NUM_ROW_ZZZ | id | k | c | pad |
+-------------+---------+---+--------------------------------------------------------------------------------------------------+----------------------------------------------------+
| 1 | 2234138 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 2 | 2419987 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 3 | 2632523 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 4 | 2680732 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 5 | 2735615 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 6 | 2773732 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 7 | 2777291 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 8 | 2792860 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 9 | 2819811 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 10 | 2837150 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 11 | 2867204 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 12 | 2875548 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 13 | 2887289 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 14 | 2915592 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 15 | 2917012 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 16 | 2930515 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 17 | 2967873 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 18 | 2983291 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 19 | 3000625 | 0 | | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
| 20 | 3007026 | 0 | 935646469-72629954-146305612-562514080-912847893-196202510-372791273-88733593-89207671-332844895 | aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy |
+-------------+---------+---+--------------------------------------------------------------------------------------------------+----------------------------------------------------+
20 rows in set (0.00 sec)
mysql>show status like '%Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 308165784 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 24 |
| Qcache_queries_in_cache | 5 |
| Qcache_total_blocks | 13 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
可以看见并没有进缓存 没看懂，试试 SQL_NO_CACHE ... 新人报道试试
页:
[1]