记录一次查询优化

一.背景描述

1.1问题和情况

1. 生产环境，有一张按每天一份数据的表（下面简称表1），跨天查询较慢，跨月查询甚至超时
2. 查询一天内的数据速度不怎么慢
3. 查询是分页的
4. 表1按照日期做了子分区，一个月一个子分区

1.2造成问题的原因

1. 生产环境 数据库本身问题，性能不佳
2. 表1的数据每天增加8万条
3. 表1还有往年历史数据，数据体量有3、4千万，更使得查询变慢。

二.优化思路

2.1针对问题考虑

考虑到造成查询较慢的原因主要有两个，一个是表的数据量较大，第二个是跨天或者跨月可能导致跨分区查询。第一个无法改变， 第二个可以从查询上优化

2.2优化思路

如果能确认此次查询页的数据，属于查询日期范围的哪一 天，那么也就只需要去一个分区中拿数据(最坏情况也只是两个分区)，而不是带着原时间范围查询。查询效率也会得到提升。

2.3优化前提

优化思路中的意思就是，比如现在是查询2024.10.1-2024.10.03日期范围内的数据，分页数是20，当前第3页，10.1号201条数据，10.2号180条数据，10.3号200条数据，那么第3页的数据肯定还在10.1号这天内，那就正常查询时间范围是10.1号的数据，如果现在到了第11页，11*20=220>201，这就说明第11页的数据要从10.1号的数据取1条，从10.2号的数据中取19条。由此可以得知，优化前提是：当前页面分页数要小于查询时间范围最小的数据，要不然优化没有意义。

因为如果分页是200，一天也就50条数据左右，那肯定会跨天。

优化前后的简化对比图：

2.4优化实现

1. 根据用户的查询条件，查出每个日期有多少数据，得到数据总数，进而得到总的分页数。比如查10.1-10.3，10.1号122条，10.2号131条，10.3号98条，那么数据总数是351条，每页20条，共18页。分组共3组，分组下标分别是0，1，2
2. 计算本次分页查询，需要从第1步返回的数据的第几组-第几组(下标)取元素。比如现在查询第6页，每页20条，那么查询的数据在第1组，如果查询第7页，那么查询的数据分布在第组和第2组，第1组要拿2条，第2组要拿18条
3. 根据页面实际的开始下标和每页大小，以及第 2 步算出来的分组下标，计算出此次真正需要去 DB 中查询的起止下标(要查询的总数，可能会存在跨天的情况，所以这里会返回一个列表)，比如上面的例子，如果是第6页，就查询时间范围是10.1，分页数是第100和120，如果是第7页，就查询时间范围是10.1，分页数是120到122，和，时间范围是10.2，分页数是1到18的三个月还有
4. 将第 3 步得到的下标列表，按原有 sql 进行查询，最后将数据合并返回。比如上面的例子，就是查2遍。

举个栗子（不跨天的情况）：

页面现在查询条件，查询第： pageOffset=3900 ， pageSize=20 （默认），查询 2024-09-12  至 2024-09-13 的数据

1. 根据查询条件查询到每天的数据是： 2024-09-12 ： 78010 ， 2024-09-13 ： 79111

2. 将前端传 pageOffset=3900 ， pageSize=20 以及查询总数 (78000+79111), 可以知道用户要的数据就在第 1 步中的列表的 0-1 （下标）。

3. 根据页面实际对应的下标是 77980-78000 ，第 2 步计算出的 0-1 （下标）以及第 1 步的日期对应的数据，可以计算出真正的查询条件是日期： 2024-09-12 ，下标：77980-78000

其实数据本来也是按日期前后分布在DB中（可以认为是前后放的），原来的查询是前端传了什么时间范围就查什么时间范围，而且可能也没有按日期order by，可能是按id order by，因为这种方式，按不按日期order by都没什么差别，改成查指定日期的数据，就相当于从前往后顺序拿数据

三.优化前

跨天查询要10秒左右

四.优化后

跨天查询3秒左右

五.思考和心得体会

1. 作为程序员还是要有一定的算法功底，这里就是针对生产实际问题和特点运用的很简单的算法，从而优化，这一点确实还不足，因为这个思路是领导提的，当时我并没有意识到
2. 可以看到sql优化，如果跟时间范围有关，那缩小时间范围肯定是可以考虑的