携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第11天，点击查看活动详情

背景

现在有一个单表，表是一张宽表，非关系型结构表，即逻辑上的一条数据，会对应表里的多张数据，以实际举例：一天的24小时的数据，在表里会存24条数据。

表中每个字段都建立了一个索引，其中特别的点是有一个开始日期字段，有一个截止日期字段。

需求

前端页面默认状态下，会选择多个条件，其中就包含是一个时间段，要求查询与选择时间段有交集的数据。

现状

最开始使用了一个最常规的写法，每个条件挨个匹配，一个SQL搞定，同时按照查询的所有结果字段进行group by，SQL：

SELECT
	...
FROM
	table_name t
WHERE
	(
		(
			t.column4 >= '2022-08-11'
			AND t.column4 <= '2022-08-22'
		)
		OR (
			t.column5 >= '2022-08-11'
			AND t.column5 <= '2022-08-22'
		)
	)
AND t.column1 IN ('1', '2')
AND t.column2 IN ('3', '0', '1', '2')
AND t.column3 IN ('0', '1')
GROUP BY
	...

由于数据库的特殊性，不能使用explain分析SQL执行情况，但是现象就是此SQL的执行时间会花费7-10s。

方案

考虑到一次查询的数据量比较大，而且需要按所有结果字段进行分组，故考虑分为两个SQL进行编写，首先第一个SQL是为了缩小数据检索范围，第二个SQL才为了检索出结果数据。

第一个SQL：

SELECT
	id
FROM
	table_name t
WHERE
	(
		(
			t.column4 >= '2022-08-11'
			AND t.column4 <= '2022-08-22'
		)
		OR (
			t.column5 >= '2022-08-11'
			AND t.column5 <= '2022-08-22'
		)
	)
AND t.column1 IN ('1', '2')
AND t.column2 IN ('3', '0', '1', '2')
AND t.column3 IN ('0', '1')
GROUP BY
	id;

第二个SQL：

SELECT
	*
FROM
	table_name t
WHERE
	t.id IN (
	    第一个SQL中查询出来的id
    )
    AND t.column9 = 1（根据此条件可以只取一条数据）

总结

经过优化重写SQL后，虽然单表数据量达到千万，但是在这么多查询条件存在的情况下，执行结果基本能保证在3s以内。

主要思路还是避免一次查询的数据量过大，导致group by的数据量也很大（对于group by 的机制暂时不太了解，不清楚数据量的差异对效率的影响有多大，但是考虑有可能会有这个因素存在）。

改写后的SQL第一次查询缩小了数据量的范围，第二次查询也使用了很少的检索条件，并且也都是有索引存在的。