今天同事给我一条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 T服务器托管网1
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优化已完成
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
相关推荐: 新版发布 | Cloudpods v3.10.3 和 v3.9.11 正式发布
Cloudpods v3.10.3 新功能 【认证】支持用户在个人中心查看消息订阅 【容器】Helm release 实例前端支持升级与回滚 【关于】增加平台升级记录（企业版） 【巡检】巡检报告支持下载（企业版） 功能优化 【主机】ssh 登陆支持密码检查 【…
