深入理解MySQL進階漂流記（六）

效能最佳化

索引

#索引指向資料庫中具體資料所在的一個位置，同事在列上建立索引可以排列該列上的信息。當伺服器需要存取該資訊進行 查詢時，就會知道在什麼地方進行查詢，因為索引指向有關位置。

如果一個欄位涉及查詢，分組，排序，索引將可以達到提高效能的效果。
帶很多重複值的索引不會產生很好的結果。
可以使用表來聯合多個非唯一的索引，以便改善效能。
索引越多，效能不一定越好。添加一個索引，並不一定能改善效能。

查詢高速緩存

在執行select查詢時，MySQL4.x會記錄下查詢並傳回結果。這是透過select每次進行查詢時，在一個特殊的高速緩存中保存 結果集實現的。然後，當再次請求伺服器做相同的查詢時，MySQL將會從快取中檢索結果，而不是再次執行該查詢。 預設是啟動這個效能。

注意，一旦表有變化，使用這個表的快取查詢就變成無效，並且將從高速緩存中刪除。這樣防止查詢從舊表上傳回 不準確的資料。經常有變化的表，將不會從高速緩存中得到利益。這種情況，可以考慮不使用高速緩存， 可以新增一個選項sql_no_cache來實作。

查詢分析

在select查詢開始處加上一個explain關鍵字，將告訴MySQL回傳一個圖表，說明這個查詢如果進行處理， 在這個圖表裡涉及了查詢將存取哪個表格的資訊以及查詢希望傳回的行數的資訊。這個資訊可以用來查看哪些表格可以加入索引， 以便加快執行速度，分析瓶頸的位置。

透過查詢的顯示結果，可以知道什麼位置可以新增索引，進行快速的修正。

優化多表查詢

子查詢是一個嵌套在另一個select語句中的select語句。子查詢常用來把一個複雜的查詢拆分為一些列的邏輯步驟， 或使用其他查詢結果回答一個查詢。其結果是不需要執行兩個或更多的單獨查詢，就可以執行一項包含一項或多項子查詢 的簡單查詢。

MySQL可以比子查詢更好的最佳化連接，所以如果發現在自己的MySQL伺服器上的負載平均值達到了無法接受的高水平，就應該檢驗應用程式程式碼，並試著重寫作為連接和連接序列的子查詢。

可以透過有效的使用MySQL的集合效能和修改程式來把低效的子查詢轉變為更有效的連結。
如果希望避免中間使用巢狀查詢，也可以使用基於會話的伺服器變數。

使用臨時表

MySQL也允許使用create temporary table指令建立臨時表。這種表之所以這麼稱呼，是因為它只針對單一的MySQL會話 過程而存在的，當使用這些表的客戶機關閉了與MySQL伺服器的連接時，它將自動刪除。

因為臨時表保存在記憶體中，所以要比基於磁碟的表明顯的快。結果可以有效的作為中間儲存區域，以便提高查詢實施的速度， 幫助把複雜的的拆分為更簡單的部件，或者作為子查詢和連接支援的代替。

最佳化表格設計

為了讓查詢能夠更精煉，需要考慮表格設計方面的一些因素。首先，如果經常查詢的表格會發生很多變化，改進效能的方式就是使用 定長字段，不適用變長字段。雖然使用定長字段將浪費更多的磁碟空間，但從查詢角度來看，MySQL處理定長字段比變長字段更快。

改善效能的另一項技術是使用optimize table指令處理經常需要修改的表格。經常修改表會導致磁碟碎片，以致花費額外的時間 去讀取沒有用的空間區塊，以便得到希望的資料。

在考慮改善效能時，也要檢查是否需要針對已經建立的所有表格。額外的表意味著性能降低。沒有必要合併的表，則應試著匹配連接的 列。

調整伺服器設定

如果希望伺服器更有效的運行，最佳解決方案是增加記憶體空間和使用更大更快的磁碟。但是更多時候，條件是不允許是的。 此時，我們需要一些改進伺服器的常規技術。

調整伺服器變量，key_buffer_size變數控制MySQL索引緩衝可以使用記憶體的數量。數值越高，索引可以使用的記憶體越多，效能越好。 一般情況，這個值維持在可使用記憶體總量的25%到30%。 table_cache變數控製表快取可以使用記憶體的數量，以及在同一時間 內MySQL可以處理表的開啟總量。對於有很多資料庫和表格的非常忙碌的伺服器來說，應該增加這個值,使用set修改。

一旦修改了一個全域伺服器變量，關閉伺服器之前，這些變數一直存在，但是重新啟動伺服器之後，變數恢復預設狀態。 所以最好是進行永久性修改。

以上是深入理解MySQL進階漂流記（六）的詳細內容。更多資訊請關注PHP中文網其他相關文章！