某国产数据库原厂高级工程师找我优化SQL，以下是他给的三个案例。😼
案例一：
慢SQL和执行计划：
SELECT c.*FROM aaaaa aINNER JOIN bbbbbbbbbbb bON a.attend_rule_id = b.attend_rule_idINNER JOIN cccccccccc cON b.work_place_id = c.idINNER JOIN ddddddddddd dON a.attend_rule_id = d.idWHERE a.staff_id = '0001A11000000005DVBN'AND nvl(a.dr, 0) = '0'AND nvl(b.dr, 0) = '0'AND nvl(c.dr, 0) = '0'AND nvl(d.dr, 0) = '0';QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------Nested Loop (cost=0.55..258.52 rows=1 width=2253) (actual time=320.842..320.845 rows=0 loops=1)-> Nested Loop (cost=0.28..251.02 rows=1 width=33) (actual time=320.841..320.843 rows=0 loops=1)-> Nested Loop (cost=0.00..235.87 rows=1 width=99) (actual time=0.050..83.992 rows=3629 loops=1)Join Filter: ((b.attend_rule_id)::text = (d.id)::text)Rows Removed by Join Filter: 540055-> Seq Scan on bbbbbbbbbbb b (cost=0.00..210.09 rows=20 width=66) (actual time=0.021..2.125 rows=3630 loops=1)Filter: (NVL(int4(dr), 0) = 0)Rows Removed by Filter: 314-> Materialize (cost=0.00..25.19 rows=2 width=33) (actual time=0.000..0.006 rows=150 loops=3630)-> Seq Scan on ddddddddddd d (cost=0.00..25.18 rows=2 width=33) (actual time=0.013..0.171 rows=302 loops=1)Filter: (NVL(int4(dr), 0) = 0)Rows Removed by Filter: 45-> Index Scan using scopepsnindex on aaaaa a (cost=0.28..15.14 rows=1 width=33) (actual time=0.065..0.065 rows=0 loops=3629)Index Cond: ((attend_rule_id)::text = (b.attend_rule_id)::text)Filter: (((staff_id)::text = '0001A11000000005DVBN'::text) AND (NVL(int4(dr), 0) = 0))Rows Removed by Filter: 5-> Index Scan using pk_attend_place on cccccccccc c (cost=0.28..5.90 rows=1 width=2253) (never executed)Index Cond: ((id)::text = (b.work_place_id)::text)Filter: (NVL(int4(dr), 0) = 0)Planning Time: 3.101 msExecution Time: 321.016 ms
(21 rows)
他将 enable_nestloop 关闭以后强行后hash,速度快了不少，但是需要使用hint干预计划。
ncc=# set enable_nestloop=off;
SET
ncc=# explain analyze SELECT c.*
ncc-# FROM aaaaa a
ncc-# INNER JOIN bbbbbbbbbbb b
ncc-# ON a.attend_rule_id = b.attend_rule_id
ncc-# INNER JOIN cccccccccc c
ncc-# ON b.work_place_id = c.id
ncc-# INNER JOIN ddddddddddd d
ncc-# ON a.attend_rule_id = d.id
ncc-# WHERE a.staff_id = '0001A11000000005DVBN'
ncc-# AND nvl(a.dr, 0) = '0'
ncc-# AND nvl(b.dr, 0) = '0'
ncc-# AND nvl(c.dr, 0) = '0'
ncc-# AND nvl(d.dr, 0) = '0';QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------Hash Join (cost=144.46..354.61 rows=1 width=2253) (actual time=0.497..0.499 rows=0 loops=1)Hash Cond: ((a.attend_rule_id)::text = (d.id)::text)-> Hash Join (cost=119.26..329.41 rows=1 width=2319) (actual time=0.282..0.283 rows=0 loops=1)Hash Cond: ((b.attend_rule_id)::text = (a.attend_rule_id)::text)-> Hash Join (cost=71.19..281.33 rows=1 width=2286) (never executed)Hash Cond: ((b.work_place_id)::text = (c.id)::text)-> Seq Scan on bbbbbbbbbbb b (cost=0.00..210.09 rows=20 width=66) (never executed)Filter: (NVL(int4(dr), 0) = 0)-> Hash (cost=71.11..71.11 rows=6 width=2253) (never executed)-> Seq Scan on cccccccccc c (cost=0.00..71.11 rows=6 width=2253) (never executed)Filter: (NVL(int4(dr), 0) = 0)-> Hash (cost=48.05..48.05 rows=1 width=33) (actual time=0.278..0.279 rows=0 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 8kB-> Seq Scan on aaaaa a (cost=0.00..48.05 rows=1 width=33) (actual time=0.278..0.278 rows=0 loops=1)Filter: (((staff_id)::text = '0001A11000000005DVBN'::text) AND (NVL(int4(dr), 0) = 0))Rows Removed by Filter: 806-> Hash (cost=25.18..25.18 rows=2 width=33) (actual time=0.199..0.199 rows=302 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 28kB-> Seq Scan on ddddddddddd d (cost=0.00..25.18 rows=2 width=33) (actual time=0.013..0.148 rows=302 loops=1)Filter: (NVL(int4(dr), 0) = 0)Rows Removed by Filter: 45Planning Time: 1.556 msExecution Time: 0.585 ms
这种简单的语句我大概看了2秒种，给出改写方案，不带思考的。
SELECT c.*FROM aaaaa aINNER JOIN bbbbbbbbbbb bON a.attend_rule_id = b.attend_rule_idINNER JOIN cccccccccc cON b.work_place_id = c.idINNER JOIN ddddddddddd dON a.attend_rule_id = d.idWHERE a.staff_id = '0001A11000000005DVBN'AND CASE WHEN a.dr IS NULL THEN 0 ELSE a.dr END = 0AND CASE WHEN b.dr IS NULL THEN 0 ELSE b.dr END = 0AND CASE WHEN c.dr IS NULL THEN 0 ELSE c.dr END = 0AND CASE WHEN d.dr IS NULL THEN 0 ELSE d.dr END = 0;
案例二：
慢SQL和执行计划：
SELECT to_char(t1.signbegintime, 'YYYY-MM-DD HH24:MI:SS') AS workSignTime,to_char(t1.signendtime, 'YYYY-MM-DD HH24:MI:SS') AS workoffSignTime,t1.f_v_8 AS signType,to_char(t1.calendar, 'YYYY-MM-DD') AS calendar,to_char(t2.begintime, 'YYYY-MM-DD HH24:MI:SS') AS workTime,to_char(t2.endtime, 'YYYY-MM-DD HH24:MI:SS') AS workOffTimeFROM ts_daystat t1INNER JOIN ts_shift t2ON t1.shift_id = t2.idWHERE t1.staff_id = '0001A110000000062QPG'AND t1.calendar LIKE '%2024-05%'ORDER BY calendar ASC;QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------Sort (cost=98960.15..98960.15 rows=1 width=167) (actual time=436.621..493.088 rows=9 loops=1)Sort Key: (to_char((t1.calendar)::timestamp without time zone, 'YYYY-MM-DD'::text))Sort Method: quicksort Memory: 26kB-> Nested Loop (cost=1000.14..98960.14 rows=1 width=167) (actual time=123.310..493.061 rows=9 loops=1)-> Gather (cost=1000.00..98951.77 rows=1 width=63) (actual time=123.209..492.857 rows=9 loops=1)Workers Planned: 2Workers Launched: 2-> Parallel Seq Scan on ts_daystat t1 (cost=0.00..97951.67 rows=1 width=63) (actual time=184.979..430.729 rows=3 loops=3)Filter: (((staff_id)::text = '0001A110000000062QPG'::text) AND ((calendar)::text ~~ '%2024-05%'::text))Rows Removed by Filter: 199107-> Index Scan using pk_shift on ts_shift t2 (cost=0.14..8.16 rows=1 width=48) (actual time=0.006..0.006 rows=1 loops=9)Index Cond: ((id)::text = (t1.shift_id)::text)Planning Time: 0.890 msExecution Time: 493.383 ms
(14 rows)
这条SQL我也是简单喵了一眼，让原厂高级工程师加个索引 t1 表对 staff_id,shift_id 两个列加个索引，但是他对我的建议表示怀疑。😂
毕竟人家是原厂工程师，对他们家的产品更加专业，更加权威，专业性这块是毋庸置疑的。
我只能放低姿态，恳请原厂老师加条索引验证下我的错误，只有认识到自己的错误，才能更好得进步。🤓
加了索引以后的执行计划，已经从 493ms 降低到 1.5ms了。
我确实也很想反思一下自己，被原厂老师指正一下，认识自己的错误，才能进步。无奈可惜哥真的是太猛了，没啥值得反思的。😁
案例三：
慢SQL和执行计划：
SELECT a.*FROM bbbbbb bINNER JOIN eeeeee eON b.pk_psndoc = e.pk_psndocINNER JOIN (SELECT max(orgrelaid) AS orgrelaid, pk_psndocFROM eeeeeeWHERE indocflag = 'Y'GROUP BY pk_psndoc) tmpON e.pk_psndoc = tmp.pk_psndocAND e.orgrelaid = tmp.orgrelaidINNER JOIN aaaaaa aON a.pk_psnorg = e.pk_psnorgAND a.lastflag = 'Y'AND a.ismainjob = 'Y'INNER JOIN oooooo oON o.PK_ADMINORG = a.PK_ORGWHERE e.indocflag = 'Y'AND e.psntype = 0AND e.endflag = 'N'AND a.endflag = 'N'AND a.pk_group = '0001A110000000000JQ6'AND length(b.birthdate) = 10AND a.pk_hrorg IN ('0001A11000000000L7XF')AND substring(b.birthdate, 6, 5) >= '05-10'AND substring(b.birthdate, 6, 5) <= '06-09'ORDER BY substring(b.birthdate, 6, 5),substring(b.birthdate, 1, 4),a.PK_ORG,a.PK_DEPT,b.CODE;QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------Sort (cost=43067.37..43067.37 rows=1 width=1887) (actual time=21375.999..21376.005 rows=38 loops=1)Sort Key: ("substring"((b.birthdate)::text, 6, 5)), ("substring"((b.birthdate)::text, 1, 4)), a.pk_org, a.pk_dept, b.codeSort Method: quicksort Memory: 63kB-> Nested Loop (cost=8706.95..43067.36 rows=1 width=1887) (actual time=137.485..21375.586 rows=38 loops=1)-> Nested Loop (cost=8706.68..43067.03 rows=1 width=1834) (actual time=137.458..21374.882 rows=38 loops=1)-> Nested Loop (cost=8706.26..43064.13 rows=1 width=45) (actual time=54.801..21358.779 rows=620 loops=1)Join Filter: ((b.pk_psndoc)::text = (e.pk_psndoc)::text)-> Nested Loop (cost=8705.85..43062.82 rows=1 width=68) (actual time=54.750..21244.441 rows=2961 loops=1)Join Filter: ((b.pk_psndoc)::text = (eeeeee.pk_psndoc)::text)Rows Removed by Join Filter: 66180959-> Seq Scan on bbbbbb b (cost=0.00..32921.80 rows=1 width=45) (actual time=0.014..79.239 rows=2969 loops=1)Filter: (("substring"((birthdate)::text, 6, 5) >= '05-10'::text) AND ("substring"((birthdate)::text, 6, 5) <= '06-09'::text) AND (length((birthdate)::text)
= 10))Rows Removed by Filter: 41327-> HashAggregate (cost=8705.85..9147.44 rows=44159 width=23) (actual time=0.014..4.810 rows=22292 loops=2969)Group Key: eeeeee.pk_psndoc-> Seq Scan on eeeeee (cost=0.00..8484.91 rows=44187 width=23) (actual time=0.005..21.398 rows=44216 loops=1)Filter: (indocflag = 'Y'::bpchar)Rows Removed by Filter: 105-> Index Scan using i_hi_psnorg_1 on eeeeee e (cost=0.41..1.30 rows=1 width=44) (actual time=0.033..0.033 rows=0 loops=2961)Index Cond: ((pk_psndoc)::text = (eeeeee.pk_psndoc)::text)Filter: ((indocflag = 'Y'::bpchar) AND (psntype = 0) AND (endflag = 'N'::bpchar) AND ((max(eeeeee.orgrelaid)) = orgrelaid))Rows Removed by Filter: 1-> Index Scan using i_hi_psnjob_8 on aaaaaa a (cost=0.41..2.89 rows=1 width=1810) (actual time=0.024..0.024 rows=0 loops=620)Index Cond: ((pk_psnorg)::text = (e.pk_psnorg)::text)Filter: ((lastflag = 'Y'::bpchar) AND (ismainjob = 'Y'::bpchar) AND (endflag = 'N'::bpchar) AND (pk_group = '0001A110000000000JQ6'::bpchar) AND ((pk_hrorg)::text = '000
1A11000000000L7XF'::text))Rows Removed by Filter: 1-> Index Only Scan using i_pk_adminorg on oooooo o (cost=0.27..0.32 rows=1 width=21) (actual time=0.011..0.011 rows=1 loops=38)Index Cond: (pk_adminorg = a.pk_org)Heap Fetches: 0Planning Time: 4.773 msExecution Time: 21376.659 ms
(31 rows)
原厂大佬觉得可以加索引解决问题，但是在下确不这么认为，还是等价改了一个版本让原厂大佬测试下：
SELECT a.*FROM bbbbbb bINNER JOIN eeeeee eON b.pk_psndoc = e.pk_psndocINNER JOIN (SELECT max(orgrelaid) AS orgrelaid, pk_psndocFROM eeeeeeWHERE indocflag = 'Y' AND ROWNUM > 0GROUP BY pk_psndoc) tmpON e.pk_psndoc = tmp.pk_psndocAND e.orgrelaid = tmp.orgrelaidINNER JOIN aaaaaa aON a.pk_psnorg = e.pk_psnorgAND a.lastflag = 'Y'AND a.ismainjob = 'Y'INNER JOIN oooooo oON o.PK_ADMINORG = a.PK_ORGWHERE e.indocflag = 'Y'AND e.psntype = 0AND e.endflag = 'N'AND a.endflag = 'N'AND a.pk_group = '0001A110000000000JQ6'AND length(b.birthdate) = 10AND a.pk_hrorg IN ('0001A11000000000L7XF')AND substring(b.birthdate, 6, 5) >= '05-10'AND substring(b.birthdate, 6, 5) <= '06-09'ORDER BY substring(b.birthdate, 6, 5),substring(b.birthdate, 1, 4),a.PK_ORG,a.PK_DEPT,b.CODE;
改写后这条语句 2.3 秒能跑出结果，比之前速度快了10倍。
原厂大佬最后还不遗余力来请教我这条SQL优化的原理，这爱上进，爱学习的精神，真的是领人感到敬佩！！！😁😁😁
本文内容都是开玩笑的，大家当段子看就行。
