【MySQL分区】使用Partition分区提升查询性能，并使用SpringBoot进行分区管理

一、前言

公司目前有一个小项目，里面的某些场景需要对日增数万条记录的表格进行以天为单位的复杂跨时段数据分析。项目刚开始投入使用时，单表总数据量并不大，那时的SQL数据分析速度还不算慢，每次4~7秒左右。但是后来运行了十几天后，表格数据量很快就到了百万级别，这时查询就明显比以前慢了很多，每次都要等待十几秒。以上都是对时间字段(datetime)已经进行了索引的情况下的性能表现。

造成性能问题的原因主要有两点：

• 即便已对时间字段建立索引，但因具体的查询分析需求不同，执行不同语句时，MySQL未必会在涉及相关字段查询时使用索引；
• 查询选择的时间跨度是个重要并且决定性的因素。时间跨度越长，需要分析的数据量就越多，耗时也自然就越久。

二、一般优化方案

针对这种情况，很自然的就会想到以下方案：

• 对数据分析结果在应用服务层进行缓存 (本地缓存或Redis缓存等)
• MySQL中建立相关统计表，事先把数据分析过程中需要的某些统计结果计算好并存储
• 将相关表数据同时存储在其他支持高性能查询的非关系型数据库 (如ElasticSearch、Redis)

以上方案通常都很有效，不过缺点也很明显：一是增加了项目复杂度以及开发和维护成本，二是降低了灵活性和可扩展性，三是降低了查询分析的实时性。

三、使用Partition分区

时间范围查询在本项目中对应的SQL语句一般形同：

SELECT * FROM table_a WHERE update_time BETWEEN DATE('2024-06-01') AND DATE('2024-06-05')

通过对SQL进行EXPLAIN会发现：即便已对update_time字段建立索引，类似范围查询还是经常会执行全表扫描

后来在网上搜索解决方案时，发现可以对表进行分区并对分区进行查询来优化性能

假设分区名命名规则为：pYYMMDD，即p240601表示所有2024年6月1日当天的数据的分区，则以上语句可以改造成：

SELECT * FROM table_a WHERE update_time PARTITION (p240601, p240602, p240603, p240604, p240605)

经过试验，对表进行按天分区，并仅对需要的分区进行查询时，查询速度有大幅提升。十几秒的查询降低到了3秒左右！

需要注意的是用于计算分区的字段必须要是主键之一，由于要对update_time字段分区，但该字段并不是主键，于是事先将其和表id主键组合成联合主键：

ALTER TABLE table_a
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `update_time`) USING BTREE;

四、管理分区

该小节是本博文的重点。原因是在网上看他人使用表分区时，通常是在建表时就已指定分区且在后期很少更改。而这根本不能满足一般项目的需要，因为项目的数据是每天都会新增，当日期超过分区指定的范围时，分区就得重建。为解决该问题，网上有个解决方案是在MySQL中建立定时任务来对分区按日新增。但这方案的缺点在于，它由于是属于MySQL数据库的自带任务，对于应用侧开发者来说，难以进行观测和管控，也不方便进行调整。

于是开始考虑如何把分区管理移动到应用侧进行。

针对此，在网上也找到了一个在Spring Boot中进行分区管理的示例。我对该示例按照自己的项目需求进行了一些改造，关键类如下：

package com.xx.yy.module.db.schedule;

import com.xx.yy.module.db.service.PartitionService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.Scheduled;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

import static com.xx.yy.module.db.constant.PartitionConstant.*;

@Configuration
@Slf4j
public class PartitionManagerTask {
 
    private final ExecutorService executorService = new ThreadPoolExecutor(5, 10,
            3000, TimeUnit.MILLISECONDS, new LinkedBlockingDeque<>(10));
    @Autowired
    private PartitionService partitionService;
 
    private static final Map<String, String> tables = new HashMap<String, String>() {{
        put("camera_status", CAMERA_PARTITION_DATE_RANGE);
        put("intercom_status", INTERCOM_PARTITION_DATE_RANGE);
    }};
 
 	// 程序启动时检查分区，确保分区可用
    @PostConstruct
    public void init() {
        tables.forEach((t, k) -> partitionService.handle(t, k));
    }

 	// 定时任务检查分区，需要调整时自动处理
    @Scheduled(cron = "${system.partition-task.schedule}")
    public void task() {
        tables.forEach((t, k) -> executorService.submit(() -> {
            try {
                partitionService.handle(t, k);
            } catch (Exception e) {
                log.error("线程执行错误", e);
                throw e;
            }
        }));
    }

    @PreDestroy
    public void destroy() {
        try {
            log.info("====关闭后台任务任务线程池====");
            executorService.shutdown();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }
}

package com.xx.yy.module.db.service.impl;

import com.xx.yy.common.utils.CaffeineRepository;
import com.xx.yy.common.utils.CollectionUtils;
import com.xx.yy.config.SystemConfig;
import com.xx.yy.module.db.entity.Partition;
import com.xx.yy.module.db.mapper.PartitionMapper;
import com.xx.yy.module.db.service.PartitionService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDate;
import java.util.*;
import java.util.stream.Collectors;

import static com.xx.yy.module.db.constant.PartitionConstant.PARTITION_RANGE_CACHE;

@Service
@Slf4j
public class PartitionServiceImpl implements PartitionService {

    @Autowired
    private PartitionMapper partitionMapper;

    @Autowired
    private CaffeineRepository caffeineRepository;

    @Autowired
    private SystemConfig systemConfig;

    @Override
    @Transactional
    public void handle(String table, String localCacheKey) {
        log.info("开始检查表{}分区", table);
        
        // 检查表当前数据中最大和最小的日期
        Map<String, Date> map = partitionMapper.queryMinMaxDate(table);
        Date minDate = map == null ? null : map.get("min");
        Date maxDate = map == null ? null : map.get("max");

        List<Partition> partitions = new ArrayList<>();

        // 表存在且有数据
        if (minDate != null && maxDate != null) {
            log.info("表{} minDate: {},maxDate: {}", table, minDate, maxDate);
            // 1. 检查：检查当前分区是否符合要求
            boolean properlyPartitioned = true;

            partitions = partitionMapper.queryPartition(table);

            LocalDate date = LocalDate.parse(minDate.toString());

            // 有数据且有分区，检查分区是否符合要求(按连续的天分区)，若不符合要求则需要整个重建
            if (CollectionUtils.hasElement(partitions)) {
                for (int i = 0; i < partitions.size(); i++) {
                    Partition partition = partitions.get(i);
                    if (partition == null || !this.partitionName(date).equals(partition.getName()) || !("'" + date.plusDays(1) + "'").equals(partition.getDescription())) {
                        properlyPartitioned = false;
                        log.error("表{}分区错误: name: {},description: {}", table, partition == null ? null : partition.getName(), partition == null ? null : partition.getDescription());
                        break;
                    }
                    date = date.plusDays(1);
                }
            } else {
                // 有数据但无分区
                log.error("表{}无分区", table);
                properlyPartitioned = false;
            }

            // 2. 操作：根据上一步检查的结果，决定是否需要新增/重建分区

            // 提前往后面几天建分区
            LocalDate endDate = LocalDate.now().plusDays(systemConfig.getPartitionDayInAdvance());

            List<Partition> currentPartitions = partitions == null ? new ArrayList<>() : partitions;
            partitions = new ArrayList<>();

            LocalDate startDate = properlyPartitioned ? LocalDate.parse(date.toString()) : LocalDate.parse(minDate.toString());

            while (!startDate.isAfter(endDate)) {
                partitions.add(new Partition(this.partitionName(startDate), startDate.plusDays(1).toString()));
                startDate = startDate.plusDays(1);
            }
            if (partitions.size() > 0) {
                if (properlyPartitioned) {
                    log.info("即将新增{}分区：from {} to {}", table, date, endDate);
                    // 分区添加
                    partitionMapper.addPartitions(table, partitions);
                    currentPartitions.addAll(partitions);
                } else {
                    log.info("即将重建{}分区：from {} to {}", table, minDate, endDate);
                    // 分区重建
                    partitionMapper.setPartitions(table, "update_time", partitions);
                    currentPartitions = partitions;
                }
            }
            partitions = currentPartitions;
            log.info("表{}分区处理完成, 是否被正确分区: {}", table, properlyPartitioned);
            
            // 本地缓存当前表分区时间范围
            caffeineRepository.set(PARTITION_RANGE_CACHE, localCacheKey, Collections.unmodifiableList(CollectionUtils.listOf(
                    LocalDate.parse(partitions.get(0).getDescription().replaceAll("'", "")).minusDays(1),
                    LocalDate.parse(partitions.get(partitions.size() - 1).getDescription().replaceAll("'", "")).minusDays(1)
            )));
        } else {
            log.info("表{}为空", table);
            caffeineRepository.set(PARTITION_RANGE_CACHE, localCacheKey, Collections.unmodifiableList(new ArrayList<>()));
        }
    }

    @Override
    public String partitionName(LocalDate date) {
        return "p" + date.toString().substring(2).replaceAll("-", "");
    }
    /**
     * 把将要查询的日期范围(dateList)转换为实际需要查询的分区
    */
    @Override
    public List<String> partitionsForSearch(List<LocalDate> dateList, Integer device) {
        String localCacheKey = device == 0 ? CAMERA_PARTITION_DATE_RANGE : INTERCOM_PARTITION_DATE_RANGE;
        Cache.ValueWrapper valueWrapper = caffeineRepository.get(PARTITION_RANGE_CACHE, localCacheKey);
        List<LocalDate> partitionRange = (List<LocalDate>) valueWrapper.get();
        if (!CollectionUtils.hasElement(partitionRange)) {
            return new ArrayList<>();
        }
        LocalDate minDate = partitionRange.get(0);
        LocalDate maxDate = partitionRange.get(1);

        Set<String> partitions = new HashSet<>();
        dateList.forEach(date -> {
            if (date.isBefore(minDate)) {
                partitions.add(partitionService.partitionName(minDate));
            } else if (!date.isAfter(maxDate)) {
                partitions.add(partitionService.partitionName(date));
            }
        });
        return new ArrayList<>(partitions);
    }
}

package com.xx.yy.module.db.mapper;

import com.xx.yy.module.db.entity.Partition;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface PartitionMapper {

    @Select("SELECT concat('p',DATE_FORMAT(date_add(now(), interval 1 day),'%Y%m%d'))")
    String queryTomorrowDate();
 
    @Select("SELECT TO_DAYS(date_add(now(), interval 1 day) )")
    String queryTomorrowDays();
 
    @Select("SELECT concat('p',DATE_FORMAT(now(),'%Y%m%d'))")
    String queryDate();
 
    @Select("SELECT TO_DAYS(now())")
    String queryDays();

    @Select("SELECT MAX(DATE(update_time)) 'max', MIN(DATE(update_time)) 'min' FROM ${table}")
    Map<String, Date> queryMinMaxDate(@Param("table") String table);

    @Select("SELECT count(1) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=#{tableName} AND PARTITION_NAME is not null;")
    Integer selectCountPartition(@Param("tableName") String tableName);
 
    @Select("SELECT count(1) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = #{tableName} AND partition_name = #{partitionName};")
    Integer selectPartitionIsExist(@Param("tableName") String tableName, @Param("partitionName") String partitionName);
 
    @Select("SELECT partition_name" +
            "        FROM   INFORMATION_SCHEMA.PARTITIONS" +
            "        WHERE  TABLE_NAME = #{tableName} AND TABLE_SCHEMA = schema() AND partition_description <= to_days(date_sub(curdate(), INTERVAL ${day} DAY))")
    List<String> selectDaysAgoPartition(@Param("tableName") String tableName, @Param("day") Integer day);
 
    @Update("ALTER TABLE ${tableName} DROP PARTITION ${partitionName};")
    int deletePartition(@Param("tableName") String tableName, @Param("partitionName") String partitionName);
 
    @Update("ALTER TABLE ${table} partition by range (TO_DAYS(${timeField}))( partition ${partitionName} values less than(${day}));")
    int setPartition(@Param("table") String table, @Param("timeField") String timeField,
                     @Param("partitionName") String partitionName, @Param("day") String day);

    @Update({"<script>",
            " ALTER TABLE ${table} PARTITION BY RANGE COLUMNS(${partitionField})",
            " <foreach collection='partitions' item='item' open='(' separator=',' close=')'>",
            "partition ${item.name} values less than ('${item.description}')",
            " </foreach>",
            "</script>"
    })
    int setPartitions(@Param("table") String table, @Param("partitionField") String partitionField, List<Partition> partitions);

    @Update({"<script>",
            " ALTER TABLE ${table} ADD PARTITION ",
            " <foreach collection='partitions' item='item' open='(' separator=',' close=')'>",
                "partition ${item.name} values less than ('${item.description}')",
            " </foreach>",
            "</script>"
    })
    int addPartitions(@Param("table") String table, List<Partition> partitions);
 
    @Select("select partition_name as name, partition_description as description" +
            "  from information_schema.partitions" +
            " where table_schema = schema() and table_name = #{table}")
    List<Partition> queryPartition(@Param("table") String table);
 
}

参考文章

• MySQL使用定时任务动态分区
• MySQL分区管理