【mysql】日志表-表分区

最新推荐文章于 2023-12-08 11:23:36 发布

boyeson

最新推荐文章于 2023-12-08 11:23:36 发布

阅读量2k

点赞数

文章标签： mysql 数据库 range分区

本文链接：https://blog.csdn.net/boyeson/article/details/121693587

版权

MySQL 普通表改分区表过程

背景
- 注意
修改过程
完成

背景

因业务量越来越大，原本日志表是普通表，日志量越来越大，已达到千万级，且因相关查询统计需要频繁查询日志表，导致查询越来越慢，严重影响体验，故提出把日志表由普通表转为分区表，设计是按照ID+transDate 来分区，按照交易量，每个月一个区。

若对业务背景没有兴趣，可跳过，直接看后面步骤。【打扰】

注意

因为特殊场景特殊业务背景，要求我们对存量日志表的数据需要保留，且对新分区表重新开始记录日志。同时为了考虑千万级别数据量为此，我们对存量表进行重命名。在复制了存量表结构的前提下，对新表进行改造成分区表。

修改过程

1. 重命名表

rename table t_trans_log to t_trans_log_bak；

千万数据量表重命名，耗时0.06s。从而不中断业务。

2. 创建新表，复制存量表的表结构

create table t_trans_log like t_trans_log_bak;

-- 检查新表结果和索引，均无误。
show index from t_trans_log；

-- 新表需要重新开始记录，需要把ID自增列设置为初始值1
select auto_incrememt from information_schema.`TABLES` where table_name=`t_trans_log` limit 1;
-- 若不为1，则需要设置为1（一般新建表，自增列是初始值，或者truncate表后，自增列也是默认值）
ALTER TABLE t_trans_log AUTO_INCREMENT=0；

3. 修改表字段

-- 增加主键，通过主键进行分区
-- 自增列无法删除主键，先去掉自增列
alter table t_trans_log modify column `id` bigint(11) not null;
-- 删除主键
alter table t_trans_log drop primary key;
-- 新增组合主键
alter table t_trans_log add primary key(`id`,`trans_date`);
-- 还原ID为自增列
alter table t_trans_log modify column `id` bigint(11) not null auto_increment;

4. 修改表为分区表

-- 创建两年的分区
 alter table t_trans_log partition by rang columns(trans_date)
 (
PARTITION `P2201` VALUES LESS THAN ('2022-02-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2202` VALUES LESS THAN ('2022-03-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2203` VALUES LESS THAN ('2022-04-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2204` VALUES LESS THAN ('2022-05-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2205` VALUES LESS THAN ('2022-06-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2206` VALUES LESS THAN ('2022-07-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2207` VALUES LESS THAN ('2022-08-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2208` VALUES LESS THAN ('2022-09-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2209` VALUES LESS THAN ('2022-10-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2210` VALUES LESS THAN ('2022-11-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2211` VALUES LESS THAN ('2022-12-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2212` VALUES LESS THAN ('2023-01-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2301` VALUES LESS THAN ('2023-02-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2302` VALUES LESS THAN ('2023-03-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2303` VALUES LESS THAN ('2023-04-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2304` VALUES LESS THAN ('2023-05-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2305` VALUES LESS THAN ('2023-06-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2306` VALUES LESS THAN ('2023-07-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2307` VALUES LESS THAN ('2023-08-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2308` VALUES LESS THAN ('2023-09-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2309` VALUES LESS THAN ('2023-10-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2310` VALUES LESS THAN ('2023-11-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2311` VALUES LESS THAN ('2023-12-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `P2312` VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION PMORE VALUES LESS THAN MAXVALUE ENGINE = InnoDB
 )

5. 查看表分区详情

select * from information_schema.`PARTITIONS` where table_name='t_trans_log';

表分区详情：

表分区详情

完成