MySQL按月自动设置表分区的实现

CREATE TABLE `ticket_history_info` (
  `CALL_DATE` datetime DEFAULT NULL,
  `SRC_ADD` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CALLING_NUM` text COLLATE utf8mb4_unicode_ci,
  `DURATION` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (to_days(`CALL_DATE`))
(PARTITION p0 VALUES LESS THAN (738764) ENGINE = InnoDB,
 PARTITION p202502 VALUES LESS THAN (739676) ENGINE = InnoDB) */;

 

开始检查

首先，确保 ticket_history_info表是一个分区表。如果未设置分区，需要修改表结构以支持分区，将 ticket_history_info 表设置为按日期范围进行分区

1
2
3
4
ALTER TABLE ticket_history_info
PARTITION BY RANGE (TO_DAYS(CALL_DATE)) (
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2022-09-01'))
);

1.创建分区函数

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建分区函数
DELIMITER //
CREATE FUNCTION get_partition_name(p_date DATE)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE p_month VARCHAR(2);
    DECLARE p_year VARCHAR(4);
    SET p_month = LPAD(MONTH(p_date), 2, '0');
    SET p_year = YEAR(p_date);
    RETURN CONCAT('p', p_year, p_month);
END//
DELIMITER ;

检查是否成功创建函数

1
SHOW FUNCTION STATUS LIKE 'get_partition_name';

2.创建存储过程，用于自动生成分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 存储过程
DELIMITER //
CREATE PROCEDURE create_monthly_partition()
BEGIN
    DECLARE next_month VARCHAR(20);
    DECLARE next_month_first_day DATE;
     
    -- 计算下一个月份的名称和下个月的第一天
    SET next_month = CONCAT('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m'));
    SET next_month_first_day = LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) + INTERVAL 1 DAY;
     
    -- 检查分区是否已存在
    IF NOT EXISTS (
        SELECT NULL
        FROM information_schema.PARTITIONS
        WHERE TABLE_NAME = 'ticket_history_info' AND TABLE_SCHEMA = 'ccm' AND PARTITION_NAME = next_month
    ) THEN
        -- 创建新的分区
        SET @sql = CONCAT('ALTER TABLE ticket_history_info ADD PARTITION (PARTITION ', next_month, ' VALUES LESS THAN (TO_DAYS(\'', next_month_first_day, '\')))');
 
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END//
DELIMITER ;

 进行测试

 检查是否成功创建函数

1
SHOW PROCEDURE STATUS LIKE 'create_monthly_partition';

执行函数

1
CALL create_monthly_partition();

查询表分区，查看是否成功分区。

1
SELECT PARTITION_NAME FROM information_schema.PARTITIONS WHERE TABLE_NAME = '表名' AND TABLE_SCHEMA = '数据库名';

可通过修改本地系统时间，来进行反复测试是否可按照月份进行分区。

返回成功，显示已创建表分区。

3.创建自动删除半年以前的表空间函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DELIMITER //
 
CREATE PROCEDURE delete_old_partitions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE drop_partition_name VARCHAR(64);
    DECLARE cur CURSOR FOR
        SELECT PARTITION_NAME
        FROM information_schema.PARTITIONS
        WHERE TABLE_SCHEMA = '数据库名'
          AND TABLE_NAME = 'ticket_history_info'
          AND PARTITION_DESCRIPTION < TO_DAYS(NOW() - INTERVAL 6 MONTH);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
    OPEN cur;
 
    read_loop: LOOP
        FETCH cur INTO drop_partition_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('ALTER TABLE ticket_history_info DROP PARTITION ', drop_partition_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
 
    CLOSE cur;
 
   -- 删除超过半年的数据
    DELETE FROM ticket_history_info_copy1
    WHERE CALL_DATE < CURDATE() - INTERVAL 6 MONTH;
 
END//
 
DELIMITER ;

4.创建调度任务，修改到每月最后一天执行执行存储过程

1
2
3
4
5
6
7
8
CREATE EVENT IF NOT EXISTS monthly_partition_event
ON SCHEDULE EVERY 1 MONTH
STARTS CONCAT(DATE_FORMAT(LAST_DAY(CURRENT_DATE), '%Y-%m-'), DAY(LAST_DAY(CURRENT_DATE)), ' 23:00:00')
DO
BEGIN
    CALL create_monthly_partition();
    CALL delete_old_partitions();
END;

查看事件调度器是否启动

1
SET GLOBAL event_scheduler = ON;

查看事件

1
SHOW EVENTS;

或

1
SHOW EVENTS FROM `数据库名`;

创建成功

5.进行测试

插入测试数据进行测试，根据CALL_DATE字段进行数据分区，

1
2
3
4
5
INSERT INTO ticket_history_info (CALL_DATE, SRC_ADD, CALLING_NUM, DURATION) VALUES
('2024-05-24 00:00:00', 'Address1', '1234567890', 60),
('2024-06-20 00:00:00', 'Address2', '0987654321', 120),
('2024-05-10 00:00:00', 'Address3', '1122334455', 180),
('2024-10-01 00:00:00', 'Address4', '5566778899', 240);

-查询每个分区的行数

1
2
3
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'ccm' AND TABLE_NAME = 'ticket_history_info';

或者单独查询某一个分区的数据

1
2
SELECT *
FROM ticket_history_info PARTITION (p202406);

 

mysql 动态创建分区表

在MySQL中，动态创建分区表通常指的是在运行时基于某些条件（例如，日期、数值范围等）动态地将数据分散到多个分区中。MySQL支持几种类型的分区，其中最常用的是范围分区（RANGE PARTITIONING）、列表分区（LIST PARTITIONING）、哈希分区（HASH PARTITIONING）和键分区（KEY PARTITIONING）。

1. 范围分区（RANGE PARTITIONING）

范围分区允许你将数据分散到连续的区间内。例如，你可以根据日期来分区数据。

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
CREATE TABLE sales (
sale_id INT AUTO_INCREMENT,
sale_date DATE,
amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021),
PARTITION p2 VALUES LESS THAN (2022),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
 
 

2. 列表分区（LIST PARTITIONING）

列表分区允许你将数据分散到预定义的列表中。例如，根据地区来分区。

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
CREATE TABLE employees (
employee_id INT,
region_code CHAR(3),
salary DECIMAL(10, 2)
)
PARTITION BY LIST (region_code) (
PARTITION pNorth VALUES IN ('NOR', 'NTH'),
PARTITION pSouth VALUES IN ('SOU', 'UTH'),
PARTITION pEast VALUES IN ('EAS'),
PARTITION pWest VALUES IN ('WES')
);
 
 

3. 哈希分区（HASH PARTITIONING）

哈希分区基于某个列的哈希值来分配数据到不同的分区。

 
 
 
 
 
 
 
 
 
 
 
 
CREATE TABLE sales_hash (
sale_id INT AUTO_INCREMENT,
sale_date DATE,
amount DECIMAL(10, 2)
)
PARTITION BY HASH(YEAR(sale_date))
PARTITIONS 4; -- 将数据分散到4个分区中
 
 

4. 键分区（KEY PARTITIONING）

键分区是结合了哈希和列表或范围分区的特性。MySQL实际上不直接支持键分区，但可以通过组合范围和哈希分区来实现类似的效果。例如，可以创建一个基于年份范围和哈希的复合分区。

动态创建分区表的方法：

虽然MySQL本身不支持在运行时动态添加或删除分区，但你可以通过以下方式“动态”管理分区：

1. 使用ALTER TABLE添加新分区：

 
 
 
 
 
 
ALTER TABLE sales ADD PARTITION (PARTITION p4 VALUES LESS THAN (2023));
 
 

这将向sales表添加一个新的分区。注意，这种方法通常在你知道将要添加的数据范围时使用。

2. 使用OPTIMIZE PARTITION或REORGANIZE PARTITION：

如果你需要重新组织现有数据到新的分区策略，可以使用REORGANIZE PARTITION：

 
 
 
 
 
 
 
 
 
 
ALTER TABLE sales REORGANIZE PARTITION p0, p1 INTO (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION pFuture VALUES LESS THAN MAXVALUE
);
 
 

这将重新定义sales表的分区策略。

3. 使用触发器自动管理分区：

虽然MySQL不直接支持触发器自动创建新分区，但你可以通过应用逻辑（例如，使用应用程序代码或存储过程）来定期检查并添加新分区。例如，当检测到新的一年时，可以自动添加新分区。这通常需要定期运行的任务或事件调度器来触发。

结论：

虽然MySQL不提供直接在运行时动态添加或删除分区的原生支持，但通过上述方法，你可以有效地管理和扩展你的分区表，以适应不断变化的数据需求。对于更复杂的动态管理需求，考虑结合使用应用程序逻辑和数据库操作来实现最佳实践。

 

posted @ 2025-01-24 12:52  CharyGao  阅读(321)  评论(0)    收藏  举报