query_cache

mysql的query_cache 相当于 oracle 里的result cache

mysql> mhow variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | DEMAND  |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.01 sec)

设置为DEMAND后，可以加hint手动开启

不加hint，可以看到多次查询速度相差不多

mysql> select count(*) from t100 a,t100 b,t100 c,emp e,t10;
+----------+
| count(*) |
+----------+
| 28753060 |
+----------+
1 row in set (1.98 sec)

mysql> select count(*) from t100 a,t100 b,t100 c,emp e,t10;
+----------+
| count(*) |
+----------+
| 28753060 |
+----------+
1 row in set (1.98 sec)

而增加hint后，第二次查询几乎不需要时间

mysql> select sql_cache count(*) from t100 a,t100 b,t100 c,emp e,t10;
+----------+
| count(*) |
+----------+
| 28753060 |
+----------+
1 row in set (2.02 sec)

mysql> select sql_cache count(*) from t100 a,t100 b,t100 c,emp e,t10;
+----------+
| count(*) |
+----------+
| 28753060 |
+----------+
1 row in set (0.00 sec)