一:參數(shù)是子查詢時(shí)，使用 EXISTS 代替 IN

如果 IN 的參數(shù)是1, 2, 3 這樣的數(shù)值列表，一般還不需要特別注意。但是如果參數(shù)是子查詢，那么就需要注意了。在大多時(shí)候，[NOT] IN 和 [NOT] EXISTS 返回的結(jié)果是相同的。但是兩者用于子查詢時(shí)，EXISTS 的速度會(huì)更快一些。我們?cè)囍鴱?Class_A 表中查出同時(shí)存在于 Class_B 表中的員工。下面兩條SQL 語(yǔ)句返回的結(jié)果是一樣的，但是使用 EXISTS 的 SQL 語(yǔ)句更快一些。

?

-- 慢
SELECT *
FROM Class_A
WHERE id IN (SELECT id
FROM Class_B);-- 快
SELECT *
FROM Class_A A
WHERE EXISTS
(SELECT *
FROM Class_B B
WHERE A.id = B.id);

使用 EXISTS 時(shí)更快的原因有以下兩個(gè)。

• 如果連接列（id ）上建立了索引，那么查詢 Class_B 時(shí)不用查實(shí)際的表，只需查索引就可以了。
• 如果使用 EXISTS ，那么只要查到一行數(shù)據(jù)滿足條件就會(huì)終止查詢，不用像使用 IN 時(shí)一樣掃描全表。在這一點(diǎn)上 NOT
  EXISTS 也一樣。

當(dāng) IN 的參數(shù)是子查詢時(shí)，數(shù)據(jù)庫(kù)首先會(huì)執(zhí)行子查詢，然后將結(jié)果存儲(chǔ)在一張臨時(shí)的工作表里（內(nèi)聯(lián)視圖），然后掃描整個(gè)視圖。很多情況下這種做法都非常耗費(fèi)資源。使用 EXISTS 的話，數(shù)據(jù)庫(kù)不會(huì)生成臨時(shí)的工作表。

要想改善 IN 的性能，除了使用 EXISTS ，還可以使用連接。前面的查詢語(yǔ)句就可以像下面這樣“扁平化”。

?

-- 使用連接代替IN
SELECT A.id, A.name
FROM Class_A A INNER JOIN Class_B B
ON A.id = B.id;

這種寫法至少能用到一張表的“id”列上的索引。而且，因?yàn)闆](méi)有了子查詢，所以數(shù)據(jù)庫(kù)也不會(huì)生成中間表。我們很難說(shuō)與 EXISTS 相比哪個(gè)更好，但是如果沒(méi)有索引，那么與連接相比，可能 EXISTS 會(huì)略勝一籌。

二:避免排序

我們?cè)诓樵兊臅r(shí)候，雖然我們沒(méi)有想要進(jìn)行排序，但是在數(shù)據(jù)庫(kù)內(nèi)部頻繁地進(jìn)行著暗中的排序。因此對(duì)于我們來(lái)說(shuō)，了解都有哪些運(yùn)算會(huì)進(jìn)行排序很有必要，會(huì)進(jìn)行排序的代表性的運(yùn)算有下面這些

• group by 子句
• order by 子句
• 聚合函數(shù)(sum、count、avg、max、min)
• distinct
• 集合運(yùn)算符(union、intersect、except)
• 窗口函數(shù)(rank、row_number等)

1.使用union all 代替union

?

select * from Class_A
union 
select * from Class_B 

這個(gè)會(huì)進(jìn)行排序,如果不在乎結(jié)果中是否有重復(fù)數(shù)據(jù)，可以使用union all 代替 union .這樣就不會(huì)進(jìn)行排序了

?

select * from Class_A
union all
select * from Class_B;

2.使用exists 代替distinct

為了排除重復(fù)數(shù)據(jù),distinct 也會(huì)進(jìn)行排序。如果需要對(duì)兩張表的連接結(jié)果進(jìn)行去重，可以考慮使用exists代替distinct,以避免排序

Items

?

SalesHistory

問(wèn)題:如何從上面的商品表Items中找出同時(shí)存在于銷售記錄表SalesHistory中的商品。簡(jiǎn)而言之，就是找出有銷售記錄的商品,使用 IN 是一種做法。但是前面我們說(shuō)過(guò)，當(dāng) IN 的參數(shù)是子查詢時(shí)，使用連接要比使用 IN 更好。因此我們像下面這樣使用item_no列對(duì)兩張表進(jìn)行連接。

?

SELECT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;

因?yàn)槭且粚?duì)多的連接，所以item_no列中會(huì)出現(xiàn)重復(fù)數(shù)據(jù)。為了排除重復(fù)數(shù)據(jù)，我們需要使用 DISTINCT 。

?

SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;

但是，使用distinct的時(shí)候會(huì)進(jìn)行排序， 其實(shí)更好的做法是使用 EXISTS 。

?

SELECT item_no
FROM Items I
WHERE EXISTS
(SELECT *
FROM SalesHistory SH
WHERE I.item_no = SH.item_no)

這條語(yǔ)句在執(zhí)行過(guò)程中不會(huì)進(jìn)行排序。而且使用 EXISTS 和使用連接一樣高效。

3.在極值函數(shù)中使用索引(MAX/MIN)

使用這兩個(gè)函數(shù)時(shí)都會(huì)進(jìn)行排序。但是如果參數(shù)字段上建有索引，則
只需要掃描索引，不需要掃描整張表。以剛才的表 Items 為例來(lái)說(shuō)，
SQL 語(yǔ)句可以像下面這樣寫。

?

SELECT MAX(item_no)
FROM Items;

這種方法并不是去掉了排序這一過(guò)程，而是優(yōu)化了排序前的查找速
度，從而減弱排序?qū)φw性能的影響。

4.能寫在 WHERE 子句里的條件不要寫在 HAVING 子句里

• 聚合后使用HAVING 子句過(guò)濾

?

SELECT sale_date, SUM(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING sale_date = '2007-10-01';

• 聚合前使用WHERE 子句過(guò)濾

?

SELECT sale_date, SUM(quantity)
FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUP BY sale_date;

雖然結(jié)果是一樣的，但是從性能上來(lái)看，第二條語(yǔ)句寫法效率更高。原因通常有兩個(gè)。第一個(gè)是在使用 GROUP BY 子句聚合時(shí)會(huì)進(jìn)行排序，如果事先通過(guò)WHERE 子句篩選出一部分行，就能夠減輕排序的負(fù)擔(dān)。第二個(gè)是在WHERE 子句的條件里可以使用索引。HAVING 子句是針對(duì)聚合后生成的視圖進(jìn)行篩選的，但是很多時(shí)候聚合后的視圖都沒(méi)有繼承原表的索引結(jié)構(gòu) 。

三:索引是真的用到了嗎

以下都是索引失效的現(xiàn)象
1.索引字段上進(jìn)行計(jì)算

?

select * from SomeTable 
whre col_1 * 1.1 >100;

這種索引就會(huì)失效,執(zhí)行的時(shí)候會(huì)進(jìn)行全表的掃描。優(yōu)化的方法就是，把運(yùn)算的表達(dá)式放到查詢條件的右側(cè)

?

 select * from SomeTable whre col_1 >100 / 1.1;

其實(shí)只要索引列上使用函數(shù)的時(shí)候，索引列就會(huì)失效

?

select * from SomeTable
where SUBTR(col_1,1,1)='a'

2.使用 IS NULL 謂詞
通常，索引字段是不存在 NULL 的，所以指定 IS NULL 和 IS NOT
NULL 的話會(huì)使得索引無(wú)法使用，進(jìn)而導(dǎo)致查詢性能低下。

?

select * from SomeTable 
where col_1 is null;

3.使用否定形式

下面的幾種否定形式也不能用到索引

• <>
• !=
• NOT IN

?

select * from SomeTable 
where col_1 <> 100;

4.使用OR
在 col_1 和 col_2 上分別建立了不同的索引，或者建立了（col_1,col_2 ）這樣的聯(lián)合索引時(shí)，如果使用 OR 連接條件，那么要么用不到索引，要么用到了但是效率比 AND 要差很多。

?

SELECT *
FROM SomeTable
WHERE col_1 > 100
OR col_2 = 'abc';

5.使用聯(lián)合索引時(shí)，列的順序錯(cuò)誤

假設(shè)存在這樣順序的一個(gè)聯(lián)合索引col_1, col_2, col_3 。聯(lián)合索引中的第一列col_1必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒。如果無(wú)法保證查詢條件里列的順序與索引一致，可以考慮將聯(lián)合索引
拆分為多個(gè)索引。

?

○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;
× SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;
× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;
× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;

6.使用 LIKE 謂詞進(jìn)行后方一致或中間一致的匹配

?

× SELECT * FROM SomeTable WHERE col_1 LIKE '%a';
× SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';
○ SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

7.進(jìn)行默認(rèn)的類型轉(zhuǎn)換

?

× SELECT * FROM SomeTable WHERE col_1 = 10;
○ SELECT * FROM SomeTable WHERE col_1 = '10';
○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

默認(rèn)的類型轉(zhuǎn)換不僅會(huì)增加額外的性能開銷，還會(huì)導(dǎo)致索引不可用，可以說(shuō)是有百害而無(wú)一利。雖然這樣寫還不至于出錯(cuò)，但還是不要嫌麻煩，在需要類型轉(zhuǎn)換時(shí)顯式地進(jìn)行類型轉(zhuǎn)換吧（別忘了轉(zhuǎn)換要寫在條件表達(dá)式的右邊）。

四:減少中間表

在 SQL 中，子查詢的結(jié)果會(huì)被看成一張新表，這張新表與原始表一樣，可以通過(guò)代碼進(jìn)行操作。這種高度的相似性使得 SQL 編程具有非常強(qiáng)的靈活性，但是如果不加限制地大量使用中間表，會(huì)導(dǎo)致查詢性能下降。頻繁使用中間表會(huì)帶來(lái)兩個(gè)問(wèn)題，一是展開數(shù)據(jù)需要耗費(fèi)內(nèi)存資源，二是原始表中的索引不容易使用到（特別是聚合時(shí)）。因此，盡量減
少中間表的使用也是提升性能的一個(gè)重要方法。
1.靈活使用 HAVING 子句
對(duì)聚合結(jié)果指定篩選條件時(shí)，使用 HAVING 子句是基本原則。不習(xí)慣使用 HAVING 子句的數(shù)據(jù)庫(kù)工程師可能會(huì)傾向于像下面這樣先生成一張中間表，然后在 WHERE 子句中指定篩選條件。

?

SELECT *
FROM (SELECT sale_date, MAX(quantity) AS max_qty
FROM SalesHistory
GROUP BY sale_date) TMP ←----- 沒(méi)用的中間表
WHERE max_qty >= 10

然而，對(duì)聚合結(jié)果指定篩選條件時(shí)不需要專門生成中間表，像下面這樣使用 HAVING 子句就可以。

?

SELECT sale_date, MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING MAX(quantity) >= 10;

HAVING 子句和聚合操作是同時(shí)執(zhí)行的，所以比起生成中間表后再執(zhí)行的 WHERE 子句，效率會(huì)更高一些，而且代碼看起來(lái)也更簡(jiǎn)潔。

?

× SELECT * FROM SomeTable WHERE col_1 = 10;
○ SELECT * FROM SomeTable WHERE col_1 = '10';
○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

在面試中，當(dāng)面試官提出這一類問(wèn)題，按照上述的方法進(jìn)行回答都是沒(méi)有問(wèn)題的，但不僅在面試中，平時(shí)練習(xí)就養(yǎng)成習(xí)慣是最好的。大多數(shù)同學(xué)都會(huì)覺(jué)得“麻煩”、“不做也沒(méi)有什么影響”，但是習(xí)慣總是慢慢養(yǎng)成的。擁有好習(xí)慣，未來(lái)在工作中，面對(duì)不同的數(shù)據(jù)量，就可以游刃有余地選擇不同的方法來(lái)降低完成時(shí)間，從而提升工作效率。

?