如果發(fā)現(xiàn) MySQL 內(nèi)存飆升，可能是什么原因呢？本文以 InnoDB 存儲引擎來聊一聊 MySQL 可能消耗內(nèi)存的地方。

為了改進性能，MySQL 分配了多個緩存。

InnoDB buffer pool

InnoDB buffer pool 是 InnoDB 引擎最重要的一個緩存區(qū)，是一塊用于緩存表、索引和其他輔助緩沖的內(nèi)存區(qū)域。它允許頻繁使用的數(shù)據(jù)直接從內(nèi)存中獲取，從而加快了處理速度。在數(shù)據(jù)庫的專用服務(wù)器上，高達 80 的內(nèi)存分配給了 buffer pool。

為了提高大容量讀取操作的效率，buffer pool 被劃分為可容納多行的緩存頁。為了提高緩存管理的效率，buffer pool 使用緩存頁作為節(jié)點的鏈表來實現(xiàn)，并且使用 LRU 算法（變體）對最近訪問較少的數(shù)據(jù)進行淘汰。

InnoDB buffer pool 的緩存區(qū)結(jié)構(gòu)如下圖（來自官網(wǎng)）：

圖片

緩存區(qū)分為 new(young) 和 old 兩個區(qū)域，old 區(qū)域的頭和 young 區(qū)域的尾相連。

• young 區(qū)域是最近頻繁被訪問過的數(shù)據(jù)，占整個緩存區(qū)的 5/8；
• old 區(qū)域則是最近訪問較少的數(shù)據(jù)，占整個緩存區(qū)的 3/8，當(dāng)有新的數(shù)據(jù)需要緩存時，會從 old 區(qū)域中淘汰掉部分數(shù)據(jù)頁。

當(dāng) InnoDB 讀取一個新的數(shù)據(jù)頁到緩存區(qū)時，會插入到 old 區(qū)域的頭部。如果有用戶訪問 old 區(qū)域的頁面（不包括系統(tǒng)預(yù)讀線程），則該頁面會立即被移動到 young 區(qū)域的頭部。

InnoDB buffer pool 中 young 和 old 兩個區(qū)域的頁面如果長時間未被訪問，則會隨著新頁面的插入慢慢移動到列表尾部而“老化”。最終，old 區(qū)域一個長期未被訪問的頁面到達 old 區(qū)域的末尾最終被淘汰。

默認情況下，只要是被讀取到的數(shù)據(jù)頁，就會被移動到 young 區(qū)域。因此類似 mysqldump 操作和不帶 where 條件的查詢語句，可能會將大量數(shù)據(jù)頁加入到 buffer pool 并且淘汰掉其他緩存頁，即使這些新加入的緩存頁以后不會再被使用。同樣，后臺預(yù)讀線程加載的數(shù)據(jù)頁也會有這個問題。當(dāng)然也有一些優(yōu)化措施。可以參考下面兩個地址的方法進行優(yōu)化：

https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-midpoint_insertion.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-read_ahead.html

InnoDB buffer pool 參數(shù)由 innodb_buffer_pool_size 配置，一般情況下，在不影響服務(wù)器上其他進程運行的情況下建議這個參數(shù)設(shè)置得盡可能大一些，推薦機器內(nèi)存 60%~80%。

sort buffer

sort buffer 這個參數(shù)用于 SQL 中的排序語句，MySQL 會給每個會話分配一個 sort buffer。MySQL 會把需要查詢的字段放入到 sort buffer，并且按照 order by 字段進行排序，最終把排序結(jié)果返回給客戶端。

如果要排序的數(shù)據(jù)超過 sort buffer 大小，就需要利用磁盤臨時文件輔助排序，性能下降。

join buffer

join buffer 是 MySQL 用來優(yōu)化 JOIN 語句的一塊緩存區(qū)，當(dāng)查詢無法使用索引時，就需要用到 join buffer。

join buffer 的核心思想是用空間換時間，通過將一部分驅(qū)動表的數(shù)據(jù)臨時存放到 buffer 中，來減少與被驅(qū)動表進行匹配時需要進行的磁盤 I/O 次數(shù)，從而加速查詢。查詢過程如下：

1. 將驅(qū)動表中需要查詢的列和連接列讀取到 join buffer； 

2. 遍歷被驅(qū)動表，拿每一行跟 join buffer 連接行進行匹配； 

3. 如果匹配成功，形成結(jié)果集返回給客戶端。

join buffer 的大小由參數(shù) join_buffer_size 控制，如果 join buffer 放不下驅(qū)動表的數(shù)據(jù)，就需要分段查詢，這會增加對被驅(qū)動的掃描。

臨時表

在某些情況下，MySQL server 在執(zhí)行 SQL 語句時會創(chuàng)建內(nèi)部臨時表，這種情況用戶是無法控制的。由 tmp_table_size 或 max_heap_table_size 這兩個參數(shù)確定，兩個參數(shù)的最小值就是內(nèi)存臨時表的最大容量。如果同時有大量查詢創(chuàng)建大臨時表，會消耗大量內(nèi)存。

在下面的情況下可能會創(chuàng)建內(nèi)存臨時表：

• UNION 語句，除了一些特殊情況，比如 UNION ALL，或者 UNION 語句中沒有全局 ORDER BY；
• 一些視圖，比如使用 TEMPTABLE 算法、UNION 或聚合的視圖；
• 衍生表，比如下面語句：

SELECT ... FROM (subquery) [AS] tbl_name ...

• 為子查詢或半連接創(chuàng)建的表；
• 包括 ORDER BY 和 GROUP BY 子句并且使用的列不一樣，或 ORDER BY 和 GROUP BY 語句使用的列不在 JOIN 中的第一個表；
• DISTINCT 和 ORDER BY 組合的語句；
• SQL_SMALL_RESULT，顯示指定使用臨時表；
• INSERT ... SELECT 語句；
• 多表 update 語句；
• GROUP_CONCAT() 或 COUNT(DISTINCT) 表達式。

其他

除了上面的配置外，還有 Read Buffer（主要用于順序讀?。?、Read Rnd Buffer（用于排序后的行讀?。┑?。

最后

本文講述了導(dǎo)致 MySQL 內(nèi)存升高的主要原因，除了 InnoDB buffer pool 外，其他配置都是會話級別的。業(yè)務(wù)量突增、SQL 編寫不規(guī)范等，都可能造成 MySQL 內(nèi)存升高。