如何寫出更快的 SQL (db2)

更多內容關注公眾號：SAP Technical 

在資料庫開發的初期，或者在系統剛上線的初期，由於資料量比較少，一些查詢 SQL 語句、檢視、儲存過程編寫等體會不出 SQL 語句各種寫法的效能優劣，但是隨著資料庫中資料的增加，像資料倉儲這種 TB 級別的海量資料，劣質SQL語句和優質SQL語句之間的速度差別可以達到上百倍，因此寫 sql 不能簡單的能查出相應的資料即可，而是要寫出高質量的 SQL 語句，提高 SQL 語句的執行速度。

下面我就自己的工作經驗，分享一下如何寫出更快的 SQL

一、檢視執行計劃來選擇更快的 SQL

在寫 SQL 的初期，你可能不知道到底是使用 UNION ALL 好還是 FULL JOIN 好，是使用 EXISTS 好，還是使用 IN 好，那麼不防將這些語句都寫出來，看看資料庫的執行計劃怎麼說。

首先要明白什麼是執行計劃

執行計劃是資料庫根據 SQL 語句和相關表的統計資訊作出的一個查詢方案，這個方案是由查詢優化器自動分析產生的，比如一條 SQL 語句如果用來從一個 10 萬條記錄的表中查 1 條記錄，那查詢優化器會選擇索引查詢方式，如果該表進行了歸檔，當前只剩下 5000 條記錄了，那查詢優化器就會改變方案，採用全表掃描方式。

可見，執行計劃並不是固定的，它是個性化的。產生一個正確的“執行計劃”有兩點很重要：
(1) SQL語句是否清晰地告訴查詢優化器它想幹什麼？
(2) 查詢優化器得到的資料庫統計資訊是否是最新的、正確的？

比如現在有個這樣的需求：有兩個客戶資訊表 custinfo_a、 custinfo_b ，主健都是客戶號 custid，現要求對這兩個表的資訊進行整合，要求合併後的表主健仍是 custid，如果同一個 custid 在這兩個表都存在，優先取 custinfo_a 表的資訊。

此時你可能會想到三種寫法：

寫法一：使用 FULL JOIN

SELECT 
NVL(A.CUSTID,B.CUSTID)   AS USTID,--使用NVL優先取A表資訊為準
NVL(A.CUSTNAME,B.CUSTNAME) AS CUSTNAME
FROM CUSTINFO_A A
FULL JOIN CUSTINFO_B  B ON A.CUSTID = B.CUSTID

在 db2 的說明查詢中檢視其成本：

image.png

可以看到總成本為 9796.56，這裡不用關心這個數字的單位是什麼，只要知道它越大，查詢的就越慢。
其中的 TBSCAN 代表整表掃描，IXSCAN 代表索引掃描，可以看出 IXSCAN 的成本是很低的。

寫法二：使用 UNION ALL 和 NOT EXISTS

SELECT A.CUSTID,        a.CUSTNAME
FROM CUSTINFO_A A
UNION  ALL
SELECT b.CUSTID,       b.CUSTNAME
FROM CUSTINFO_b b
WHERE NOT EXISTS     (SELECT '1'      FROM CUSTINFO_A A      WHERE A.CUSTID = b.CUSTID)

在 db2 的說明查詢中檢視其成本：

 

 

可以看到總成本為 6375.67。

寫法三：使用 row_nubmer() over() 過濾

SELECT CUSTID,        CUSTNAME
FROM
(SELECT CUSTID,        CUSTNAME, 
          ROW_NUMBER()OVER(PARTITION BY CUSTID ORDER BY PRIORITY) BH 
    FROM
     (SELECT A.CUSTID, A.CUSTNAME, '1' AS PRIORITY       FROM CUSTINFO_A A
      UNION  ALL SELECT B.CUSTID, B.CUSTNAME, '2' AS PRIORITY      FROM CUSTINFO_B B
      )
)
WHERE BH =1

在 db2 的說明查詢中檢視其成本：

 

image.png

可以看到總成本為 6147.56

因此追求快速響應的的可以使用 方法三。

那麼如何使用 db2 的執行計劃呢？
windows 使用者，可以在程式中找到 控制中心，圖示如下圖所示：

image.png

 

點選開啟後，查詢相應的資料庫，右鍵選擇說明查詢，如下圖所示：

 

image.png

 

再將查詢的 SQL 貼上到輸入框中，確定即可看到上面所示的執行計劃圖，如果未登陸會要求讓你輸入使用者名稱密碼。

LINUX 或 AIX 使用者
在下面的指令碼中的 SQL 語句替換為你自己的 SQL ，執行此 shell 指令碼，即可生成 explain.out ，檢視 explain.out 可以看到和 windows 下一樣效果的文字資訊和更多的 CPU 、I/O 消耗等。

#!/bin/sh
db2 connect to edwdb user dsadm using dsadm 
db2 -tvf /home/edwinst/sqllib/misc/EXPLAIN.DDL
db2 set current explain mode explain

db2 "
SELECT 
NVL(A.CUSTID,B.CUSTID)   AS USTID,
NVL(A.CUSTNAME,B.CUSTNAME) AS CUSTNAME
 FROM edw.CUSTINFO_A A
FULL JOIN edw.CUSTINFO_B  B ON A.CUSTID = B.CUSTID
";

db2 set current explain mode no
db2exfmt -d edwdb -g TIC -w -1 -l -# 0 -s % -n % -o explain.out  #輸出資訊到檔案
#db2exfmt -d edwdb -g TIC -w -1 -l -# 0 -s % -n % -t  #輸出資訊到終端
db2 terminate

注意 /home/edwinst/sqllib/ 是 db2 的 HOME 路徑。

二、一些原則和經驗

• 避免全表掃描

Where 條件中儘可能少用否定，如 NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE，它們會引起全表掃描。那些可以過濾掉最大數量記錄的條件寫在 Where 子句的末尾。

• 避免Select *

Selcet 中每少提取一個欄位，資料的提取速度就會有相應的提升。提升的速度還要看您捨棄的欄位的大小來判斷。應避免使用Select * ，就算查詢記錄數，也不要使用 *，可以使用 select 1 from tablename 。

• 用 Where 子句替代 having 子句

避免使用 having 子句，having 只會在檢索出所有記錄之後才對結果集進行過濾。

• exists 代替 in

當（）中的資料量較大時使用 exists() ，較少時可以使用 in ()。

• IS NULL 與 IS NOT NULL

資料庫不能用 NULL 作索引，任何包含 NULL 值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有 NULL ，該列就會從索引中排除。也就是說如果某列存在 NULL 值，即使對該列建索引也不會提高效能。任何在 where 子句中使用 IS NULL 或 IS NULL 的語句優化器是不使用索引的。

• 聯接列

對於有聯接的列，即使最後的聯接值為一個靜態值，優化器是不會使用索引的。
like ‘%xx%’ 不會執行索引
like ‘y%xx%’ 會執行索引

• 用 TRUNCATE 替代 DELETE 來清空一個表

當刪除表中的記錄時，在通常情況下, 回滾段 (rollback segments ) 用來存放可以被恢復的資訊。如果你沒有COMMIT 事務，db2 可以將資料恢復到刪除之前的狀態，而當運用 TRUNCATE 時, 回滾段不再存放任何可被恢復的資訊，當命令執行後，資料不能被恢復，因此很少的資源被呼叫，執行時間也會很短，TRUNCATE 只在刪除全表適用。

• 用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN：

在許多基於基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接。在這種情況下， 使用EXISTS（或 NOT EXISTS）通常將提高查詢的效率. 在子查詢中，NOT IN 子句將執行一個內部的排序和合並。無論在哪種情況下，NOT IN 都是最低效的（因為它對子查詢中的表執行了一個全表遍歷）。為了避免使用NOT IN ,我們可以把它改寫成外連線(Outer Joins)或NOT EXISTS.
例子：

（高效）SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’) 
（低效）SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)

• 用索引提高效率

使用索引同樣能提高效率，但是我們也必須注意到它的代價，索引需要空間來儲存，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味著每條記錄的 INSERT、DELETE 、UPDATE 將為此多付出 4 , 5 次的磁碟 I/O 。因為索引需要額外的儲存空間和處理，那些不必要的索引反而會使查詢反應時間變慢，定期的重構索引是有必要的：

ALTER INDEX REBUILD

• 用 EXISTS 替換 DISTINCT

當提交一個包含一對多表資訊（比如部門表和僱員表）的查詢時，避免在SELECT 子句中使用 DISTINCT， 一般可以考慮用 EXIST 替換, EXISTS 使查詢更為迅速，因為 RDBMS 核心模組將在子查詢的條件一旦滿足後，立刻返回結果。

(低效): SELECT  DISTINCT  DEPT_NO,DEPT_NAME  FROM  DEPT D , EMP E 
WHERE  D.DEPT_NO = E.DEPT_NO 
(高效): SELECT  DEPT_NO,DEPT_NAME  FROM  DEPT D  WHERE  EXISTS ( SELECT ‘X' 
FROM  EMP E  WHERE E.DEPT_NO = D.DEPT_NO); 

• 避免在索引列上使用 NOT

我們要避免在索引列上使用 NOT ， NOT 會產生在和在索引列上使用函式相同的影響，會導致使用索引轉而執行全表掃描。

• 避免在索引列上使用計算

WHERE 子句中，如果索引列是函式的一部分．優化器將不使用索引而使用全表掃描．

• 用>=替代>

高效: 
 SELECT * FROM  EMP  WHERE  DEPTNO >=4 
低效: 
 SELECT * FROM  EMP  WHERE  DEPTNO >3 

兩者的區別在於， 前者 DBMS 將直接跳到第一個 DEPT 等於 4 的記錄而後者將首先定位到 DEPTNO =3 的記錄並且向前掃描到第一個 DEPT 大於 3 的記錄。

• 用 UNION 替換 OR (適用於索引列)

通常情況下， 用 UNION 替換 WHERE 子句中的 OR 將會起到較好的效果，對索引列使用 OR 將造成全表掃描。注意， 以上規則只針對多個索引列有效。如果有 column 沒有被索引， 查詢效率可能會因為你沒有選擇 OR 而降低。在下面的例子中， LOC _ ID 和 REGION 上都建有索引：

高效: 
 SELECT  LOC _ ID ， LOC _ DESC ， REGION 
 FROM  LOCATION 
 WHERE  LOC _ ID = 10 
 UNION 
 SELECT  LOC _ ID ， LOC _ DESC ， REGION 
 FROM  LOCATION 
 WHERE  REGION = “ MELBOURNE ” 
低效: 
 SELECT  LOC _ ID ， LOC _ DESC ， REGION 
 FROM  LOCATION 
 WHERE  LOC _ ID = 10 OR  REGION = “ MELBOURNE ” 

如果你堅持要用 OR ， 那就需要返回記錄最少的索引列寫在最前面。

• 總是使用索引的第一個列

如果索引是建立在多個列上， 只有在它的第一個列（leading column）被 where 子句引用時，優化器才會選擇使用該索引。這也是一條簡單而重要的規則，當僅引用索引的第二個列時，優化器使用了全表掃描而忽略了索引 。

• 用 UNION - ALL 替換 UNION ( 如果有可能的話)

UNION ALL 將重複輸出兩個結果集合中相同記錄，UNION 將對結果集合排序，這個操作會使用到 SORT_AREA_SIZE 這塊記憶體. 對於這塊記憶體的優化也是相當重要的。

• 用 WHERE 替代 ORDER BY ：

ORDER BY 子句只在兩種嚴格的條件下使用索引。
ORDER BY 中所有的列必須包含在相同的索引中並保持在索引中的排列順序。
ORDER BY 中所有的列必須定義為非空。
WHERE 子句使用的索引和 ORDER BY 子句中所使用的索引不能並列。

• 避免使用耗費資源的操作:

帶有 DISTINCT ， UNION ， MINUS ， INTERSECT ， ORDER BY 的 SQL 語句會啟動 SQL 引擎
執行耗費資源的排序( SORT )功能. DISTINCT 需要一次排序操作， 而其他的至少需要執行兩次排序。通常， 帶有 UNION ， MINUS ， INTERSECT 的 SQL 語句都可以用其他方式重寫，如果你的資料庫的 SORT_AREA_SIZE 調配得好， 使用 UNION ， MINUS ， INTERSECT 也是可以考慮的， 畢竟它們的可讀性很強。