MySQL 記憶體模型

對於任何一個資料庫管理系統來說，記憶體的分配使用絕對可以算的上是其核心之一了，所以很多希望更為深入瞭解某資料庫管理系統的人，都會希望一窺究竟，我也不例外。

從記憶體的使用方式MySQL 資料庫的記憶體使用量主要分為以下兩類

* 線程獨享記憶體
* 全域共用記憶體

今天這篇文章暫時先分析 MySQL 中主要的 “線程獨享記憶體” 的。

在 MySQL 中，線程獨享記憶體主要用於各用戶端連接線程儲存各種操作的獨享資料，如線程棧資訊，分組排序操作，資料讀寫緩衝，結果集暫存等等，而且大多數可以通過相關參數來控制記憶體的使用量。

線程棧資訊使用記憶體(thread_stack)：主要用來存放每一個線程自身的標識資訊，如線程id，線程運行時基本資料等等，我們可以通過 thread_stack 參數來設定為每一個線程棧分配多大的記憶體。

排序使用記憶體(sort_buffer_size)：MySQL 用此記憶體地區進行排序操作（filesort），完成用戶端的排序請求。當我們設定的排序區緩衝大小無法滿足排序實際所需記憶體的時候，MySQL 會將資料寫入磁碟檔案來完成排序。由於磁碟和記憶體的讀寫效能完全不在一個數量級，所以sort_buffer_size參數對排序操作的效能影響絕對不可小視。排序操作的實現原理請參考：MySQL Order By 的實現分析。

Join操作使用記憶體(join_buffer_size)：應用程式經常會出現一些兩表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的時候（all/index join），為了減少參與Join的“被驅動表”的讀取次數以提高效能，需要使用到 Join Buffer 來協助完成 Join操作（具體 Join 實現演算法請參考：MySQL 中的 Join 基本實現原理）。當 Join Buffer 太小，MySQL 不會將該 Buffer 存入磁碟檔案，而是先將Join Buffer中的結果集與需要 Join 的表進行 Join 操作，然後清空 Join Buffer 中的資料，繼續將剩餘的結果集寫入此 Buffer 中，如此往複。這勢必會造成被驅動表需要被多次讀取，成倍增加 IO 訪問，降低效率。

順序讀取資料緩衝區使用記憶體(read_buffer_size)：這部分記憶體主要用於當需要順序讀取資料的時候，如無發使用索引的情況下的全表掃描，全索引掃描等。在這種時候，MySQL 按照資料的儲存順序依次讀取資料區塊，每次讀取的資料快首先會暫存在read_buffer_size中，當 buffer 空間被寫滿或者全部資料讀取結束後，再將buffer中的資料返回給上層調用者，以提高效率。

隨機讀取資料緩衝區使用記憶體(read_rnd_buffer_size)：和順序讀取相對應，當 MySQL 進行非順序讀取（隨機讀取）資料區塊的時候，會利用這個緩衝區暫存讀取的資料。如根據索引資訊讀取表資料，根據排序後的結果集與表進行Join等等。總的來說，就是當資料區塊的讀取需要滿足一定的順序的情況下，MySQL 就需要產生隨機讀取，進而使用到 read_rnd_buffer_size 參數所設定的記憶體緩衝區。

串連資訊及返回用戶端前結果集暫存使用記憶體(net_buffer_size)：這部分用來存放用戶端連接線程的串連資訊和返回用戶端的結果集。當 MySQL 開始產生可以返回的結果集，會在通過網路返回給用戶端請求線程之前，會先暫存在通過 net_buffer_size 所設定的緩衝區中，等滿足一定大小的時候才開始向用戶端發送，以提高網路傳輸效率。不過，net_buffer_size 參數所設定的僅僅只是該緩衝區的初始化大小，MySQL 會根據實際需要自行申請更多的記憶體以滿足需求，但最大不會超過 max_allowed_packet 參數大小。

批量插入暫存使用記憶體(bulk_insert_buffer_size)：當我們使用如 insert … values(…),(…),(…)… 的方式進行批量插入的時候，MySQL 會先將提交的資料放如一個緩衝空間中，當該緩衝空間被寫滿或者提交完所有資料之後，MySQL 才會一次性將該緩衝空間中的資料寫入資料庫並清空緩衝。此外，當我們進行 LOAD DATA INFILE 操作來將文字檔中的資料 Load 進資料庫的時候，同樣會使用到此緩衝區。

暫存資料表使用記憶體(tmp_table_size)：當我們進行一些特殊操作如需要使用暫存資料表才能完成的 Order By，Group By 等等，MySQL 可能需要使用到暫存資料表。當我們的暫存資料表較小（小於 tmp_table_size 參數所設定的大小）的時候，MySQL 會將暫存資料表建立成記憶體暫存資料表，只有當 tmp_table_size 所設定的大小無法裝下整個暫存資料表的時候，MySQL 才會將該表建立成 MyISAM 儲存引擎的表存放在磁碟上。不過，當另一個系統參數 max_heap_table_size 的大小還小於 tmp_table_size 的時候，MySQL 將使用 max_heap_table_size 參數所設定大小作為最大的記憶體暫存資料表大小，而忽略 tmp_table_size 所設定的值。而且 tmp_table_size 參數從 MySQL 5.1.2 才開始有，之前一直使用 max_heap_table_size。

上面所列舉的 MySQL 線程獨享記憶體僅僅只是所有線程獨享記憶體中的部分，並不是全部，選擇的原則是可能對 MySQL 的效能產生較大的影響，且可以通過系統參數進行調節。

由於以上記憶體都是線程獨享，極端情況下的記憶體總體使用量將是所有連接線程的總倍數。所以各位朋友在設定過程中一定要謹慎，切不可為了提升效能就盲目的增大各參數值，避免因為記憶體不夠而產生 Out Of Memory 異常或者是嚴重的 Swap 交換反而降低整體效能。

MySQL 全域共用記憶體的使用介紹。
全域共用內則主要是 MySQLInstance（mysqld進程）以及底層儲存引擎用來暫存各種全域運算及可共用的暫存資訊，如儲存查詢快取的 QueryCache，緩衝連接線程的 Thread Cache，緩衝表檔案控制代碼資訊的 Table Cache，緩衝二進位日誌的 BinLogBuffer， 緩衝 MyISAM 儲存引擎索引鍵的 Key Buffer以及儲存 InnoDB 資料和索引的 InnoDB BufferPool 等等。下面針對 MySQL 主要的共用記憶體進行一個簡單的分析。
查詢快取（Query Cache）：查詢快取是 MySQL 比較獨特的一個快取區域，用來緩衝特定Query 的結果集（Result Set）資訊，且共用給所有用戶端。通過對 Query 語句進行特定的 Hash 計算之後與結果集對應存放在Query Cache 中，以提高完全相同的 Query 語句的相應速度。當我們開啟 MySQL 的 Query Cache 之後，MySQL接收到每一個 SELECT 類型的 Query 之後都會首先通過固定的 Hash 演算法得到該 Query 的 Hash 值，然後到 QueryCache 中尋找是否有對應的 Query Cache。如果有，則直接將 Cache的結果集返回給用戶端。如果沒有，再進行後續操作，得到對應的結果集之後將該結果集緩衝到 Query Cache中，再返回給用戶端。當任何一個表的資料發生任何變化之後，與該表相關的所有 Query Cache 全部會失效，所以 Query Cache對變更比較頻繁的表並不是非常適用，但對那些變更較少的表是非常合適的，可以極大程度的提高查詢效率，如那些靜態資源表，配置表等等。為了儘可能高效的利用 Query Cache，MySQL 針對 Query Cache 設計了多個 query_cache_type 值和兩個 QueryHint：SQL_CACHE 和 SQL_NO_CACHE。當 query_cache_type 設定為0（或者 OFF）的時候不使用Query Cache，當設定為1（或者 ON）的時候，若且唯若 Query 中使用了 SQL_NO_CACHE 的時候 MySQL 會忽略Query Cache，當 query_cache_type 設定為2（或者DEMAND）的時候，若且唯若Query 中使用了SQL_CACHE 提示之後，MySQL 才會針對該 Query 使用 Query Cache。可以通過 query_cache_size來設定可以使用的最大記憶體空間。
連接線程緩衝（Thread Cache）：連接線程是 MySQL為了提高建立連接線程的效率，將部分閒置連接線程保持在一個緩衝區以備新進串連請求的時候使用，這尤其對那些使用短連線應用程式程式來說可以極大的提高建立串連的效率。當我們通過 thread_cache_size設定了連接線程緩衝池可以緩衝的連接線程的大小之後，可以通過(Connections - Threads_created) /Connections * 100% 計算出連接線程緩衝的命中率。注意，這裡設定的是可以緩衝的連接線程的數目，而不是記憶體空間的大小。
表緩衝（Table Cache）：表緩衝區主要用來緩衝表檔案的檔案控制代碼資訊，在MySQL5.1.3之前的版本通過 table_cache 參數設定，但從MySQL5.1.3開始改為 table_open_cache來設定其大小。當我們的用戶端程式提交 Query 給 MySQL 的時候，MySQL 需要對 Query所涉及到的每一個表都取得一個表檔案控制代碼資訊，如果沒有 Table Cache，那麼 MySQL就不得不頻繁的進行開啟關閉檔案操作，無疑會對系統效能產生一定的影響，Table Cache 正是為瞭解決這一問題而產生的。在有了 TableCache 之後，MySQL 每次需要擷取某個表檔案的控制代碼資訊的時候，首先會到 Table Cache中尋找是否存在空閑狀態的表檔案控制代碼。如果有，則取出直接使用，沒有的話就只能進行開啟檔案操作獲得檔案控制代碼資訊。在使用完之後，MySQL會將該檔案控制代碼資訊再放回 Table Cache池中，以供其他線程使用。注意，這裡設定的是可以緩衝的表檔案控制代碼資訊的數目，而不是記憶體空間的大小。
表定義資訊緩衝（Table definition Cache）：表定義資訊緩衝是從MySQL5.1.3 版本才開始引入的一個新的緩衝區，用來存放表定義資訊。當我們的 MySQL中使用了較多的表的時候，此緩衝無疑會提高對錶定義資訊的訪問效率。MySQL 提供了 table_definition_cache參數給我們設定可以緩衝的表的數量。在 MySQL5.1.25 之前的版本中，預設值為128，從 MySQL5.1.25版本開始，則將預設值調整為 256 了，最大設定值為524288。注意，這裡設定的是可以緩衝的表定義資訊的數目，而不是記憶體空間的大小。
二進位日誌緩衝區（Binlog Buffer）：二進位日誌緩衝區主要用來緩衝由於各種資料變更操做所產生的Binary Log 資訊。為了提高系統的效能，MySQL 並不是每次都是將二進位日誌直接寫入 Log File，而是先將資訊寫入Binlog Buffer 中，當滿足某些特定的條件（如 sync_binlog參數設定）之後再一次寫入 Log File 中。我們可以通過binlog_cache_size 來設定其可以使用的記憶體大小，同時通過 max_binlog_cache_size限制其最大大小（當單個事務過大的時候 MySQL 會申請更多的記憶體）。當所需記憶體大於 max_binlog_cache_size參數設定的時候，MySQL 會報錯：“Multi-statement transaction required more than‘max_binlog_cache_size’ bytes of storage”。
MyISAM索引緩衝（Key Buffer）：MyISAM 索引緩衝將 MyISAM 表的索引資訊緩衝在記憶體中，以提高其訪問效能。這個緩衝可以說是影響 MyISAM 儲存引擎效能的最重要因素之一了，通過 key_buffere_size 設定可以使用的最大記憶體空間。

InnoDB 日誌緩衝區（InnoDB Log Buffer）：這是 InnoDB儲存引擎的交易記錄所使用的緩衝區。類似於 Binlog Buffer，InnoDB 在寫交易記錄的時候，為了提高效能，也是先將資訊寫入Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit參數所設定的相應條件（或者日誌緩衝區寫滿）之後，才會將日誌寫到檔案（或者同步到磁碟）中。可以通過 innodb_log_buffer_size參數設定其可以使用的最大記憶體空間。
註：innodb_flush_log_trx_commit 參數對 InnoDB Log 的寫入效能有非常關鍵的影響。該參數可以設定為0，1，2，解釋如下：

• 0：log buffer中的資料將以每秒一次的頻率寫入到log file中，且同時會進行檔案系統到磁碟的同步操作，但是每個事務的commit並不會觸發任何log buffer 到log file的重新整理或者檔案系統到磁碟的重新整理操作；

• 1：在每次事務提交的時候將log buffer 中的資料都會寫入到log file，同時也會觸發檔案系統到磁碟的同步；

• 2：事務提交會觸發log buffer 到log file的重新整理，但並不會觸發磁碟檔案系統到磁碟的同步。此外，每秒會有一次檔案系統到磁碟同步操作。

此外，MySQL文檔中還提到，這幾種設定中的每秒同步一次的機制，可能並不會完全確保非常準確的每秒就一定會發生同步，還取決於進程調度的問題。實際上，InnoDB 能否真正滿足此參數所設定值代表的意義正常 Recovery 還是受到了不同 OS下檔案系統以及磁碟本身的限制，可能有些時候在並沒有真正完成磁碟同步的情況下也會告訴 mysqld 已經完成了磁碟同步。
InnoDB 資料和索引緩衝（InnoDB Buffer Pool）：InnoDB BufferPool 對 InnoDB 儲存引擎的作用類似於 Key Buffer Cache 對 MyISAM 儲存引擎的影響，主要的不同在於InnoDB Buffer Pool 不僅僅緩衝索引資料，還會緩衝表的資料，而且完全按照資料檔案中的資料快結構資訊來緩衝，這一點和Oracle SGA 中的 database buffer cache 非常類似。所以，InnoDB Buffer Pool 對 InnoDB儲存引擎的效能影響之大就可想而知了。可以通過 (Innodb_buffer_pool_read_requests -Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%計算得到 InnoDB Buffer Pool 的命中率。
InnoDB 字典資訊緩衝（InnoDB Additional Memory Pool）:InnoDB字典資訊緩衝主要用來存放 InnoDB 儲存引擎的字典資訊以及一些 internal 的共用資料結構資訊。所以其大小也與系統中所使用的InnoDB 儲存引擎表的數量有較大關係。不過，如果我們通過 innodb_additional_mem_pool_size參數所設定的記憶體大小不夠，InnoDB 會自動申請更多的記憶體，並在 MySQL 的 Error Log 中記錄警告資訊。
這裡所列舉的各種共用記憶體，是我個人認為對 MySQL 效能有較大影響的集中主要的共用記憶體。實際上，除了這些共用記憶體之外，MySQL 還存在很多其他的共用記憶體資訊，如當同時請求串連過多的時候用來存放串連請求資訊的back_log隊列等。
 

本文轉自網站http://imysqler.com