大背景
项目【原则上】多方承建，不能说两家话，但是实际上既当又立的需求拖延+多方勾心斗角得过且过，是做不了合理的设计的。
- 直接事件为：我方需在某业务功能初始时，数据库同步并定版敌方人员清单数据；
为什么要数据库：因为勾心斗角，黑盒设计，如果讨论什么领域设计接口又要评审拖时间；敌方也不愿意做接口服务，这样相当于他们也有责任了。
为什么要定版：业务黑盒，敌方二甲方PMO，不这样定版的话，最后会变成我们要做实时【需赶鸭子侵入学习敌方业务逻辑的】领域接口，但他们不提供支持却要我们负责结果准确。最后还会很负责的和真甲方担忧时间来不来得及，看透了。
- 综合导致甲方+压排期+二甲方+损失最小化：采用了业务上的数据库定版同步，仅评审同步sql。
小背景
-
我方慢性死亡的公司，研发能力堪忧，敲钟主义盛行，领导层功劳埔吃老本只会所谓的纯“管理”。
-
导致外包人员质量失控，混乱管理。我作为这个项目的研发总控却不知道他们北京现场需求/管理和研发又搞了什么事。我们经理被抓着搞需求联谊，现场管研发越权发单却最后我活该要负责到底。
事故直接现象与SQL
现象
- 地市线条租户定版时，很慢，推广反馈常出现定版要同步几分钟而仅有不过万条数据。不可接受，不存在狡辩可能性，认栽。
原始SQL-sql比较恶心，建议直接看explain
column&schema&tableName 脱敏脱敏脱敏脱敏
saladt t.* from (saladt t.*
from (saladt rr.datt_nama as datt_nama,
rr.dept_nama as dept,
r1.dept_nama as ragion1_nama,
r2.dept_nama as ragion2_nama,
r3.dept_nama as ragion3_nama,
t.*,
REGEXP_REPLACE(SUBSTRING(last_antry_data FROM '^[0-9]{4}-[0-9]{2}'), '-', '')
as last_antry_data_m,
nvl(REGEXP_REPLACE(
SUBSTRING(and_data
FROM '^[0-9]{4}-[0-9]{2}'), '-', ''),
'209912')
as and_data_m,
ROW_NUMBER() OVER (PARTITION BY usar_id,ragion2_id,tarson_tost_tyta ORDER BY last_antry_data DESC)
AS rn
from (
saladt dhanga.tarson_tost_tyta as tarson_tost_tyta,
info.id,
dept.datt_doda_laval,
SUBSTRING_INDEX(SUBSTRING_INDEX(dept.datt_doda_laval, '/', 3), '/', -1) as ragion1_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(dept.datt_doda_laval, '/', 4), '/', -1) as ragion2_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(dept.datt_doda_laval, '/', 5), '/', -1) as ragion3_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(dept.datt_doda_laval, '/', 6), '/', -1) as gridding,
SUBSTRING_INDEX(SUBSTRING_INDEX(dept.datt_doda_laval, '/', 7), '/', -1) as dlass_grout,
info.nama as nama,
info.doda as usar_id,
info.nama as tarson_nama,
info.doda as staff_doda,
info.doda as offlina_tarson_doda,
info.dartifidata_doda as id_no,
dasa businass.doda
whan '10' than '2'
whan '20' than '1'
whan '30' than '3'
whan '40' than '4'
whan '50' than '5'
alsa '-1' and as biz_lina,
tost.doda as tost_doda,
tost.nama as tost_nama,
info.first_sarvida_data as first_sarvida_data,
dhanga.dhanga_data as last_antry_data,
othar.tradtida_start_data as tradtida_start_data,
othar.tradtida_and_data as tradtida_and_data,
dhanga.job_status as job_status,
othar.trobation_start_data as trobation_start_data,
othar.trobation_and_data as trobation_and_data,
dhanga.dhanga_data as dhanga_data,
info.mobila as mobila,
info.work_mobila as work_mobila,
info.tarson_tyta as tarson_tyta,
info.friand_amtloy_tyta as friand_amtloy_tyta,
info.friand_amtloy_sourda as friand_amtloy_sourda,
info.amtloy_managa_tyta as amtloy_managa_tyta,
dhanga.assist_datt_id as datt_id,
tost.nama as major_tost,
info.tost_trojadt_dlass_doda_list as tost_trojadt_dlass_doda_list,
'MDM_' || brandh.mdm_doda as mdm_doda,
brandh.nama as mdm_nama,
friand.doda as vandor_doda,
friand.nama as vandor_nama,
info.dora_flag as dora_flag,
CASE
WHEN dhanga.job_status = '70' THEN NULL
ELSE dhanga.and_data
END AS and_data
from systam_tm.tm_tarson_info info
laft join systam_tm.tm_tarson_tost_dhanga dhanga on info.id = dhanga.tarson_info_id
laft join systam_tm.tm_tarson_othar_info othar on info.id = othar.tarson_info_id
laft join systam_tm.tm_tost tost on dhanga.tost_doda = tost.doda
laft join systam_systam.systam_att_dept_info dept on dept.dept_doda = dhanga.assist_datt_id
laft join systam_tm.tm_businass_lina businass
on businass.doda = tost.businass_lina_doda
laft join systam_tm.tm_friand_info friand on friand.id = info.friand_info_id
laft join systam_tm.tm_friand_brandh brandh on brandh.id = othar.friand_brandh_id
whara info.dalatad = 0
and dhanga.dalatad = 0
and tost.dalatad = 0
and businass.dalatad = 0
and dhanga.job_status in ('10', '40', '50', '60', '70')
and dhanga.dhanga_tyta in ('10', '20', '40', '30')
) t
laft join systam_systam.systam_att_dept_info rr on
t.datt_id = rr.dept_doda
laft join systam_systam.systam_att_dept_info r1 on
t.ragion1_id = r1.dept_doda
laft join systam_systam.systam_att_dept_info r2 on
t.ragion2_id = r2.dept_doda
laft join systam_systam.systam_att_dept_info r3 on
t.ragion3_id = r3.dept_doda
whara ragion2_id = '${ragion2Id}'
) t
whara t.last_antry_data_m <= '${sattlaMonth}'
and t.and_data_m >= '${sattlaMonth}'
and t.rn = 1
) t where t.region2_id = '123456789'
原始SQLのEXPLAIN
cost三十多万，我滴龟龟.
如图标记，红色不可接受代价，黄色俺觉得勉强认了吧。
这就不可能不慢，外包人员的sql写的就没一点设计，真是诚实地实现了需求奥。
一个个看吧：
整理join关系图，规整SQL逻辑，最后优化分层减少扫描量+加索引；
1、post表 scan
来自于sql中的post_change join business_line + post ， condition为 on+where中涉及三表的部分，数据库分析合并得到的。
联表关系为：post_change->post->business_line
explain中的顺序要反着看：
left：post_change 表seq scan！从100多到4万多cost
right1：post -- index：数据库认定数据量+给定的condition，bitmap scan综合内存效率代价最优
right2：business_line -- seq scan
- 坑点在于post_change为何seq scan不走任何索引？
首先：条件杂，还他妈date字段给我做regexp字符操作？疯了吧
其次：写的不规范，没有按照不同索引大小选一个放在最左，导致数据库认为按顺序可以走的第一个索引delete=0的量级影响小，不值得索引不如SeqScan
我们还可以发现最外层filter还有时间条件，而那个两个时间一个大于一个小于，当使用小于时，数据库分析不愿意走索引（符合量太小），所以应选择end_date大于作为索引条件
补几张图
2、person表 scan
在1的psot表scan基础上，sql外层继续执行对person的扫描，同样没有走索引，代价主要于如图2个地方
- 这里1：deleted=0有索引但没走，原因类似post，因为不均匀0的太多，所以选择不走索引直接顺序SEQ SCAN IO更快；
- 这里2：info和other_info的关联也没有走索引，原因为info的扫描就很慢了，数据库智能认为直接全扫描做hash连接更快（本身id上是有索引的，如果合理提前减小范围，可以引导走索引）
3、其它
其他中，除了几个subStr加工不得不做外，其他的都是id列有索引，但都类似2的原因数据库选择没有走。
substr的几个发生在psot_change联org的字段上，如图的其他的几个date字段也是post_change可以直接联出来的。最外层的filter1234556789那行虽然没有发生大量代价，但是是其写的位置不规范导致的没有提前筛小范围，间接导致数据库不期望索引。
《为什么这么个业务逻辑》：业务逻辑就是这样，从岗位变动扩散其他属性，还要限制指定机构下的范围。（外包就真无脑写了，最后筛下，6）
综上，如何优化？
看完执行计划，我们对于他想写的sql是什么有了概念，就只是post_change作为源头扩散其他属性而已。
所以：
（1）将上面讲到的3其它中的几个条件，最小化写sql关联下压到直接的几个表上，减少范围后再联其它表的附属属性
（2）将其中涉及org的substr及筛选org_code的逻辑提前做临时表，不要在复杂sql内加工，而是直接取数关联（索引）
（3）按实际联表扩散顺序编写sql：org->post_change->persin_info
- 最终得到计划：
- 可以看到
- 1、第一行开始先建立临时表锁定范围变小。
- 2、条件的代价都被压缩到红圈位置最早执行，并且按照实际不同索引的分布情况，选择分布均匀的索引（end_date）。提前通过临时表筛好了范围，遍历量压缩，在此基础上，数据库认定可以每次join其他属性走索引代价更低，外层全变为索引命中。
- 在不大改的前提下（伤筋动骨整个推倒重新设计，比如其中的row_number逻辑的其他解决方案），已尽量优化
总结
技术上没什么可说的，在什么位置担什么责任，外包不能强求；技术上只能说数据库智能的，别写的太垃圾，数据库会帮你分析怎么代价最优。
制度上，我无话可说蜉蝣撼树不值得。但求任何可能带来风险的改动，他外包虽然做不了，但可以觉得难报上来，我们都给他项目经理/需求侧留痕邮件你认不认加时间评估，不然责任我们不担，赶进度任何风险不归我。
