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 < 100 AND 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”