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: 0


mysql> 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个