mysql 缓存设置

有的sql 运行时，比较耗时，消耗系统资源，且无法进一步优化，并且每次获得数据，还不太发生变化。
那么就把这个sql 语句获得信息给缓存起来，供下次执行使用
这样非常节省系统资源

查看query缓存大小

mysql> select * from t_user_auth where real_name like '%zanyuiiy%';
Empty set (1.58 sec)

mysql> select * from t_user_auth where real_name like '%zanyuiiy%';
Empty set (1.64 sec)

mysql> show variables like 'query_%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_alloc_block_size       | 8192     |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 67108864 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
| query_prealloc_size          | 8192     |
+------------------------------+----------+
7 rows in set (0.00 sec)

mysql> set global query_cache_size=64*1024*1024;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set global query_cache_size=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'query_%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.00 sec)

mysql> select * from t_user_auth where real_name like '%zanyuiiy%';
Empty set (1.77 sec)

mysql> select * from t_user_auth where real_name like '%zanyuiiy%';
Empty set (1.59 sec)

mysql> select * from t_user_auth where real_name like '%zanyuiiy%';
Empty set (1.60 sec)

mysql> select * from t_user_auth where real_name like '%zanyuiiy%';
Empty set (1.63 sec)

mysql> set global query_cache_size=64*1024*1024;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from t_user_auth where real_name like '%zanyuiiy%';
Empty set (1.58 sec)

mysql> select * from t_user_auth where real_name like '%zanyuiiy%';
Empty set (1.58 sec)

mysql> select * from t_user_auth where real_name like '%zanyuiiy%';
Empty set (1.61 sec)

mysql> 

缓存失效

数据表的数据有变化，或则数据表的结构有变化，则缓存失效。

什么情况不适用缓存

sql 语句每次获得的数据有变化