前言
以下用于SQL查询的数据均为测试环境的数据,关键数据都已打码。
背景
我们的日常开放中都会遇到 查询某个时间段的数据,像这样:
select * from test(表名) where time BETWEEN '2022-08-20 00:00:00' AND '2022-09-19 00:00:00'
但如果时间段跨度稍微长一些呢?以上我作为例子的SQL跨度就跨了1个月
正文开始
假设我有这么一张运单表,它有一个时间字段:route_time,现在我需要查询这张表某个日期段间(并且route_state=80)的数据,时间的跨度需要1个月,每条运单 只算一条(分组)
我们先看看这张表有多少数据:
SELECT count(*) FROM waybill_route;
在不加任何索引的情况下查询:
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;
EXPLAIN SELECT waybill_no FROM waybill_route WHERE route_time BETWEEN '2022-08-20 00:00:00' AND '2022-09-19 00:00:00' ;
加上 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' ;
EXPLAIN SELECT waybill_no FROM waybill_route WHERE route_time BETWEEN '2022-08-20 00:00:00' AND '2022-09-19 00:00:00' ;
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';
然后再把它作为一个条件之一加到我们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;
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;
type 的那一栏目为 range,执行时间多次执行取平均值过后,大约在 3.7 秒左右,相比于上次快了接近 1 秒,但是我们可以明显的看到索引我们走到了主键索引
最优到最差分别为:system > const > eq_ref > ref > range > index > ALL一般来说,得保证查询达到range级别,最好达到ref