zero

記錄我所知道的網路資訊

0%

SQL 達人的工作現場攻略筆記 - CH-11 要讓 SQL 加速囉

發表於 更新於 分類於

前言

SQL 的效能一直是 DB 工程師必須面對的主要課程之一，甚至有些人認為是唯一的課題。此文章是改善 SQL 的效能，讓執行速度更快減少更多資源。

使用效率的搜尋

若子查詢為參數

使用 EXISTS 代替 IN，在 IN 述詞很方便，也很好讀，但卻會遇到效能瓶頸的問題，若當 IN 參數 (1,2,3) 這種 List 時繼就不需要改寫，但如果以子查詢為參數就必須要注意，通常 NOT IN 跟 NOT EXISTS 都是回傳相同的結果，但是使用 EXISTS 建立子查詢卻快的很多。

Class_A

id (識別子) name (姓名)
1 郁
2 瑋
3 銘

Class_B

id (識別子) name (姓名)
1 郁
2 瑋
4 靚

試著從 Class_A 表找出 Class_B 資料表也有的學生。

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

結果:

id (識別子) name (姓名)
1 郁
2 瑋 
1
2
3
-- 為什麼 EXISTS 會比較快?大致上有兩個理由。
1. 如果連結key帶有索引值，就可能直接按照索引值，不需觀察 Class_B 的資料表。
2. EXISTS 只要找到一筆符合的列，就會立刻停止搜尋，不會是 IN 得全面搜尋一遍。

子查詢為參數時，使用連結代替 IN

要改善 IN 的效能，除了使用 EXISTS 改寫，也可以利用連結方式去實現。

1
SELECT A.id,A.name FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;

避開排序

SQL 與程式語言不同，使用者無法直接命令 DBMS 指定排序運算，不過這不代表 DBMS 不執行排序，更精準的說是 DBMS 非常頻繁的偷偷執行排序，也導致使用者不用去理會哪中運算正在執行排序。

會執行排序如下:

  1. GROUP BY。
  2. ORDER BY。
  3. 彙總函數 (SUM、COUNT、AVG、MAX、MIN)。
  4. DISTINCT。
  5. UNION、INTERSECT、EXCEPT。
  6. 視窗函數 (RANK ROW_NUMBER)。

善用集合運算的 ALL 選項

SQL 具有 UNION、INTERSECT、EXCEPT 三種集合運算子，如果直接使用一定會為了排除重複資料而執行排序。

1
2
3
SELECT * FROM Class_A
UNION
SELECT * FROM Class_B;

結果:

id (識別子) name (姓名)
1 郁
2 瑋
3 銘
4 靚

如果不用在乎重複資料或事先知道不會有重複資料，可以利用 UNION ALL 代替，避免執行排序。

以 EXISTS 代替 DISTINCT

DISTINCT 也會為了排除重複資料而執行排序。若是為了統整兩張表連結結果而使用 DISTINCT 的情況，也可以使用 EXISTS 代替，也能避免執行排序。

Items

item_no item
10 SD 記憶卡
20 CD-R
30 USB 隨身碟
40 DVD

SalesHistory

sale_date item_no quantity
2020-10-01 10 4
2020-10-01 20 10
2020-10-01 30 3
2020-10-03 10 32
2020-10-03 30 12
2020-10-04 20 22
2020-10-04 30 7

根據上方商品主資料表選出業績表的商品，簡單說就是找出有業績的商品，此時書中說使用 IN 雖然不錯。但前面提到用連結會更好。

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

結果:

item_no
10
10
20
20
30
30
30

由於是一對多連結，就會出現重複資料，若要將重複資料排除勢必使用 DISTINCT 那這樣就會執行到排序，所以書中說到最佳解答是使用 EXISTS，如此一來就不會執行排序。

1
2
3
SELECT item_no FROM Items I WHERE EXISTS(
    SELECT * FROM SalesHistory SH WHERE I.item_no = SH.item_no
);

於極值函數 MAX / MIN 使用索引值

SQL內建 MAX 跟 MIN 函數，使用時都會進行排序，但如果參數欄位已有索引值，就只需要掃描到該索引值，不需要搜尋整張表。

1
2
-- 這段會需要搜尋整張表格
SELECT MAX(item) FROM Items
1
2
-- 這段可以使用到索引值
SELECT MAX(item_no) FROM Items

由於 item_no 主鍵的索引值，所以效果更好，即時是複合索引值，只要有首欄位就能使用。雖然這種做法無法真的不排序，但至少可讓前半段搜尋變快，也能提高效率。

寫在 WHERE 的條件不寫在 HAVING 上

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

結果:

sale_date sum (quantity)
2007-10-01 17

從效能來看一定是後者，原因是第一個使用 GROUP BY 之後彙整進行排序與運算，所以減少列數，才能減輕排序時負擔。第二個就是 WHERE 條件可使用索引值。某種意義上，sale_date 應該是基數非常高的欄位，所以只要有索引值，應該就能讓列數有效率減少。

GROUP BY 以及 ORDER BY 使用索引值

這兩種方式雖然會執行排序，但只要在欄位上加上索引值，就能讓搜尋速度變快，尤其是 UNIQUE 索引值欄位，有些系統的規格更是能跳過排序。

索引值真的派得上用場嗎?

一般來說資料表有一定規模後，通常就會有索引值。把索引值想像成 C 語言的指標陣列，意思就是比起搜尋規模較大的物件，搜尋較小的指標會更有效率。

  1. 不是索引越多越好，索引可以加快查詢速度，但注意它是以空間換取時間。

  2. 懂的使用 Explain 來分析你的 SQL 索引性能解析，https://segmentfault.com/a/1190000008131735 。

  3. 索引不是建了就會使用。例如: SELECT * FROM user WHERE sex = 0 ( 0 代表女生 )，答案是不一定，如果大部份的值都是女生，那 MYSQL 會用全掃，如果大部份值為男生，那 MYSQL 會用索引。

  4. 連合索引的欄位順序，通常是由最左邊的欄位的才能使用。

  5. 儘可能使用索引的排序。

  6. 有時太多索引，反而會讓優化器混亂。

加工索引值欄位

1
2
3
4
5
--  NO
SELECT * FROM SomeTable WHERE col_1 * 1.1 > 100;

--  OK
SELECT * FROM SomeTable WHERE col_1 > 100 / 1.1

若於搜尋條件右側使用公式，就會用到索引值。因此可以改成下列方式。同樣的在左側使用函數就無法使用到索引值。

1
SELECT * FROM SomeTable WHERE SUBSTR(col_1, 1, 1) = 'a';

如果非要左邊進行運算，書中說到可以用函數索引，但這種方式很麻煩也不太建議。

  • 使用索引值時候，欄位最好是乾淨的。

索引值欄位有NULL

索引值有 NULL 是很難處理的，而且不同規格有不同處理的方式，這也是因為受到使用者使用 IS NULL 或是 IS NOT NULL，就無法使用索引值，或是 NULL 偏多的欄位無法使用索引的限制導致。此外該如何與 IS NOT NULL 同等條件使用索引值，可以使用下列方式

1
2
-- IS NOT NULL 替代方案
SELECT * FROM SomeTable WHERE col_1 > 0;

使用否定型的情況

列出否定型不會使用索引值

  • <>
  • !=
  • NOT IN

因此下列程式也無法取使用索引值。

1
SELECT * FROM SomeTable WHERE col_1 <> 100;

使用 OR 的情況

假設 col_1 與 col_2 各有索引值情況，或者 col_1, col_2 有複合索引，使 OR 撰寫連結條件，就無法使用索引值，而且就算可以使用，搜尋效率也會比 AND 差。

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

再複合索引下，欄位順序是錯誤的

假設以 col_1, col_2, col_3 這個順序下建立索引值，建立索引值欄位順序就相對重要。

1
2
3
4
5
6
7
8
-- O 
SELECT * FROM SomeTable WHERE col_1 ＝ 10 AND col_2 = 100 AND col_3 = 500;
-- O 
SELECT * FROM SomeTable WHERE col_1 ＝ 10 AND col_2 = 100;
-- X 
SELECT * FROM SomeTable WHERE col_1 ＝ 10 AND col_3 = 500;
-- X 
SELECT * FROM SomeTable WHERE col_2 ＝ 100 AND col_3 = 500;

第一個欄位一定寫在開頭，順序不能錯亂。某些 DB 雖然可能順序錯誤情況也可以使用索引值，但是效能還是比順序正確時來得低。

使用後方一致或中間一致的LIKE

1
2
3
4
5
6
-- X 
SELECT * FROM SomeTable WHERE col_1 LIKE '%10' ;
-- X 
SELECT * FROM SomeTable WHERE col_1 LIKE '%10%' ;
-- O 
SELECT * FROM SomeTable WHERE col_1 LIKE '10%' ;

會進行預設型態轉換

下列是對字串類型的 col_1 設定條件的情況。

1
2
3
4
5
6
-- X 
SELECT * FROM SomeTable WHERE col_1 = 10 ;
-- O 
SELECT * FROM SomeTable WHERE col_1 = '10' ;
-- O 
SELECT * FROM SomeTable WHERE col_1 = CAST(10,AS CHAR(2));

減少中介資料表

SQL 會將子查詢結果為新的資料表，也能在程式碼裡，像是操作原始資料表一般操作。這種高正交性讓 SQL 程式碼更加靈活，但若是大量使用中介資料也會造成性能下滑，所以盡可能減少中介資料。

1
2
3
4
SELECT * FROM(
    SELECT sale_date, MAX(quantity) AS max_qty FROM SalesHistory GROUP BY sale_date
) TMP --多餘的中介資料
WHERE max_qty >= 10;

不過，要將彙整的結果設定條件，是不需要建立多餘中介資料，只需要使用下列 HAVING 陳述句。

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

HAVING 會一邊執行，一邊進行彙整，所以會比建立中介資料表之後才執行 WHERE 更有效率，且可讀性比較好。

利用IN使用多個 KEY 時，將 KEY 整理在同一個位置

自從 SQL-92 之後，就內建許多列間比較的功能，所以 =、<、> 這類比較或 IN 的參數不僅可以是純量，還能是 LIST。讓我們想想下列這種使用多個 Key 組成 IN 內容情況。

1
2
3
4
5
6
7
SELECT id, state, city FROM Address1 A1 
WHERE 
status IN(
    SELECT status FROM Address2 A2 WHERE A1.id = A2.id      
) AND city IN (
    SELECT city FROM Address2 A2 WHERE A1.id = A2.id      
)
1
2
3
SELECT * FROM Address1 A1 WHERE id || status || city IN(
    SELECT id || state || city FROM Address1 A2);
)

如此一來既可將子查詢寫成非關聯式類型，還能將搜尋減少為一次，如果是支援列間比較 DB，還能如下在 IN 的參數設定欄位組合。

1
2
3
SELECT * FROM Address1 A1 WHERE (id, status, city) IN(
    SELECT id , state , city FROM Address1 A2);
)

相較於合併字串，這個方法有兩個優點。其一是不用擔心連結的類型轉換，另一個需要加工欄位，所以可使用索引值。

先連結在彙整

當需要連結使用，盡可能先連結在彙整，才能省去建立中介資料的步驟。之所以能夠省略，全在於集合運算的連結屬於乘法，若具一對一或是一對多的關聯性
，列數不會在連結之際增加。

計畫性地使用視圖

視圖是非常方便的工具，但如果隨便訂了複雜的視圖將使效能下降，尤其定義是圖的查詢含有下列運算時，SQL 的效率將變得更差，執行速度也會出乎意料的下滑。

總結

1.在 IN 使用子查詢，可以利用EXISTS或改寫連結。
2.使用索引值，基本上左邊是空白的。
3.SQL 雖然不需要明定排序部分，但有許多運算會偷偷執行排序，需多加留意。
4.盡可能減少不需要的中介資料。
5.趁早撰寫能減少紀錄筆數的條件。

參考資料:

  1. SQL 達人的工作現場攻略筆記。

  2. https://ithelp.ithome.com.tw/articles/10221971 。