MySQL查詢的性能優化(查詢緩存、排序跟索引) · php開發筆記

[mysql](http://www.gaodaima.com/tag/mysql)查詢的性能優化(查詢緩存、排序和索引) 詢是數據庫技術中最常用的操作。查詢操作的過程比較簡單，首先從客戶端發出查詢的SQL語句，數據庫服務端在接收到由客戶端發來的SQL語句后，執行這條SQL語句，然后將查詢到的結果返回給客戶端。雖然過程很簡單，但不同的查詢方式和數據庫設置，對查詢的性能將會有很在的影響。因此，本文就在MySQL中常用的查詢優化技術進行討論。討論的內容如：通過查詢緩沖提高查詢速度；MySQL對查詢的自動優化；基于索引的排序；不可達查詢的檢測和使用各種查詢選擇來提高性能。一、?通過查詢緩沖提高查詢速度一般我們使用SQL語句進行查詢時，數據庫服務器每次在收到客戶端發來SQL后，都會執行這條SQL語句。但當在一定間隔內(如1分鐘內)，接到完全一樣的SQL語句，也同樣執行它。雖然這樣可以保證數據的實時性，但在大多數時候，數據并不要求完全的實時，也就是說可以有一定的延時。如果是這樣的話，在短時間內執行完全一樣的SQL就有些得不償失。幸好MySQL為我們提供了查詢緩沖的功能(只能在MySQL?4.0.1及以上版本使用查詢緩沖)。我們可以通過查詢緩沖在一定程度上提高查詢性能。我們可以通過在MySQL安裝目錄中的my.ini文件設置查詢緩沖。設置也非常簡單，只需要將query\_cache\_type設為1即可。在設置了這個屬性后，MySQL在執行任何SELECT語句之前，都會在它的緩沖區中查詢是否在相同的SELECT語句被執行過，如果有，并且執行結果沒有過期，那么就直接取查詢結果返回給客戶端。但在寫SQL語句時注意，MySQL的查詢緩沖是區分大小寫的。如下列的兩條SELECT語句: 復制內容到剪貼板代碼: SELECT?\*?from?TABLE1 SELECT?\*?FROM?TABLE1 上面的兩條SQL語句對于查詢緩沖是完全不同的SELECT。而且查詢緩沖并不自動處理空格，因此，在寫SQL語句時，應盡量減少空格的使用，尤其是在SQL首和尾的空格(因為，查詢緩沖并不自動截取首尾空格)。雖然不設置查詢緩沖，有時可能帶來性能上的損失，但有一些SQL語句需要實時地查詢數據，或者并不經常使用(可能一天就執行一兩次)。這樣就需要把緩沖關了。當然，這可以通過設置query\_cache\_type的值來關閉查詢緩沖，但這就將查詢緩沖永久地關閉了。在MySQL?5.0中提供了一種可以臨時關閉查詢緩沖的方法: 復制內容到剪貼板代碼: SELECT?SQL\_NO\_CACHE?field1,?field2?FROM?TABLE1 以上的SQL語句由于使用了SQL\_NO\_CACHE，因此，不管這條SQL語句是否被執行過，服務器都不會在緩沖區中查找，每次都會執行它。我們還可以將my.ini中的query\_cache\_type設成2，這樣只有在使用了SQL\_CACHE后，才使用查詢緩沖。復制內容到剪貼板代碼: SELECT?SQL\_CALHE?\*?FROM?TABLE1 二、MySQL對查詢的自動優化索引對于數據庫是非常重要的。在查詢時可以通過索引來提高性能。但有時使用索引反而會降低性能。我們可以看如下的SALES表: 復制內容到剪貼板代碼: CREATE?TABLE?SALES ( ID?INT(10)?UNSIGNED?NOT?NULL?AUTO\_INCREMENT, NAME?VARCHAR(100)?NOT?NULL, PRICE?FLOAT?NOT?NULL, SALE\_COUNT?INT?NOT?NULL, SALE\_DATE?DATE?NOT?NULL, PRIMARY?KEY(ID)， INDEX?(NAME)， INDEX?(SALE\_DATE) ); 假設這個表中保存了數百萬條數據，而我們要查詢商品號為1000的商品在2004年和2005年的平均價格。我們可以寫如下的SQL語句: SELECT?AVG(PRICE)?FROM?SALES WHERE?ID?=?1000?AND?SALE\_DATE?BETWEEN?‘2004-01-01′?AND?‘2005-12-31′; 如果這種商品的數量非常多，差不多占了SALES表的記錄的50%或更多。那么使用SALE\_DATE字段上索引來計算平均數就有些慢。因為如果使用索引，就得對索引進行排序操作。當滿足條件的記錄非常多時(如占整個表的記錄的50%或更多的比例)，速度會變慢，這樣還不如對整個表進行掃描。因此，MySQL會自動根據滿足條件的數據占整個表的數據的比例自動決定是否使用索引進行查詢。對于MySQL來說，上述的查詢結果占整個表的記錄的比例是30%左右時就不使用索引了，這個比例是MySQL的開發人員根據他們的經驗得出的。然而，實際的比例值會根據所使用的數據庫引擎不同而不同。三、?基于索引的排序 MySQL的弱點之一是它的排序。雖然MySQL可以在1秒中查詢大約15,000條記錄，但由于MySQL在查詢時最多只能使用一個索引。因此，如果WHERE條件已經占用了索引，那么在排序中就不使用索引了，這將大大降低查詢的速度。我們可以看看如下的SQL語句: 復制內容到剪貼板代碼: SELECT?\*?FROM?SALES?WHERE?NAME?=?“name”?ORDER?BY?SALE\_DATE?DESC; 在以上的SQL的WHERE子句中已經使用了NAME字段上的索引，因此，在對SALE\_DATE進行排序時將不再使用索引。為了解決這個問題，我們可以對SALES表建立復合索引: 復制內容到剪貼板代碼: ALTER?TABLE?SALES?DROP?INDEX?NAME,?ADD?INDEX?(NAME,?SALE\_DATE) 這樣再使用上述的SELECT語句進行查詢時速度就會大副提升。但要注意，在使用這個方法時，要確保WHERE子句中沒有排序字段，在上例中就是不能用SALE\_DATE進行查詢，否則雖然排序快了，但是SALE\_DATE字段上沒有單獨的索引，因此查詢又會慢下來。四、?不可達查詢的檢測在執行SQL語句時，難免會遇到一些必假的條件。所謂必假的條件是無論表中的數據如何變化，這個條件都為假。如WHERE?value??200。我們永遠無法找到一個既小于100又大于200的數。如果遇到這樣的查詢條件，再去執行這樣的SQL語句就是多此一舉。幸好MySQL可以自動檢測這種情況。如我們可以看看如下的SQL語句: 復制內容到剪貼板代碼: SELECT?\*?FROM?SALES?WHERE?NAME?=?“name1”?AND?NAME?=?“name2” 以上的查詢語句要查找NAME既等于name1又等于name2的記錄。很明顯，這是一個不可達的查詢，WHERE條件一定是假。MySQL在執行SQL語句之前，會先分析WHERE條件是否是不可達的查詢，如果是，就不再執行這條SQL語句了。為了驗證這一點。我們首先對如下的SQL使用EXPLAIN進行測試: 復制內容到剪貼板代碼: EXPLAIN?SELECT?\*?FROM?SALES?WHERE?NAME?=?“name1” 上面的查詢是一個正常的查詢，我們可以看到使用EXPLAIN返回的執行信息數據中table項是SALES。這說明MySQL對SALES進行操作了。再看看下面的語句: 復制內容到剪貼板代碼: EXPLAIN?SELECT?\*?FROM?SALES?WHERE?NAME?=?“name1”?AND?NAME?=?“name2” 我們可以看到，table項是空，這說明MySQL并沒有對SALES表進行操作。五、?使用各種查詢選擇來提高性能 SELECT語句除了正常的使用外，MySQL還為我們提供了很多可以增強查詢性能的選項。如上面介紹的用于控制查詢緩沖的SQL\_NO\_CACHE和SQL\_CACHE就是其中兩個選項。在這一部分，我將介紹幾個常用的查詢選項。 1.?STRAIGHT\_JOIN:強制連接順序當我們將兩個或多個表連接起來進行查詢時，我們并不用關心MySQL先連哪個表，后連哪個表。而這一切都是由MySQL內部通過一系列的計算、評估，最后得出的一個連接順序決定的。如下列的SQL語句中，TABLE1和TABLE2并不一定是誰連接誰: 復制內容到剪貼板代碼: SELECT?TABLE1.FIELD1,?TABLE2.FIELD2?FROM?TABLE1?,TABLE2?WHERE?… 如果開發人員需要人為地干預連接的順序，就得使用STRAIGHT\_JOIN關鍵字，如下列的SQL語句: 復制內容到剪貼板代碼: SELECT?TABLE1.FIELD1,?TABLE2.FIELD2?FROM?TABLE1?STRAIGHT\_JOIN?TABLE2?WHERE?… 由上面的SQL語句可知，通過STRAIGHT\_JOIN強迫MySQL按TABLE1、TABLE2的順序連接表。如果你認為按自己的順序比MySQL推薦的順序進行連接的效率高的話，就可以通過STRAIGHT\_JOIN來確定連接順序。 2.?干預索引使用，提高性能在上面已經提到了索引的使用。一般情況下，在查詢時MySQL將自己決定是否使用索引，使用哪一個索引。但在一些特殊情況下，我們希望MySQL只使用一個或幾個索引，或者不希望使用某個索引。這就需要使用MySQL的控制索引的一些查詢選項。限制使用索引的范圍有時我們在數據表里建立了很多索引，當MySQL對索引進行選擇時，這些索引都在考慮的范圍內。但有時我們希望MySQL只考慮幾個索引，而不是全部的索引，這就需要用到USE?INDEX對查詢語句進行設置。復制內容到剪貼板代碼: SELECT?\*?FROM?TABLE1?USE?INDEX?(FIELD1,?FIELD2)?… 從以上SQL語句可以看出，無論在TABLE1中已經建立了多少個索引，MySQL在選擇索引時，只考慮在FIELD1和FIELD2上建立的索引。限制不使用索引的范圍如果我們要考慮的索引很多，而不被使用的索引又很少時，可以使用IGNORE?INDEX進行反向選取。在上面的例子中是選擇被考慮的索引，而使用IGNORE?INDEX是選擇不被考慮的索引。復制內容到剪貼板代碼: SELECT?\*?FROM?TABLE1?IGNORE?INDEX?(FIELD1,?FIELD2)?… 在上面的SQL語句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。強迫使用某一個索引上面的兩個例子都是給MySQL提供一個選擇，也就是說MySQL并不一定要使用這些索引。而有時我們希望MySQL必須要使用某一個索引(由于MySQL在查詢時只能使用一個索引，因此只能強迫MySQL使用一個索引)。這就需要使用FORCE?INDEX來完成這個功能。復制內容到剪貼板代碼: SELECT?\*?FROM?TABLE1?FORCE?INDEX?(FIELD1)?… 以上的SQL語句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。 3.?使用臨時表提供查詢性能當我們查詢的結果集中的數據比較多時，可以通過SQL\_BUFFER\_RESULT.選項強制將結果集放到臨時表中，這樣就可以很快地釋放MySQL的表鎖(這樣其它的SQL語句就可以對這些記錄進行查詢了)，并且可以長時間地為客戶端提供大記錄集。復制內容到剪貼板代碼: SELECT?SQL\_BUFFER\_RESULT?\*?FROM?TABLE1?WHERE?… 和SQL\_BUFFER\_RESULT.選項類似的還有SQL\_BIG\_RESULT，這個選項一般用于分組或DISTINCT關鍵字，這個選項通知MySQL，如果有必要，就將查詢結果放到臨時表中，甚至在臨時表中進行排序。復制內容到剪貼板代碼: SELECT?SQL\_BUFFER\_RESULT?FIELD1,?COUNT(\*)?FROM?TABLE1?GROUP?BY?FIELD1 六、?結論在程序設計中同樣存在一個“二八原則”，即20%的代碼用去了80%的時間。數據庫應用程序的開發亦然。數據庫應用程序的優化，重點在于SQL的執行效率。而數據查詢優化的重點，則是使得數據庫服務器少從磁盤中讀數據以及順序讀頁而不是非順序讀頁。