大数据分页查询 or 导出 慢sql治理

背景

当前日增数据量将近千万，条件查询虽然做了联合索引，但扫描行数仍有30w左右，会出现超一秒的慢sql

缺陷

原因分析
1、联合索引首个未添加分库分表键
2、当前分库分表键是根据业务选择，数据分布不均匀
3、根据时间查询，跨度较大，扫描行多

要求

1、由于数据量太大，添加索引困难，并且查询场景为web页面，频率较低，为不影响线上业务，不去修改索引（存在4种不同时间查询，修改要加4个）
2、由于业务需要，时间查询跨度为7天，为不影响使用，不缩短查询时间范围

优化方案

1、时间拆分查询
主要的优化方案，也是接下来要讲的方案
2、count 加缓存
页面分页查询，多次count影响性能，虽然不能解决慢sql问题，但对查询性能有部分提升
3、部分走强制索引
页面允许使用单号查询，单号为唯一键，但mysql查询仍然走了时间索引，这部分查询强制走单号索引

适用场景

1、大数据量导出。
2、查询。注：对查询速度要求不高，允许损耗部分时间以达到完全去除慢sql查询

方案优点

页面是分页查询，条数最大50条，实际总数据量2-3万，用户只会查询前几页，根据时间拆分查询时，查询第1天数据就完全满足，相对原来只是多了一次count，并且扫描行大大降低，对查询时间影响较小

方案缺点

原来一次查询数据库，优化后需要多次count和数据查询，增加了查询时间
注：实际发现时间确实有增加，但是增加不多

时间拆分如处理分页查询问题

1、在对count进行拆分时，只需要对时间按天 or 小时拆分，对拆分后多次count,求和即可，但是页面的要求是分页查询，如何实现时间拆分的同时，可以实现分页查询？

方案说明

由于数据查询根据时间按天拆分，那么拆分后每次查询需要对sql中偏移量和查询条数进行重新计算

例如： 查询签收时间在6天内的数据，分页查询，每页50条，查询第n页
需要查询的数据为 select * from [表A] [条件] limit 50(n-1),50

要求：查询第3页数据

如果查询的数据分布如下 day1下符合数据30条，day2下100条…

思路：从day1开始获取数据，到day6结束，如果提前获取到数据，则直接返回
n=3时，即第3页数据查询 limit 100,50 查询的数据为第101条开始，到150条结束 [101,150]
第一次count ，得到day1 数据 30条，数据范围ab [1,30],不符合
第二次count ，得到day2 数据 100条，数据范围cd [31,130],部分符合，符合数据范围ef[101,130] ,
根据符合范围，计算偏移量和查询条数
需要查询的数据为 select * from [表A] [条件 时间=day2] limit A,B
A=e-c=101-31=70
B= 130 - A = 30
得到查询数据30条，说明需要继续查询20条
第三次count ，得到day3 数据 30条，数据范围 gh[131,160],部分符合，符合数据范围ij[131,150] ,
根据符合范围，计算偏移量和查询条数
需要查询的数据为 select * from [表A] [条件 时间=day3] limit A,B
A=i-g=131-131=0
B= 150 - A = 20
得到查询数据20条，加上day2查询的30条，共50条，查询结束 END

使用说明

// e是查询对象
//初始化
PageQueryUtil<WaybillWholeQueryDTO,WaybillWholeDO> pageQueryUtil = new PageQueryUtil();

pageQueryUtil.init(query.getOffset(),query.getPageSize());

//时间拆分，获取map,开始时间和结束时间
Map<LocalDateTime, LocalDateTime>  dateTimeMap = LocalDateTimeUtil.splitByDay(query.getCollectTimeBegin(), query.getCollectTimeEnd());
//根据原来查询对象e,把查询时间修改，其他不该，获取多个e查询对象
List<E> list = build(dateTimeMap,e);
for (E queryObj : list) {
   
//查询某天总页数
Long count = waybillWholeDAO.count(e);
//判断是否需要查询，如果要，会自动设置pageSize和offset
Boolean continueQuery = pageQueryUtil.isContinueQuery(count, e);
//判断是否需要查询                
if (continueQuery){
	List<WaybillWholeDO> waybillWholeDOS=waybillWholeDAO.queryWithCollectTimeIndex(e);
	pageQueryUtil.addAll(waybillWholeDOS);
   }    
 }

分页查询工具

@Data
public class PageQueryUtil<T extends Page,V> {
    //开启使用
    Boolean open = Boolean.FALSE;
    //查询偏移量
    Integer originalOffset;
    //查询条数
    Integer originalPageSize;
    //累计数
    Integer cumulativeCount = 0;
    //查询数据范围起始值
    Integer queryStart = 1;
    //是否继续查询
    Boolean continueQuery = Boolean.TRUE;
    //是否最后一次查询
    Boolean isLastQuery = Boolean.FALSE;
    List<V> data = new ArrayList<>();


    public void init(Integer originalOffset,Integer originalPageSize){
        this.originalOffset = originalOffset;
        this.originalPageSize = originalPageSize;
        this.open = true;
    }
    public void addAll(List<V> data){
        this.data.addAll(data);
    }

    public Boolean isContinueQuery(Long count,T query){
        if (!open){
            throw new RuntimeException("not pen page query");
        }

        if (isLastQuery){
            return Boolean.FALSE;
        }

        //需要查询数据范围为集合 ab [a,b]
        int a = originalOffset + 1;
        int b = originalOffset + originalPageSize;

        //本次查询数据范围为集合 cd [c,d]
        int c = queryStart;
        int d = queryStart + count.intValue() - 1;
        //下个查询数据集合开始值
        queryStart = d + 1;

        // 本次是否需要查询
        continueQuery = !(d < a || c > b);
        if (!continueQuery) {
            return Boolean.FALSE;
        }

        //本次是否需要查询数据  求 cd 与 ab交集 ef [] ，得
        int e = c <= a ? a : c;
        int f = d >= b ? b : d;

        //本次查询数
        int queryCount = f - e + 1;
        //剩余需要查询数
        int needQueryCount =  originalPageSize - cumulativeCount;
        if (needQueryCount < queryCount){
               queryCount = needQueryCount;
        }

        //累计数添加
        cumulativeCount = cumulativeCount + queryCount;
        //是否最后一次查询
        isLastQuery = cumulativeCount == originalPageSize;

        //设置查询范围
        query.setOffset(e - c);
        query.setPageSize(queryCount);
        query.setOpen(true);
        return continueQuery;
    }
}

时间拆分工具

public class LocalDateTimeUtil {

    private static final String OFFSET_ID = "+8";
    private static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
    public static final DateTimeFormatter YYYY_MM_DD_COMPACT_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");

    /**
     * toEpochMilli
     *
     * @param localDateTime
     * @return
     */
    public static long toEpochMilli(LocalDateTime localDateTime) {
        if (localDateTime == null) {
            return 0L;
        }
        return localDateTime.toInstant(ZoneOffset.of(OFFSET_ID)).toEpochMilli();
    }

    /**
     * format by default pattern
     *
     * @param localDateTime
     * @return
     */
    public static String format(LocalDateTime localDateTime) {
        return format(localDateTime, YYYY_MM_DD_HH_MM_SS);
    }

    /**
     * format by pattern
     *
     * @param localDateTime
     * @param pattern
     * @return
     */
    public static String format(LocalDateTime localDateTime, String pattern) {
        if (localDateTime == null || StringUtils.isEmpty(pattern)) {
            return "";
        }
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern);
        return localDateTime.format(formatter);
    }
    /**
     * format by pattern
     *
     * @param localDateTime
     */
    public static String format(LocalDateTime localDateTime, DateTimeFormatter formatter) {
        if (localDateTime == null) {
            return "";
        }
        return localDateTime.format(formatter);
    }

    /**
     * 指定日期当天开始时间
     * @param localDateTime
     * @return
     */
    public static LocalDateTime getStartTime(LocalDateTime localDateTime){
        return LocalDateTime.of(localDateTime.toLocalDate(), LocalTime.of(0, 0));
    }

    /**
     * 指定日期当天结束时间
     * @param localDateTime
     * @return
     */
    public static LocalDateTime getEndTime(LocalDateTime localDateTime){
        return LocalDateTime.of(localDateTime.toLocalDate(), LocalTime.of(23, 59, 59, 999999999));
    }

    /**
     * 指定时间
     *
     * @param
     * @return
     */
    public static LocalDateTime getEndTime(String time, String pattern){
        DateTimeFormatter df = DateTimeFormatter.ofPattern(pattern);
        return LocalDateTime.parse(time,df);
    }

    /**
     * 按天切割
     *
     * @param start 开始时间
     * @param end   结束时间
     * @return
     */
    public static Map<LocalDateTime, LocalDateTime> splitByDay(LocalDateTime start, LocalDateTime end) {
        if (start == null || end == null || start.isAfter(end)) {
            return null;
        }
        Map<LocalDateTime, LocalDateTime> map = new TreeMap<>();
        int days = Period.between(start.toLocalDate(), end.toLocalDate()).getDays();
        if (days == 0) {
            map.put(start, end);
            return map;
        }
        LocalDateTime currentDay = start;
        for (int i = 1; i <= days; i++) {
            map.put(currentDay, LocalDateTimeUtil.getEndTime(currentDay));
            currentDay = LocalDateTimeUtil.getStartTime(currentDay).plusDays(1);
        }
        map.put(LocalDateTimeUtil.getStartTime(currentDay), end);
        return map;
    }

    public static void main(String[] args) {
        LocalDateTime start = LocalDateTime.now();

        LocalDateTime end = LocalDateTime.now().plusMinutes(1);

        System.out.println(splitByDay(start, end));
    }

}