MySQL查詢最佳化講座之管理員的最佳化措施

MySQL使用者能夠執行的。可以控制MySQL伺服器或電腦的系統管理員能夠執行額外的最佳化措施。例如，有些伺服器參數附屬於查詢處理過程，並且是可以調整的，而且某些硬體設定因素對查詢處理速度有直接的影響。在很多情況下，這些最佳化措施提高了整個伺服器的效能，因此可以讓所有的MySQL使用者都受益。

　　一般來說，當你執行管理員最佳化的時候，應該緊記以下規則：

　　· 訪問記憶體中的資料快於訪問磁碟上的資料。

　　· 盡量把資料儲存在記憶體中可以減少磁碟操作。

　　· 保留索引中的資訊比保留資料記錄的內容更重要。

　　我們在後面將討論如何應用這些規則。

　　增加伺服器緩衝的大小。伺服器擁有很多參數（系統變數），你可以改變這些參數來影響伺服器的操作。其中的幾個參數直接地影響查詢處理的速度。你可以改變的最重要的參數是資料表緩衝的大小和儲存引擎用於緩衝索引操作資訊的緩衝大小。如果你擁有可用的記憶體，就把它分配給伺服器的緩衝，以允許資訊儲存在記憶體中並減少磁碟操作。這會有很好的效果，因為訪問記憶體中的資訊比從磁碟讀取資訊的速度快得多。

　　· 當伺服器開啟表檔案的時候，它試圖保持這些檔案的開啟狀態，以減少開啟檔案操作的數量。為了實現這樣的功能，它在表緩衝中維護開啟檔案的資訊。table_cache系統變數控制著這個緩衝的大小。如果伺服器訪問了大量的表，表緩衝就會被填滿，並且伺服器會關閉那些有一段時間沒有使用的表，為開啟新表留出空間。你可以通過檢查Opened_tables狀態指示器來訪問表緩衝的效果：

SHOW STATUS LIKE ’Opened_tables’;

　　Opened_tables顯示了某個資料表必須開啟的次數（因為它還沒有開啟）。這個值也顯示為mysqladmin狀態命令的輸出資訊中的Opens值。如果這個數字是穩定的或緩慢增長，那麼它的設定可能是正確的。如果這個數字增長得很快，就意味著這個緩衝太小了，必須經常關閉資料表來為開啟其它的資料表留出空間。如果你擁有檔案描述資訊，增加表緩衝大小將減少資料表開啟操作的數量。

　　· MyISAM儲存引擎使用鍵緩衝來保持與索引相關的操作的索引資訊塊。它的大小是由key_buffer_size系統變數控制的。這個值越大，MySQL就一次性在記憶體中保持更多的索引資訊塊，可以增加在記憶體中（而不用從磁碟上讀取新的資訊塊）找到索引值的可能性。金鑰快取的預設大小是8MB。如果你擁有很多的記憶體，這是一個很保守的值，你可以直接增加它的大小，並且會看到基於索引的檢索、索引的建立和修改操作的效能有很大改善。

　　在MySQL 4.1以上版本中，你可以為MyISAM資料表建立附加的金鑰快取，並指定某些表使用它們。這樣可以協助提高這些資料表上的查詢處理速度。

　　· InnoDB和BDB引擎擁有自己的用於緩衝資料和索引值的緩衝。它們的大小是由innodb_buffer_pool_size和bdb_cache_size變數控制的。InnoDB引擎還維護了一個日誌緩衝。innodb_log_buffer_size變數可以控制它的大小。

　　· 另一個專用的緩衝是查詢快取，我們在"使用查詢快取"部分中解釋。

　　當你改變這些參數值的時候，應該遵循下面一些原則：

　　· 每次只改變一個參數。如果你一次改變多個相互獨立的變數，那麼就很難評估每種改變的效果了。

　　· 逐漸地增加系統變數值。根據理論，數量越多，效能越好，但是如果你使某個變數變得太大了，有可能造成系統資源匱乏，導致逆向效果，降低速度。

　　· 不要在運行業務MySQL資料庫的伺服器上做調整參數的實驗，最好建立一個獨立的測試伺服器。

　　· 為了大致瞭解哪種參數變數可能適合自己的系統，你可以查看MySQL發布文檔中包含的my-small.cnf、my-medium.cnf、my-large.cnf和my-huge.cnf選項檔案（在Unix系統上，你可以在源發布檔案的支援檔案目錄和二進位發布檔案的共用目錄總找到這些檔案。在Windows上，它們位於基本的安裝目錄中，其副檔名可能是.ini）。這些檔案可能讓你知道最好改變伺服器上的那些參數以適應不同的使用層次，並且為這些參數提供了一些典型值。

　　用於提高伺服器的操作效能的其它一些策略還包括：

　　禁止不需要的儲存引擎。伺服器不會為禁止的引擎分配任何記憶體，因此我們可以利用這一點。如果從源檔案建立MySQL，那麼在配置的時候，大多數儲存引擎就可以被排除在伺服器之外。對於那些包含在伺服器中的引擎來說，使用適當的啟動選項可以在運行時禁止其中的大多數。
保持授權表許可的簡單性。儘管伺服器在記憶體中緩衝了授權表內容，但是如果你在tables_priv或columns_priv表中有一些資料行的話，伺服器就必須為每個查詢語句檢查表層次和列層次的許可權。如果這些表是空的，那麼伺服器就能最佳化自己的許可權檢查過程，略過這些層次。

　　如果你從源檔案建立MySQL，那麼就把它配置為使用靜態類庫，而不要使用共用類庫。使用共用類庫的動態二進位檔案節約磁碟空間，然而靜態二進位檔案速度更快。但是，如果你使用了使用者自訂函數（UDF）機制，那麼有些系統要求使用動態連結。在這類系統上，靜態二進位檔案不能工作。

　　使用MyISAM金鑰快取

　　當MySQL執行某個利用了MyISAM資料表索引的語句的時候，它會使用金鑰快取來保持索引值。這種緩衝減少了磁碟I/O：如果在緩衝中找到了某個資料表需要的索引值，就不需要再次從磁碟中讀取。不幸的是，這種金鑰快取是有限的，並且在預設情況下，它是所有的MyISAM資料表共用使用的。如果在金鑰快取中沒有找到索引值並且金鑰快取是滿的，爭用將會導致：必須丟棄緩衝中的某些值，為新值留出空間。如果下次需要那些已經被丟棄的值，就必須再次從磁碟上讀取。

　　如果你很倚重MyISAM資料表，那麼把它的鍵儲存在記憶體中效果會很好，但是緩衝中的爭用卻會導致相反的效果。從同一張表或不同的表讀取資料都可能引起爭用。你可以通過把金鑰快取設定成足以儲存某個特定資料表的全部索引，從而避免同一張資料表的爭用，但是其它資料表的鍵仍然需要爭用緩衝空間。

　　MySQL 4.1以上版本為這個問題提供了一種解決方案：它支援我們建立多個金鑰快取，並允許我們把某張資料表的索引指定並且預先裝入某個緩衝。如果你的資料表使用得很頻繁，並且你有足夠的記憶體，能夠把它的索引載入緩衝中，那麼這種操作就是有用的。這種能力允許你同時避免同一張表和不同的表的爭用：建立一個足夠大的緩衝，讓它儲存資料表的全部索引，並且指定該緩衝專門用於那張資料表。在鍵被載入緩衝之後，不在需要磁碟I/O操作。同時，索引值永遠不會被丟棄，對資料表的鍵的查看操作可以在記憶體中完成。

　　下面的例子顯示了如何為sampdb資料庫的member資料表建立一個金鑰快取，該緩衝的名稱是member_cache，大小為1MB。執行這些指令的時候，你必須有超級（SUPER）許可權。

　　1.建立一個足夠容納資料表索引的獨立的緩衝：

mysql> SET GLOBAL member_cache.key_buffer_size = 1024*1024;

　　2.給資料表指定金鑰快取：

mysql> CACHE INDEX member IN member_cache;
+---------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------------------+----------+----------+
| sampdb.member | assign_to_keycache | status | OK |
+---------------+--------------------+----------+----------+

　　3.把資料表索引預先讀入它的金鑰快取中：

mysql> LOAD INDEX INTO CACHE member;
+---------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------------+----------+----------+
| sampdb.member | preload_keys | status | OK |+---------------+--------------+----------+----------+

　　如果你希望把其它的資料表載入同一個緩衝中，或者為其它的資料表建立金鑰快取，上面的操作就足夠了。

 

 

　　使用查詢快取

　　MySQL伺服器可以使用查詢快取來提高那些重複執行的SELECT語句的處理速度。它對效能的提高通常都是驚人的。查詢快取的工作方式如下所示：

　　· 第一次執行某條SELECT語句的時候，伺服器記住該查詢的常值內容和它返回的結果。

　　· 伺服器下一次碰到這個語句的時候，它不會再次執行該語句。作為代替，它直接從查詢快取中的得到結果並把結果返回給用戶端。

　　· 查詢快取是基於伺服器所接收到的查詢字串的常值內容的。如果某些查詢的文本完全相同，那些它就認為這些查詢是相同的。如果某些查詢的字元不同，或者來自那些使用了不同的字元集或通訊協議的用戶端，那麼它會認為這些查詢是不同的。同樣，如果某些查詢採用其它的功能相當、但是實際上沒有指向相同的資料表（例如引用了不同的資料庫中的同名資料表），那麼它們也是不同的。

　　· 當資料表被更新了之後，涉及到該資料表的任何緩衝查詢都變成無效的，並且會被丟棄。這可以防止伺服器返回到期的結果。

　　在預設情況下，MySQL對查詢快取的支援是內建的。如果你不希望使用這種緩衝，並且想避免它所導致的效能開銷，可以使用--without-query-cache選項來回合組態指令碼建立伺服器。

　　如果需要檢測某個伺服器是否支援查詢快取，可以檢查它的have_query_cache系統變數：

mysql> SHOW VARIABLES LIKE ’have_query_cache’;
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+

　　對於那些支援查詢快取的伺服器來說，緩衝的操作是基於三個系統變數值的：

　　· query_cache_type決定查詢快取的操作模式。下表顯示了可以使用的模式值：

模式	含義
0	不要緩衝查詢結果或檢索緩衝的結果。
1	緩衝查詢，除非它們以SELECT SQL_NO_CACHE開頭。
2	根據需要只緩衝那些以SELECT SQL_CACHE開頭的查詢。

　　· query_cache_size決定分配給緩衝的記憶體數量，單位是位元組。

　　· query_cache_limit設定被緩衝的最大結果集大小；比這個值大的查詢結果不會被緩衝。

　　例如，為了啟用查詢快取並為它分配16MB記憶體，在設定檔中使用下面的設定：

[mysqld]
query_cache_type=1
query_cache_size=16M

　　即使query_cache_type的值設定為零，query_cache_size指定記憶體數量也會被分配。為了避免浪費記憶體，只有在希望啟用緩衝的時候才把大小設定成大於零。同時，即使query_cache_type不為零，查詢快取的大小設定為零也會禁用緩衝。

　　使用了查詢快取的獨立用戶端會在伺服器的預設緩衝模式狀態下操作。用戶端可以使用下面的語句改變自己的查詢的預設緩衝模式：

SET query_cache_type = val;

　　其中的val可以是0、1或2，它的意義與設定伺服器啟動時的query_cache_type變數的意義是相同的。在SET語句中，OFF、ON和DEMAND這些符號值與0、1和2對應。

　　用戶端還可以通過在SELECT關鍵字後天添加調節符來控制個別查詢的快取作業。如果緩衝模式是ON或DEMAND，那麼SELECT SQL_CACHE語句會讓查詢結果被緩衝。SELECT SQL_NO_CACHE語句會使查詢結果不被緩衝。

　　如果某些查詢從經常改變的資料表中檢索資訊，那麼抑止對這些查詢的快取作業是有用的。在這種情況下，緩衝未必有多大用處。假設你把Web伺服器請求的日誌儲存在MySQL資料表中，同時周期性地運行該資料表上的一組統計查詢。對於很繁忙的Web伺服器來說，會頻繁地出現新行插入該資料表的操作，因此該資料表的任何緩衝了的查詢結果很快就變成無效的了。其含義是，儘管你周期性地提交統計查詢，但是查詢快取可能對這些查詢沒有什麼價值。在這種情況下，最好使用SQL_NO_CACHE調節符告訴伺服器不要緩衝這些查詢的結果。

　　硬體問題

　　本文前面的部分中討論的協助你提高伺服器效能的技術是沒有考慮硬體設定的。你當然可以通過使用更好的硬體來讓伺服器運行地更快。但是並非所有的與硬體相關的改變都有相同的價值。當我們評估哪些硬體提高了效能的時候，最重要的原則與調整伺服器參數的原則是相同的：儘可能地把最多的資訊放在最快的儲存中，並讓這些資訊儘可能地保持在該儲存中。

　　你可以改變幾種硬體設定來提升伺服器的效能：

　　在電腦上安裝更多的記憶體。這可以讓你把伺服器的緩衝和緩衝區大小值配置成更大的，從而使資料儲存在記憶體中的時間更長，從磁碟上讀取資訊的需要更少。

　　重新設定系統，如果你擁有足夠的記憶體，能夠在記憶體檔案系統中執行全部的交換操作，那麼就刪除所有的磁碟交換裝置。否則，即使你擁有足夠的用於交換操作的RAM，某些系統仍然會跟磁碟進行交換操作。

　　增加更快的磁碟以改善I/O等待時間。在這種情況下，尋道時間是有代表性的主要的效能決定因素。橫向移動磁頭的速度比較慢，在磁頭定位以後，從磁軌上讀取資訊塊的速度相對較快。但是，如果需要選擇是添加更多的記憶體還是更快的磁碟，那麼最好選擇添加更多的記憶體。記憶體總是比磁碟快，而且添加記憶體可以讓你使用更大的緩衝，從而減少磁碟活動。

　　通過在物理裝置上劃分磁碟活動來擷取並行操作的優勢。如果你可以在多個物理裝置上劃分讀操作和寫操作，那麼其速度就會比從同一個裝置讀寫要快一些。例如，如果你把資料庫儲存在一個裝置上，把日誌儲存在另一個裝置上，那麼同時向兩個裝置寫入資訊的速度就比資料庫和日誌共用同一個裝置的速度要快。請注意，使用同一個物理裝置上的不同分區不算是並行操作。這是沒有好處的，因為它們仍然需要爭用相同的實體資源（磁頭）。

　　在把資料重新部署到另外一個裝置之前，你要確保自己知道系統的負載狀況。如果在某個特定的物理裝置上正在運行一些重要的業務，那麼把資料庫放在該裝置上有可能使效能更差。例如，如果你正在處理大量的Web業務，同時把資料庫移動到Web伺服器文檔目錄所在的裝置上，就可能感覺不到任何優勢。

　　使用RAID裝置可以讓你擷取並行操作的優勢。

　　使用多處理器硬體。對於類似MySQL伺服器的多線程應用程式來說，多處理器硬體可以同時執行多個線程。