mysql join索引组合优化,性能提升百倍

组合索引怎么用比较快:

在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响应.