| 知乎專欄 ｜ 多維度架構 | 微信號 netkiller-ebook | QQ群：128659835 請註明“讀者” |
以 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%';
例 13.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%';
例 13.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%';
例 13.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)