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).

参考:
https://dev.mysql.com/doc/refman/5.5/en/mysql-installer-workflow.html