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 query
SET @@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多，那個汗啊，，，

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