MySql 单表分区创建

案例一：按数据量进行分区处理

-- 创建表及手动创建分区，每个分区50条数据

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `partition_key` int(8) NOT NULL COMMENT '分区键(格式:yyyyMMdd)',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`partition_key`),
  UNIQUE KEY `id_UNIQUE` (`id`,`partition_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range(id)( 
partition p1 values less than(51),
partition p2 values less than(101), 
partition p3 values less than(151), 
partition p4 values less than(201), 
partition p5 values less than MAXVALUE );

单表按行数进行数据分区，上面代码中:

分区1，会写入 1~50 条数据

分区2，会写入 51~100 条数据

... 以此类推

最后一个分区5，写入201~以后的所有数据

这段代码设定，也奠定了MySql 采用时间进行分区实例的基础，也看出每个分区中， 设定的最大值小于 than 中的设定值

 

案例二：按时间 yyyyMMdd 格式进行分区处理

DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `partition_key` int(8) NOT NULL COMMENT '分区键(格式:yyyyMMdd)',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`partition_key`),
  UNIQUE KEY `id_UNIQUE` (`id`,`partition_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range(partition_key)( 
PARTITION p0 VALUES LESS THAN (20180619) ENGINE = InnoDB,
PARTITION p20180619 VALUES LESS THAN (20180620) ENGINE = InnoDB,
PARTITION p20180620 VALUES LESS THAN (20180621) ENGINE = InnoDB,
PARTITION p20180621 VALUES LESS THAN (20180622) ENGINE = InnoDB,
PARTITION p20180622 VALUES LESS THAN (20180623) ENGINE = InnoDB,
PARTITION p20180623 VALUES LESS THAN (20180624) ENGINE = InnoDB,
PARTITION p20180624 VALUES LESS THAN (20180625) ENGINE = InnoDB
);

这段代码中，设定 2018年6月19日的时间分区，一定小于 then(20180620) 的设定，所以，分区 p20180619 中的数据为 2018年6月19日的数据，其余设定以此类推

 

案例三：按时间字段进行分区处理

DROP TABLE IF EXISTS `test3`;
CREATE TABLE `test3` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `partition_key` int(8) NOT NULL COMMENT '分区键(格式:yyyyMMdd)',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`create_time`),
  UNIQUE KEY `id_UNIQUE` (`id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range(TO_DAYS(create_time))( 
PARTITION p20180618 VALUES LESS THAN (TO_DAYS('2018-06-19')) ENGINE = InnoDB,
PARTITION p20180619 VALUES LESS THAN (TO_DAYS('2018-06-20')) ENGINE = InnoDB,
PARTITION p20180620 VALUES LESS THAN (TO_DAYS('2018-06-21')) ENGINE = InnoDB,
PARTITION p20180621 VALUES LESS THAN (TO_DAYS('2018-06-22')) ENGINE = InnoDB,
PARTITION p20180622 VALUES LESS THAN (TO_DAYS('2018-06-23')) ENGINE = InnoDB,
PARTITION p20180623 VALUES LESS THAN (TO_DAYS('2018-06-24')) ENGINE = InnoDB,
PARTITION p20180624 VALUES LESS THAN (TO_DAYS('2018-06-25')) ENGINE = InnoDB
);

案例三同案例二原理一致

 

新增分区案例：沿用案例三进行分区新增

-- 新增分区 2018年6月25日分区
alter TABLE `test6` add PARTITION(
 PARTITION p20180625 VALUES LESS THAN (TO_DAYS('2018-06-26')) ENGINE = InnoDB
);

 

删除分区：沿用案例三进行分区删除

alter table `test3`  drop PARTITION p20180618;

 

删除分区，新增分区注意事项：

       删除旧的分区后，不能再次创建，原因新的分区中的自增属性，持续增加，不能将新的自增添加至旧的分区中，所以，删除分区需要额外注意

 

对未分区的表进行分区处理

alter TABLE `test6` PARTITION BY RANGE(TO_DAYS(create_time)) (
    PARTITION p20180618 VALUES LESS THAN (TO_DAYS('2018-06-19')) ENGINE = InnoDB,
    PARTITION p20180619 VALUES LESS THAN (TO_DAYS('2018-06-20')) ENGINE = InnoDB,
    PARTITION p20180620 VALUES LESS THAN (TO_DAYS('2018-06-21')) ENGINE = InnoDB,
    PARTITION p20180621 VALUES LESS THAN (TO_DAYS('2018-06-22')) ENGINE = InnoDB,
    PARTITION p20180622 VALUES LESS THAN (TO_DAYS('2018-06-23')) ENGINE = InnoDB,
    PARTITION p20180623 VALUES LESS THAN (TO_DAYS('2018-06-24')) ENGINE = InnoDB,
    PARTITION p20180624 VALUES LESS THAN (TO_DAYS('2018-06-25')) ENGINE = InnoDB,
    PARTITION p20180625 VALUES LESS THAN (TO_DAYS('2018-06-26')) ENGINE = InnoDB
);

 

自动创建分区：

mysql中无法自动创建分区，需要进行定时任务执行，或者程序定时创建，2选1

 

后续更新 mysql 定时创建分区任务....