Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

10.6. 怎麼控制資料庫緩存

以 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 表示緩存已經開啟。

10.6.1. SQL_CACHE 緩存

預設情況 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表示緩存已經生效。


10.6.2. 禁止緩存 SQL_NO_CACHE

這裡我們主要講怎樣禁止緩存,使查詢出的結果集不進入緩存。

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


10.6.3. 關閉緩存 set session query_cache_type=off

我們使用 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)