mysql的分区表

2024-10-23 来源：个人技术集锦

1.SQL表创建

下面以时间范围进行创建（每月一个分区，表中创建了四个月的分区）

创建：

CREATE TABLE test_table (  
    id INT NOT NULL AUTO_INCREMENT,  
    content VARCHAR(255),  
    create_time DATETIME NOT NULL,
   PRIMARY KEY (id, create_time) 
) PARTITION BY RANGE (TO_DAYS(create_time)) (  
    PARTITION p20240601 VALUES LESS THAN (TO_DAYS('2024-06-01')),  
    PARTITION p20240701 VALUES LESS THAN (TO_DAYS('2024-07-01')),  
    PARTITION p20241801 VALUES LESS THAN (TO_DAYS('2024-08-01')),  
    PARTITION p20240901 VALUES LESS THAN (TO_DAYS('2024-09-01'))
);  

查询分区详情：
SELECT *
FROM 
    INFORMATION_SCHEMA.PARTITIONS 
WHERE 
    TABLE_NAME = 'test_table';

2、mapper文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="*.infrastructure.mapper.TestTableMapper">

    <resultMap id="TestTable" type="*.domain.entity.TestTable">
        <id column="id" property="id" typeHandler="org.apache.ibatis.type.LongTypeHandler"/>
        <result property="content" column="content" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"
                typeHandler="org.apache.ibatis.type.LocalDateTimeTypeHandler"/>
    </resultMap>

    <!-- 创建新分区 -->
    <update id="createNewPartition">
        ALTER TABLE TEST_TABLE
            ADD PARTITION (  
                PARTITION ${partitionName} VALUES LESS THAN (TO_DAYS(#{lessThanValue}))
            )
    </update>

    <!-- 删除旧分区 -->
    <update id="dropPartition">
        ALTER TABLE TEST_TABLE
        DROP
        PARTITION
        ${partitionName}
    </update>

    <!--查询是否存在分区-->
    <select id="exitsPartition" resultType="boolean">
        SELECT COUNT(1) > 0
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_NAME = 'TEST_TABLE'
          AND PARTITION_NAME = #{partitionName}
    </select>

</mapper>

3、service

package *.domain.service;

import *.domain.entity.TestTable;
import *.infrastructure.repo.TestTableRepository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.Random;

@Service
public class TestTableService {
    @Autowired
    TestTableRepository repository;

	// 插入数据，如果分区不存在，就创建分区，重新插入
    public void insert() {
        TestTable testTable = new TestTable();
        testTable.setContent("test");

        Random random = new Random();
        int i = Math.abs(random.nextInt()) % 365;
        LocalDateTime dateTime = LocalDateTime.now().minusDays(i);
        testTable.setCreateTime(dateTime);

        try {
            repository.getBaseMapper().insert(testTable);
        } catch (DataAccessException e) {
            LocalDate nextMonthFirstDay = YearMonth.from(dateTime).plusMonths(1).atDay(1);
            String lessThanValue = nextMonthFirstDay.format(DateTimeFormatter.ISO_DATE);
            String partitionName = "p" + lessThanValue.replaceAll("-", "");
            // 创建分区时加锁，如果是多节点，需要分布式锁
            synchronized (this) {
                if (!repository.getBaseMapper().exitsPartition(partitionName)) {
                    repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);
                }
            }
            repository.getBaseMapper().insert(testTable);
        }
    }

	// 创建分区
    public void createNewPartition(String partitionName, String lessThanValue) {
        repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);
    }

	// 删除分区
    public void dropPartition(String partitionName) {
        repository.getBaseMapper().dropPartition(partitionName);
    }
}

----------------分割线-------------------------------

上述方法用代码来判断分区，新增分区，可能会引入一些奇奇怪怪的问题，因此，优化如下：

【针对mysql，使用mysql的定时事件】

1、首先确认mysql的时间调度器是否已经开启：

-- 查询事件调度器是否开启
SHOW VARIABLES LIKE 'event_scheduler'; 

-- 确保事件调度器已经开启  
SET GLOBAL event_scheduler = ON;

2、写存储过程，用于创建新的分区, 这里是按天创建新的分区

DELIMITER //  
  
CREATE PROCEDURE `AddDailyPartition`()  
BEGIN  
    DECLARE tomorrow DATE;  
    DECLARE partition_name VARCHAR(20);  
  
    -- 计算明天的日期  
    SET tomorrow = DATE_FORMAT(CURDATE() + INTERVAL 1 DAY, '%Y-%m-%d');  
    SET partition_name = CONCAT('p', DATE_FORMAT(tomorrow, '%Y%m%d'));  
  
    -- 构建ALTER TABLE语句来添加分区  
    SET @sql = CONCAT('ALTER TABLE TEST_TABLE ',  
                      'ADD PARTITION (PARTITION ', partition_name,   
                      ' VALUES LESS THAN (TO_DAYS(\'', tomorrow, '\')))');  
  
    -- 执行ALTER TABLE语句  
    PREPARE stmt FROM @sql;  
    EXECUTE stmt;  
    DEALLOCATE PREPARE stmt;  
END //  
  
DELIMITER ;

3、创建定时事件，调用存储过程

-- 创建定时事件  
CREATE EVENT `CreateDailyPartition`  
    ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE())  
    DO CALL AddDailyPartition();

4、查看已经创建的定时事件

SELECT * FROM information_schema.EVENTS; 

在查看事件时，重要的列包括：
EVENT_NAME: 事件的名称。
EVENT_SCHEMA: 事件所属的数据库。
STATUS: 事件的状态，比如是否为ENABLED或DISABLED。
STARTS: 事件开始的时间。
ENDS: 事件结束的时间（如果有设置的话）。
LAST_EXECUTED: 事件上一次执行的时间。
EVENT_DEFINITION: 事件定义，即事件中要执行的SQL语句。

显示全文

全部栏目

mysql的分区表