mysql 開啟查詢快取方法與查詢例子

開啟緩衝，設定緩衝大小，具體實施如下：

1、修改設定檔,windows下是my.ini，linux下是my.cnf;

在設定檔的最後追加上：

代碼如下	複製代碼
query_cache_type = 1 query_cache_size = 600000

需要重啟mysql生效；

那麼採用第二種方式；

b) 開啟緩衝,兩種方式：

a)使用mysql命令：

代碼如下	複製代碼
set global query_cache_type = 1;  set global query_cache_size = 600000;

如果報錯：

query cache is disabled; restart the server with query_cache_type=1...

在mysql命令列輸入

show variables like "%query_cache%" 查看是否設定成功，現在可以使用緩衝了；
當然如果你的資料表有更新怎麼辦，沒關係mysql預設會和這個表有關係的緩衝刪掉，下次查詢的時候會直接讀表然後再緩衝

下面是一個簡單的例子：

代碼如下

複製代碼

[MySQL@csdba1850 ~]$ MySQL -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or /g. Your MySQL connection id is 3 Server version: 5.0.45-community MySQL Community Edition (GPL) Type 'help;' or '/h' for help. Type '/c' to clear the buffer. MySQL> set global query_cache_size = 600000; 設定緩衝記憶體 Query OK, 0 rows affected (0.00 sec) MySQL> set session query_cache_type = ON; 開啟查詢快取 Query OK, 0 rows affected (0.00 sec) MySQL> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | animals | | person | +----------------+ 5 rows in set (0.00 sec) mysql> select count(*) from animals; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) Qcache_hits表示mysql緩衝查詢在緩衝中命中的累計次數，是累加值。 mysql> SHOW STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 0 | --0次 +---------------+-------+ 8 rows in set (0.00 sec) mysql> select count(*) from animals; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 1 |  表示sql在緩衝中直接得到結果，不需要再去解析 +---------------+-------+ 8 rows in set (0.00 sec) mysql> select count(*) from animals; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from animals; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 3 |  上面的sql也是是從緩衝中直接取到結果 +---------------+-------+ 1 row in set (0.00 sec) mysql> insert into animals select 9,'testsds' ;  插入資料後，跟這個表所有相關的sql緩衝就會被清空掉 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select count(*) from animals; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 3 |  還是等於3，說明上一條sql是沒有直接從緩衝中直接得到的 +---------------+-------+ 1 row in set (0.00 sec) mysql> select count(*) from animals; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache_hits'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 4 | +---------------+-------+ 1 row in set (0.00 sec)

以上的相關內容就是對mysql緩衝查詢和設定的介紹，望你能有所收穫。

補充

第一： query_cache_type 使用查詢快取的方式
一般，我們會把 query_cache_type 設定為 ON,預設情況下應該是ON

代碼如下	複製代碼
mysql> select @@query_cache_type; +--------------------+ | @@query_cache_type | +--------------------+ | ON | +--------------------+

query_cache_type有3個值 0代表關閉查詢快取OFF，1代表開啟ON，2（DEMAND）代表當sql語句中有SQL_CACHE關鍵詞時才緩衝，如：select SQL_CACHE user_name from users where user_id = '100';
這樣 當我們執行 select id,name from tableName; 這樣就會用到查詢快取。
①在 query_cache_type 開啟的情況下，如果你不想使用緩衝，需要指明
select sql_no_cache id,name from tableName;
②當sql中用到mysql函數，也不會緩衝
 
當然也可以禁用查詢快取： mysql> set session query_cache_type=off;
第二： 系統變數 have_query_cache 設定查詢快取是否可用

代碼如下	複製代碼
mysql> show variables like 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES |

+------------------+-------+
上面的顯示，表示設定查詢快取是可用的。
第三： 系統變數 query_cache_size
表示查詢快取大小，也就是分配記憶體大小給查詢快取，如果你分配大小為0，
那麼 第一步 和 第二步 起不到作用，還是沒有任何效果。

代碼如下	複製代碼
mysql> select @@global.query_cache_size; +---------------------------+ | @@global.query_cache_size | +---------------------------+ | 16777216 | +---------------------------+

上面是 mysql6.0設定預設的，之前的版本好像預設是0的，那麼就要自己設定下。
設定 set @@global.query_cache_size=1000000; 這裡是設定1M左右，900多K。
再次查看下

代碼如下	複製代碼
select @@global.query_cache_size; +---------------------------+ | @@global.query_cache_size | +---------------------------+ | 999424 | +---------------------------+

顯示我們設定新的大小，表示設定成功。
第四： query_cache_limit 控制緩衝查詢結果的最大值
例如： 如果查詢結果很大， 也緩衝？？？？這個明顯是不可能的。
MySql 可以設定一個最大的緩衝值，當你查詢快取數結果資料超過這個值就不會
進行緩衝。預設為1M，也就是超過了1M查詢結果就不會緩衝。

代碼如下	複製代碼
mysql> select @@global.query_cache_limit; +----------------------------+ | @@global.query_cache_limit | +----------------------------+ | 1048576 | +----------------------------+

這個是預設的數值，如果需要修改，就像設定緩衝大小一樣設定，使用set
重新指定大小。
好了，通過4個步驟就可以 開啟了查詢快取，具體值的大小和查詢的方式 這個因不同
的情況來指定了。
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             | 16777216 | | query_cache_type             | ON       | | query_cache_wlock_invalidate | OFF      | +------------------------------+----------+ 6 rows in set (0.00 sec)

第五：查看緩衝的狀態

代碼如下

複製代碼

mysql> show status like '%Qcache%'; +-------------------------+----------+ | Variable_name           | Value    | +-------------------------+----------+ | Qcache_free_blocks      | 11       | | Qcache_free_memory      | 16610552 | | Qcache_hits             | 10       | | Qcache_inserts          | 155      | | Qcache_lowmem_prunes    | 0        | | Qcache_not_cached       | 21       | | Qcache_queries_in_cache | 111      | | Qcache_total_blocks     | 256      | +-------------------------+----------+ 8 rows in set (0.00 sec)

MySQL 提供了一系列的 Global Status 來記錄 Query Cache 的目前狀態，具體如下：
Qcache_free_blocks：目前還處於空閑狀態的 Query Cache 中記憶體 Block 數目
Qcache_free_memory：目前還處於空閑狀態的 Query Cache 記憶體總量
Qcache_hits：Query Cache 叫用次數
Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數，也就是沒有命中的次數
Qcache_lowmem_prunes：當 Query Cache 記憶體容量不夠，需要從中刪除老的 Query Cache 以給新的 Cache 對象使用的次數
Qcache_not_cached：沒有被 Cache 的 SQL 數，包括無法被 Cache 的 SQL 以及由於 query_cache_type 設定的不會被 Cache 的 SQL
Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 數量
Qcache_total_blocks：Query Cache 中總的 Block 數量
 
第六：檢查查詢快取使用方式
檢查是否從查詢快取中受益的最簡單的辦法就是檢查快取命中率
當伺服器收到SELECT 語句的時候，Qcache_hits 和Com_select 這兩個變數會根據查詢快取
的情況進行遞增
查詢快取命中率的計算公式是：Qcache_hits/(Qcache_hits + Com_select)。

代碼如下	複製代碼
mysql> show status like '%Com_select%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select    | 1     | +---------------+-------+

 
 
query_cache_min_res_unit的配置是一柄”雙刃劍”，預設是4KB，設定值大對大資料查詢有好處，但如果你的查詢都是小資料 查詢，就容易造成記憶體片段和浪費。

查詢快取片段率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查詢快取片段率超過20%，可以用FLUSH QUERY CACHE整理緩衝片段，或者試試減小query_cache_min_res_unit，如果你的查詢都是小資料量的話。

查詢快取利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

查詢快取利用率在25%以下的話說明query_cache_size設定的過大，可適當減小;查詢快取利用率在80%以上而且 Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是片段太多。

查詢快取命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

樣本伺服器 查詢快取片段率 = 20.46%，查詢快取利用率 = 62.26%，查詢快取命中率 = 1.94%，命中率很差，可能寫操作比較頻繁吧，而且可能有些片段。