mysql 分区管理和对NULL的处理

Mysql 分区对NULL的处理

• range分区中， NULL值会被当做最小值处理
• list分区中， NULL值必须出现在分区枚举值中， 否在在插入数据是会报错
• hash和key会将NULL当做0处理

分区管理

• 添加分区
  ：range —> alter table talbeName add partition(partition partitionName values less than(values));

| name  | CREATE TABLE `name` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (id)SUBPARTITION BY HASH (id)SUBPARTITIONS 2(PARTITION p0 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB) */ |mysql> alter table name add partition( partition p2 values less than(10));Query OK, 0 rows affected (0.14 sec)Records: 0  Duplicates: 0  Warnings: 0| name  | CREATE TABLE `name` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (id)SUBPARTITION BY HASH (id)SUBPARTITIONS 2(PARTITION p0 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (10) ENGINE = InnoDB) */ |lter table name add partition(    -> partition p3 values less than(7));ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition//不能在中间插入

：list —> alter table tableName add partiton(partition partitionName values in(v1, v2));

| listPartition | CREATE TABLE `listPartition` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(128) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8/*!50100 PARTITION BY LIST (id)(PARTITION p0 VALUES IN (1,2,4) ENGINE = InnoDB, PARTITION p1 VALUES IN (3,5) ENGINE = InnoDB) */ | alter table listPartition add partition( partition p2 values in(6, 7));Query OK, 0 rows affected (0.12 sec)Records: 0  Duplicates: 0  Warnings: 0| listPartition | CREATE TABLE `listPartition` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(128) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8/*!50100 PARTITION BY LIST (id)(PARTITION p0 VALUES IN (1,2,4) ENGINE = InnoDB, PARTITION p1 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p2 VALUES IN (6,7) ENGINE = InnoDB) */ |

• 删除分区
  ： alter table tableName drop partition partitionName;

• 调整分区
  **：range —> alter table tableName reorganize partition partitionName into(partition pName1 values less than(v1),
  partition pName2 values less than(v2));**

| name  | CREATE TABLE `name` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (id)SUBPARTITION BY HASH (id)SUBPARTITIONS 2(PARTITION p0 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (10) ENGINE = InnoDB) */ |alter table name reorganize partition p2 into( partition p2 values less than(7), partition p3 values less than(10));Query OK, 0 rows affected (0.22 sec)Records: 0  Duplicates: 0  Warnings: 0| name  | CREATE TABLE `name` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (id)SUBPARTITION BY HASH (id)SUBPARTITIONS 2(PARTITION p0 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (10) ENGINE = InnoDB) */ |

**：list —> alter tableName reorganize partition partitionName1, partitionName2, partitionName3 into(partition p1 values in (v1, v2),
partition p2 values in(v3, v4));**

| listPartition | CREATE TABLE `listPartition` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(128) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8/*!50100 PARTITION BY LIST (id)(PARTITION p0 VALUES IN (1,2,4) ENGINE = InnoDB, PARTITION p1 VALUES IN (3,5) ENGINE = InnoDB, PARTITION p2 VALUES IN (6,7) ENGINE = InnoDB) */ |alter table listPartition reorganize partition p0, p1, p2 into( partition p0 values in(1, 2), partition p1 values in(3, 4, 5, 6, 7));Query OK, 0 rows affected (0.21 sec)Records: 0  Duplicates: 0  Warnings: 0| listPartition | CREATE TABLE `listPartition` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(128) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8/*!50100 PARTITION BY LIST (id)(PARTITION p0 VALUES IN (1,2) ENGINE = InnoDB, PARTITION p1 VALUES IN (3,4,5,6,7) ENGINE = InnoDB) */ |

HASH / KEY 分区管理

• 合并分区：alter table tableName coalesce partitions n;

  注意：不用coalesce 来增加分区
  可以使用alter table tableName add partition partitions n; 来增加分区， 但是这样增加的分区是原来分区数量和n的和；

| hashPartition | CREATE TABLE `hashPartition` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(128) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY HASH (id)PARTITIONS 5 */ |mysql> alter table hashPartition coalesce  partition 4;Query OK, 0 rows affected (0.20 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table hashPartition coalesce  partition 6;ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead| hashPartition | CREATE TABLE `hashPartition` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(128) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY HASH (id)PARTITIONS 1 */ |mysql> alter table hashPartition add partition partitions 2;Query OK, 0 rows affected (0.16 sec)Records: 0  Duplicates: 0  Warnings: 0| hashPartition | CREATE TABLE `hashPartition` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(128) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY HASH (id)PARTITIONS 3 */ | alter table hashPartition  coalesce partition 2;Query OK, 0 rows affected (0.19 sec)Records: 0  Duplicates: 0  Warnings: 0//注意， coalesce 是做减法操作， 就是原来有5分区， 执行coalesce partitoin 3后只有2个分区， 并不是3个