mysql 禁用查询缓存 query cache详解数据库

os:centos 6.8
mysql: 5.5.49

MySQL Query Cache 会缓存select 查询，但是在调优sql查询及测试数据库的性能时需要禁用该功能。

查看变量、状态

mysql> show global variables like '%cache%'; 
+------------------------------+----------------------+ 
| Variable_name                | Value                | 
+------------------------------+----------------------+ 
| binlog_cache_size            | 8388608              | 
| binlog_stmt_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        | 536870912            | 
| max_binlog_stmt_cache_size   | 18446744073709547520 | 
| metadata_locks_cache_size    | 1024                 | 
| query_cache_limit            | 4194304              | 
| query_cache_min_res_unit     | 4096                 | 
| query_cache_size             | 134217728            | 
| query_cache_type             | ON                   | 
| query_cache_wlock_invalidate | OFF                  | 
| stored_program_cache         | 256                  | 
| table_definition_cache       | 400                  | 
| table_open_cache             | 400                  | 
| thread_cache_size            | 16                   | 
+------------------------------+----------------------+ 
18 rows in set (0.00 sec) 
 
mysql> show global status like '%cache%hit%'; 
+-------------------------+--------+ 
| Variable_name           | Value  | 
+-------------------------+--------+ 
| Qcache_hits             | 552743 | 
| Ssl_callback_cache_hits | 0      | 
| Ssl_session_cache_hits  | 0      | 
+-------------------------+--------+ 
3 rows in set (0.00 sec) 
 

注意参数 query_cache_size、query_cache_type

临时会话修改

mysql> set query_cache_type=0;

临时全局修改

mysql> set global query_cache_size=0; 
mysql> set global query_cache_type=0;

永久修改

# vi my.cnf 
query_cache_type=0 
query_cache_size=0 

还有一种方式是添加类似oracle的hint

select sql_no_cache count(*) from mysql.user; 

mysql 5.5 文档上描述
Note
Query cache was deprecated in MySQL 5.7 and removed in MySQL 8.0 (and later).

参考:
IT虾米网