我们现在有一张表,tb_member_login_logs 用户登录日志表,但数据量太大,需要分区,建议新增一个days字段,int类型,值为 Ymd。

ALTER TABLE `tb_member_login_logs` ADD COLUMN `days` INT (11) UNSIGNED NOT NULL DEFAULT 19700101 COMMENT '日期(格式:Ymd)' AFTER `ID`;

mysql创建分区,分区的字段必须在主键内,不然后报如下错误:

1503 - A PRIMARY KEY must include all columns in the table's partitioning function

所以我们需要创建一个联合主键,首先把 主键 ID 自增删除,

ALTER TABLE `tb_member_login_logs` MODIFY COLUMN `id` INT (11) UNSIGNED NOT NULL COMMENT 'ID' FIRST;

然后再删除主键 ID

ALTER TABLE `tb_member_login_logs` DROP PRIMARY KEY;

添加 ID 与 days 的联合主键

ALTER TABLE `tb_member_login_logs` ADD PRIMARY KEY (`id`, `days`);

然后恢复 ID 的自增

ALTER TABLE `tb_member_login_logs` MODIFY COLUMN `id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID' FIRST;

然后根据需要创建分区

ALTER TABLE `tb_member_login_logs` PARTITION BY RANGE(days) (
	PARTITION `p197001` VALUES LESS THAN (19700101),
	PARTITION `p202003` VALUES LESS THAN (20200301),
	PARTITION `p202004` VALUES LESS THAN (20200401),
	PARTITION `p202005` VALUES LESS THAN (20200501),
	PARTITION `p202006` VALUES LESS THAN (20200601),
	PARTITION `p202007` VALUES LESS THAN (20200701),
	PARTITION `p202008` VALUES LESS THAN (20200801),
	PARTITION `p202009` VALUES LESS THAN (20200901),
	PARTITION `p202010` VALUES LESS THAN (20201001),
	PARTITION `p202011` VALUES LESS THAN (20201101),
	PARTITION `p202012` VALUES LESS THAN (20201201),
	PARTITION `p202101` VALUES LESS THAN (20210101),
	PARTITION `p202102` VALUES LESS THAN (20210201),
	PARTITION `p202103` VALUES LESS THAN (20210301)
);

当然这种做法,运行时间会比较长。

可以创建一个结构与老表一样并分好区的新表,把老表的数据导入新表中。

posted on 2020-03-31 11:02  怀素真  阅读(15308)  评论(0编辑  收藏  举报