OceanBase 金融项目优化案例
作者：不在线第一只蜗牛
- 2024-06-13 福建
本文字数：10916 字
阅读完需：约 36 分钟
领导让我帮忙支持下其他项目的 SQL 优化工作，呦西，是收集案例的好机会。😍
下面 SQL 都是在不能远程的情况下，按照原 SQL 的逻辑等价改写完成发给现场同学验证。
案例一
慢 SQL，4.32 秒：
SELECT MY_.*, RMFROM (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 执行计划：
改写优化，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; 复制代码
改写优化后执行计划：
优化思路：
1、原 SQL 有很多子查询，可能会导致计划走 NL，改成 JOIN 后让 CBO 自动判断是否走 HASH 还是 NL。
2、换了个标准的分页框架。
案例二
慢 SQL，2.6 秒：
SELECT MY_.*, RMFROM (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;复制代码
改写优化一，3.4 秒：
SELECT MY_.*, RMFROM (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;复制代码
使用 CTE 递归改写方案在 PostgreSQL 上是个通用的做法，也能取得比较好的性能效果。
但是在 OB 上反而效果更差点，NL 算子性能不够强，使用 NESTED-LOOP JOIN 性能反而没有 NESTED-LOOP CONNECT BY 算子好。
OB 研发在 NESTED-LOOP JOIN 算子上还有继续优化的空间。
改写优化二，1.5 秒：
既然使用 NL 性能不够理想的情况下，就要想办法使用 HASH 来优化 SQL 整体的执行效率。
将自动递归的方式改成手动。
1、首先需要知道数据整体的层级有多少。
SELECT DISTINCT lvFROM (SELECT level lv FROM tgtgtgtg tc START WITH TC.ORG_ID = '6000001' CONNECT BY PRIOR ORG_ID = tc.parent_id) t; 复制代码
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_level103 FROM tgtgtgtg v1104 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id105 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id106 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id107 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id108 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id109 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id110 WHERE v1.ORG_ID = '6000001'111 112 UNION ALL113 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_level121 FROM tgtgtgtg v1122 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id123 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id124 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id125 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id126 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id127 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id128 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id129 WHERE v1.ORG_ID = '6000001'130 131 UNION ALL132 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_level140 FROM tgtgtgtg v1141 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id142 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id143 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id144 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id145 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id146 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id147 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id148 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id149 WHERE v1.ORG_ID = '6000001'150 151 UNION ALL152 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_level160 FROM tgtgtgtg v1161 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id162 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id163 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id164 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id165 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id166 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id167 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id168 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id169 JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id170 WHERE v1.ORG_ID = '6000001'171 172 UNION ALL173 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_level181 FROM tgtgtgtg v1182 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id183 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id184 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id185 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id186 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id187 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id188 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id189 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id190 JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id191 JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id192 WHERE v1.ORG_ID = '6000001'193 194 UNION ALL195 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_level203 FROM tgtgtgtg v1204 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id205 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id206 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id207 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id208 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id209 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id210 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id211 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id212 JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id213 JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id214 JOIN tgtgtgtg v12 ON v11.ORG_ID = v12.parent_id215 WHERE v1.ORG_ID = '6000001'216 217 UNION ALL218 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_level226 FROM tgtgtgtg v1227 JOIN tgtgtgtg v2 ON v1.ORG_ID = v2.parent_id228 JOIN tgtgtgtg v3 ON v2.ORG_ID = v3.parent_id229 JOIN tgtgtgtg v4 ON v3.ORG_ID = v4.parent_id230 JOIN tgtgtgtg v5 ON v4.ORG_ID = v5.parent_id231 JOIN tgtgtgtg v6 ON v5.ORG_ID = v6.parent_id232 JOIN tgtgtgtg v7 ON v6.ORG_ID = v7.parent_id233 JOIN tgtgtgtg v8 ON v7.ORG_ID = v8.parent_id234 JOIN tgtgtgtg v9 ON v8.ORG_ID = v9.parent_id235 JOIN tgtgtgtg v10 ON v9.ORG_ID = v10.parent_id236 JOIN tgtgtgtg v11 ON v10.ORG_ID = v11.parent_id237 JOIN tgtgtgtg v12 ON v11.ORG_ID = v12.parent_id238 JOIN tgtgtgtg v13 ON v12.ORG_ID = v13.parent_id239 WHERE v1.ORG_ID = '6000001') x240 WHERE org_level <= 3241 ORDER BY CASE242 WHEN ',' || NVL(NULL, 'fingard') || ',' LIKE '%,' || ORG_ID || ',%' THEN243 CASE244 WHEN LENGTH(NVL(org_order, '')) = '9' THEN245 org_order || ''246 ELSE '1' || org_code247 END248 WHEN LENGTH(NVL(org_order, '')) = '9' THEN249 '99999999' || org_order || ''250 ELSE '999999991' || org_code END ) a)251 WHERE rownum <= 10)252 WHERE rn >= 1;复制代码
现场同学差集比较，确认改写后的 SQL 是等价的，执行时间从 2.6 秒降低到 1.5 秒能跑出结果。
原来 18 行的 SQL 改成了 250 多行后才优化了 1 秒的执行时间，实在没其他办法了，希望 OB 产研后续能 CBO 算子继续优化下。😂😂😂
文章转载自：小至尖尖
划线
评论
复制
发布于: 刚刚阅读数: 4
不在线第一只蜗牛
关注
还未添加个人签名 2023-06-19 加入
还未添加个人简介
评论