如果發現 MySQL 內存飆升，可能是什么原因呢？本文以 InnoDB 存儲引擎來聊一聊 MySQL 可能消耗內存的地方。

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

InnoDB buffer pool

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

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

InnoDB buffer pool 的緩存區結構如下圖（來自官網）：

圖片

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

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

當 InnoDB 讀取一個新的數據頁到緩存區時，會插入到 old 區域的頭部。如果有用戶訪問 old 區域的頁面（不包括系統預讀線程），則該頁面會立即被移動到 young 區域的頭部。

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

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

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 參數由 innodb_buffer_pool_size 配置，一般情況下，在不影響服務器上其他進程運行的情況下建議這個參數設置得盡可能大一些，推薦機器內存 60%~80%。

sort buffer

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

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

join buffer

join buffer 是 MySQL 用來優化 JOIN 語句的一塊緩存區，當查詢無法使用索引時，就需要用到 join buffer。

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

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

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

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

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

臨時表

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

在下面的情況下可能會創建內存臨時表：

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

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

• 為子查詢或半連接創建的表；
• 包括 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（用于排序后的行讀取）等。

最后

本文講述了導致 MySQL 內存升高的主要原因，除了 InnoDB buffer pool 外，其他配置都是會話級別的。業務量突增、SQL 編寫不規范等，都可能造成 MySQL 內存升高。