淺析MySQL記憶體的使用說明（全域緩衝+線程緩衝）

首先我們來看一個公式，MySQL中記憶體分為全域記憶體和線程記憶體兩大部分（其實並不全部，只是影響比較大的 部分）：

複製代碼 代碼如下:per_thread_buffers=(read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+join_buffer_size+binlog_cache_size
+tmp_table_size)*max_connections
global_buffers=
innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+key_buffer_size+query_cache_size
total_memory=global_buffers+per_thread_buffers

全域緩衝：
key_buffer_size：決定索引處理的速度，尤其是索引讀的速度。預設值是16M，通過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設定是否合理。比例key_reads / key_read_requests應該儘可能的低，至少是1:100，1:1000更好（上述狀態值可以使用'key_read%'獲得用來顯示狀態資料）。key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁碟表是MyISAM表，也要使用該值。可以使用檢查狀態值'created_tmp_disk_tables'得知詳情。

innodb_buffer_pool_size：InnoDB使用該參數指定大小的記憶體來緩衝資料和索引，這個是Innodb引擎中影響效能最大的參數。

innodb_additional_mem_pool_size：指定InnoDB用來儲存資料字典和其他內部資料結構的記憶體池大小。預設值是8M。通常不用太大，只要夠用就行，應該與表結構的複雜度有關係。如果不夠用，MySQL會在錯誤記錄檔中寫入一條警告資訊。

innodb_log_buffer_size：指定InnoDB用來儲存日誌資料的緩衝大小，如果您的表操作中包含大量並發事務（或大規模事務），並且在事務提交前要求記錄記錄檔，請盡量調高此項值，以提高日誌效率。

query_cache_size：是MySQL的查詢緩衝大小。（從4.0.1開始，MySQL提供了查詢緩衝機制）使用查詢緩衝，MySQL將SELECT語句和查詢結果存放在緩衝區中，今後對於同樣的SELECT語句（區分大小寫），將直接從緩衝區中讀取結果。根據MySQL使用者手冊，使用查詢緩衝最多可以達到238%的效率。通過檢查狀態值'Qcache_%'，可以知道query_cache_size設定是否合理：如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩衝使用非常頻繁，此時需要增加緩衝大小；如果Qcache_hits的值不大，則表明你的查詢重複率很低，這種情況下使用查詢緩衝反而會影響效率，那麼可以考慮不用查詢緩衝。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩衝。

線程緩衝
每個串連到MySQL伺服器的線程都需要有自己的緩衝。大概需要立刻分配256K，甚至線上程空閑時，它們使用預設的線程堆棧，網路緩衝等。事務開始之後，則需要增加更多的空間。運行較小的查詢可能僅給指定的線程增加少量的記憶體消耗，然而如果對資料表做複雜的操作例如掃描、排序或者需要暫存資料表，則需分配大約read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的記憶體空間。不過它們只是在需要的時候才分配，並且在那些操作做完之後就釋放了。有的是立刻分配成單獨的組塊。tmp_table_size 可能高達MySQL所能分配給這個操作的最大記憶體空間了。

read_buffer_size：是MySQL讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySQL會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一緩衝區的大小。如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能。

sort_buffer_size：是MySQL執行排序使用的緩衝大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能，可以嘗試增加sort_buffer_size變數的大小。

read_rnd_buffer_size：是MySQL的隨機讀緩衝區大小。當按任意順序讀取行時(例如，按照排序次序)，將分配一個隨機讀緩衝區。進行排序查詢時，MySQL會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySQL會為每個客戶串連發放該緩衝空間，所以應盡量適當設定該值，以避免記憶體開銷過大。

tmp_table_size：是MySQL的暫存資料表緩衝大小。所有聯合在一個DML指令內完成，並且大多數聯合甚至可以不用暫存資料表即可以完成。大多數暫存資料表是基於記憶體的(HEAP)表。具有大的記錄長度的暫存資料表 (所有列的長度的和)或包含BLOB列的表格儲存體在硬碟上。如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將記憶體中的heap表改為基於硬碟的MyISAM表。還可以通過設定tmp_table_size選項來增加暫存資料表的大小。也就是說，如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯結查詢速度的效果。

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

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

binlog_cache_size：在事務過程中容納二進位日誌SQL 陳述式的緩衝大小。二進位日誌緩衝是伺服器支援事務儲存引擎並且伺服器啟用了二進位日誌(—log-bin 選項)的前提下為每個用戶端分配的記憶體，注意，是每個Client 都可以分配設定大小的binlog cache 空間。如果系統中經常會出現多語句事務的話，可以嘗試增加該值的大小，以獲得更好的效能。當然，我們可以通過MySQL 的以下兩個狀態變數來判斷當前的binlog_cache_size 的狀況：Binlog_cache_use 和Binlog_cache_disk_use。“max_binlog_cache_size”：和"binlog_cache_size"相對應，但是所代表的是binlog 能夠使用的最大cache 記憶體大小。當我們執行多語句事務的時候，max_binlog_cache_size 如果不夠大的話，系統可能會報出“ Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”的錯誤。
其中需要注意的是：table_cache表示的是所有線程開啟的表的數目，和記憶體無關。