本文详细介绍了MySQL查询缓存的工作原理及其使用方法，包括如何开启、禁止和关闭查询缓存，并通过具体示例展示了不同场景下的应用效果。
以 MySQL 为例
mysql> show 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 | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 6 rows in set (0.04 sec)
编辑 my.cnf 文件，加入配置项 query_cache_type=1 然后重启mysql服务
mysql> show 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 | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 6 rows in set (0.00 sec)
query_cache_type | ON 表示缓存已经开启。
默认情况 select 查询操作只要符合数据库缓存规则那么结果集就会被缓存，如果你的数据库没有开启缓存，请参考下面
set session query_cache_type=on; flush tables; show status like 'qcache_q%'; select sql_cache * from member where id=1; show status like 'qcache_q%'; select sql_cache * from member where id=1; show status like 'qcache_q%';
例 10.1. 演示 SQL_CACHE
mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'qcache_q%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> select sql_cache * from member where id=1; +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | id | age | ctime | ip_address | mobile | mtime | name | picture | sex | status | wechat | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | 1 | 1 | 2017-08-24 17:05:43 | 1 | NULL | NULL | 1 | 1 | 1 | Enable | NULL | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ 1 row in set (0.00 sec) mysql> show status like 'qcache_q%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 1 row in set (0.01 sec) mysql> select sql_cache * from member where id=1; +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | id | age | ctime | ip_address | mobile | mtime | name | picture | sex | status | wechat | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | 1 | 1 | 2017-08-24 17:05:43 | 1 | NULL | NULL | 1 | 1 | 1 | Enable | NULL | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ 1 row in set (0.00 sec) mysql> show status like 'qcache_q%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 1 row in set (0.01 sec)
我们可以看到 Qcache_queries_in_cache 值由0转为1表示缓存已经生效。
这里我们主要讲怎样禁止缓存，使查询出的结果集不进入缓存。
SELECT SQL_NO_CACHE * FROM table where id=xxxx
下面的用法比较安全，切换到其他数据库也能正常工作
SELECT /*!40001 SQL_NO_CACHE */ * FROM table
set session query_cache_type=on; flush tables; show status like 'qcache_q%'; select sql_no_cache * from member where id=1; show status like 'qcache_q%'; select sql_no_cache * from member where id=1; show status like 'qcache_q%';
例 10.2. 演示 SQL_NO_CACHE
mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'qcache_q%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> select sql_no_cache * from member where id=1; +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | id | age | ctime | ip_address | mobile | mtime | name | picture | sex | status | wechat | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | 1 | 1 | 2017-08-24 17:05:43 | 1 | NULL | NULL | 1 | 1 | 1 | Enable | NULL | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ 1 row in set (0.00 sec) mysql> show status like 'qcache_q%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> select sql_no_cache * from member where id=1; +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | id | age | ctime | ip_address | mobile | mtime | name | picture | sex | status | wechat | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | 1 | 1 | 2017-08-24 17:05:43 | 1 | NULL | NULL | 1 | 1 | 1 | Enable | NULL | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ 1 row in set (0.00 sec) mysql> show status like 'qcache_q%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec)
使用 sql_no_cache 查询 Qcache_queries_in_cache 值始终是 0
我们使用 set session query_cache_type=off 可以关闭本次查询缓存。
set session query_cache_type=off; flush tables; show status like 'qcache_q%'; select sql_cache * from member where id=1; show status like 'qcache_q%'; select sql_cache * from member where id=1; show status like 'qcache_q%';
例 10.3. 演示 query_cache_type=off 关闭查询缓存
mysql> set session query_cache_type=off; Query OK, 0 rows affected (0.00 sec) mysql> mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'qcache_q%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> select sql_cache * from member where id=1; +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | id | age | ctime | ip_address | mobile | mtime | name | picture | sex | status | wechat | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | 1 | 1 | 2017-08-24 17:05:43 | 1 | NULL | NULL | 1 | 1 | 1 | Enable | NULL | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ 1 row in set (0.00 sec) mysql> show status like 'qcache_q%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> select sql_cache * from member where id=1; +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | id | age | ctime | ip_address | mobile | mtime | name | picture | sex | status | wechat | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ | 1 | 1 | 2017-08-24 17:05:43 | 1 | NULL | NULL | 1 | 1 | 1 | Enable | NULL | +----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+ 1 row in set (0.00 sec) mysql> show status like 'qcache_q%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 1 row in set (0.00 sec)
原文出处：Netkiller 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
