今天同事给我一条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优化已完成😎
![洛谷 P11345 [KTSC 2023 R2] 基地简化 题解](http://www.itfaba.com/wp-content/themes/kemi/timthumb.php?src=http://www.itfaba.com/wp-content/themes/kemi/img/random/3.jpg&w=218&h=124&zc=1)
