今天同事給我一條5秒的SQL看看能不能最佳化。
表資料量：
select count(1) from AAAA
union all
select count(1) from XXXXX;
count
---------
1000001
998000
(2 rows)
原始SQL：
SELECT A1.PK_DEPT, A1.ENABLESTATE
FROM AAAA A1
JOIN AAAA A2 ON A1.PK_DEPT = A2.PK_DEPT
WHERE ((A1.PK_GROUP = 'Group9' AND A1.PK_ORG IN ('Org9')))
AND (A1.PK_DEPT IN (SELECT T1.ORGID
FROM XXXXX T1
INNER JOIN (SELECT (CASE WHEN ORGID3 IS NULL THEN ORGID2 ELSE ORGID3 END) ORGID
FROM XXXXX
WHERE ORGID = 'Org108') T2
ON (T1.ORGID2 = T2.ORGID OR T1.ORGID3 = T2.ORGID)))
AND (A1.ENABLESTATE IN (2))
ORDER BY A1.PK_DEPT, A1.ENABLESTATE;
執行計劃：
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
Sort (cost=16098.39..16098.40 rows=1 width=13) (actual time=5435.964..5454.953 rows=1000000 loops=1)
Sort Key: a1.pk_dept
Sort Method: quicksort Memory: 79264kB
-> Nested Loop Semi Join (cost=1039.46..16098.38 rows=1 width=13) (actual time=0.389..5338.781 rows=1000000 loops=1)
Join Filter: ((a1.pk_dept)::text = (t1.orgid)::text)
-> Gather (cost=1038.61..16089.43 rows=1 width=22) (actual time=0.368..55.998 rows=1000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=38.61..15089.33 rows=1 width=22) (actual time=0.246..49.481 rows=333333 loops=3)
Hash Cond: ((a2.pk_dept)::text = (a1.pk_dept)::text)
-> Parallel Seq Scan on aaaa a2 (cost=0.00..13491.33 rows=415833 width=9) (actual time=0.009..14.206 rows=332667 loops=3)
-> Hash (cost=38.60..38.60 rows=1 width=13) (actual time=0.193..0.195 rows=1000 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 51kB
-> Bitmap Heap Scan on aaaa a1 (cost=34.58..38.60 rows=1 width=13) (actual time=0.068..0.142 rows=1000 loops=3)
Recheck Cond: (((pk_org)::text = 'Org9'::text) AND ((pk_group)::text = 'Group9'::text))
Filter: (enablestate = 2)
Heap Blocks: exact=9
-> BitmapAnd (cost=34.58..34.58 rows=1 width=0) (actual time=0.062..0.063 rows=0 loops=3)
-> Bitmap Index Scan on idx_aaaa_pkorg (cost=0.00..17.17 rows=632 width=0) (actual time=0.031..0.031 rows=1000 loops=3)
Index Cond: ((pk_org)::text = 'Org9'::text)
-> Bitmap Index Scan on idx_aaaa_pkgroup (cost=0.00..17.17 rows=632 width=0) (actual time=0.030..0.030 rows=1000 loops=3)
Index Cond: ((pk_group)::text = 'Group9'::text)
-> Nested Loop (cost=0.85..8.94 rows=1 width=9) (actual time=0.005..0.005 rows=1 loops=1000000)
Join Filter: (((t1.orgid2)::text = (CASE WHEN (xxxxx.orgid3 IS NULL) THEN xxxxx.orgid2 ELSE xxxxx.orgid3 END)::text) OR ((t1.orgid3)::text = (CASE WHEN (xxxxx.orgid3 IS
NULL) THEN xxxxx.orgid2 ELSE xxxxx.orgid3 END)::text))
-> Index Scan using idx_xxxxx_orgid on xxxxx t1 (cost=0.42..0.49 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=1000000)
Index Cond: ((orgid)::text = (a2.pk_dept)::text)
-> Index Scan using idx_3_4 on xxxxx (cost=0.42..8.44 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=1000000)
Index Cond: ((orgid)::text = 'Org108'::text)
Planning Time: 0.326 ms
Execution Time: 5478.431 ms
(30 rows)
如果經常做最佳化的同學對於簡單的SQL，相信可以使用瞪眼大法基本定位到語句慢的位置 ?
AAAA、XXXXX 兩張表都不算是小表，資料量在百萬級別，在執行計劃中，謂詞都是有索引進行過濾的。
但是兩張表關聯以後卻走了巢狀迴圈（Nested Loop），導致t1表和t2表關聯後的內聯檢視作為被驅動表被幹了1000000次，很明顯這個執行計劃是錯誤的。
最主要原因就是關聯條件是or的邏輯條件。
可以透過等價改寫來搞一下這條SQL，讓 Nested Loop 改變成 hash join ? 等價改寫SQL：
SELECT A1.PK_DEPT, A1.ENABLESTATE
FROM AAAA A1
JOIN AAAA A2 ON A1.PK_DEPT = A2.PK_DEPT
JOIN (SELECT T1.ORGID
FROM XXXXX T1
INNER JOIN (SELECT COALESCE(ORGID3, ORGID2) ORGID FROM XXXXX WHERE ORGID = 'Org108') T2
ON T1.ORGID2 = T2.ORGID
UNION
SELECT T1.ORGID
FROM XXXXX T1
INNER JOIN (SELECT COALESCE(ORGID3, ORGID2) ORGID FROM XXXXX WHERE ORGID = 'Org108') T2
ON T1.ORGID3 = T2.ORGID) X ON A1.PK_DEPT = X.ORGID
WHERE ((A1.PK_GROUP = 'Group9' AND A1.PK_ORG IN ('Org9')))
AND (A1.ENABLESTATE IN (2))
ORDER BY A1.PK_DEPT, A1.ENABLESTATE;
改寫後執行計劃：
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1072.44..16123.32 rows=1 width=13) (actual time=190.512..312.537 rows=1000000 loops=1)
Join Filter: ((a1.pk_dept)::text = (t1.orgid)::text)
Rows Removed by Join Filter: 3000000
-> Unique (cost=33.83..33.84 rows=2 width=516) (actual time=0.073..0.086 rows=4 loops=1)
-> Sort (cost=33.83..33.84 rows=2 width=516) (actual time=0.072..0.079 rows=5 loops=1)
Sort Key: t1.orgid
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.85..33.82 rows=2 width=516) (actual time=0.037..0.068 rows=5 loops=1)
-> Nested Loop (cost=0.85..16.90 rows=1 width=9) (actual time=0.037..0.045 rows=2 loops=1)
-> Index Scan using idx_3_4 on xxxxx (cost=0.42..8.44 rows=1 width=18) (actual time=0.022..0.023 rows=2 loops=1)
Index Cond: ((orgid)::text = 'Org108'::text)
-> Index Scan using idx_xxxxx_orgid2 on xxxxx t1 (cost=0.42..8.44 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=2)
Index Cond: ((orgid2)::text = (COALESCE(xxxxx.orgid3, xxxxx.orgid2))::text)
-> Nested Loop (cost=0.85..16.90 rows=1 width=9) (actual time=0.014..0.021 rows=3 loops=1)
-> Index Scan using idx_3_4 on xxxxx xxxxx_1 (cost=0.42..8.44 rows=1 width=18) (actual time=0.003..0.003 rows=2 loops=1)
Index Cond: ((orgid)::text = 'Org108'::text)
-> Index Scan using idx_xxxxx_orgid3 on xxxxx t1_1 (cost=0.42..8.44 rows=1 width=18) (actual time=0.008..0.008 rows=2 loops=2)
Index Cond: ((orgid3)::text = (COALESCE(xxxxx_1.orgid3, xxxxx_1.orgid2))::text)
-> Materialize (cost=1038.61..16089.43 rows=1 width=22) (actual time=0.096..43.254 rows=1000000 loops=4)
-> Gather (cost=1038.61..16089.43 rows=1 width=22) (actual time=0.384..44.877 rows=1000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=38.61..15089.33 rows=1 width=22) (actual time=0.257..48.484 rows=333333 loops=3)
Hash Cond: ((a2.pk_dept)::text = (a1.pk_dept)::text)
-> Parallel Seq Scan on aaaa a2 (cost=0.00..13491.33 rows=415833 width=9) (actual time=0.009..14.053 rows=332667 loops=3)
-> Hash (cost=38.60..38.60 rows=1 width=13) (actual time=0.217..0.219 rows=1000 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 51kB
-> Bitmap Heap Scan on aaaa a1 (cost=34.58..38.60 rows=1 width=13) (actual time=0.085..0.160 rows=1000 loops=3)
Recheck Cond: (((pk_org)::text = 'Org9'::text) AND ((pk_group)::text = 'Group9'::text))
Filter: (enablestate = 2)
Heap Blocks: exact=9
-> BitmapAnd (cost=34.58..34.58 rows=1 width=0) (actual time=0.077..0.078 rows=0 loops=3)
-> Bitmap Index Scan on idx_aaaa_pkorg (cost=0.00..17.17 rows=632 width=0) (actual time=0.039..0.039 rows=1000 loops=3)
Index Cond: ((pk_org)::text = 'Org9'::text)
-> Bitmap Index Scan on idx_aaaa_pkgroup (cost=0.00..17.17 rows=632 width=0) (actual time=0.035..0.036 rows=1000 loops=3)
Index Cond: ((pk_group)::text = 'Group9'::text)
Planning Time: 0.236 ms
Execution Time: 337.656 ms
(38 rows)
差集比對
SELECT A1.PK_DEPT, A1.ENABLESTATE
FROM AAAA A1
JOIN AAAA A2 ON A1.PK_DEPT = A2.PK_DEPT
JOIN (SELECT T1.ORGID
FROM XXXXX T1
INNER JOIN (SELECT COALESCE(ORGID3, ORGID2) ORGID FROM XXXXX WHERE ORGID = 'Org108') T2
ON T1.ORGID2 = T2.ORGID
UNION
SELECT T1.ORGID
FROM XXXXX T1
INNER JOIN (SELECT COALESCE(ORGID3, ORGID2) ORGID FROM XXXXX WHERE ORGID = 'Org108') T2
ON T1.ORGID3 = T2.ORGID) X ON A1.PK_DEPT = X.ORGID
WHERE ((A1.PK_GROUP = 'Group9' AND A1.PK_ORG IN ('Org9')))
AND (A1.ENABLESTATE IN (2))
EXCEPT
SELECT A1.PK_DEPT, A1.ENABLESTATE
FROM AAAA A1
JOIN AAAA A2 ON A1.PK_DEPT = A2.PK_DEPT
WHERE ((A1.PK_GROUP = 'Group9' AND A1.PK_ORG IN ('Org9')))
AND (A1.PK_DEPT IN (SELECT T1.ORGID
FROM XXXXX T1
INNER JOIN (SELECT (CASE WHEN ORGID3 IS NULL THEN ORGID2 ELSE ORGID3 END) ORGID
FROM XXXXX
WHERE ORGID = 'Org108') T2
ON (T1.ORGID2 = T2.ORGID OR T1.ORGID3 = T2.ORGID))
)
AND (A1.ENABLESTATE IN (2));
pk_dept | enablestate
---------+-------------
(0 rows)
Time: 5740.419 ms (00:05.740)
可以看到改寫完以後，A1和A2表已經被物化，t1 內聯檢視作為一個整體和A1和A2進行關聯，SQL執行時間也從5S降到337ms就能出結果。
透過差集比對，兩條SQL是等價的，本次案例的SQL最佳化已完成?