查詢是資料庫技術中最常用的操作。查詢操作的過程比較簡單，先從客戶端發出查詢的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”
我們可以看到，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的執行效率。而資料查詢最佳化的重點，則是使得資料庫伺服器少從磁碟中讀取資料以及順序讀頁而不是非順序讀頁。