MySQL基礎篇--全局緩存和線程緩存

MySQL全局緩存

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