SQL关于Date类型时间段查询优化(时间跨度稍长)(记一次自己工作开发中遇到的SQL优化经验)

前言

以下用于SQL查询的数据均为测试环境的数据，关键数据都已打码。

背景

我们的日常开放中都会遇到 查询某个时间段的数据，像这样：

select * from test(表名) where time BETWEEN '2022-08-20 00:00:00' AND '2022-09-19 00:00:00'
1

但如果时间段跨度稍微长一些呢？以上我作为例子的SQL跨度就跨了1个月

---

正文开始

假设我有这么一张运单表，它有一个时间字段：route_time，现在我需要查询这张表某个日期段间（并且route_state=80）的数据，时间的跨度需要1个月，每条运单 只算一条（分组）

---

我们先看看这张表有多少数据：

	SELECT count(*) FROM waybill_route;
1

在不加任何索引的情况下查询：

SELECT waybill_no FROM waybill_route WHERE  route_state = 80 and route_time BETWEEN '2022-08-20 00:00:00' AND '2022-09-19 00:00:00' GROUP BY waybill_no;
1

EXPLAIN SELECT waybill_no FROM waybill_route WHERE  route_time BETWEEN '2022-08-20 00:00:00' AND '2022-09-19 00:00:00' ;
1

加上 EXPLAIN 关键字，我们看一下它的执行结果：

type 的那一栏目为 All， 这一次的查询走了全表的扫描，从3994458行数据中找出了74231条数据，但执行时间很长，多次执行取平均值 在4.9秒左右，这显然不是我们理想的结果。

---

我们为 route_time 、route_state 这个l两个字段加上一个普通索引过后：

SELECT waybill_no FROM waybill_route WHERE  route_time BETWEEN '2022-08-20 00:00:00' AND '2022-09-19 00:00:00' ;
1

EXPLAIN SELECT waybill_no FROM waybill_route WHERE  route_time BETWEEN '2022-08-20 00:00:00' AND '2022-09-19 00:00:00' ;
1

type 的那一栏目为 All， 这一次的查询也走了全表的扫描，从3994458行数据中找出了74231条数据，执行时间多次执行取平均值过后，大约在4.5秒左右，减少了0.5秒，加了索引查询速度也不是很明显

filtered 表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比。

我们再关注Extra这一栏目的信息:

Using temporary： 创建了一个内部临时表

Using filesort： 对查询的数据进行了排序，没有走索引

整理思路

显然最终的结果仍然不在我们能接收的范围之内，首先 type=All 我们需要想想其它的思路。

我们是不是能找出 时间大于我们时间段开始时间的数据的 最小id呢？

SELECT MIN(id) FROM waybill_route WHERE route_time>'2022-08-20 00:00:00';
1

然后再把它作为一个条件之一加到我们SQL里，最终的优化的SQL:

SELECT
waybill_no
FROM
	waybill_route 
WHERE
	 id > ( SELECT MIN( id ) FROM waybill_route WHERE route_time > '2022-08-20 00:00:00' ) 
	AND route_time BETWEEN '2022-08-20 00:00:00' 
	AND '2022-09-19 00:00:00' 
	GROUP BY waybill_no;
1
2
3
4
5
6
7
8
9

EXPLAIN SELECT
waybill_no
FROM
	waybill_route 
WHERE
	 id > ( SELECT MIN( id ) FROM waybill_route WHERE route_time > '2022-08-20 00:00:00' ) 
	AND route_time BETWEEN '2022-08-20 00:00:00' 
	AND '2022-09-19 00:00:00' 
	GROUP BY waybill_no;
1
2
3
4
5
6
7
8
9

type 的那一栏目为 range，执行时间多次执行取平均值过后，大约在 3.7 秒左右，相比于上次快了接近 1 秒，但是我们可以明显的看到索引我们走到了主键索引

最优到最差分别为：system > const > eq_ref > ref > range > index > ALL一般来说，得保证查询达到range级别，最好达到ref