Day.25 提升大数据资料管理 - 资料表分区 ( MYSQL Partition)_2

今天延续昨天的内容，继续介绍表分区类型～明天呢我们就将资料表分区的流程实际用到实务上使用，达到每天自行清除旧资料与新建後续分区的作业。

范例表结构:

CREATE TABLE `act` (
  `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活动ID',
  `status` bigint(20) DEFAULT NULL COMMENT '活动状态 0(下架) 1(上架) 2(准备中)',
  `start_time` int(11) unsigned NOT NULL,
  `end_time` int(11) unsigned NOT NULL,
  `platform_id` bigint(20) DEFAULT NULL,
  `updated_at` int(11) unsigned NOT NULL,
  `created_at` int(11) unsigned NOT NULL,
  PRIMARY KEY (`actives_id`)
);

• hash

  • Hash partition : 让资料在指定分区个数下均匀分布。
  • partition key: 值要是整数型别。

  (1.) 将资料使用hash分区分成5个partition。

  ALTER TABLE act
  PARTITION BY HASH (actives_id) 
  PARTITIONS 5;
  

  (2.) 一次插入10笔资料，看数据分布状态。

  INSERT INTO `act` ( `status`, `start_time`, `end_time`, `platform_id`, `updated_at`, `created_at`) VALUES ( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '5', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200');  
  

  

  ---

• key

  • Key partition : 类似於hash分区的方式，但资料存放的运算方式不同。

  (1.) 将资料使用key分区分成5个partition。

  ALTER TABLE act
  PARTITION BY KEY (actives_id) 
  PARTITIONS 5;
  

  ps. 有PK或unique key的状况下分区栏位可不指定～

  ALTER TABLE act
  PARTITION BY KEY () 
  PARTITIONS 5;
  

• sub

  • Sub partition : 分区下在细分一次。简单来说就是分区在分区(切割2次)

  (1.) 使用actives_id进行sub分区

  ALTER TABLE act
  PARTITION BY RANGE(actives_id)
  SUBPARTITION BY KEY (actives_id)
  SUBPARTITIONS 3 (
  PARTITION p0 VALUES LESS THAN (5),
  PARTITION p1 VALUES LESS THAN (10),
  PARTITION p2 VALUES LESS THAN (15),
  PARTITION pmax VALUES LESS THAN MAXVALUE
  );
  

  (2.) 插入10笔数据

  INSERT INTO `act` ( `status`, `start_time`, `end_time`, `platform_id`, `updated_at`, `created_at`) VALUES ( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '5', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200');  
  

  • 数据分布状态可以看到，在我们设定的每个分区下又个别拆分成3个。 (4*3 = 12个分区)
    

  • 查询其中某一个分区的方式，以上图标示的那笔p2纪录来说～

  mysql>     SELECT * FROM act PARTITION (p2sp0);
  

  

  ---

  管理分区的指令:

(现有表结构)

CREATE TABLE `act` (
 `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `status` bigint(20) DEFAULT NULL COMMENT ' 0() 1() 2()',
 `start_time` int(11) unsigned NOT NULL,
 `end_time` int(11) unsigned NOT NULL,
 `platform_id` bigint(20) DEFAULT NULL,
 `updated_at` int(11) unsigned NOT NULL,
 `created_at` int(11) unsigned NOT NULL,
 PRIMARY KEY (`actives_id`)
)
PARTITION BY RANGE (actives_id)(
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

• 在现有分区中新增分区

1. (没有) 设定MAXVALUE分区的状况下直接新增

   ps.注意只能根据最後一个分区去做往後新增动作！

   ALTER TABLE act ADD PARTITION (PARTITION p3 VALUES LESS THAN(20));
   

2. (有) 设定MAXVALUE分区的状况下重组拆分分区

   ALTER TABLE act REORGANIZE PARTITION pmax INTO (
    PARTITION p3 VALUES LESS THAN(20),
    PARTITION pmax VALUES LESS THAN MAXVALUE
   );
   

   补充: 如果要合并分区

   
   ALTER TABLE act REORGANIZE PARTITION p3,pmax INTO (
     PARTITION pmax VALUES LESS THAN MAXVALUE
   );