mysql的快取機制

在伺服器級別只提供了query cache，而在儲存引擎級別，MyISAM和InnoDB分別引入了key cache和buffer pool

 

什麼是query cache

Mysql沒有shared_pool快取執行計劃，但是提供了query cache快取sql執行結果和文字，如果在生命週期內完全相同的sql再次執行，則連sql解析都免去了；

所謂完全相同，包含如下條件

Sql的大小寫必須完全一樣；

發起sql的客戶端必須使用同樣的字符集和通訊協議；

sql查詢同一資料庫下的同一個表(不同資料庫可能有同名表)；

Sql查詢結果必須確定，即不能帶有now()等函式；

當查詢表發生DML或DDL，其快取即失效；

針對mysql/information_schema/performance_schema的查詢不快取；

使用臨時表的sql也不能快取；

 

開啟快取後，每個select先檢查是否有可用快取(必須對這些表有select許可權)，而每個寫入操作先執行查詢語句並使相關快取失效；

5.5起可快取基於檢視的查詢

 

Mysql維護一個hash表用來查詢快取，其key為sql text，資料庫名以及客戶端協議的版本等

相應引數

Have_query_cache：伺服器是否支援查詢快取

Query_cache_type：0(OFF)不快取；1(ON)快取查詢但不包括使用SQL_NO_CACHE的sql；2(DEMAND)只快取使用SQL_CACHE的sql

Query_cache_size：位元組為單位，即使query_cache_type=0也會為分配該記憶體，所以應該一併設定為0

Query_cache_limit：允許快取的最大結果集，大於此的sql不予快取

Query_cache_min_res_limit：用於限定塊的最小尺寸，預設4K；

 

快取的metadata佔有40K記憶體，其可分為大小不等的多個子塊，各塊之間使用雙向連結串列連結；根據其功能分別儲存查詢結果，基表和sql text等；

每個sql至少用到兩個塊：分別儲存sql文字和查詢結果，查詢引用到的表各佔一個塊；

為了減少響應時間，每產生1行資料就傳送給客戶端；

資料庫啟動時呼叫malloc()分配查詢快取

 

查詢快取擁有一個全域性鎖，一旦有會話獲取就會阻塞其他訪問快取的會話，因此當快取大量sql時，快取invalidation可能會消耗較長時間；

 

Innodb也可以使用查詢快取，每個表在資料字典中都有一個事務ID計數器，ID小於此值的事務不可使用快取；表如果有鎖(任何鎖)則也不可使用查詢快取；

 

 狀態變數

有關query cache的狀態變數都以Qcache打頭

mysql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+--------+

| Variable_name           | Value  |

+-------------------------+--------+

| Qcache_free_blocks      | 36     |

| Qcache_free_memory      | 138488 |

| Qcache_hits             | 79570  |

| Qcache_inserts          | 27087  |

| Qcache_lowmem_prunes    | 3114   |

| Qcache_not_cached       | 22989  |

| Qcache_queries_in_cache | 415    |

| Qcache_total_blocks     | 912    |

+-------------------------+--------+

Qcache_inserts—被加到快取中query數目

Qcache_queries_in_cache—註冊到快取中的query數目

快取每被命中一次，Qcache_hits就加1；

計算快取query的平均大小=(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache

Com_select = Qcache_not_cached + Qcache_inserts + queries with errors found during the column-privileges check

Select = Qcache_hits + queries with errors found by parser

 

 

Buffer pool

innodb即快取表又快取索引，還有設定多個緩衝池以增加併發，很像oracle

採用LRU演算法：

所有buffer塊位於同一列表，其中後3/8為old，每當新讀入一個資料塊時，先從隊尾移除同等塊數然後插入到old子列的頭部，如再次訪問該塊則將其移至new子列的頭部

Innodb_buffer_pool_size:  buffer pool大小

Innodb_buffer_pool_instances: 子buffer pool數量，buffer pool至少為1G時才能生效

Innodb_old_blocks_pct: 範圍5 – 95， 預設為37即3/8，指定old子列的比重

Innodb_old_blocks_time: 以ms為單位，新插入old子列的buffer塊必須等待指定時間後才能移入new列，適用於one-time scan頻繁的操作，以避免經常訪問的資料塊被剔出buffer pool

 

可透過狀態變數獲知當前buffer pool的執行資訊

Innodb_buffer_pool_pages_total：快取池總頁數

Innodb_buffer_pool_bytes_data：當前buffer pool快取的資料大小，包括髒資料

Innodb_buffer_pool_pages_data：快取資料的頁數量

Innodb_buffer_pool_bytes_dirty：快取的髒資料大小

Innodb_buffer_pool_pages_diry：快取髒資料頁數量

Innodb_buffer_pool_pages_flush：重新整理頁請求數量

Innodb_buffer_pool_pages_free：空閒頁數量

Innodb_buffer_pool_pages_latched：快取中被latch的頁數量，這些頁此刻正在被讀或寫；然而計算此變數比較消耗資源，只有在UNIV_DEBUG被定義了才可用

相關原始碼如下

#ifdef UNIV_DEBUG
  {"buffer_pool_pages_latched",
  (char*) &export_vars.innodb_buffer_pool_pages_latched,  SHOW_LONG},
#endif /* UNIV_DEBUG */

 

Innodb_buffer_pool_pages_misc：用於維護諸如行級鎖或自適應hash索引的記憶體頁=總頁數-空閒頁-使用的頁數量

Innodb_buffer_pool_read_ahead：預讀入快取的頁數量

Innodb_buffer_pool_read_ahead_evicted：預讀入但是1次都沒用就被剔出快取的頁

Innodb_buffer_pool_read_requests：InnoDB的邏輯讀請求次數

Innodb_buffer_pool_reads：直接從磁碟讀取資料的邏輯讀次數

Innodb_buffer_pool_wait_free：快取中沒有空閒頁滿足當前請求，必須等待部分頁回收或重新整理，記錄等待次數

Innodb_buffer_pool_write_requests：向快取的寫數量

 

 

可使用innodb standard monitor監控buffer pool的使用情況，主要有如下指標：

Old database pages: old子列中的頁數

Pages made young, not young: 從old子列移到new子列的頁數，old子列中沒有被再次訪問的頁數

Youngs/s  non-youngs/s: 訪問old並導致其移到new列的次數

 

 

 

Key cache

5.5僅支援一個結構化變數，即key cache，其包含4個部件

Key_buffer_size

Key_cache_block_size：單個塊大小，預設1k

Key_cache_division_limit：warm子列的百分比(預設100)，key cache buffer列表的分隔點，用於分隔host和warm子列表

Key_cache_age_threshold：頁在hot子列中的生命週期，值越小則越快的移至warm列表

 

MyISAM只快取索引，

可建立多個key buffer—set global hot_cache.key_buffer_size=128*1024

索引指定key buffer—cache index t1 in hot_cache

可在資料庫啟動時load index into key_buffer提前載入快取，也可透過配置檔案自動把索引對映到key cache

 

key_buffer_size = 4G

hot_cache.key_buffer_size = 2G

cold_cache.key_buffer_size = 2G

init_file=/path/to/data-directory/mysqld_init.sql

mysqld_init.sql內容如下

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache

CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache

 

預設採用LRU演算法，也支援名為中間點插入機制midpoint insertion strategy

索引頁剛讀入key cache時，被放在warm列的尾部，被訪問3次後則移到hot列尾並迴圈移動，如果在hot列頭閒置連續N次都沒訪問到，則會被移到warm列頭，成為被剔出cache的首選；

N= block no* key_cache_age_threshold/100