MySQL8中长sql多join的优化

业务场景问题

insert into W
select A.BUSNO, A.ORGCODE, A.FACETIME，A.FACERESULT,
	B.b1
	C.EQUTYPE,
	D.d1,
	E.e1,
	E.e2
	K.g1,
	K.g2,
	K.g3
from A
left join B on A.BUSNO = B.busno
left join C on A.MachID = C.machid
left join D on A.LIVEBNO = D.LIVEBNO
left ioin (SELECT * FROM F) E ON A.BUSNO = E.NUM_SEO and trandate = #{time}
left join (select g1, g2, g3 from 
	(select g1, g2, 
		g3 NUMBER() OVER(PARTITION BY g4 ORDER BY NUM JRN DESC) as g3
		from G where TRANDATE=#{time}
	) a 
	where a.NUM IRN = 1
) K on A.BUSNO = K.NUM_SEO

其中A表数据量6万多条，B、C表也2万多条，D表几十条，E、K表将近20万条，目前在线上插入时间已经超过了1个小时，监控已经报警。

分析业务场景

以A表为主表，BCDEK表都是取出相关联的字段。

排查sql语句查询缓慢原因

• 使用explain关键字查看发现，扫描的row数比较多，E , K两表每日的数据量比较大。
• 其次join关键字采用笛卡尔积的方式，这样需要拿主表中的记录与join表挨个比对。
• K采用窗口函数排序，这样explain的extra信息显示file sort，可见其排序完也是根据一个字段去重排序，取出重复字段的最小值，却需要给20万条记录排序，浪费时间。

优化方案

优化1，索引优化

将join之间的一些表中关联的非主键字段添加索引。

create index busno_idx on B(busno);

优化2，SQL语句优化

1. join本质上还是使用临时表，那么缩小join的查询范围，去除不必要的扫描

left ioin (SELECT * FROM F) E ON A.BUSNO = E.NUM_SEO and trandate = #{time}

==> 优化为:

left ioin (SELECT * FROM F where trandate = #{time}) E ON A.BUSNO = E.NUM_SEO

2. 优化排序子查询，取消无用的file sort排序

本质上就是为了去重取出最小的字段而已。

left join (select g1, g2, g3 from 
	(select g1, g2, 
		g3 NUMBER() OVER(PARTITION BY g4 ORDER BY NUM JRN DESC) as g3
		from G where TRANDATE=#{time}
	) a 
	where a.NUM IRN = 1
) K on bus.BUSNO = K.NUM_SEO

==> 优化为:

select g1, g2, min(g3) from G where TRANDATE=#{time}
group by g1, g2

问题，但是在上面这些简单的优化之后，查询依然很慢，本质上还是join的表是大数据表。

优化3，SQL语句改造为java程序

将一次多join的sql语句，转化为多次查询的sql语句，然后在java代码中取交并集。

将E、K两张20万的大表放到java的HashMap中，其中key为join关联的键BUSNO，而value为需要获取字段的对象。这样我们可以减少两个大表join，然后将查询的结果遍历，使用Map映射，获取对应的值对象。

注意，针对当前的jvm的参数配置，我们要先进行压力测试，防止出现线上OOM的情况。

优化4，foreach循环多次分批插入

• 原因：数据库拼接sql语句长度的限制。

• 解决：

1. dao层代码
   使用foreach拼接sql语句

int insertForBatch(@Param("dataDTOList") List<DataDTO> dataDTOList);

<insert id="insertForBatch">
	insert into W values
	<foreach collection="dataDTOList" item="datDTO" separator=",">
		(#{datDTO.busno}, #{datDTO.orgcode}, #{datDTO.facetime}, #{datDTO.faceresult},
		#{datDTO.b1}, #{datDTO.equtype}, #{datDTO.d1}, #{datDTO.e1},
		#{datDTO.e2}, #{datDTO.g1}, #{datDTO.g2}, #{datDTO.g3})
	</foreach>
</insert>

2. service层代码
   将整体的List按照每段10000条记录切分成段，然后使用多线程批量插入

@Autowired
private ThreadPoolExecutor executor;

public void insertData(List<DataDTO> dataDTOList) {
	// 总行数
	int allNum = dataDTOList.size();
	// 切分出来的份数
	int partNum = allNum / 10000;

	for (int i = 0 ; i < partNum ; i++) {
		if (i == partNum - 1) {
			List<DataDTO> dataDTOS = dataDTOList.subList(i * 10000, allNum);
			insertTask(dataDTOS);
		} else {
			List<DataDTO> dataDTOS = dataDTOList.subList(i * 10000, (i + 1) * 10000);
			insertTask(dataDTOS);
		}
	}
}

public void insertTask(List<DataDTO> dataDTOList) {
	executor.execute(() -> {
		menuMapper.insertForBatch(dataDTOList);
	});
}