MySQL 設定變數的副作用

動態設定變數可能導致意外的副作用，例如從緩衝中重新整理髒塊。務必小心那些可以線上更改的設定，因為它們可能導致資料庫做大量的工作。

有時可以通過名稱推斷一個變數的作用。例如，max_heap_table_size的作用就行聽起來那樣：它指定隱式記憶體暫存資料表最大允許的大小。然而，命名的約定並不完全一樣，所以不能總是通過名稱來猜測一個變數有什麼效果。

讓我們來看一些常用的變數和動態修改它們的效果：

key_buffer_size

  設定這個變數可以一次性為鍵緩衝區(也叫金鑰快取)分配所有指定的空間。然而，作業系統不會真的立刻分配記憶體，而是到使用時才真正分配。例如設定鍵緩衝的大小為1GB，並不意味著伺服器立刻分配1GB的記憶體。

  MySQL允許建立多個金鑰快取。如果把非預設金鑰快取的這個變數設定為0，MySQL將丟棄存在該金鑰快取中的索引，轉而使用預設金鑰快取，並且當不再有任何引用時會刪除該金鑰快取。為一個不存在的金鑰快取設定這個變數，將會建立新的金鑰快取。對一個已經存在的金鑰快取設定非零值，會導致重新整理該金鑰快取的內容。這會阻塞所有嘗試訪問該金鑰快取的操作，知道重新整理操作完成。

table_cache_size

  設定這個變數不會立即生效---會延遲到下次有線程開啟表才有效果。當有線程開啟表時，MySQL會檢查這個變數的值。如果大於緩衝中的表的數量，線程可以把最新開啟的表放入緩衝；如果值比緩衝中的表小，MySQL將從緩衝中刪除不常使用的表。

thread_cache_size

  設定這個變數不會立即生效---將在下次串連被關閉時產生效果。當有串連被關閉時，MySQL檢查緩衝中是否還有空間來緩衝線程。如果有空間，則緩衝該線程以被下次串連重用；如果沒有空間，它將銷毀該線程而不再緩衝。在這個情境中，緩衝中的線程數，以及線程緩衝使用的記憶體，並不會立刻減少；只有在新的串連刪除緩衝中的一個線程並使用後才會減少。MySQL只在關閉串連時才在緩衝中增加線程，只在建立新串連時才從緩衝中刪除線程。）

query_cache_size

  MySQL在啟動的時候，一次性分配並且初始化這塊記憶體。如果修改這個變數(即使設定為與當前一樣的值)，MySQL會立刻刪除所有緩衝的查詢，重新分配這片緩衝到指定大小，並且重新初始化記憶體。這可能花費較長的時間，在完成初始化之前伺服器都無法提供服務，因為MySQL是逐個清理緩衝的查詢，不是一次性全部刪掉。

read_buffer_size

  MySQL只會在有查詢需要時才會為該緩衝分配記憶體，並且會一次性分配該參數指定大小的全部記憶體。

read_rnd_buffer_size

  MySQL只會在有查詢需要使用時才會為該緩衝分配記憶體，並且只會分配需要的記憶體大小而不是全部指定的大小。

sort_buffer_size

  MySQL只會在有查詢需要做排序操作才會為該緩衝分配記憶體。然後，一旦需要排序，MySQL就會立刻分配該參數指定的大小的全部記憶體，而不管該排序是否需要這麼大的記憶體。

這裡不是一個完整的參數列表，這裡的目的只是簡單的告訴大家，當修改一些常見的變數時，會有哪些期望的行為發生。

對於串連層級的設定，不要輕易地在全域層級增加它們的值，除非確認這樣做是對的。有一些緩衝會一次性分配指定大小的全部記憶體，而不管實際上是否需要這麼大，所以一個很大的全域設定可能導致浪費大量的記憶體。更好的方法是，當查詢需要時在串連層級單獨調大這些值。

最常見的例子是sort_buffer_size，該參數控制排序操作的緩衝大小，應該在設定檔裡把它配置的小一些，然後在某些查詢需要排序時，再在串連中把它調大。在分配記憶體後，MySQL會執行一些初始化的工作。

另外，即使是非常小的排序操作，排序緩衝也會分配全部的大小的記憶體，所以如果把參數設定得超過平均排序需求太多，將會浪費很多記憶體，增加額外的記憶體配置開銷。許多讀者認為記憶體配置是一個很簡單的操作，聽到記憶體配置的代價可能會很吃驚。不需要深入很多技術細節就可以將清楚為什麼記憶體配置也是昂貴的操作，記憶體配置包括了地址空間的分配，這相對來說是比較昂貴的。特別是在Linux上，記憶體配置根據大小使用多種開銷不同的策略。

總的來說，設定很大的排序緩衝代價可能非常高，所以除非確定必須要這麼大，否則不要增加排序緩衝的大小。

如果查詢必須使用一個更大的排序緩衝才能比較好地執行，可以在查詢執行前增加sort_buffer_size的值，執行完成後恢複DEFAULT。

eg：

SET @@session.sort_buffer_size := <value>;#Execute the querySET @@session.sort_buffer_size := DEFAULT;

可以將類似的代碼封在函數中以方便使用。其它可以設定的單個串連層級的變數有read_buffer_size，read_rnd_buffer_size，tmp_table_size，以及myisam_sort_buffer_size。

特別說明：本文章是來自<High Performance MySQL>的一章節。

個人感悟：理解可動態更改的變數產生的影響是蠻重要的，一不小心，可能導致負載飛一般的暴漲，CPU刷刷的飆升，甚至宕機。

糗事：本人曾線上上資料庫動態修改query_cache_szie的值，本來server的負載都40-50的樣子啦，當時也不沒考慮那麼多，就直接改了，負載瞬間飆升到了200多，那個汗啊，，，

好的東西是拿出來分享的，那樣它就會更美！！！

本文出自 “Focus on the database” 部落格，請務必保留此出處http://lgdvsehome.blog.51cto.com/3360656/1246000