資料庫查詢如何優化效能？ （詳解）

資料庫查詢如何最佳化效能？ （詳解）

查詢是資料庫技術中最常用的操作。查詢操作的過程比較簡單，先從客戶端發出查詢的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的执行效率。而数据查询优化的重点，则是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。 

推荐教程：《MySQL教程》

以上是資料庫查詢如何優化效能？ （詳解）的詳細內容。更多資訊請關注PHP中文網其他相關文章！