【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(() -> {