MySQL緩存之Qcache與buffer pool對比

Q：innodb buffer pool和Qcache的緩存區別？

A：

1、Qcacche緩存的是SQL語句及對應的結果集，緩存在內存，最簡單的情況是SQL一直不重複，那Qcache的命令率肯定是0；

2、buffer pool中緩存的是整張表中的數據，緩存在內存，SQL再變只要數據都在內存，那麼命中率就是100%。

一、查詢緩存（QueryCache）

1、關於查詢緩存機制

開啟了緩存，會自動將查詢語句和結果集返回到內存，下次再查直接從內存中取；

查詢緩存會跟蹤系統中每張表，若表發生變化，則和該張表相關的所有查詢緩存全部失效，這是和buffer pool緩存機制很大的區別；

檢查查詢緩存時，MYSQL不會對SQL做任何處理，它精確的使用客戶端傳來的查詢，只要字元大小寫或注釋有點不同，查詢緩存就認為是不同的查詢；

任何一個包含不確定的函數（比如now()、curren_date()）的查詢不會被緩存。

注意：

查詢緩存可改善性能，但是開啟查詢緩存對讀寫增加了額外開銷。

1、對於讀，在查詢前需先檢查緩存；

2、對於寫，寫入後需更新緩存。

一般情況下這些開銷相對較小，因此需要根據業務權衡是否開啟查詢緩存。

2、Qcache參數

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 |
+------------------------------+---------+

參數解析：

1、have_query_cache：該MySQL是否支持Query Cache；

2、query_cache_limit：緩存塊大小，超過該大小不會被緩存

3、query_cache_min_res_unit：每個qcache最小的緩存空間大小

4、query_cache_size：分配給查詢緩存的總內存

5、query_cache_type：是否開啟查詢緩存

6、query_cache_wlock_invalidate：控制當有鎖加在表上的時候，是否先讓該表相關的 Query Cache失效

3、配置查詢緩存：在配置文件中修改如下參數

query_cache_type = on #開啟查詢緩存

query_cache_size = 200M #分配給查詢緩存的總內存，一般建議不超過256M

query_cache_limit = 1M #限制MySQL存儲的最大結果；如果查詢的結果比limit大，那麼就不會被緩存。

4、監控Qcache使用情況

mysql> show status like "qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 5476 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+

解析：

1、Qcache_free_blocks ：Query Cache中目前還有多少剩餘的blocks。如果該值顯示較大，則說明Query Cache中的內存碎片較多了，可能需要尋找合適的機會進行整理。處理辦法：mysql> FLUSH QUERY CACHE;清理查詢緩存碎片以提高內存使用性能。(該語句不從緩存中移出任何查詢)

2、Qcache_free_memory：Query Cache 中目前剩餘的內存大小

3、Qcache_hits：緩存命中次數

4、Qcache_inserts：多少次未命中然後插入

Query Cache命中率 = Qcache_hits /(Qcache_hits + Qcache_inserts)

5、Qcache_lowmem_prunes：多少條Query 因為內存不足而被清除出Query

6、Qcache_not_cached：因為query_cache_type的設置off或者不能被cache的Query的數量

7、Qcache_queries_in_cache：當前Query Cache中cache的Query數量

8、Qcache_total_blocks：當前Query Cache中的block數量

註：

命中率低，說明沒從內存中取，還是從磁碟取，則多走了一步。看是否sql老變還是什麼問題；

Qcache_not_cached 數值大，開啟了查詢緩存沒有緩存的數據則說明設置緩存的大小太小了，好多無法緩存。

二、存儲引擎層-innodb buffer pool

buffer pool是innodb存儲引擎帶的一個緩存池，查詢數據的時候，它首先會從內存中查詢，如果內存中存在的話，直接返回，從而提高查詢響應時間。Buffer pool是設置的越大越好，一般設置為伺服器物理內存的70%。

1、Innodb_buffer_pool參數

mysql> show variables like "%innodb_buffer_pool%";
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+

參數解析：

1、innodb_buffer_pool_size：Innodb_buffer_pool的大小

2、innodb_buffer_pool_filename ：熱數據文件名稱

3、innodb_buffer_pool_dump_now：默認是OFF；如果開啟則立刻InnoDB將InnoDB緩衝池中的熱數據保存到本地硬碟。(組合innodb_buffer_pool_load_now使用)

4、innodb_buffer_pool_load_now：默認是OFF；如果開啟則立刻通過載入數據頁預熱innodb緩衝池。

5、innodb_buffer_pool_load_at_startup：默認是OFF；如果開啟該參數，啟動MySQL服務時，MySQL將本地熱數據載入到InnoDB緩衝池中。

6、innodb_buffer_pool_dump_at_shutdown：默認是OFF；如果開啟該參數，停止mysq服務時是否自動保存InnoDB buffer pool中熱數據。

Q：如何快速重啟使用資料庫---Preloading the InnoDB Buffer Pool for Faster Restart

A：

開啟innodb_buffer_pool_load_at_startup、innodb_buffer_pool_dump_at_shutdown參數。重啟資料庫，停止時保存熱數據，啟動時載入熱數據到buffer pool。

2、Innodb_buffer_pool狀態

mysql> show status like "%innodb_buffer_pool%";
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170430 7:07:12 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 241 |
| Innodb_buffer_pool_bytes_data | 3948544 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 176 |
| Innodb_buffer_pool_pages_free | 7951 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 53710 |
| Innodb_buffer_pool_reads | 201 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 45242 |
+---------------------------------------+--------------------------------------------------+

Innodb_buffer_pool_read_requests #邏輯讀(緩存讀)請求次數，也是讀的請求次數

Innodb_buffer_pool_reads #從物理磁碟中獲取到數據的次數

註：

邏輯讀就是從buffer pool的讀，但是也會包含物理讀，因為物理讀也要是先將從disk中讀取的數據放入buffer pool里，然後再進行邏輯讀。所以：總的邏輯讀也就是讀的請求次數。

讀的命中率=(Innodb_buffer_pool_read_requests- Innodb_buffer_pool_reads)/ Innodb_buffer_pool_read_requests

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！