MYSQL普通表在线改成分区表

海鲨数据库架构师 2021-09-02

2737

普通表的结构拥有170万数据 400MB

Mysql5.7范围分区操作

比如普通表结构如下:

CREATE TABLE `account_history` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guest_no` varchar(30) NOT NULL,
  `money` decimal(20,2) NOT NULL COMMENT '余额',
  `type` smallint(3) NOT NULL COMMENT '类型',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `before_money` decimal(20,2) DEFAULT NULL COMMENT '改变前余额',
  `after_money` decimal(20,2) DEFAULT NULL COMMENT '改变后余额'
  PRIMARY KEY (`Id`),
  UNIQUE KEY `idx_guest_no` (`guest_no`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3997619 DEFAULT CHARSET=utf8;

复制

第一步改变表主键

ALTER TABLE account_history  DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`create_time`);

复制

改变后的结构

CREATE TABLE `account_history` 
(
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
   `guest_no` varchar(30) NOT NULL,
  `money` decimal(20,2) NOT NULL,
  `type` smallint(3) NOT NULL COMMENT '类型',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `before_money` decimal(20,2) DEFAULT NULL COMMENT '改变前余额',
  `after_money` decimal(20,2) DEFAULT NULL COMMENT '改变后余额'
  PRIMARY KEY (`Id`,`create_time`),
  UNIQUE KEY `idx_guest_no` (`guest_no`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3997619 DEFAULT CHARSET=utf8;

复制

第二步修改成分区

alter table account_history partition by range(to_days(create_time))
(
 PARTITION create_time_20210902 VALUES LESS THAN (to_days('2021-09-02')),
 PARTITION create_time_DEFAULTE VALUES LESS THAN MAXVALUE 
); 
) ENGINE=InnoDB AUTO_INCREMENT=3997619 DEFAULT CHARSET=utf8;

复制

结果都报错：

Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function'

复制

网上大意是说主键必须包含分区字段，可是前面已经修改了主键添加了分区字段啊

试错 1 把分区字段做成唯一索引

ALTER TABLE account_history DROP INDEX create_time_idx;
CREATE UNIQUE INDEX `create_time_idx` USING BTREE ON account_history (create_time);

复制

Error Code: 1503

试错 2 建立一个包含主键在内的组合唯一索引

CREATE UNIQUE INDEX `idx_combo_idcreatetime` USING BTREE ON account_history (Id, create_time);

复制

Error Code: 1503

试错 3 把所有唯一索引追加到主键列表

ALTER TABLE account_history  DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`create_time`,`guest_no`);

复制

Error Code: 1503

试错4 建立3个字段（ID,CREATETIME,GUESTNO)组合唯一索引

ALTER TABLE account_history DROP INDEX idx_combo_idcreatetime;
CREATE UNIQUE INDEX `idx_combo_idcreatetime` USING BTREE ON account_history (flow_no, Id, create_time);

复制

试错 5 把唯一索引删除

是所有的唯一索引

ALTER TABLE account_history DROP INDEX idx_guest_no;
ALTER TABLE account_history DROP INDEX idx_create_time;
ALTER TABLE account_history DROP INDEX idx_combo_idcreatetime;

复制

此时的表结构如下

CREATE TABLE `account_history` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guest_no` varchar(30) NOT NULL,
  `money` decimal(20,2) NOT NULL,
  `type` smallint(3) NOT NULL COMMENT '类型',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `before_money` decimal(20,2) DEFAULT NULL COMMENT '改变前余额',
  `after_money` decimal(20,2) DEFAULT NULL COMMENT '改变后余额'
  PRIMARY KEY (`Id`,`create_time`,`guest_no`),
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3997619 DEFAULT CHARSET=utf8;

复制

执行成功不再报错误了

alter table account_history partition by range(to_days(create_time))
(
 PARTITION create_time_20210902 VALUES LESS THAN (to_days('2021-09-02')),
 PARTITION create_time_DEFAULTE VALUES LESS THAN MAXVALUE 
); 
) ENGINE=InnoDB AUTO_INCREMENT=3997619 DEFAULT CHARSET=utf8;8;

复制

CREATE TABLE `account_history` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guest_no` varchar(30) NOT NULL,
  `money` decimal(20,2) NOT NULL,
  `type` smallint(3) NOT NULL COMMENT '类型',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `before_money` decimal(20,2) DEFAULT NULL COMMENT '改变前余额',
  `after_money` decimal(20,2) DEFAULT NULL COMMENT '改变后余额'
  PRIMARY KEY (`Id`,`create_time`,`guest_no`),
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3997619 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(create_time))
(PARTITION create_time_20210902 VALUES LESS THAN (738400) ENGINE = InnoDB,
 PARTITION create_time_DEFAULTE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

复制

这段报错信息翻译过来是唯一索引包含的字段必须在分区函数OR分区功能里

A UNIQUE INDEX must include all columns in the table's partitioning function'

显然很难理解。

同样的错误代码 1503 网上大部分都是说分区字段必须包含在主键列表中。

而此次是说唯一索引的问题，通过实验说明了

1 唯一索引字段在主键列表里，

在实验下唯一索引字段可以不用在主键列表里，如果你把唯一索引给删除了。

2 不能由单独的唯一索引

这能理解，唯一索引分区后，就变成了本地索引，只能局部保证唯一性，全分区就无法保证，

所以必须包含在主键列表中。

##添加分区

ALTER TABLE DBA_merchant_account_history ADD PARTITION (PARTITION createtime_20210908 VALUES LESS THAN (to_days('2021-09-08')));

复制

Error: MAXVALUE can only be used in last partition definition

ALTER TABLE DBA_merchant_account_history  
    REORGANIZE PARTITION create_time_DEFAULTE INTO (
    PARTITION createtime_20210908 VALUES LESS THAN (to_days('2021-09-08'))
    );

复制

Error: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range

 ALTER TABLE DBA_merchant_account_history  
    REORGANIZE PARTITION create_time_DEFAULTE INTO (
    PARTITION create_time_20210908 VALUES LESS THAN (to_days('2021-09-08')),
    PARTITION create_time_DEFAULTE VALUES LESS THAN MAXVALUE
    );

复制

 CREATE TABLE `account_history` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guest_no` varchar(30) NOT NULL,
  `money` decimal(20,2) NOT NULL,
  `type` smallint(3) NOT NULL COMMENT '类型',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `before_money` decimal(20,2) DEFAULT NULL COMMENT '改变前余额',
  `after_money` decimal(20,2) DEFAULT NULL COMMENT '改变后余额'
  PRIMARY KEY (`Id`,`create_time`),
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3997619 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(create_time))
(PARTITION create_time_20210902 VALUES LESS THAN (738400) ENGINE = InnoDB,
 PARTITION createtime_20210908 VALUES LESS THAN (738406) ENGINE = InnoDB,
 PARTITION create_time_DEFAULTE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

复制

Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function'

无论哪种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

言外之意分区字段添加到唯一索引中去？

 ALTER TABLE `account_history` DROP INDEX flow_no_idx;
CREATE UNIQUE INDEX `idx_guest_no` USING BTREE ON account_history (guest_no, create_time);

复制

居然也OK 了

 CREATE TABLE `account_history` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guest_no` varchar(30) NOT NULL,
  `money` decimal(20,2) NOT NULL,
  `type` smallint(3) NOT NULL COMMENT '类型',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `before_money` decimal(20,2) DEFAULT NULL COMMENT '改变前余额',
  `after_money` decimal(20,2) DEFAULT NULL COMMENT '改变后余额'
  PRIMARY KEY (`Id`,`create_time`),
  UNIQUE KEY `idx_guest_no` (`guest_no`,`create_time`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3997619 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(create_time))
(PARTITION create_time_20210902 VALUES LESS THAN (738400) ENGINE = InnoDB,
 PARTITION createtime_20210908 VALUES LESS THAN (738406) ENGINE = InnoDB,
 PARTITION create_time_DEFAULTE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

复制

这主要是考虑为什么呢? 如果说把分区字段添加到主键里面,主要是帮助主键完成数据裁剪? 唯一索引也要添加分区字段是保证其唯一性,从本地索引变成了全局索引? 欢迎大家评论

mysql

文章转载自海鲨数据库架构师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MYSQL普通表 在线 改成 分区表

评论

相关阅读

MYSQL普通表在线改成分区表