【调优】log日志海量数据分表后查询速度调优

原始实现

使用pagehelper实现分页

      // 提取开始时间的年份和月份，拼装成表名
        List<String> timeBetween = getTimeBetween(condition);
        List<String> fullTableName = getFullTableName(Constants.LOG_TABLE_NAME, timeBetween);
        PageHelperUtil.startPage(condition);
        List<PulSysLogPageVo> list = logMapper.queryByPage(condition, fullTableName);
        return new PageSimpleInfo<>(list);

getTimeBetween 为从查询条件中把开始时间至结束时间取出

  private static List<String> getTimeBetween(PulSysLogCondition condition) {
        Timestamp startTime = condition.getStartTime();
        Timestamp endTime = condition.getEndTime();
        if (Objects.isNull(startTime)) {
            // 获取当前时间
            LocalDateTime currentTime = LocalDateTime.now();
            // 计算六个月前的时间
            LocalDateTime sixMonthsAgo = currentTime.minus(Period.ofMonths(6));
            // 将六个月前的时间转换为Timestamp
            startTime = Timestamp.valueOf(sixMonthsAgo);
        }
        if (Objects.isNull(endTime)) {
            endTime = new Timestamp(System.currentTimeMillis() + (24 * 60 * 60 * 1000L));
        }
        YearMonth startYearMonth = YearMonth.from(startTime.toLocalDateTime());
        // 提取结束时间的年份和月份
        YearMonth endYearMonth = YearMonth.from(endTime.toLocalDateTime());
        List<String> suffix = Lists.newArrayList();
        // 构建年份和月份的字符串格式，添加到列表中
        while (startYearMonth.isBefore(endYearMonth) || startYearMonth.equals(endYearMonth)) {
            String yearMonthString = String.format("%04d_%02d", startYearMonth.getYear(),
                    startYearMonth.getMonthValue());
            suffix.add(yearMonthString);
            startYearMonth = startYearMonth.plusMonths(1);
        }
        return suffix;
    }

getFullTableName 是将时间拼接为表名

    private static List<String> getFullTableName(String tableName, List<String> suffixList) {
        List<String> fullTableNameList = Lists.newArrayList();
        // 构建年份和月份的字符串格式，添加到列表中
        for (String suffix : suffixList) {
            String logTableName = tableName + "_" + suffix;
            fullTableNameList.add(logTableName);
        }
        return fullTableNameList;
    }

queryByPage 使用mybtis查询

    SELECT  需要的字段
        FROM
        <foreach item="tableName" collection="tableNames" separator=" UNION ALL" open="(" close=") AS s" index="">
            SELECT 需要的字段
            FROM ${tableName}
            <where>
                <if test="condition.startTime != null and condition.endTime != null">
                    AND created_at &gt;= #{condition.startTime,jdbcType=TIMESTAMP}
                    AND created_at &lt;= #{condition.endTime,jdbcType=TIMESTAMP}
                </if>
                其他条件
            </where>
        </foreach>

优化后

  // 提取开始时间的年份和月份，拼装成表名
        List<String> timeBetween = getTimeBetween(condition);
        List<String> fullTableName = getFullTableName(Constants.LOG_TABLE_NAME, timeBetween);
        List<PulSysLogPageVo> resultList = new ArrayList<>();
        int remaining = condition.getRows();
        long total = 0;
        Map<String, Integer> tableCountMap = new HashMap<>(fullTableName.size());
        // 按表优先级逐个查询
        for (String tableName : fullTableName) {
            if (remaining <= 0) {
                break;
            }
            // 单表查询
            int singleTotal = logMapper.selectSingleTableCount(condition, tableName);
            total += singleTotal;
            tableCountMap.put(tableName, singleTotal);
        }
        long previousTotal = 0;
        int globalStart = (condition.getPage() - 1) * condition.getRows();
        if (globalStart > total) {
            return new PageSimpleInfo<>();
        }
        for (String tableName : fullTableName) {
            int singleTotal = tableCountMap.get(tableName);
            if (singleTotal <= 0) {
                continue;
            }
            // 当前表之前的记录总数
            previousTotal += singleTotal;
            if (previousTotal <= globalStart) {
                continue;
            }
            // 当前表实际起始位置 =   全局起始 -当前表之前的记录总数 ;
            int localStart = Math.toIntExact(globalStart - (previousTotal - singleTotal));
            if (condition.getRows() > remaining) {
                localStart = 0;
            }
            // 当前表最多能取的数量
            int localSize = Math.min(remaining, singleTotal);
            // 在计算localSize后增加校验
            if (localSize <= 0) {
                continue; // 跳过该表查询
            }
            List<PulSysLogPageVo> list = logMapper.querySingleTable(
                    condition,
                    tableName,
                    localStart,
                    localSize
            );
            resultList.addAll(list);
            int actualFetched = list.size();
            remaining -= actualFetched;
            if (remaining <= 0) {
                break;
            }
        }
        PageSimpleInfo<PulSysLogPageVo> pageInfo = new PageSimpleInfo<>(resultList);
        pageInfo.setTotal(total);
        return pageInfo;

selectSingleTableCount 获取每个表的数据量

        SELECT COUNT(1) FROM ${tableName}
        <where>
            <if test="condition.startTime != null and condition.endTime != null">
                AND created_at &gt;= #{condition.startTime,jdbcType=TIMESTAMP}
                AND created_at &lt;= #{condition.endTime,jdbcType=TIMESTAMP}
            </if>
其他查询条件
        </where>

querySingleTable 查询单表

 SELECT
       需要的字段
        FROM ${tableName} s
        <where>
            <if test="condition.startTime != null and condition.endTime != null">
                AND created_at &gt;= #{condition.startTime,jdbcType=TIMESTAMP}
                AND created_at &lt;= #{condition.endTime,jdbcType=TIMESTAMP}
            </if>
          其他查询条件
        </where>
        ORDER BY created_at DESC
        LIMIT #{offset}, #{pageSize}

经过测试

原始查询单表百万级,查询半年记录也就是6个表,12s+
优化后查询单表百万级,查询半年记录也就是6个表,100ms+