經(jīng)常聽到有做應(yīng)用的朋友抱怨數(shù)據(jù)庫的性能問題，比如非常低的并發(fā)，令人崩潰的響應(yīng)時(shí)間，長(zhǎng)時(shí)間的鎖等待，鎖升級(jí) , 甚至是死鎖，等等。在解決這些問題的過程中，DBA 經(jīng)常發(fā)現(xiàn)應(yīng)用開發(fā)人員對(duì)數(shù)據(jù)庫的“誤用”。包括 , 返回過多不必要的數(shù)據(jù) , 不必要和不適當(dāng)加鎖，對(duì)隔離級(jí)別的誤用和對(duì)存儲(chǔ)過程的誤用等等。但是，面對(duì)浩如煙海的數(shù)據(jù)庫知識(shí) , 要求完全掌握 , 對(duì)應(yīng)用開發(fā)人員來說也確實(shí)枯燥艱深 . 因此，筆者特別提煉對(duì)應(yīng)用開發(fā)人員有幫助的 SQL 書寫部分，以期望能對(duì)數(shù)據(jù)庫開發(fā)人員有所幫助。

“根據(jù)我們的經(jīng)驗(yàn)（由很多業(yè)界專家證明），在 SQL Server 上取得的性能提高有 80% 來自對(duì) SQL 編碼的改進(jìn)，而不是來自于對(duì)于配置或系統(tǒng)性能的調(diào)整?！?br style="padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">—?jiǎng)P文 克萊恩等，Transact-SQL Programming 作者

“經(jīng)驗(yàn)表明 80%-90% 的性能調(diào)優(yōu)是在應(yīng)用級(jí)做的，而不是在數(shù)據(jù)庫級(jí)”
—托馬斯 白特，Expert One on One: Oracle 作者

本文將主要討論基于語法的優(yōu)化以及簡(jiǎn)簡(jiǎn)單的查詢條件?；谡Z法的優(yōu)化指的是為不考慮任何的非語法因素（例如，索引，表大小和存儲(chǔ)等），僅考慮 在 SQL 語句中對(duì)于詞語的選擇以及書寫的順序。

一般規(guī)則

這一部分，將看一下一些在書寫簡(jiǎn)單查詢語時(shí)需要注意的通用的規(guī)則。

根據(jù)權(quán)值來優(yōu)化查詢條件

最好的查詢語句是將簡(jiǎn)單的比較操作作用于最少的行上。以下兩張表，表 1 和表 2 以由好到差的順序列出了典型查詢條件操作符并賦與權(quán)值。

表 1. 查詢條件中操作符的權(quán)值

表 2. 查詢條件中操作數(shù)的權(quán)值

根據(jù)表 1 和表 2 中分配的權(quán)值，可以看出最好的查詢條件應(yīng)該是像下面這樣的：

… WHERE smallint_column = 789 

這個(gè)例子得到 27 分，計(jì)算如下：

• 左側(cè)只有列名（smallint_column）得 5 分
• 操作數(shù)為精確數(shù)據(jù)類型（smallint_column）得 2 分
• 等號(hào)（=）操作符得 10 分
• 右側(cè)是文字字符（789）得 10 分

下面是另外一個(gè)例子

… WHERE char_column >= varchar_column || ‘ x ’

這種類型的查詢權(quán)值得分就很低，只有 13 分

• 左側(cè)只有列名（char_column）得 5 分
• CHAR 類型的操作數(shù)得 0 分
• 大于等于操作符得 5 分
• 左側(cè)是多操作數(shù)表達(dá)示得 3 分
• VARCHAR 類型的操作數(shù)得 0 分

上面表格中的權(quán)值數(shù)可能在不同類型的數(shù)據(jù)庫系統(tǒng)中會(huì)有所不同，所以記住這些具體數(shù)值是沒有意義的，只需要了解它們的排序即可。用時(shí)越少的比較 條件，得分也就越高，這樣的比較條件通常是那些操作的行數(shù)少或者易于比較的。

傳遞法則

傳遞法則是這樣定義的：

 IF 
 
 (A <comparison operator> B) IS TRUE 
 AND (B <comparison operator> C) IS TRUE 
 
 THEN 
 
 (A <comparison operator> C) IS TRUE 
 AND NOT (A <comparison operator> C) IS FALSE 

比較運(yùn)算符包括：=、>、>=、<、<、+, 但不包括：<>、LIKE。

通過傳遞法則，我們可以看出，可以用 C 來替換 B，而不使表達(dá)式的意思發(fā)生變化。

下面的兩個(gè)例子表達(dá)了同樣的含義，但是第二個(gè)表達(dá)示要比第一個(gè)表達(dá)式執(zhí)行的快。

表達(dá)式一：

 ... WHERE column1 < column2 

 AND column2 = column3 

 AND column1 = 5 

表達(dá)式二：

 ... WHERE 5 < column2 

 AND column2 = column3 

 AND column1 = 5 

大多數(shù)的數(shù)據(jù)庫管理系統(tǒng)都會(huì)自動(dòng)的做這樣的調(diào)整，但是當(dāng)表達(dá)式中含有括號(hào)時(shí)，它們就不會(huì)自動(dòng)調(diào)整了。例如一個(gè)如下的 SELECT 語句：

 SELECT * FROM Table1 

 WHERE column1 = 5 AND 

 NOT (column3 = 7 OR column1 = column2) 

如果進(jìn)行轉(zhuǎn)化的話，會(huì)得到如下的語句：

 SELECT * FROM Table1 

 WHERE column1 = 5 

 AND column3 <> 7 

 AND column2 <> 5 

進(jìn)行這樣變化后的語句會(huì)比第一個(gè)執(zhí)行的更快。

1. Sargability

理想的 SQL 表達(dá)式應(yīng)該采用下面這種通用的格式：

 <column> <comparison operator> <literal> 

早些時(shí)候，IBM 研究人員將這種查詢條件語名命名為”sargable predicates”，因?yàn)?SARG 是 Search ARGument 的組合。

根據(jù)這一規(guī)則，查詢條件的左側(cè)應(yīng)該是一個(gè)列名；右側(cè)應(yīng)該是一個(gè)很容易進(jìn)行查找的值。

遵循這一規(guī)則，所有的數(shù)據(jù)庫系統(tǒng)都會(huì)將如下的表達(dá)式：

 5 = column1 

轉(zhuǎn)換成：

 column1 = 5 

但是當(dāng)查詢條件中包含算術(shù)表達(dá)式時(shí)，只有部分的數(shù)據(jù)庫系統(tǒng)進(jìn)行轉(zhuǎn)換。

例如：

 ... WHERE column1 - 3 = -column2 

轉(zhuǎn)換成：

 ... WHERE column1 = -column2 + 3 

還是可以帶來查詢性能的優(yōu)化的。

針對(duì)專門操作符的調(diào)優(yōu)

前面，講的是關(guān)于查詢條件的一般規(guī)則，在這一節(jié)中，將討論如何使用專門的操作符來改進(jìn) SQL 代碼的性能。

與 (AND) 

數(shù)據(jù)庫系統(tǒng)按著從左到右的順序來解析一個(gè)系列由 AND 連接的表達(dá)式，但是 Oracle 卻是個(gè)例外，它是從右向左地解析表達(dá)式。可以利用數(shù)據(jù)庫系統(tǒng)的這一特性，來將概率小的表達(dá)示放在前面，或者是如果兩個(gè)表達(dá)式可能性相同，那么可將相對(duì)不復(fù) 雜的表達(dá)式放在前面。這樣做的話，如果第一個(gè)表達(dá)式為假的話，那么數(shù)據(jù)庫系統(tǒng)就不必再費(fèi)力去解析第二個(gè)表達(dá)式了。例如，可以這樣轉(zhuǎn)換：

 ... WHERE column1 = 'A' AND column2 = 'B'

轉(zhuǎn)換成：

 ... WHERE column2 = 'B' AND column1 = 'A'

這里假設(shè) column2 = 'B'的概率較低，如果是 Oracle 數(shù)據(jù)庫的話，只需將規(guī)則反過來用即可。

或 (OR)

和與 (AND) 操作符相反，在用或 (OR) 操作符寫 SQL 語句時(shí)，就應(yīng)該將概率大的表達(dá)示放在左面，因?yàn)槿绻谝粋€(gè)表達(dá)示為假的話，OR 操作符意味著需要進(jìn)行下一個(gè)表達(dá)示的解析。

與 + 或

按照集合的展開法則，

 A AND (B OR C) 與 (A AND B) OR (A AND C) 是等價(jià)表達(dá)示。

假設(shè)有如表 3 所示的一張表，要執(zhí)行一個(gè) AND 操作符在前的表達(dá)示

 SELECT * FROM Table1 
 
 WHERE (column1 = 1 AND column2 = 'A') 
 
 OR (column1 = 1 AND column2 = 'B') 

表 3. AND+OR 查詢

當(dāng)數(shù)據(jù)庫系統(tǒng)按照查詢語進(jìn)行搜索時(shí)，它按照下面的步驟執(zhí)行：

• 索引查找 column1 = 1, 結(jié)果集 = {row 3}
• 索引查找 column2 = ‘ A ’ , 結(jié)果集 = {row1}
• AND 合并結(jié)果集，結(jié)果集 = {}
• 索引查找 column 1 = 1, 結(jié)果集 = {row 3}
• 索引查找 column 2 = ‘ B ’ , 結(jié)果集 = {row2}
• AND 合并結(jié)果集，結(jié)果集 = {}
• OR 合并結(jié)集，結(jié)果集 = {}

現(xiàn)在根據(jù)集合的展開法則，對(duì)上面的語句進(jìn)行轉(zhuǎn)換：

 SELECT * FROM Table1 

 WHERE column1 = 1 

 AND (column2 = 'A' OR column2 = 'B') 

按照新的順序進(jìn)行查搜索時(shí)，它按照下面的步驟執(zhí)行：

• 索引查找 column2 = ‘ A ’ , 結(jié)果集 = {row1}
• 索引查找 column 2 = ‘ B ’ , 結(jié)果集 = {row2}
• OR 合并結(jié)集，結(jié)果集 = {}
• 索引查找 column1 = 1, 結(jié)果集 = {row 3}
• AND 合并結(jié)果集，結(jié)果集 = {}

由此可見搜索次數(shù)少了一次。雖然一些數(shù)據(jù)庫操作系統(tǒng)會(huì)自動(dòng)的進(jìn)行這樣的轉(zhuǎn)換，但是對(duì)于簡(jiǎn)單的查詢來說，這樣的轉(zhuǎn)換還是有好處的。

非 (NOT)

讓非 (NOT) 表達(dá)示轉(zhuǎn)換成更易讀的形式。簡(jiǎn)單的條件能通過將比較操作符進(jìn)行反轉(zhuǎn)來達(dá)到轉(zhuǎn)換的目的，例如：

 ... WHERE NOT (column1 > 5) 

轉(zhuǎn)換成：

 ... WHERE column1 <= 5 

比較復(fù)雜的情況，根據(jù)集合的摩根定理：

 NOT (A AND B) = (NOT A) OR (NOT B) 和 NOT (A OR B) = (NOT A) AND (NOT B) 

根據(jù)這一定理，可以看出它可以至少二次的搜索有可能減少為一次。如下的查詢條件：

 ... WHERE NOT (column1 > 5 OR column2 = 7) 

可以轉(zhuǎn)換成：

 ... WHERE column1 <= 5 

 AND column2 <> 7 

但是，當(dāng)轉(zhuǎn)換成后的表達(dá)示中有不等操作符 <>，那么性能就會(huì)下降，畢竟，在一個(gè)值平均分布的集合中，不等的值的個(gè)數(shù)要遠(yuǎn)遠(yuǎn)大于相等的值的個(gè)數(shù)，正因?yàn)槿绱?，一些?shù)據(jù)庫系統(tǒng)不會(huì)對(duì)非比 較進(jìn)行索引搜索，但是他們會(huì)為大于或小于進(jìn)行索引搜索，所以可以將下面的查詢進(jìn)行如下轉(zhuǎn)換：

 ... WHERE NOT (column1 = 0) 

轉(zhuǎn)換成：

 ... WHERE column <0 

 OR column > 0 

IN

很多人認(rèn)為如下的兩個(gè)查詢條件沒有什么差別，因?yàn)樗鼈兎祷氐慕Y(jié)果集是相同的：

條件 1：

 ... WHERE column1 = 5 

 OR column1 = 6 

條件 2：

 ... WHERE column1 IN (5, 6) 

這樣的想法并不完全正確，對(duì)于大多數(shù)的數(shù)據(jù)庫操作系統(tǒng)來說，IN 要比 OR 執(zhí)行的快。所以如果可以的話，要將 OR 換成 IN

當(dāng) IN 操作符，是一系列密集的整型數(shù)字時(shí)，最好是查找哪些值不符合條件，而不是查找哪些值符合條件，因此，如下的查詢條件就應(yīng)該進(jìn)行如下的轉(zhuǎn)換：

 ... WHERE column1 IN (1, 3, 4, 5) 

轉(zhuǎn)換成：

 ... WHERE column1 BETWEEN 1 AND 5 
 AND column1 <> 2 

當(dāng)一系列的離散的值轉(zhuǎn)換成算數(shù)表達(dá)示時(shí)，也可獲得同樣的性能提高。

UNION

在 SQL 中，兩個(gè)表的 UNION 就是兩個(gè)表中不重復(fù)的值的集合，即 UNION 操作符返返回的兩個(gè)或多個(gè)查詢結(jié)果中不重復(fù)行的集合。這是一個(gè)很好的合并數(shù)據(jù)的方法，但是這并不是最好的方法。

查詢 1：

 SELECT * FROM Table1 

 WHERE column1 = 5 

 UNION 

 SELECT * FROM Table1 

 WHERE column2 = 5 

查詢 2：

 SELECT DISTINCT * FROM Table1 

 WHERE column1 = 5 

 OR column2 = 5 

在上面的例子中，column1 和 column2 都沒有索引。如果查詢 2 總是比查詢 1 執(zhí)行的快的話，那么就可以建議總是將查詢 1 轉(zhuǎn)換成查詢 2，但是有一種情況，這樣做在一些數(shù)據(jù)庫系統(tǒng)中可能會(huì)帶來性能變差，這是由于兩個(gè)優(yōu)化缺陷所造成的。

第一個(gè)優(yōu)化缺陷就是很多優(yōu)化器只優(yōu)化一個(gè) SELECT 語句中一個(gè) WHERE 語句，所以查詢 1 的兩個(gè) SELECT 語句都被執(zhí)行。首先優(yōu)化器根據(jù)查詢條件 column1 = 5 為真來查找所有符合條件的所有行，然后據(jù)查詢條件 column2 = 5 為真來查找所有符合條件的所有行，即兩次表掃描，因此，如果 column1 = 5 沒有索引的話，查詢 1 將需要 2 倍于查詢 2 所需的時(shí)間。如果 column1 = 5 有索引的話，仍然需要二次掃描，但是只有在某些數(shù)據(jù)庫系統(tǒng)存在一個(gè)不常見的優(yōu)化缺陷卻將第一個(gè)優(yōu)化缺陷給彌補(bǔ)了。當(dāng)一些優(yōu)化器發(fā)現(xiàn)查詢中存在 OR 操作符時(shí)，就不使用索引查詢，所以在這種情況下，并且只有在這種情況下，UNION 才比 OR 性能更高。這種情況很少見，所以仍然建議大家當(dāng)待查詢的列沒有索引時(shí)使用 OR 來代替 UNION。

以上是作者對(duì)如何提高 SQL 性能的一些總結(jié)，這些規(guī)則并一定在所有的數(shù)據(jù)庫系統(tǒng)上都能帶來性能的提高，但是它們一定不會(huì)對(duì)數(shù)據(jù)庫的性能帶來下降，所以掌握并使用這些規(guī)則可以對(duì)數(shù)據(jù)庫 應(yīng)用程序的開發(fā)有所幫助。本文總結(jié)的是一些 SQL 性能調(diào)優(yōu)的比較初級(jí)的方面，SQL 調(diào)優(yōu)還包括 Order by，Group by 以及 Index 等等。