| 知乎專欄 ｜ 多維度架構 | 微信號 netkiller-ebook | QQ群：128659835 請註明“讀者” |
這裡講的緩存是資料庫本身的緩存，並不是外部緩存例如Redis/Memcache等等。
資料庫的數據分為冷數據和熱資料庫，通俗的講冷數據是存儲在磁碟上不經常查詢的數據；而熱數據是頻繁查詢的數據，這部分數據會被緩存到內存中。
頻繁訪問且返回相同結果集的情況下使用緩存。
偶爾查詢一次且間隔時間較長的情況下不要使用緩存。
尺寸較大的結果集不建議使用緩存，因為數據太大太大，緩存不足以存儲，會導致頻繁載入與銷毀，命中率低。
通常資料庫預設情況是開啟緩存的，也就是說正常的select查詢，如果符合緩存規則就會經過緩存。
當一條SQL查詢時如果結果集在內存中稱作“命中”
通常DBA只能控制資料庫緩存是否開啟，分配多少內存給緩存使用，過期銷毀時間，以及策略等等.
上面我已經說過，通常資料庫預設都開啟緩存，所以更多的時候我們的操作是禁用緩存。這就需要開發人員來通過特定的SQL操作來控制資料庫緩存。
以 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%';
例 21.2. 演示 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%';
例 21.3. 演示 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%';
例 21.4. 演示 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)