中兴GoldenDB（MYSQL）营运商SQL优化案例（超复杂SQL）
GoldenDB哥们今天他在营运商遇到2条性能慢的SQL，说找了好多专家老手看了都优化不了，然后就找到我打算给我玩玩。😊
第一次接触营运商行业的SQL，妈呦还真的复杂😂
SQL1:
SELECT WORK_ORDER_ID as workOrderId, WORK_ITEM_ID as workItemId, TRACE_ID as traceID, TOTAL_DATE as totalDate, SEND_TIME as sendTime, SEND_STATUS as sendStatus, REQUEST_TIME as requestTime, REMARKS as remarks, PROVINCE as province, PRIORITY as priority, nvl(PHONE_NO, '0') as phoneNo, PARENTCALLID as parentcallid, OTHER_CHAR2 as otherChar2, OTHER_CHAR1 as otherChar1, OTHER_CHAR as otherChar, OSS_ORDER_ID as ossOrderId, nvl(ORDER_TYPE, '0') as orderType, ORDER_CREATE_TIME as orderCreateTime, OP_TIME as opTime, OP_CODE as opCode, nvl(MOD_VALUE, '0') as modValue, nvl(LOGIN_NO, '0') as loginNo, IOM_RECEIVE_TIME as iomReceiveTime, IOM_COMPLETE_TIME as iomCompleteTime, nvl(IMSI_NO, '0') as imsiNo, ID_NO as idNo, HLR_CODE as hlrCode, GROUP_ID as groupId, CRM_ORDER_ID as crmOrderId, COMMAND_ID as commandId, COMMAND_COUNT as commandCount, COMMAND_CODE as commandCode, CMS_RECEIVE_TIME as cmsReceiveTime, CMS_COMPLETE_TIME as cmsCompleteTime, ACK_TIME as ackTime, ACK_INFO as ackInfo, ACK_CODE as ackCode FROM px_lu WHERE send_status = '0' and mod(to_number(trim(phone_no)), 1) = 0 ORDER BY request_time, command_id limit 500;
执行时间：500 rows in set (0.853 sec)
这条分页SQL在 ORACLE 上 30ms 就可以跑出来，在 GoldenDB 要跑近 1s 才能出结果。
SQL1 索引优化:
create index idx_1_2_3 on px_lu (send_status,request_time, command_id );
500 rows in set (0.013 sec)
索引加上以后 0.013 s 就可以跑出结果，非常简单。
SQL2:
select new_busi_code, new_status_cd, table_name, new_group_id, count(1) count from (select (select nvl((select x.rule_value from vccccx t, frtgh x where t.rule_code = '1000000048' and t.busi_rule_code = x.busi_rule_code and t.busi_code = a.busi_code), decode(busi_level, '2', par_busi_code, BUSI_CODE)) from sffsss where busi_code = a.busi_code) as new_busi_code, (nvl((select x.rule_value from vccccx t, frtgh x where t.rule_code = '1000000046' and t.busi_rule_code = x.busi_rule_code and t.busi_code = a.busi_code), 's')) new_status_cd, (select op_note as table_name from swbbbbbb where maindata_code = 'RS-NO-0001' and maindata_value = (nvl((select x.rule_value from vccccx t, frtgh x where t.rule_code = '1000000046' and t.busi_rule_code = x.busi_rule_code and t.busi_code = a.busi_code), 's'))) table_name, nvl((select parent_group_id from dsdsd where group_id = a.group_id and parent_level = nvl((select x.rule_value from vccccx t, frtgh x where t.rule_code = '1000000047' and t.busi_rule_code = x.busi_rule_code and t.busi_code = a.busi_code), 3)), a.group_id) as new_group_id from dsdsd b, rsrsrs a WHERE b.group_id = a.group_id AND a.status_cd in ('2', 'y') AND b.parent_group_id = '14' and a.region_code = '2201' and a.tenant_id = '22' and b.tenant_id = '22' AND a.rec_Time <= to_date(TO_CHAR(SYSDATE, 'YYYYMMDD') || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) where new_busi_code is not null and new_status_cd is not null and new_group_id is not null group by new_busi_code, new_status_cd, table_name, new_group_id;
10 rows in set (9 min 46.240 sec)
这条SQL的表连接关系是真的超级复杂，来在ORACLE上跑30s可以出结果，在GoldenDB 却快跑10分钟才能出结果。
其实主要就是慢在标量子查询，只要改写成左连接性能就可以提升上来，里面的逻辑真的是太复杂了，我花了不少时间搞清关系，营运商SQL的复杂程度总算见到了。😅
SQL2等价改写:
select new_busi_code, new_status_cd, table_name, new_group_id, count(1) count from (
SELECT COALESCE(rv1.rule_value, DECODE(cb.busi_level, '2', cb.par_busi_code, cb.BUSI_CODE)) AS new_busi_code, COALESCE(rv2.rule_value, 's') AS new_status_cd, mv.op_note AS table_name, COALESCE(cgr.parent_group_id, a.group_id) AS new_group_id FROM rsrsrs a LEFT JOIN dsdsd b ON b.group_id = a.group_id LEFT JOIN sffsss cb ON cb.busi_code = a.busi_code LEFT JOIN vccccx br1 ON br1.busi_code = a.busi_code AND br1.rule_code = '1000000048' LEFT JOIN frtgh rv1 ON br1.busi_rule_code = rv1.busi_rule_code LEFT JOIN vccccx br2 ON br2.busi_code = a.busi_code AND br2.rule_code = '1000000046' LEFT JOIN frtgh rv2 ON br2.busi_rule_code = rv2.busi_rule_code LEFT JOIN swbbbbbb mv ON mv.maindata_value = COALESCE(rv2.rule_value, 's') AND mv.maindata_code = 'RS-NO-0001' LEFT JOIN vccccx br3 ON br3.busi_code = a.busi_code AND br3.rule_code = '1000000047' LEFT JOIN frtgh rv3 ON br3.busi_rule_code = rv3.busi_rule_code LEFT JOIN dsdsd cgr ON cgr.group_id = a.group_id AND cgr.parent_level = COALESCE(rv3.rule_value, 3) WHERE a.status_cd IN ('2', 'y') AND b.parent_group_id = '14' AND a.region_code = '2201' AND a.tenant_id = '22' AND b.tenant_id = '22' AND a.rec_Time <= to_date(TO_CHAR(SYSDATE, 'YYYYMMDD') || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND COALESCE(rv1.rule_value, DECODE(cb.busi_level, '2', cb.par_busi_code, cb.BUSI_CODE)) IS NOT NULL AND COALESCE(rv2.rule_value, 's') IS NOT NULL AND COALESCE(cgr.parent_group_id, a.group_id) IS NOT NULL
) where new_busi_code is not null and new_status_cd is not null and new_group_id is not null group by new_busi_code, new_status_cd, table_name, new_group_id;
10 rows in set (3 min 12.370 sec)
改写完以后运行速度从9分钟可以降到3分钟左右就能出结果，依然是很慢，还有继续优化空间。
SQL2创建索引继续优化:
create index idx_1_2_3 ON rsrsrs(group_id,busi_code, status_cd, region_code, tenant_id, rec_Time); create index idx_4_5_6 ON dsdsd(group_id, parent_group_id, tenant_id); create index idx_7_8_9 ON sffsss(busi_code); create index idx_11_12_13 ON vccccx(busi_code, rule_code, busi_rule_code); create index idx_14_15_16 ON frtgh(busi_rule_code); create index idx_17_18_19 ON swbbbbbb(maindata_value, maindata_code);
10 rows in set (48.876 sec)
最终通过等价改写 + 索引优化手段，SQL2从9分钟左右的执行时间降到48秒就可以出结果，基本没有继续优化的空间了。
