mysql分区

-- 1. 分区相关
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE ens_rb.add_partitions_by_day(in table_name varchar(30), start_str varchar(30), end_str varchar(30))
BEGIN
        declare start_d date default DATE_FORMAT(start_str, '%Y-%m-%d');
        declare end_d date default DATE_FORMAT(end_str, '%Y-%m-%d');

    WHILE start_d < end_d DO
        SET @sql = CONCAT('ALTER TABLE ', table_name, ' ADD PARTITION (PARTITION p', REPLACE(start_d, '-', ''), ' VALUES LESS THAN (TO_DAYS("', start_d, '")))');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET start_d = DATE_ADD(start_d, INTERVAL 7 day);
    END WHILE;
END$$

DELIMITER ;


-- 删除已有分区
alter table ens_rb.rb_accr_hist drop PARTITION p202409;
alter table ens_rb.rb_accr_hist drop PARTITION p202410;
alter table ens_rb.rb_accr_hist drop PARTITION p202411;
alter table ens_rb.rb_accr_hist drop PARTITION p202412;
alter table ens_rb.rb_accr_hist drop PARTITION p202501;


-- 批量创建分区
call add_partitions_by_day('ens_rb.rb_accr_hist', '20240807', '20260101');


-- 2. 备份数据

-- 复制表
create table ens_rb.rb_accr_his_bak like ens_rb.rb_accr_hist;

-- 数据迁移
insert into ens_rb.rb_accr_his_bak select * from ens_rb.rb_accr_hist partition(p202310);
insert into ens_rb.rb_accr_his_bak select * from ens_rb.rb_accr_hist partition(p202311);
insert into ens_rb.rb_accr_his_bak select * from ens_rb.rb_accr_hist partition(p202312);
insert into ens_rb.rb_accr_his_bak select * from ens_rb.rb_accr_hist partition(p202401);
insert into ens_rb.rb_accr_his_bak select * from ens_rb.rb_accr_hist partition(p202402);
insert into ens_rb.rb_accr_his_bak select * from ens_rb.rb_accr_hist partition(p202403);
insert into ens_rb.rb_accr_his_bak select * from ens_rb.rb_accr_hist partition(p202404);
insert into ens_rb.rb_accr_his_bak select * from ens_rb.rb_accr_hist partition(p202405);

-- 3. 删除分区数据
alter table ens_rb.rb_accr_hist drop PARTITION p202310;
alter table ens_rb.rb_accr_hist drop PARTITION p202311;
alter table ens_rb.rb_accr_hist drop PARTITION p202312;
alter table ens_rb.rb_accr_hist drop PARTITION p202401;
alter table ens_rb.rb_accr_hist drop PARTITION p202402;
alter table ens_rb.rb_accr_hist drop PARTITION p202403;
alter table ens_rb.rb_accr_hist drop PARTITION p202404;
alter table ens_rb.rb_accr_hist drop PARTITION p202405;

 

-- 分区7天
-- 备份到历史表， 只保留最近三个月的数据

-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE add_partitions_by_day(in table_name varchar(30), start_str varchar(30), end_str varchar(30))
BEGIN
        declare start_d date default DATE_FORMAT(start_str, '%Y-%m-%d');
        declare end_d date default DATE_FORMAT(end_str, '%Y-%m-%d');

    WHILE start_d < end_d DO
        SET @sql = CONCAT('ALTER TABLE ', table_name, ' ADD PARTITION (PARTITION p', REPLACE(start_d, '-', ''), ' VALUES LESS THAN (TO_DAYS("', start_d, '")))');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET start_d = DATE_ADD(start_d, INTERVAL 7 day);
    END WHILE;
END$$

DELIMITER ;

call add_partitions_by_day('ens_rb.rb_accr_hist', '202', '20250110');

-- 更新表统计
ANALYZE TABLE ens_rb.rb_accr_hist;
FLUSH TABLES;

-- 查看分区信息
SELECT
    partition_name part,
    partition_expression expr,
    partition_description descr,
    from_days(partition_description),
    table_rows 
FROM
    information_schema.PARTITIONS 
WHERE
    table_schema = 'ens_rb' 
    AND table_name = 'rb_accr_hist';
    
    select min(ACCR_DATE), max(ACCR_DATE) from ens_rb.rb_accr_hist;
 select DATE_FORMAT(ACCR_DATE, '%Y-%m') , count(1) as cnt from  ens_rb.rb_accr_hist group by DATE_FORMAT(ACCR_DATE, '%Y-%m') order by  DATE_FORMAT(ACCR_DATE, '%y-%m')

-- 正式操作流程 ------------------------

select count(1) from rb_accr_hist;     -- 2383643
select count(1) from rb_accr_hist;  -- 2383643
-
-- 删除所有分区
ALTER TABLE ens_rb.rb_accr_hist REMOVE PARTITIONING;

-- 对新表创建分区
ALTER TABLE ens_rb.rb_accr_hist 
PARTITION BY RANGE (TO_DAYS(ACCR_DATE)) (
    PARTITION p20240101 VALUES LESS THAN (TO_DAYS('20240101')),
        PARTITION p20240201 VALUES LESS THAN (TO_DAYS('20240201')),
        PARTITION p20240301 VALUES LESS THAN (TO_DAYS('20240301')),
        PARTITION p20240401 VALUES LESS THAN (TO_DAYS('20240401')),
        PARTITION p20240501 VALUES LESS THAN (TO_DAYS('20240501')),
        PARTITION p20240601 VALUES LESS THAN (TO_DAYS('20240601')),
        PARTITION p20240701 VALUES LESS THAN (TO_DAYS('20240701')),
        PARTITION p20240801 VALUES LESS THAN (TO_DAYS('20240801')),
        PARTITION p20240901 VALUES LESS THAN (TO_DAYS('20240901')),
        PARTITION p20241001 VALUES LESS THAN (TO_DAYS('20241001')),
        PARTITION p20241007 VALUES LESS THAN (TO_DAYS('20241007'))
);

select * from ens_rb.rb_accr_hist partition(p20240301);

select from_days('739495')

-- 删除已有的分区（这里会删除分区的数据）
alter table ens_rb.rb_accr_hist drop PARTITION p20250101;

-- 添加单个分区
ALTER TABLE ens_rb.rb_accr_hist ADD PARTITION (PARTITION p20231001 VALUES LESS THAN (TO_DAYS('20231001')));


-- 批量添加分区
call add_partitions_by_month('ens_rb.rb_accr_hist', '20240714', '20240901');
call add_partitions_by_day('ens_rb.rb_accr_hist', '20241014', '20250101');


-- 分区数据备份
insert into ens_rb.rb_accr_hist_bak select * from ens_rb.rb_accr_hist PARTITION(p20240101);
insert into ens_rb.rb_accr_hist_bak select * from ens_rb.rb_accr_hist PARTITION(p20240201);
insert into ens_rb.rb_accr_hist_bak select * from ens_rb.rb_accr_hist PARTITION(p20240301);
insert into ens_rb.rb_accr_hist_bak select * from ens_rb.rb_accr_hist PARTITION(p20240401);
insert into ens_rb.rb_accr_hist_bak select * from ens_rb.rb_accr_hist PARTITION(p20240501);
insert into ens_rb.rb_accr_hist_bak select * from ens_rb.rb_accr_hist PARTITION(p20240601);
insert into ens_rb.rb_accr_hist_bak select * from ens_rb.rb_accr_hist PARTITION(p20240701);

select count(1) from ens_rb.rb_accr_hist_bak;
select min(ACCR_DATE), max(ACCR_DATE) from ens_rb.rb_accr_hist_bak;

-- 删除已有的分区（这里会删除分区的数据）
alter table ens_rb.rb_accr_hist drop PARTITION p20240101;
alter table ens_rb.rb_accr_hist drop PARTITION p20240201;
alter table ens_rb.rb_accr_hist drop PARTITION p20240301;
alter table ens_rb.rb_accr_hist drop PARTITION p20240401;
alter table ens_rb.rb_accr_hist drop PARTITION p20240501;
alter table ens_rb.rb_accr_hist drop PARTITION p20240601;
alter table ens_rb.rb_accr_hist drop PARTITION p20240701;