一个语句:
(select ...)
union
(select ...)
union
...
首先在其它条件都符合的情况下(如query_cache_limit等值都足够大),这样的查询是否会被缓存?
如果缓存的话,是整个的union语句被缓存, 还是分开一个一个的select进行缓存?
(select ...)
union
(select ...)
union
...
首先在其它条件都符合的情况下(如query_cache_limit等值都足够大),这样的查询是否会被缓存?
如果缓存的话,是整个的union语句被缓存, 还是分开一个一个的select进行缓存?
-> ;
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| binlog_cache_size | 32768 |
| have_query_cache | YES |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 4294963200 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| table_definition_cache | 256 |
| table_open_cache | 256 |
| thread_cache_size | 8 |
+------------------------------+------------+
14 rows in set (0.00 sec)
那一个是记录现缓存大小的?
mysql> select * from user union select * from tt;
+--------+-----------+
| userid | username |
+--------+-----------+
| 1 | g蜈ャ蜿ク |
| 2 | 螟ァ譏ッ荳ェ |
| 3 | 譏ッ蠕キ蝗ス |
| 4 | |
| 5 | |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
+--------+-----------+
10 rows in set (0.00 sec)
mysql> show variables like "%cache%"
-> ;
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| binlog_cache_size | 32768 |
| have_query_cache | YES |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 4294963200 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| table_definition_cache | 256 |
| table_open_cache | 256 |
| thread_cache_size | 8 |
+------------------------------+------------+
14 rows in set (0.00 sec)
上述变量都没变化
要看状态,你应该show status,而不是show variables
-> ;
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Com_assign_to_keycache | 0 |
| 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 |
| Ssl_callback_cache_hits | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_used_session_cache_entries | 0 |
| Threads_cached | 1 |
+--------------------------------+-------+
20 rows in set (0.00 sec)
说的对
可是表示查询缓存的是哪个状态呢 union怎么好像没走缓存呢
mysql> set global query_cache_size=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like "%cache%";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Com_assign_to_keycache | 0 |
| 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 |
| Ssl_callback_cache_hits | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_used_session_cache_entries | 0 |
| Threads_cached | 2 |
+--------------------------------+-------+
20 rows in set (0.00 sec)
那个变量是表示缓存的?如何开启
这个表示已经打开了
query_cache_size 0
这个才为0,设置个正数就可以了
不过设置1太小了, 起码能装下一个sql语句及其结果集!按照你的例子,1024估计差不多了
1。有人开始看MYSQL的源代码了。
2。有人开始注重效率了和优化了。
3。高手越来越多了。
呵呵,没办法,等我把那个sending data优化了就不当这个二把刀DBA了!
不过在版主英明的领导下,和众高手的指点下,确实越发感兴趣了!!!