MySQL伺服器級最佳化詳解

前面各段介紹了普通的 MySQL 使用者利用表建立和索引操作，以及利用查詢的編寫能夠進行的最佳化。不過，還有一些只能由 MySQL 管理員和系統管理員來完成的最佳化，這些管理員在 MySQL 伺服器或運行 MySQL 的機器上具有控制權。有的伺服器參數直接適用於查詢處理，可將它們開啟。而有的硬體設定問題直接影響查詢處理速度，應該對它們進行調整。　　磁碟問題

正如前面所述，磁碟尋道是一個效能的大瓶頸。當資料開始增長以致緩衝變得不可能時，這個問題變得越來越明顯。對大資料庫，在那你或多或少地要隨機存取資料，你可以依靠你將至少需要一次磁碟尋道來讀取並且幾次磁碟尋道寫入。為了使這個問題最小化，使用有低尋道時間的磁碟。

為了增加可用的磁碟軸的數量(並且從而減少尋道開銷)，符號聯結檔案到不同磁碟或分割磁碟是可能的。

1、使用符號串連

這意味著你將索引/資料檔案符號從正常的資料目錄連結到其他磁碟(那也可以被分割的)。這使得尋道和讀取時間更好(如果磁碟不用於其他事情)

2、分割

分割意味著你有許多磁碟並把第一塊放在第一個磁碟上，在第二塊放在第二個磁碟上，並且第 n塊在第(n mod number_of_disks)磁碟上，等等。這意味著，如果你的正常資料大小於分割大小(或完美地排列過)，你將得到較好一些的效能。注意，分割是否 很依賴於OS和分割大小。因此用不同的分割大小測試你的應用程式。見10.8 使用你自己的基準。注意對分割的速度差異很依賴於參數，取決於你如何分割參數和磁碟數量，你可以得出以數量級的不同。注意你必須選擇為隨機或順序存取優 化。

為了可靠，你可能想要使用襲擊RAID 0+1(分割+鏡像)，但是在這種情況下，你將需要2*N個磁碟機來儲存N個磁碟機的資料。如果你有錢，這可能是最好的選擇!然而你也可能必須投資一些卷管理軟體投資以高效地處理它。

一個好選擇是讓稍重要的資料(它能再生)上存在RAID 0磁碟上，而將確實重要的資料(像主機資訊和記錄檔)存在一個RAID 0+1或RAID N磁碟上。如果因為更新奇偶位你有許多寫入，RAID N可能是一個問題。

你也可以對資料庫使用的檔案系統設定參數。一個容易的改變是以noatime選項掛裝檔案系統。這是它跳過更新在inode中的最後訪問時間，而且這將避免一些磁碟尋道。

硬體問題

可利用硬體更有效地改善伺服器的效能：

1、在機器中安裝更多的記憶體。這樣能夠增加伺服器的快取和緩衝區的尺寸，使伺服器更經常地使用存放在記憶體中的資訊，降低從磁碟取資訊的要求。

2、如果有足夠的 RAM 使所有交換在記憶體檔案系統中完成，那麼應該重新設定系統，去掉所有磁碟交換設定。否則，即使有足以滿足交換的 RAM，某些系統仍然要與磁碟進行交換。

3、增加更快的磁碟以減少 I/O 等待時間。尋道時間是這裡決定效能的主要因素。逐字地移動磁頭是很慢的，一旦磁頭定位，從磁軌讀塊則較快。

在不同的物理裝置上設法重新分配磁碟活動。如果可能，應將您的兩個最繁忙的資料庫存放在不同的物理裝置上。請注意，使用同一物理裝置上的不同分區是不夠的。這樣沒有協助，因為它們仍將爭用相同的實體資源(磁碟頭)。移動資料庫的過程在第 10 章中介紹。

4、在將資料重新放到不同裝置之前，應該保證瞭解該系統的裝載特性。如果在特定的物理裝置上已經有了某些特定的主要活動，將資料庫放到該處實際上可能會使效能更壞。例如，不要把資料庫移到處理大量Web 通訊的Web 服務器裝置上。

5、在設定 MySQL 時，應該配置其使用靜態庫而不是共用庫。使用共用庫的動態二進位系統可節省磁碟空間，但靜態二進位系統更快(然而，如果希望裝入使用者自訂的函數，則不能使用靜態二進位系統，因為 UDF 機制依賴於動態串連)。

伺服器參數的選擇

伺服器有幾個能夠改變從而影響其操作的參數(或稱變數)。系統變數的當前值可以通過執行mysqladmin varibles命令來檢查，其中幾個參數主要與查詢有關，有必要在此提一下：

delayed_queue_size

此參數在執行其他 INSERT DELAYED 語句的客戶機阻塞以前，確定來自 INSERT DELAYED 語句的放入隊列的行的數目。增加這個參數的值使伺服器能從這種請求中接收更多的行，因而客戶機可以繼續執行而不阻塞。

key_buffer_size

此參數為用來存放索引塊的緩衝區尺寸。如果記憶體多，增加這個值能節省索引建立和修改的時間。較大的值使 MySQL 能在記憶體中儲存更多的索引塊，這樣增加了在記憶體中找到索引值而不用讀磁碟塊的可能性。

在 MySQL 3.23 版及以後的版本中，如果增加了鍵緩衝區的尺寸，可能還希望用 --init-file 選項啟動伺服器。這樣能夠指定一個伺服器啟動時執行的 SQL 陳述式檔案。如果有想要存放在記憶體中的唯讀表，可將它們拷貝到索引尋找非常快的 HEAP 表。

back_log

引入客戶機串連請求的數量，這些請求在從當前客戶機中處理時排隊。如果你有一個很忙的網站，可以增加改變數的值。

編譯和連結怎樣影響MySQL的速度

大多數下列測試在Linux上並用MySQL基準進行的，但是它們應該對其他動作系統和工作負載給出一些指示。

當你用-static連結時，你得到最快的可執行檔。使用Unix通訊端而非TCP/IP串連一個資料庫也可給出好一些的效能。

在Linux上，當用pgcc和-O6編譯時間，你將得到最快的代碼。為了用這些選項編譯 “sql_yacc.cc”，你需要大約200M記憶體，因為gcc/pgcc需要很多記憶體使所有函數嵌入(inline)。在配置MySQL時，你也應該 設定CXX=gcc以避免包括libstdc++庫(它不需要)。

只通過使用一個較好的編譯器或較好的編譯器選項，在應用中你能得到一個10-30%的加速。如果你自己編譯SQL伺服器，這特別重要!

在Intel上，你應該例如使用pgcc或Cygnus CodeFusion編譯器得到最大速度。我們已經測試了新的 Fujitsu編譯器，但是它是還沒足夠不出錯來最佳化編譯MySQL。

這裡是我們做過的一些測量表：

·如果你以-O6使用pgcc並且編譯任何東西，mysqld伺服器是比用gcc快11%(用字串99的版本)。

·如果你動態地連結(沒有-static)，結果慢了13%。注意你仍能使用一個動態串連的MySQL庫。只有伺服器對效能是關鍵的。

·如果你使用TCP/IP而非Unix通訊端，結果慢7.5%。

·在一個Sun SPARCstation 10上，gcc2.7.3是比Sun Pro C++ 4.2快13%。

·在Solaris 2.5.1上，在單個處理器上MIT-pthreads比帶原生線程的Solaris慢8-12%。以更多的負載/cpus，差別應該變得更大。

由TcX提供的MySQL-Linux的分發用pgcc編譯並靜態連結。

總結

本節簡單介紹了如何在伺服器級最佳化資料庫的效能，以及提高資料庫效能涉及到的硬體問題。選擇一個盡量快的系統，使用RAID磁碟陣列是非常容易想到的方法。

對於資料庫精靈，既可以在編譯時間就提供合適的參數，也可以在選項檔案中提供需要最佳化的參數。

思考題

1、使用ANALYSE過程分析語句SELECT * FROM pet，判斷是否有必要改變列的類型。

2、現在讓你需要最佳化表pet，可以用那些手段做到?