調整組態、優化SQL指令　動手找回MySQL效能

系統參數可分為總體（Global）參數及會話（Session）參數，此兩種系統參數最大的區別為設定總體參數會影響整個資料庫的運作，而設定會話參數僅會影響當前資料庫連接的運作。

使用者在登入MySQL伺服器後，如圖2所示，可利用下列指令來取得所有總體參數資訊，\G參數可顯示易讀的格式：

show global status \G

▲圖2 查詢所有總體參數的資訊。

MySQL伺服器提供了相當多樣化的系統參數，這裡只探討可能會影響資料庫運作效能的系統參數。

允許來源端的最大連線數

在資料庫的服務中，首先要探討的是根據主機的硬體配備，應該要限定在相同時間的情況下，資料庫能同時服務多少個連線，因此可設定以下的系統變數來指定最大連線數：

max_connections

設定資料庫在同時間服務的情況下，最多能同時服務幾個來源端。可利用「set global max_connections= [連線數]」來設定同時服務的連線數。

max_used_connections

設定資料庫在同時間服務的情況下，最多能同時服務連線到資料庫的個別用戶連線數。設定為0，表示不限制連線數。

設定完成後，可利用如圖3所示的指令來查看目前所設定的最大連線數資訊。

▲圖3 查看目前所設定的最大連線數。

關於傳輸參數

由於MySQL伺服器所允許的來源連線數是有限的，並且通常都希望當來源端結束查詢作業後即結束連線，以避免占用MySQL伺服器過多的系統資源，此時就需要設定適當的逾時（Timeout）機制來結束相關連線。

當發現連線數過多時，即可適當地調整此參數，儘快將無用的連線關閉。MySQL伺服器提供下列的系統參數來設定逾時的資訊：

wait_timeout

設定關閉非交互連線的逾時時間（單位為秒），預設為8小時。

interactive_timeout

設定關閉交互連線的逾時時間（單位為秒），預設為8小時。

一般而言，交互連線指的是使用MySQL工具程式登入伺服器進行連線的動作。而非交互連線，通常指的是利用資料庫程式進行連線的動作。可利用如圖4所示的指令來取得相關逾時參數的資訊。

▲圖4 顯示相關逾時參數。

MyISAM的效能調整

管理者通常會利用索引的方式來增進資料庫的處理效能，因此可利用設定適當的索引緩衝區（Buffer）空間來增進索引的效能。

MySQL伺服器提供key_buffer_size參數來設定合理的緩衝區空間。圖5所示為顯示目前所設定的緩衝區空間，單位為Bytes。

▲圖5 顯示目前所設定的緩衝區空間。

怎樣才能算是合理的緩衝區空間，基本上可利用key_read_requests和key_reads的比值來確認是否已設定合理的緩衝區空間。其中key_read_requests表示索引從快取（Cache）取得的次數（即快取命中率），而key_reads則代表索引從硬碟中取得的次數。

在key_buffer_size參數設定合理的情況下，key_reads參數值會遠小於key_read_requests參數值，亦即key_read_requests/key_reads的比率值越小越好。如圖6所示，為查看目前系統上key_read_requests和key_reads的參數值。

▲圖6 查看目前系統內key_read_requests與key_reads的參數值。

最佳化表格

當系統上線一段時間後，資料庫表格（Table）將歷經了無數的新增、刪除等動作，其實在表格中也會遺留數量龐大的空洞資料，而這些無用的空洞資料會影響資料庫存取的效能。

以MyISAM表格為例，管理者可以試著刪除表格中的紀錄，而後再查看該表格的檔案（副檔名為myd），將會發現該檔案的大小並不會有任何的改變，這就表示所刪除的紀錄依然會以無用的空洞資料形式儲存在該檔案中。

可透過定時執行「optimize table [表格名稱]」的方式來刪除表格中無用的空洞資料（對MyISAM、InnoDB等類型有效），藉由縮減表格的容量來提升存取的效率，如圖7所示。

▲圖7 刪除表格中無用的空洞資料。

簡單說明過幾個可能影響MySQL伺服器效能的參數後，接著繼續探討如何優化SQL指令來提升MySQL伺服器的運作效能。

分析程式實際運作效能

相信很多程式設計師對於下列的情景應該都心有同感，在資料庫應用系統上線的初期，相關的系統運作均十分順暢，但在運作一段時間後，資料累積到一定的數量時，就會發現系統運作開始變得很不順暢，即使更換成高檔的硬體設備，還是沒有多大的改善。