组合索引怎么用比较快:
在join的时候需要三个on条件 `organization`,`policy_type`,`version` ,一个where条件
-- join 条件 where 条件
ON a.organization=b.organization and a.policy_type=b.policy_type and a.version= b.version
where a.version='20190225'
考虑建立如下索引:
a表索引:(15W row,目标数据10w)
KEY `with_ncd_premium_ratios_index` (`with_ncd_premium_ratios`),
KEY `vbdl_premium_score_mid_calc_v` (`version`),
KEY `vbdl_premium_score_mid_calc_org_pt_v` (`organization`,`policy_type`)
b表索引:(200 row)
PRIMARY KEY (`id`),
KEY `vbdl_premium_score_base_ratio_v` (`version`),
KEY `vbdl_premium_score_base_ratio_org_pt_v` (`organization`,`policy_type`)
join update 需要60秒时间
根据业务需求,进一步尝试优化索引 ,将索引拆分:
a.表,b.表索引一致 ,删除更新字段的索引:with_ncd_premium_ratios_index
KEY `vbdl_premium_score_base_ratio_v` (`version`),
KEY `vbdl_premium_score_base_ratio_org_pt_v` (`organization`,`policy_type`)
KEY `vbdl_premium_score_mid_calc_v` (`version`),
KEY `vbdl_premium_score_mid_calc_org_pt_v` (`organization`,`policy_type`)
此次优化有只需要0.6秒,性能提升100倍,索引加的好,乌龟也能跑.
做到10万级数据join更新,600ms响应.