【调优】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));// 将六个月前的时间转换为TimestampstartTime = 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 DESCLIMIT #{offset}, #{pageSize}

经过测试

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