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

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

?

-- 慢
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 時更快的原因有以下兩個。

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

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

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

?

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

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

二:避免排序

我們在查詢的時候，雖然我們沒有想要進行排序，但是在數(shù)據(jù)庫內(nèi)部頻繁地進行著暗中的排序。因此對于我們來說，了解都有哪些運算會進行排序很有必要，會進行排序的代表性的運算有下面這些

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

1.使用union all 代替union

?

select * from Class_A
union 
select * from Class_B 

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

?

select * from Class_A
union all
select * from Class_B;

2.使用exists 代替distinct

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

Items

?

SalesHistory

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

?

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

因為是一對多的連接，所以item_no列中會出現(xiàn)重復數(shù)據(jù)。為了排除重復數(shù)據(jù)，我們需要使用 DISTINCT 。

?

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

但是，使用distinct的時候會進行排序， 其實更好的做法是使用 EXISTS 。

?

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

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

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

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

?

SELECT MAX(item_no)
FROM Items;

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

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

• 聚合后使用HAVING 子句過濾

?

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

• 聚合前使用WHERE 子句過濾

?

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

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

三:索引是真的用到了嗎

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

?

select * from SomeTable 
whre col_1 * 1.1 >100;

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

?

 select * from SomeTable whre col_1 >100 / 1.1;

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

?

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

2.使用 IS NULL 謂詞
通常，索引字段是不存在 NULL 的，所以指定 IS NULL 和 IS NOT
NULL 的話會使得索引無法使用，進而導致查詢性能低下。

?

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)合索引時，如果使用 OR 連接條件，那么要么用不到索引，要么用到了但是效率比 AND 要差很多。

?

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

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

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

?

○ 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 謂詞進行后方一致或中間一致的匹配

?

× 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.進行默認的類型轉(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));

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

四:減少中間表

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

?

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

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

?

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

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

?

× 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));

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

?