我們先來回顧一下交友平臺使用者表的表結構：
CREATE TABLE `user` （ `id` int（11） NOT NULL， `user_id` int（8） DEFAULT NULL COMMENT ‘使用者id’， `user_name` varchar（29） DEFAULT NULL COMMENT ‘使用者名稱’， `user_introduction` varchar（498） DEFAULT NULL COMMENT ‘使用者介紹’， `sex` tinyint（1） DEFAULT NULL COMMENT ‘性別’， `age` int（3） DEFAULT NULL COMMENT ‘年齡’， `birthday` date DEFAULT NULL COMMENT ‘生日’， PRIMARY KEY （`id`）， KEY `index_un_age_sex` （`user_name`，`age`，`sex`）， KEY `index_age_sex` （`age`，`sex`）） ENGINE=InnoDB DEFAULT CHARSET=utf8；
其中，user_introduction欄位：使用者介紹，裡面允許使用者填寫非常長的內容，所以，我將這個欄位的設為varchar（498），加上其他欄位，單條記錄的長度可能就會比較大了，這時，如果執行下面這條SQL：
select user_id， user_name， user_introduction from user where age > 20 and age < 50
假設使用者表中已經儲存300w條記錄，執行上面的SQL，會發生什麼情況呢？
對MySQL有初步瞭解的同學肯定知道Query Cache，它的作用就是快取查詢結果，透過首次查詢時，建立SQL與結果的對映關係，相同SQL再次查詢時，可以命中Query Cache，以此來提升後續相同查詢的效率。
因此，對於上面的SQL查詢，MySQL可以在首次執行這條SQL後，將查詢結果寫入Query Cache，下次相同SQL執行時，可以從Query Cache中取出結果返回。
但是，你有沒有想過，如果滿足查詢條件的使用者數超過10w，那麼，這10w條記錄能否完全寫進Query Cache呢？
今天，我就從Query Cache的結構說起，逐步揭曉答案。
在《導讀》中我提到MySQL透過建立SQL與查詢結果的對映關係來實現再次查詢的快速命中，那麼，問題來了：為了實現這樣的一個對映關係，總得有個結構承載這樣的關係吧！那麼，MySQL使用什麼結構來承載這樣的對映關係呢？
或許你已經想到了：HashMap！沒錯，MySQL的確使用了HashMap來表達SQL與結果集的對映關係。進而我們就很容易想到這個HashMap的Key和Value是什麼了。
Key：MySQL使用query + database + flag組成一個key。這個key的結構還是比較直觀的，它表示哪個庫的哪條SQL使用了Query Cache。
Value：MySQL使用一個叫query_cache_block的結構作為Map的value，這個結構存放了一條SQL的查詢結果。
Query Cache Block
那麼，一條SQL的查詢結果又是如何存放在query_cache_block中的呢？下面我們就結合《導讀》中的SQL，來看看一個query_cache_block的結構：
如上圖所示，一個query_cache_block主要包含3個核心欄位：
used：存放結果集的大小。MySQL透過block在記憶體中的偏移量 + 這個大小來獲取結果集。如上圖，假設《導讀》中SQL查詢的結果為<10001， Jack， I‘m Jack>，那麼，used為這個查詢結果的大小。
type：Block的型別。包含{FREE， QUERY， RESULT， RES_CONT， RES_BEG， RES_INCOMPLETE， TABLE， INCOMPLETE}這幾種型別。這裡我重點講解QUERY和RESULT，其他型別你可以自行深入瞭解。 QUERY：表示這個block中存放的是查詢語句。為什麼要快取查詢語句呢？ 在併發場景中，會存在多個會話執行同一條查詢語句，因此，為了避免重複構造《導讀》中所說的HashMap的Key，MySQL快取了查詢語句的Key，保證查詢Query Cache的效能。 RESULT：表示這個block中存放的是查詢結果。如上圖，《導讀》中SQL的查詢結果<10001， Jack， I’m Jack>放入block，所以，block型別為RESULT。
n_tables：查詢語句使用的表的數量。那麼，block又為什麼要存表的數量呢？ 因為MySQL會快取table結構，一張table對應一個table結構，多個table結構組成一條連結串列，MySQL需要維護這條連結串列增刪改查，所以，需要n_tables欄位。
現在我們知道了一個query_cache_block的結構了，下面我簡稱block。
現在有這麼一個場景：
已知一個block的大小是1KB，而《導讀》中的查詢語句得到的結果記錄數有10w，它的大小有1MB，那麼，顯然一個block放不下1MB的結果，此時，MySQL會怎麼做呢？
為了能夠快取1MB的查詢結果，MySQL設計了一個雙向連結串列，將多個block串聯起來，1MB的資料分別放在連結串列中多個block裡。於是，就有了下面的結構：邏輯塊連結串列。
圖中，MySQL將多個block透過一個雙向連結串列串聯起來，每個block就是我上面講到的block結構。透過雙向連結串列我們就可以將一條查詢語句對應的結果集串聯起來。
比如針對《導讀》中SQL的查詢結果，圖中，前兩個block分別存放了兩個滿足查詢條件的結果：<10001，Jack，I‘m Jack>和<10009，Lisa，I’m Lisa>。同時，兩個block透過雙向指標串聯起來。
還是《導讀》中的SQL案例，已知一個block的大小是1K，假設SQL的查詢結果為<10001，Jack，I‘m Jack>這一條記錄，該記錄的大小隻有100Byte，那麼，此時查詢結果小於block大小，如果把這個查詢結果放到1K的block裡，就會浪費1024-100=924 位元組的block空間。所以，為了避免block空間的浪費，MySQL又引入了一個新結構：
如上圖，下面的物理塊就是MySQL為了解決block空間浪費引入的新結構。該結構也是一個多block組成的雙向連結串列。
以《導讀》中的SQL為例，已知SQL查詢的結果為<10001，Jack，I’m Jack>，那麼，將邏輯塊連結串列和物理塊連結串列結合起來，這個結果在block中是如何表達的呢？
如上圖，邏輯塊連結串列的第一個block存放了<10001，Jack，I‘m Jack>這個查詢結果。
由於查詢結果大小為100B，小於block的大小1K，所以，見上圖，MySQL將邏輯塊連結串列中的第一個block分裂，分裂出下面的兩個物理塊block，即紅色箭頭部分，將<10001，Jack，I’m Jack>這個結果放入第一個物理塊中。其中，第一個物理塊block大小為100B，第二個物理塊block大小為924B。
講完了query_cache_block，我想你應該對其有了較清晰的理解。但是，我在上面多次提到一個block的大小，那麼，這個block的大小又是如何決定的呢？為什麼block的大小是1K，而不是2K，或者3K呢？
要回答這個問題，就要涉及MySQL對block的記憶體管理了。MySQL為了管理好block，自己設計了一套記憶體管理機制，叫做query_cache_memory_bin。
下面我就詳細講講這個query_cache_memory_bin。
Query Cache Memory Bin
MySQL將整個Query Cache劃分多層大小不同的多個query_cache_memory_bin（簡稱bin），如下圖：
說明：
steps：為層號，如上圖中，從上到下分為0、1、2、3這4層。
bin：每一層由多個bin組成。其中，bin中包含以下幾個屬性： size：bin的大小 free_blocks：空閒的query_cache_block連結串列。每個bin包含一組query_cache_block連結串列，即邏輯塊連結串列和物理塊連結串列，也就是《Query Cache Block》中我講到的兩個連結串列組成一組query_cache_block。 每層bin的個數透過下面的公式計算得到： bin個數 = 上一層bin數量總和 + QUERY_CACHE_MEM_BIN_PARTS_INC） * QUERY_CACHE_MEM_BIN_PARTS_MUL 其中，QUERY_CACHE_MEM_BIN_PARTS_INC = 1 ，QUERY_CACHE_MEM_BIN_PARTS_MUL = 1。2 因此，如上圖，得到各層的bin個數如下： 第0層：bin個數為1 第1層：bin個數為2 第2層：bin個數為3 第3層：bin個數為4 每層都有其固定大小。這個大小的計算公式如下： 第0層的大小 = query_cache_size >> QUERY_CACHE_MEM_BIN_FIRST_STEP_PWR2 >> QUERY_CACHE_MEM_BIN_STEP_PWR2 其餘層的大小 = 上一層的大小 >> QUERY_CACHE_MEM_BIN_STEP_PWR2 其中，QUERY_CACHE_MEM_BIN_FIRST_STEP_PWR2 = 4，QUERY_CACHE_MEM_BIN_STEP_PWR2 = 2 因此，假設query_cache_size = 25600K，那麼，得到計算各層的大小如下： 第0層：400K 第1層：100K 第2層：25K 第3層：6K 每層中的bin也有固定大小，但最小不能小於QUERY_CACHE_MIN_ALLOCATION_UNIT。這個bin的大小的計算公式採用對數逼近法如下： bin的大小 = 層大小 / 每一層bin個數，無法整除向上取整 其中，QUERY_CACHE_MIN_ALLOCATION_UNIT = 512B 因此，如上圖，得到各層bin的大小如下： 第0層：400K / 1 = 400K 第1層：100K / 2 = 50K 第2層：25K / 3 = 9K，從最左邊的bin開始分配大小： 第1個bin：9K 第2個bin：8K 第3個bin：8K 第3層：6K / 4 = 2K，從最左邊的bin開始分配大小： 第1個bin：2K 第2個bin：2K 第3個bin：1K 第4個bin：1K
透過對MySQL管理Query Cache使用記憶體的講解，我們應該猜到MySQL是如何給query_cache_block分配記憶體大小了。我以上圖為例，簡單說明一下：
由於每個bin中包含一組query_cache_block連結串列（邏輯塊和物理塊連結串列），如果一個block大小為1K，這時，透過遍歷bin找到一個大於1K的bin，然後，把該block連結到bin中的free_blocks連結串列就行了。具體過程，我在下面會詳細講解。
在瞭解了query_cache_block、query_cache_memory_bin這兩種結構之後，我想你對Query Cache在處理時用到的資料結構有了較清晰的理解。那麼，結合這兩種資料結構，我們再看看Query Cache的幾種處理場景及實現原理。
Cache寫入
我們結合《導讀》中的SQL，先看一下Query Cache寫入的過程：
結合上面HashMap的Key的結構，根據查詢條件age > 20 and age < 50構造HashMap的Key：age > 20 and age < 50 + user + flag，
其中flag包含了查詢結果
，將Key寫入HashMap。如上圖，Result就是這個Key。
根據Result對query_cache_mem_bin的層進行二分查詢，找到層大小大於Result大小的層。如上圖，假設第1層為找到的目標層。
根據Result從右向左遍歷第1層的bin（
因為每層bin大小從左向右降序排列，MySQL從小到大開始分配
），計算bin中的剩餘空間大小，如果剩餘空間大小大於Result大小，那麼，就選擇這個bin存放Result，否則，繼續向左遍歷，直至找到合適的bin為止。如上圖灰色bin，選擇了第2層的第一個bin存放Result。
根據Result從左向右掃描上一步得到的bin中的free_blocks連結串列中的邏輯塊連結串列，找到第一個block大小大於Result大小的block。如上圖，找到第2個邏輯塊block。
假設Result大小為100B，第2個邏輯塊block大小為1k，由於block大於Result大小，所以，分裂該邏輯塊block為2個物理塊block，其中，分裂後第一個物理塊block大小為100B，第二個物理塊block大小為924B。
將Result結果寫入第1個物理塊block。如上圖，將<10001， Jack， I‘m Jack>這個Result寫入灰色的物理塊block。
根據Result所在的block，找到對應的block_table，更新table資訊到block_table中。
Cache失效
當一個表發生改變時，所有與該表相關的cached queries將失效。一個表發生變化，包含多種語句，比如 INSERT， UPDATE， DELETE， TRUNCATE TABLE，ALTER TABLE， DROP TABLE， 或者 DROP DATABASE。
Query Cache Block Table
， 為了能夠快速定位與一張表相關的Query Cache，將這張表相關的Query Cache失效，MySQL設計一個數據結構：Query_cache_block_table。如下圖：
這是一個雙向連結串列，對於一條SQL，如果包含多表聯接，那麼，就可以將這條SQL對應多張錶鏈接起來，再插入這張連結串列，比如，我們把user和t_user_view（訪客表）聯接，查詢使用者訪客資訊，那麼，在圖中，假設邏輯塊連結串列存放就是聯表查詢的結果，因此，我們就看到user表和t_user_view都指向了該邏輯塊連結串列。
我們來看一下這個結構包含的核心屬性：
block：與一張表相關的query_cache_block連結串列。如上圖是user表的query_cache_block_table，該block中的block屬性指向了邏輯塊block連結串列，該連結串列中第1個block包含《導讀》中SQL的查詢結果<10001， Jack， I’m Jack>。
table：同樣以user和t_user_view（訪客表）聯接，查詢使用者訪客資訊為例，這時，我對這個訪客資訊建立了檢視，那麼，MySQL如何表達表的關係呢？為了解決這個問題，MySQL引入了table，透過這個table記錄檢視資訊，檢視來源表都指向這個table來表達表的關係。如上圖，user和t_user_view都指向了user_view，來表示user和t_user_view（訪客表）對應的檢視是user_view。
和Query Cache的HashMap結構一樣，為了根據表名可以快速找到對應的query_cache_block，MySQL也設計了一個表名跟query_cache_block對映的HashMap，這樣，MySQL就可以根據表名快速找到query_cache_block了。
透過上面這些內容的講解，我想你應該猜到了一張表變更時，MySQL是如何失效Query Cache的？
我們來看下上面這張圖，關注紅線部分：
根據user表找到其對應的query_cache_block_table。如上圖，找到第2個table block。
根據query_cache_block_table中的block屬性，找到table下的邏輯塊連結串列。如上圖，找到了右側的邏輯塊連結串列。
遍歷邏輯塊連結串列及每個邏輯塊block下的物理塊連結串列，釋放所有block。
Cache淘汰
如果query_cache_mem_bin中沒有足夠空間的block存放Result，那麼，將觸發query_cache_mem_bin的記憶體淘汰機制。
這裡我借用《Cache寫入》的過程，一起來看看Query Cache的淘汰機制：
結合上面HashMap的Key的結構，根據查詢條件age > 20 and age < 50構造HashMap的Key：age > 20 and age < 50 + user + flag，
其中flag包含了查詢結果
，將Key寫入HashMap。如上圖，Result就是這個Key。
根據Result對query_cache_mem_bin的層進行二分查詢，找到層大小大於Result大小的層。如上圖，假設第1層為找到的目標層。
根據Result從右向左遍歷第1層的bin（
因為每層bin大小從左向右降序排列，MySQL從小到大開始分配
），計算bin中的剩餘空間大小，如果剩餘空間大小大於Result大小，那麼，就選擇這個bin存放Result。如上圖灰色bin，選擇了第2層的第一個bin存放Result。
根據Result從左向右掃描上一步得到的bin中的block連結串列中的邏輯塊連結串列，找到第一個block大小大於Result大小的block。如上圖，找到第2個邏輯塊block。
假設Result大小為100B，第2個邏輯塊block大小為1k，由於block大於Result大小，所以，分裂該邏輯塊block為2個物理塊block，其中，分裂後第一個物理塊block大小為100B，第二個物理塊block大小為924B。
由於第1個物理塊block已經被佔用，所以，MySQL不得不淘汰該block，用以放入Result，淘汰過程如下：
發現相鄰的第2個物理塊block最少使用，所以，將該物理塊和第1個物理塊block合併成一個新block。如上圖右側灰色block和虛線block合併成下面的一個灰色block。
將Result結果寫入合併後的物理塊block。如上圖，將<10001， Jack， I‘m Jack>這個Result寫入合併後的灰色block。
在Cache淘汰這個場景中，我們重點關注一下第6步，我們看下這個場景：
從第1個物理塊block開始掃描，合併相鄰的第2個block跟第1個block為一個新block
如果合併後block大小仍然不足以存放Result，繼續掃描下一個block，重複第1步
如果合併後block大小可以存放Result，結束掃描
將Result寫入合併後block
透過上面的場景描述，我們發現如果Result很大，那麼，MySQL將不斷掃描物理塊block，然後，不停地合併block，這是不小的開銷，因此，我們要儘量避免這樣的開銷，保證Query Cache查詢的效能。
有什麼辦法避免這樣的開銷呢？
我在最後小結的時候回答一下這個問題。
小結
好了，這篇內容我講了很多東西，現在，我們來總結一下今天講解的內容：
資料結構：講解了Query Cache設計的資料結構： 資料結構說明Query_cache_block存放了一條SQL的查詢結果Query_cache_mem_binquery_cache_block的記憶體管理結構Query_cache_block_table一張表對應一個block_table，方便快速失效query cache
Query Cache處理的場景：Cache寫入、Cache失效和Cache淘汰。
最後，我們再回頭看一下文章開頭的那個問題：10w條使用者記錄是否可以寫入Query Cache？我的回答是：
我們先對使用者表的10w記錄大小做個計算： 使用者表包含user_id（8），user_name（29），user_introduction（498），age（3），sex（1）這幾個欄位，按欄位順序累加，一條記錄的長度為8+30（
varchar型別長度可以多儲存1或2byte
）+500+3+1=542byte，那麼，10w條記錄最大長度為542 * 10w = 54200000byte。 如果要將10w條記錄寫入Query Cache，則需要將近54200K大小的Query Cache來儲存這10w條記錄，而
Query Cache大小預設為1M
，所以，如果欄位user_introduction在業務上非必須出現，請在select子句中排除該欄位，減少查詢結果集的大小，使結果集可以完全寫入Query Cache，**這也是為什麼DBA建議開發不要使用select
的原因，但是如果select 取出的欄位都不大，查詢結果可以完全寫入Query Cache，那麼，後續相同查詢條件的查詢效能也是會提升的，
。
調大query_cache_size這個MySQL配置引數，如果業務上一定要求select所有欄位，而且記憶體足夠用，那麼，可以將query_cache_size調至可以容納10w條使用者記錄，即54200K。
調大query_cache_min_res_unit這個MySQL配置引數，使MySQL在第一次執行查詢並寫入Query Cache時，儘可能不要發生過多的bin合併，減少物理塊block連結串列的合併開銷。那麼，
query_cache_min_res_unit調成多少合適呢？
這需要結合具體業務場景綜合衡量，比如，在使用者中心繫統中，一般會有一個會員中心的功能，而這個功能中，使用者查詢自己的資訊是一個高頻的查詢操作，為了保證這類操作的查詢效能，我們勢必會將這個查詢結果，即單個使用者的基本資訊寫入Query Cache，在我的回答的第1條中，我說過一條使用者記錄最大長度為542byte，結合10w條使用者記錄需要54200K的Query Cache，那麼，設定query_cache_min_res_unit = 542byte就比較合適了。 這樣，有兩點好處： 保證查詢單個使用者資訊，其直接可分配的bin大小大於542byte，寫入單個使用者資訊時可以避免了bin的合併和空間浪費。 10w條使用者記錄寫入Query Cache，雖然第一次分配快取時，仍然需要合併bin，但是，綜合單使用者查詢的場景，這個合併過程是可以接受的，畢竟，只會在第一次寫快取時發生bin合併，後續快取失效後，再次分配時，可以直接取到合併後的那個bin分配給10w條記錄，不會再產生bin的合併，所以，這個合併過程是可以接受的。
調大query_cache_limit這個MySQL配置引數，我在本章節中沒有提到這個引數，它是用來控制Query Cache最大快取結果集大小的，預設是1M，所以，10w條記錄，建議調大這個引數到54200K。
思考題
最後，對比前面《告訴面試官，我能最佳化groupBy，而且知道得很深！》這篇文章，發現MySQL特別喜歡自己實現記憶體的管理，而不用Linux核心的記憶體管理機制（比如：夥伴系統），為什麼呢？
