前言

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

一般來(lái)說(shuō)，對(duì)于在線交易處理（OLTP）系統(tǒng)的數(shù)據(jù)庫(kù)，減少數(shù)據(jù)庫(kù)磁盤I/O是SQL語(yǔ)句性能優(yōu)化的首要方法，因?yàn)榇疟P訪問(wèn)通常是數(shù)據(jù)庫(kù)性能的瓶頸所在。

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

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

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

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

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

對(duì)于復(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)建了一個(gè)測(cè)試表test，然后利用一個(gè)遞歸通用表表達(dá)式插入了1000行數(shù)據(jù)。

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

CREATE INDEX idx_test
ON test(col2,col1);

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

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

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

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

例如，頻繁更新的字段不適合創(chuàng)建索引，因?yàn)楦滤饕残枰冻龃鷥r(jià)。表中的數(shù)據(jù)量很少時(shí)無(wú)須創(chuàng)建索引，因?yàn)樵谶@種情況下全表掃描可能更快。

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

避免索引失效

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

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

查詢條件中的UPPER函數(shù)會(huì)導(dǎo)致索引失效，因?yàn)樗饕胁](méi)有存儲(chǔ)大寫形式的email。

其次，我們?cè)谑褂肔IKE運(yùn)算符進(jìn)行匹配時(shí)，如果通配符出現(xiàn)在左側(cè)，也會(huì)導(dǎo)致索引失效，例如：

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

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

如果我們?cè)谀硞€(gè)字段上創(chuàng)建了索引，則應(yīng)該盡量將其設(shè)置為NOT NULL。這是因?yàn)椴皇撬械臄?shù)據(jù)庫(kù)在使用IS [NOT] NULL運(yùn)算符時(shí)，都會(huì)通過(guò)索引查找數(shù)據(jù)。

以下是一個(gè)Oracle示例：

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

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

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

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

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

以下是一個(gè)Oracle示例：

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

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

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

優(yōu)化多表連接

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

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

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

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

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

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

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

盡量避免使用子查詢

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

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

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

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

優(yōu)化集合操作

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

例如，以下是一個(gè)Oracle中的示例：

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

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

完整SQL查詢語(yǔ)句：

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

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

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

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

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

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

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

（7）如果還有集合操作符（UNION、INTERSECT、EXCEPT）和其他的SELECT語(yǔ)句，執(zhí)行該查詢，之后合并兩個(gè)結(jié)果集。對(duì)于集合操作中的多個(gè)SELECT語(yǔ)句，數(shù)據(jù)庫(kù)通?？梢灾С植l(fā)執(zhí)行。

（8）隨后應(yīng)用ORDER BY子句對(duì)結(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ù)過(guò)濾，除非業(yè)務(wù)邏輯需要基于聚合函數(shù)的結(jié)果進(jìn)行過(guò)濾。

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

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

另一個(gè)需要注意的操作就是GROUP BY，常見錯(cuò)誤示例：

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

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

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

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

第二個(gè)查詢語(yǔ)句將所有的過(guò)濾條件都放在ON子句中，結(jié)果返回了所有員工的姓名。這是因?yàn)樽笸膺B接會(huì)返回左表中的全部數(shù)據(jù)，即使ON子句中指定了員工姓名，也不會(huì)生效。

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

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

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

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

最新評(píng)論