前言

查詢優(yōu)化的本質(zhì)是讓數(shù)據(jù)庫優(yōu)化器為SQL語句選擇最佳的執(zhí)行計劃。

一般來說，對于在線交易處理（OLTP）系統(tǒng)的數(shù)據(jù)庫，減少數(shù)據(jù)庫磁盤I/O是SQL語句性能優(yōu)化的首要方法，因為磁盤訪問通常是數(shù)據(jù)庫性能的瓶頸所在。

另外，我們還需要考慮降低CPU和內(nèi)存的消耗。例如DISTINCT、GROUP BY、ORDER BY等操作都會涉及CPU運算，需要占用內(nèi)存或者使用臨時磁盤文件，這些都是我們優(yōu)化的目標(biāo)。

創(chuàng)建合適的索引

索引是優(yōu)化查詢性能的重要方法，因此我們首先需要了解哪些字段適合創(chuàng)建索引：

• 基于經(jīng)常出現(xiàn)在WHERE條件中的字段建立索引，可以避免全表掃描。
• 基于多表連接查詢的關(guān)聯(lián)字段（通常是外鍵）建立索引，可以提高連接查詢的性能。
• 將GROUP BY分組字段加入索引中，可以利用索引實現(xiàn)分組。
• 將ORDER BY排序字段加入索引中，可以避免額外的排序操作。

另外，我們在創(chuàng)建索引時盡量選擇區(qū)分度高的字段，比如手機號、姓名等。“性別”這種重復(fù)性極高的字段不適合單獨創(chuàng)建索引，必要時可以考慮和其他字段一起創(chuàng)建復(fù)合索引。

對于復(fù)合索引，查詢條件中最常出現(xiàn)的字段應(yīng)該放在索引的最左邊，這被稱為復(fù)合索引最左前綴原則，例如：

--創(chuàng)建表
CREATE TABLE test(
  id number not null,
  col1 number,
  col2 number,
  col3 varchar2(100),
  PRIMARY KEY (id)
);
--利用遞歸往表中插入1000條記錄
INSERT INTO test
WITH t (id,c1,c2,c3) AS (
   SELECT 1 id,1 c1,1 c2,1 c3 FROM dual
   UNION ALL
   SELECT id+1,c1+1,c2+2,c3+3 FROM t
   WHERE id<1000
)
SELECT * FROM t;

我們首先創(chuàng)建了一個測試表test，然后利用一個遞歸通用表表達(dá)式插入了1000行數(shù)據(jù)。

假如我們經(jīng)常同時使用col1和col2字段作為查詢條件，另外也會單獨使用col2字段作為查詢條件，可以創(chuàng)建以下復(fù)合索引：

CREATE INDEX idx_test
ON test(col2,col1);

其中col2字段在前，col1字段在后。下面以O(shè)racle數(shù)據(jù)庫為例，查看這兩種查詢條件下的執(zhí)行計劃：

執(zhí)行計劃顯示，在這兩種情況下，均可以通過索引idx_test查找數(shù)據(jù)。

如果我們需要單獨使用col1字段作為查詢條件，則通過全表掃描來查找數(shù)據(jù)。

另外，我們還需要了解一些不適合創(chuàng)建索引的情況。

例如，頻繁更新的字段不適合創(chuàng)建索引，因為更新索引也需要付出代價。表中的數(shù)據(jù)量很少時無須創(chuàng)建索引，因為在這種情況下全表掃描可能更快。

最后，對于大文本數(shù)據(jù)的檢索可以考慮使用全文搜索技術(shù)。

避免索引失效

雖然我們已經(jīng)創(chuàng)建了合適的索引，但是如果查詢語句中的WHERE子句編寫不當(dāng)，仍然可能會導(dǎo)致數(shù)據(jù)庫無法使用索引。

首先，在查詢條件中對索引字段進(jìn)行運算或者使用函數(shù)都會導(dǎo)致索引失效，例如：

查詢條件中的UPPER函數(shù)會導(dǎo)致索引失效，因為索引中并沒有存儲大寫形式的email。

其次，我們在使用LIKE運算符進(jìn)行匹配時，如果通配符出現(xiàn)在左側(cè)，也會導(dǎo)致索引失效，例如：

以上語句將會使用全表掃描的方式來查找數(shù)據(jù)，只有匹配模式左側(cè)是確定的內(nèi)容（比如“張%”）時，才可能會使用索引查找數(shù)據(jù)。

如果業(yè)務(wù)需求中確實存在這類模糊匹配，我們可以考慮使用全文索引或者專門的全文搜索引擎。

如果我們在某個字段上創(chuàng)建了索引，則應(yīng)該盡量將其設(shè)置為NOT NULL。這是因為不是所有的數(shù)據(jù)庫在使用IS [NOT] NULL運算符時，都會通過索引查找數(shù)據(jù)。

以下是一個Oracle示例：

Oracle不會針對索引字段為NULL的數(shù)據(jù)進(jìn)行索引，因此該查詢使用了全表掃描。

另外，在之前的文章中介紹了NULL值可能導(dǎo)致的各種問題。因此，建議將索引字段設(shè)置為NOT NULL，并且為其指定一個特殊的默認(rèn)值來表示缺失值。

只返回需要的結(jié)果

SELECT*表示查詢表中的全部字段，這種寫法通常會返回不必要的字段，從而影響查詢的性能。

這是因為數(shù)據(jù)庫需要讀取更多的數(shù)據(jù)，同時需要通過網(wǎng)絡(luò)傳輸更多的數(shù)據(jù)，而客戶端可能并不需要這些信息。

以下是一個Oracle示例：

第一個查詢語句需要返回所有的字段，使用了全表掃描。

第二個查詢只需返回員工的姓名，通過掃描索引 idx_emp_name就可以得到查詢結(jié)果，甚至不用訪問表。

因此，我們在開發(fā)和測試過程中可以使用SELECT*這種方式快速編寫查詢，但是在實際應(yīng)用中應(yīng)該嚴(yán)格控制只返回業(yè)務(wù)需要的字段。

優(yōu)化多表連接

連接查詢首先需要避免缺少連接條件導(dǎo)致的笛卡兒積，因為這是非常消耗資源的操作。

對于連接查詢中使用的關(guān)聯(lián)字段，我們應(yīng)該確保它們的數(shù)據(jù)類型和字符集相同，并且創(chuàng)建了合適的索引。

對于多表連接查詢，數(shù)據(jù)庫的實現(xiàn)算法通常有以下三種。

1. 嵌套循環(huán)連接（Nested Loop Join）：針對驅(qū)動表（外表）中的每條記錄，遍歷另一個表并找到匹配的數(shù)據(jù)，相當(dāng)于兩層FOR循環(huán)。這種方式適用于驅(qū)動表數(shù)據(jù)比較少，并且連接表中有索引的情況。
2. 哈希連接（Hash Join）：將其中一個表的連接字段計算出一個哈希表，然后從另一個表中一次獲取記錄并計算哈希值，根據(jù)兩個哈希值來匹配符合條件的記錄。這種方式在數(shù)據(jù)量大且沒有創(chuàng)建索引的情況下的性能可能更好。
3. 排序合并連接（Sort Merge Join）：首先將兩個表中的數(shù)據(jù)基于連接字段分別進(jìn)行排序，然后合并排序后的結(jié)果。這種方式通常用于沒有創(chuàng)建索引，并且數(shù)據(jù)已經(jīng)排序的情況。

數(shù)據(jù)庫優(yōu)化器選擇哪種算法取決于許多因素，比如表中的數(shù)據(jù)量、關(guān)聯(lián)字段是否已經(jīng)排序或者創(chuàng)建索引等。

一般連接查詢的表較少時，優(yōu)化器可以自行選擇合適的實現(xiàn)方法。當(dāng)復(fù)雜查詢性能不夠理想時，我們可以通過執(zhí)行計劃來查看是否需要采用創(chuàng)建索引、調(diào)整多表連接的順序或者指定連接方法等進(jìn)行優(yōu)化。

另外，還有一種優(yōu)化連接查詢的方法，就是通過增加冗余字段來減少連接查詢的數(shù)量。

盡量避免使用子查詢

非關(guān)聯(lián)子查詢可以單獨執(zhí)行，比較容易處理。我們通常需要優(yōu)化的是關(guān)聯(lián)子查詢。以下是一個Oracle示例，該查詢返回了月薪高于部門平均月薪的員工：

以下語句將該子查詢替換為等價的連接查詢，從而實現(xiàn)了子查詢的展開（Subquery Unnest）：

從執(zhí)行計劃看，兩種方式?jīng)]有差別，但是如果換作MySQL數(shù)據(jù)庫，使用第二種方法查詢效率會更高。建議使用第二種寫法。

第二種寫法利用了物化（Materialization）技術(shù)，將子查詢的結(jié)果保存為一個內(nèi)存臨時表，然后與employee表進(jìn)行嵌套循環(huán)連接。

優(yōu)化集合操作

集合操作符，其中UNION和UNION ALL都是并集操作符，它們的主要區(qū)別在于UNION需要將合并后的結(jié)果進(jìn)行去重。

例如，以下是一個Oracle中的示例：

從執(zhí)行計劃中可以看出，UNION操作符需要執(zhí)行一個額外SORT UNIQUE的操作。

記住SQL子句的邏輯執(zhí)行順序

完整SQL查詢語句：

以上是SQL查詢中各個子句的編寫順序，前面括號內(nèi)的數(shù)字代表了它們的邏輯執(zhí)行順序。也就是說，數(shù)據(jù)庫并非按照編寫順序先執(zhí)行SELECT子句，然后再執(zhí)行FROM子句等。從邏輯上講，SQL子句的執(zhí)行順序如下：

（1）首先，F(xiàn)ROM和JOIN是SQL語句執(zhí)行的第一步。它們的結(jié)果是一個笛卡兒積，該結(jié)果決定了接下來要操作的數(shù)據(jù)集。注意，邏輯執(zhí)行順序并不代表物理執(zhí)行順序，實際上數(shù)據(jù)庫在獲取表中的數(shù)據(jù)之前會應(yīng)用ON和WHERE過濾條件進(jìn)行訪問優(yōu)化。

（2）然后，應(yīng)用ON條件對上一步的結(jié)果進(jìn)行過濾，并生成新的數(shù)據(jù)集。

（3）接著執(zhí)行WHERE子句，對上一步的數(shù)據(jù)集進(jìn)行過濾。WHERE和ON子句在大多數(shù)情況下的效果相同，但是在外連接查詢中有所區(qū)別。

（4）下一步，基于GROUP BY子句指定的表達(dá)式進(jìn)行分組，同時對于每個分組計算聚合函數(shù)agg_func的結(jié)果。經(jīng)過GROUP BY處理之后，數(shù)據(jù)集的結(jié)構(gòu)就發(fā)生了變化，只保留了分組字段和聚合函數(shù)的結(jié)果。

（5）如果存在GROUP BY子句，可以進(jìn)一步利用HAVING子句對分組后的結(jié)果進(jìn)行過濾。

（6）接下來，SELECT子句可以指定要返回的字段。如果指定了DISTINCT關(guān)鍵字，數(shù)據(jù)庫需要對結(jié)果進(jìn)行去重操作。另外，數(shù)據(jù)庫還會為指定了AS的字段生成別名。

（7）如果還有集合操作符（UNION、INTERSECT、EXCEPT）和其他的SELECT語句，執(zhí)行該查詢，之后合并兩個結(jié)果集。對于集合操作中的多個SELECT語句，數(shù)據(jù)庫通常可以支持并發(fā)執(zhí)行。

（8）隨后應(yīng)用ORDER BY子句對結(jié)果進(jìn)行排序。如果存在GROUP BY子句或者DISTINCT關(guān)鍵字，就只能使用分組字段和聚合函數(shù)進(jìn)行排序；否則可以使用表中的任何字段排序。

（9）最后，利用OFFSET和FETCH（LIMIT、TOP）子句限定返回的行數(shù)。

理解以上SQL子句的邏輯執(zhí)行順序也可以幫助我們進(jìn)行查詢優(yōu)化。例如，WHERE子句在HAVING子句之前執(zhí)行，因此我們應(yīng)該盡量使用WHERE子句進(jìn)行數(shù)據(jù)過濾，除非業(yè)務(wù)邏輯需要基于聚合函數(shù)的結(jié)果進(jìn)行過濾。

另外，了解SQL子句的邏輯執(zhí)行順序還可以幫助我們避免一些常見的錯誤，例如：

該語句的錯誤在于WHERE條件中引用了列別名。從SQL子句的邏輯執(zhí)行順序中可以看出，數(shù)據(jù)庫使用WHERE條件過濾數(shù)據(jù)時還沒有執(zhí)行SELECT子句，也就還沒有生成字段的別名。

另一個需要注意的操作就是GROUP BY，常見錯誤示例：

經(jīng)過GROUP BY子句處理之后，結(jié)果中只保留了分組字段和聚合函數(shù)的值，示例中的emp_name字段已經(jīng)不存在了。從邏輯上來說，按照部門分組統(tǒng)計之后再顯示某個員工的姓名沒有意義。

如果需要同時顯示員工信息和所在部門的匯總結(jié)果，可以使用窗口函數(shù)。

還有一些邏輯問題可能不會直接導(dǎo)致查詢出錯，但是會返回不正確的結(jié)果，例如外連接查詢中的ON和WHERE子句。以下是一個左外連接查詢的示例：

第一個查詢語句在ON子句中指定了連接的條件，然后通過WHERE子句找出“張飛”。

第二個查詢語句將所有的過濾條件都放在ON子句中，結(jié)果返回了所有員工的姓名。這是因為左外連接會返回左表中的全部數(shù)據(jù)，即使ON子句中指定了員工姓名，也不會生效。

如果把查詢二中的LEFT JOIN 更換為INNER JOIN，也可以避免該問題：

SQL語句的聲明性使得我們無須關(guān)心具體的數(shù)據(jù)庫實現(xiàn)，但同時也可能因此導(dǎo)致查詢的性能問題。

SQL語句性能優(yōu)化只是數(shù)據(jù)庫性能優(yōu)化的一部分，其他技術(shù)還包括表結(jié)構(gòu)的優(yōu)化、數(shù)據(jù)庫配置參數(shù)優(yōu)化、操作系統(tǒng)和硬件調(diào)整以及架構(gòu)優(yōu)化（分庫分表、讀寫分離等）。

到此這篇關(guān)于SQL查詢的優(yōu)化技巧詳解的文章就介紹到這了,更多相關(guān)SQL查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！