OceanBase 金融项目优化案例
作者:不在线第一只蜗牛
- 2024-06-13 福建
本文字数:10916 字
阅读完需:约 36 分钟
![](https://static001.geekbang.org/infoq/a8/a89aaa47bd4c760f1dc32f43061a5724.png)
领导让我帮忙支持下其他项目的 SQL 优化工作,呦西,是收集案例的好机会。😍
下面 SQL 都是在不能远程的情况下,按照原 SQL 的逻辑等价改写完成发给现场同学验证。
案例一
慢 SQL,4.32 秒:
SELECT MY_.*, RM
FROM (SELECT ROWNUM RM, V_.*
FROM (SELECT *
FROM (select count(1) processidnum,
t.processid,
t.proc_name_ as procname
FROM tkdkdkdk t
WHERE 1 = 1
and (t.ASSIGNEE_ = 'server' or exists(select 1
FROM pepepep p
WHERE p.task_ = t.ID_
and (p.agent_userid_ = 'server' or
(substr(p.groupid_, 6) in
(select role_code
FROM upupupup
WHERE user_code = 'server') or
p.userid_ = 'server'))))
GROUP BY t.processid, t.proc_name_)) V_
WHERE ROWNUM <= 100000) MY_
WHERE RM >= 1;
复制代码
慢 SQL 执行计划:
![](https://static001.geekbang.org/infoq/be/be4bff238a2a4f5e9fb748e08ad36b0b.png)
改写优化,445ms:
SELECT *
FROM (SELECT *
FROM (SELECT a.*,
rownum rn
FROM (SELECT count(1) processidnum,
t.processid,
t.proc_name_ AS procname
FROM tkdkdkdk t
LEFT JOIN
(SELECT distinct p.task_
FROM pepepep p
LEFT JOIN
(SELECT role_code
FROM upupupup
WHERE user_code = 'server'
GROUP BY role_code) tsu
ON (substr(p.groupid_, 6) = tsu.role_code)
WHERE (p.agent_userid_ = 'server'
OR (tsu.role_code is NOT null
OR p.userid_ = 'server'))) x
ON t.ID_ = x.task_
WHERE 1 = 1
AND (t.ASSIGNEE_ = 'server'
OR x.task_ is NOT NULL)
GROUP BY t.processid, t.proc_name_) a)
WHERE rownum <= 100000)
WHERE rn >= 1;
复制代码
改写优化后执行计划:
![](https://static001.geekbang.org/infoq/cf/cfe48996b8f47343df477d24e78fbd81.png)
优化思路:
1、原 SQL 有很多子查询,可能会导致计划走 NL,改成 JOIN 后让 CBO 自动判断是否走 HASH 还是 NL。
2、换了个标准的分页框架。
案例二
慢 SQL,2.6 秒:
SELECT MY_.*, RM
FROM (SELECT ROWNUM RM, V_.*
FROM (SELECT *
FROM (select t.*, t.org_code || '-' || t.org_name as codename
FROM (select tc.*
FROM tgtgtgtg tc
start with TC.ORG_ID = '6000001'
connect by prior ORG_ID = tc.parent_id) t
WHERE org_level <= 3
ORDER BY CASE
WHEN ',' || nvl(null, 'fingard') || ',' like '%,' || ORG_ID || ',%' THEN CASE
WHEN length(nvl(org_order, '')) = '9'
then org_order || ''
else '1' || org_code end
when length(nvl(org_order, '')) = '9' then '99999999' || org_order || ''
else '999999991' || org_code end)) V_
WHERE ROWNUM <= 10) MY_;
WHERE RM >= 1;
复制代码
![](https://static001.geekbang.org/infoq/e3/e375ade83bae52fece5f9f1e81e6b4bc.png)
改写优化一,3.4 秒:
SELECT MY_.*, RM
FROM (SELECT ROWNUM RM, V_.*
FROM (SELECT *
FROM (select a.*, a.org_code || '-' || a.org_name as codename
FROM (WITH t(
lv,
codename,
ORG_ID,
parent_id,
org_order,
org_code,
org_name,
org_level
) AS (SELECT 1 as lv,
tc.org_code || '-' || tc.org_name AS codename,
tc.org_name,
tc.ORG_ID,
tc.parent_id,
tc.org_order,
tc.org_code,
tc.org_level
FROM tgtgtgtg tc
WHERE tc.ORG_ID = '6000001'
UNION ALL
SELECT t.lv + 1,
e.org_code || '-' || e.org_name AS codename,
e.org_name,
e.ORG_ID,
e.parent_id,
e.org_order,
e.org_code,
e.org_level
FROM tgtgtgtg e
INNER JOIN t ON t.ORG_ID = e.parent_id)
SELECT *
FROM t) a
WHERE a.org_level <= 3
ORDER BY CASE
WHEN ',' || nvl(null, 'fingard') || ',' like '%,' || ORG_ID || ',%' THEN CASE
WHEN length(nvl(org_order, '')) = '9'
then org_order || ''
else '1' || org_code end
when length(nvl(org_order, '')) = '9' then '99999999' || org_order || ''
else '999999991' || org_code end)) V_
WHERE ROWNUM <= 10) MY_;
WHERE RM >= 1;
复制代码
![](https://static001.geekbang.org/infoq/78/7872364f1a9c6176a6e714aed088afa2.png)
使用 CTE 递归改写方案在 PostgreSQL 上是个通用的做法,也能取得比较好的性能效果。
但是在 OB 上反而效果更差点,NL 算子性能不够强,使用 NESTED-LOOP JOIN 性能反而没有 NESTED-LOOP CONNECT BY 算子好。
OB 研发在 NESTED-LOOP JOIN 算子上还有继续优化的空间。
改写优化二,1.5 秒:
既然使用 NL 性能不够理想的情况下,就要想办法使用 HASH 来优化 SQL 整体的执行效率。
将自动递归的方式改成手动。
1、首先需要知道数据整体的层级有多少。
SELECT DISTINCT lv
FROM (SELECT level lv
FROM tgtgtgtg tc
START WITH TC.ORG_ID = '6000001'
CONNECT BY PRIOR ORG_ID = tc.parent_id) t;
复制代码
![](https://static001.geekbang.org/infoq/ca/cad37be9550a8dc03af89871c67b35e6.png)
2、了解到整体的数据是 13 层,然后使用 self join 将不同层级的数据关联起来。
1 SELECT *
2 FROM (SELECT *
3 FROM (SELECT a.*, rownum rn
4 FROM (SELECT x.*
5 FROM (WITH tgtgtgtg AS
6 (SELECT org_code, org_name, org_id, parent_id, org_order, org_level
7 FROM tgtgtgtg)
8
9 SELECT 1 AS lv,
10 v1.org_code || '-' || v1.org_name AS codename,
11 v1.ORG_ID,
12 v1.parent_id,
13 v1.org_order,
14 v1.org_code,
15 v1.org_level
16 FROM tgtgtgtg v1
17 WHERE v1.ORG_ID = '6000001'
18
19 UNION ALL
20
21 SELECT 2 AS lv,
22 v2.org_code || '-' || v2.org_name AS codename,
23 v2.ORG_ID,
24 v2.parent_id,
25 v2.org_order,
26 v2.org_code,
27 v2.org_level
28 FROM tgtgtgtg v1
29 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
30 WHERE v1.ORG_ID = '6000001'
31
32 UNION ALL
33
34 SELECT 3 AS lv,
35 v3.org_code || '-' || v3.org_name AS codename,
36 v3.ORG_ID,
37 v3.parent_id,
38 v3.org_order,
39 v3.org_code,
40 v3.org_level
41 FROM tgtgtgtg v1
42 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
43 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
44 WHERE v1.ORG_ID = '6000001'
45
46 UNION ALL
47
48 SELECT 4 AS lv,
49 v4.org_code || '-' || v4.org_name AS codename,
50 v4.ORG_ID,
51 v4.parent_id,
52 v4.org_order,
53 v4.org_code,
54 v4.org_level
55 FROM tgtgtgtg v1
56 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
57 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
58 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
59 WHERE v1.ORG_ID = '6000001'
60
61 UNION ALL
62
63 SELECT 5 AS lv,
64 v5.org_code || '-' || v5.org_name AS codename,
65 v5.ORG_ID,
66 v5.parent_id,
67 v5.org_order,
68 v5.org_code,
69 v5.org_level
70 FROM tgtgtgtg v1
71 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
72 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
73 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
74 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
75 WHERE v1.ORG_ID = '6000001'
76
77 UNION ALL
78
79 SELECT 6 AS lv,
80 v6.org_code || '-' || v6.org_name AS codename,
81 v6.ORG_ID,
82 v6.parent_id,
83 v6.org_order,
84 v6.org_code,
85 v6.org_level
86 FROM tgtgtgtg v1
87 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
88 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
89 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
90 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
91 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
92 WHERE v1.ORG_ID = '6000001'
93
94 UNION ALL
95
96 SELECT 7 AS lv,
97 v7.org_code || '-' || v7.org_name AS codename,
98 v7.ORG_ID,
99 v7.parent_id,
100 v7.org_order,
101 v7.org_code,
102 v7.org_level
103 FROM tgtgtgtg v1
104 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
105 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
106 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
107 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
108 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
109 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
110 WHERE v1.ORG_ID = '6000001'
111
112 UNION ALL
113
114 SELECT 8 AS lv,
115 v8.org_code || '-' || v8.org_name AS codename,
116 v8.ORG_ID,
117 v8.parent_id,
118 v8.org_order,
119 v8.org_code,
120 v8.org_level
121 FROM tgtgtgtg v1
122 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
123 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
124 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
125 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
126 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
127 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
128 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
129 WHERE v1.ORG_ID = '6000001'
130
131 UNION ALL
132
133 SELECT 9 AS lv,
134 v9.org_code || '-' || v9.org_name AS codename,
135 v9.ORG_ID,
136 v9.parent_id,
137 v9.org_order,
138 v9.org_code,
139 v9.org_level
140 FROM tgtgtgtg v1
141 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
142 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
143 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
144 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
145 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
146 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
147 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
148 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
149 WHERE v1.ORG_ID = '6000001'
150
151 UNION ALL
152
153 SELECT 10 AS lv,
154 v10.org_code || '-' || v10.org_name AS codename,
155 v10.ORG_ID,
156 v10.parent_id,
157 v10.org_order,
158 v10.org_code,
159 v10.org_level
160 FROM tgtgtgtg v1
161 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
162 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
163 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
164 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
165 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
166 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
167 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
168 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
169 JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
170 WHERE v1.ORG_ID = '6000001'
171
172 UNION ALL
173
174 SELECT 11 AS lv,
175 v11.org_code || '-' || v11.org_name AS codename,
176 v11.ORG_ID,
177 v11.parent_id,
178 v11.org_order,
179 v11.org_code,
180 v11.org_level
181 FROM tgtgtgtg v1
182 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
183 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
184 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
185 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
186 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
187 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
188 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
189 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
190 JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
191 JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id
192 WHERE v1.ORG_ID = '6000001'
193
194 UNION ALL
195
196 SELECT 12 AS lv,
197 v12.org_code || '-' || v12.org_name AS codename,
198 v12.ORG_ID,
199 v12.parent_id,
200 v12.org_order,
201 v12.org_code,
202 v12.org_level
203 FROM tgtgtgtg v1
204 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
205 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
206 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
207 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
208 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
209 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
210 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
211 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
212 JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
213 JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id
214 JOIN tgtgtgtg v12 ON v11.ORG_ID = v12.parent_id
215 WHERE v1.ORG_ID = '6000001'
216
217 UNION ALL
218
219 SELECT 13 AS lv,
220 v13.org_code || '-' || v13.org_name AS codename,
221 v13.ORG_ID,
222 v13.parent_id,
223 v13.org_order,
224 v13.org_code,
225 v13.org_level
226 FROM tgtgtgtg v1
227 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id
228 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id
229 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id
230 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id
231 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id
232 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id
233 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id
234 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id
235 JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id
236 JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id
237 JOIN tgtgtgtg v12 ON v11.ORG_ID = v12.parent_id
238 JOIN tgtgtgtg v13 ON v12.ORG_ID = v13.parent_id
239 WHERE v1.ORG_ID = '6000001') x
240 WHERE org_level <= 3
241 ORDER BY CASE
242 WHEN ',' || NVL(NULL, 'fingard') || ',' LIKE '%,' || ORG_ID || ',%' THEN
243 CASE
244 WHEN LENGTH(NVL(org_order, '')) = '9' THEN
245 org_order || ''
246 ELSE '1' || org_code
247 END
248 WHEN LENGTH(NVL(org_order, '')) = '9' THEN
249 '99999999' || org_order || ''
250 ELSE '999999991' || org_code END ) a)
251 WHERE rownum <= 10)
252 WHERE rn >= 1;
复制代码
![](https://static001.geekbang.org/infoq/32/3281c01c5f27f6b0cd6a584c3b935e97.png)
现场同学差集比较,确认改写后的 SQL 是等价的,执行时间从 2.6 秒降低到 1.5 秒能跑出结果。
原来 18 行的 SQL 改成了 250 多行后才优化了 1 秒的执行时间,实在没其他办法了,希望 OB 产研后续能 CBO 算子继续优化下。😂😂😂
文章转载自:小至尖尖
划线
评论
复制
发布于: 刚刚阅读数: 4
![用户头像](http://static001.geekbang.org/account/avatar/00/37/bf/f2/812a21cd.jpg?x-oss-process=image/resize,w_200,h_200)
不在线第一只蜗牛
关注
还未添加个人签名 2023-06-19 加入
还未添加个人简介
评论