开发篇-MySQL分区（三）

DBA天团 2021-02-05

549

6.2.6 子分区

子分区（subpartitioning）是分区表中对每个分区的再次分割，又被称为复合分区（composite partitioning）。MySQL从MySQL 5.1开始支持对已经通过RANGE或者LIST分区了的表再进行子分区，子分区既可以使用HASH分区，也可以使用KEY分区。例如：

mysql> CREATE TABLE ts (id INT, purchased DATE)

-> PARTITION BY RANGE(YEAR(purchased))

-> SUBPARTITION BY HASH(TO_DAYS(purchased))

-> SUBPARTITIONS 2

-> (

-> PARTITION p0 VALUES LESS THAN (1990),

-> PARTITION p1 VALUES LESS THAN (2000),

-> PARTITION p2 VALUES LESS THAN MAXVALUE

-> );

Query OK, 0 rows affected (0.11 sec)

表ts有三个RANGE分区，这三个分区中的每个分区（p0,p1,p2）又被进一步分成2个子分区，实际上，整个表被分成了3*2 = 6个分区，由于PARTITION BY RANGE子句的作用，第一、二个分区只保存 purchased 列中值小于1990的记录。

复合分区适用于保存非常大量的数据记录。

6.2.7 MySQL分区处理NULL值的方式

MySQL 不禁止在分区键值上使用NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL的分区把NULL当作零值，或者一个最小值进行处理。

注意：RANGE分区中，NULL值会被当做最小值来处理；LIST分区中，NULL值必须出现在枚举列表中，否则不被接受；HASH/KEY分区中，NULL值会被当做零值来处理。

例如，创建tb_range表，按照id进行RANGE分区，在RANGE分区中写入NULL值：

mysql>CREATE TABLE tb_range (

-> id INT,

-> name VARCHAR(5)

-> )

-> PARTITION BY RANGE(id) (

-> PARTITION p0 VALUES LESS THAN (-6),

-> PARTITION p1 VALUES LESS THAN (0),

-> PARTITION p2 VALUES LESS THAN (1),

-> PARTITION p3 VALUES LESS THAN MAXVALUE

-> );

Query OK, 0 rows affected (0.06 sec)

mysql>insert into tb_range values (null, 'NULL');

Query OK, 1 row affected (0.00 sec)

查询 INFORMATION_SCHEMA.PARTITIONS 表确认写入的NULL值被当做最小值处理，NULL值被分配在分区p0内：

mysql>SELECT

-> partition_name part,

-> partition_expression expr,

-> partition_description descr,

-> table_rows

-> FROM

-> INFORMATION_SCHEMA.partitions

-> WHERE

-> TABLE_SCHEMA = schema()

-> AND TABLE_NAME='tb_range';

+------+------+----------+------------+

+------+------+----------+------------+

| p0 | id | -6 | 1 |

| p1 | id | 0 | 0 |

| p2 | id | 1 | 0 |

| p3 | id | MAXVALUE | 0 |

+------+------+----------+------------+

4 rows in set (0.00 sec)

例如，在LIST分区中写入NULL值，分区定义不包含NULL值的时候，会返回一个错误 ERROR 1526 (HY000): Table has no partition for value NULL

mysql>CREATE TABLE tb_list (

-> id INT,

-> name VARCHAR(5)

-> )

-> PARTITION BY LIST(id) (

-> PARTITION p1 VALUES IN (0),

-> PARTITION p2 VALUES IN (1)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql>insert into tb_list values (null, 'NULL');

ERROR 1526 (HY000): Table has no partition for value NULL

在LIST分区的增加NULL的定义之后，就能够成功写入NULL值：

mysql>CREATE TABLE tb_list (

-> id INT,

-> name VARCHAR(5)

-> )

-> PARTITION BY LIST(id) (

-> PARTITION p1 VALUES IN (0, NULL),

-> PARTITION p2 VALUES IN (1)

-> );

Query OK, 0 rows affected (0.01 sec)

root@localhost:test 16:43>insert into tb_list values (NULL, 'NULL');

Query OK, 1 row affected (0.00 sec)

root@localhost:test 16:43>SELECT

-> partition_name part,

-> partition_expression expr,

-> partition_description descr,

-> table_rows

-> FROM

-> INFORMATION_SCHEMA.partitions

-> WHERE

-> TABLE_SCHEMA = schema()

-> AND TABLE_NAME='tb_list';

+------+------+--------+------------+

+------+------+--------+------------+

| p1 | id | NULL,0 | 1 |

| p2 | id | 1 | 0 |

+------+------+--------+------------+

2 rows in set (0.00 sec)

例如，创建 tb_hash 表，按照id列HASH分区，在HASH分区中写入NULL值：

mysql>CREATE TABLE tb_hash (

-> id INT,

-> name VARCHAR(5)

-> )

-> PARTITION BY HASH(id)

-> PARTITIONS 2;

Query OK, 0 rows affected (0.04 sec)

mysql>insert into tb_hash values (null, 'NULL');

Query OK, 1 row affected (0.00 sec)

mysql>SELECT

-> partition_name part,

-> partition_expression expr,

-> partition_description descr,

-> table_rows

-> FROM

-> INFORMATION_SCHEMA.partitions

-> WHERE

-> TABLE_SCHEMA = schema()

-> AND TABLE_NAME='tb_hash';

+------+------+-------+------------+

+------+------+-------+------------+

| p0 | id | NULL | 1 |

| p1 | id | NULL | 0 |

+------+------+-------+------------+

2 rows in set (0.00 sec)

由于针对不同的分区类型，NULL值时而被当做零值处理，时而被当做最小值处理，为了避免在处理NULL值的时候出现误判，更推荐通过设置字段非空和默认值来绕开MySQL默认对NULL值的处理。

6.3 分区管理

MySQL5.1提供了添加、删除、重定义、合并、拆分分区的命令，这些操作都可以通过ALTER TABLE命令来进行实现。

6.3.1 RANGE&LIST分区管理

在添加、删除、重新定义分区的处理上，RANGE分区和LIST分区非常相似，所以合并一起来说。

从一个RANGE或者LIST分区的表中删除一个分区，可以使用ALTER TABLE DROP PARTITION 语句来实现，以之前创建的按照表达式 YEAR(seperated) 的值进行RANGE 分区的 emp_date 表为例，执行如下语句创建 emp_date 表：

mysql> CREATE TABLE emp_date (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE (YEAR(separated)) (

-> PARTITION p0 VALUES LESS THAN (1995),

-> PARTITION p1 VALUES LESS THAN (2000),

-> PARTITION p2 VALUES LESS THAN (2005),

-> PARTITION p3 VALUES LESS THAN (2015)

-> );

Query OK, 0 rows affected (0.08 sec)

写入测试数据：

mysql>insert into emp_date(id, ename, hired, separated, job, store_id) values -> (7499, 'ALLEN', '1981-02-20', '2003-08-03', 'SALESMAN', 30 ),

-> (7521, 'WARD', '1981-02-22', '1993-09-01', 'SALESMAN', 30),

-> (7566, 'JONES', '1981-04-02', '2000-08-01', 'MANAGER', 20),

-> (7654, 'MARTIN','1981-09-28', 2012-12-31', 'SALESMAN', 30),

-> (7698, 'BLAKE', '1981-05-01', '1998-09-08', 'MANAGER', 30),

-> (7782, 'CLARK', '1981-06-09', '2007-08-01', 'MANAGER', 10),

-> (7788, 'SCOTT', '1987-04-19', '2012-05-01', 'ANALYST', 20),

-> (7839, 'KING', '1981-11-17', '2011-03-09', 'PRESIDENT', 10),

-> (7844, 'TURNER','1981-09-08', '2010-12-31', 'SALESMAN', 30),

-> (7876, 'ADAMS', '1987-05-23', '2000-01-01', 'CLERK', 20),

-> (7900, 'JAMES', '1981-12-03', '2004-09-02', 'CLERK', 30),

-> (7902, 'FORD', '1981-12-03', '2010-12-31', 'ANALYST', 20),

-> (7934, 'MILLER','1982-01-23', '2011-12-31', 'CLERK', 10);

Query OK, 13 rows affected (0.01 sec)

Records: 13 Duplicates: 0 Warnings: 0

通过下面的查询语句查看哪些记录在分区 p2 中（LESS THAN 2005）：

mysql>select * from emp_date where separated between '2000-01-01' and '2004-12-31';

+------+-------+------------+------------+----------+----------+

+------+-------+------------+------------+----------+----------+

| 7499 | ALLEN | 1981-02-20 | 2003-08-03 | SALESMAN | 30 |

| 7566 | JONES | 1981-04-02 | 2000-08-01 | MANAGER | 20 |

| 7876 | ADAMS | 1987-05-23 | 2000-01-01 | CLERK | 20 |

| 7900 | JAMES | 1981-12-03 | 2004-09-02 | CLERK | 30 |

+------+-------+------------+------------+----------+----------+

4 rows in set (0.00 sec)

执行下面的语句删除p2分区：

mysql>alter table emp_date drop partition p2;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

注意：删除分区的命令执行之后，并不显示实际从表中删除的行数，并不是真的没有记录被删除。

从表结构定义上，可以观察到p2分区确实被删除了：

mysql>show create table emp_date\G

*************************** 1. row ***************************

Table: emp_date

Create Table: CREATE TABLE `emp_date` (

`id` int(11) NOT NULL,

`ename` varchar(30) DEFAULT NULL,

`hired` date NOT NULL DEFAULT '1970-01-01',

`separated` date NOT NULL DEFAULT '9999-12-31',

`job` varchar(30) NOT NULL,

`store_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (YEAR(separated))

(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB) */

1 row in set (0.00 sec)

删除了p2分区，那么也同时删除了该分区中所有数据，重新执行前面的查询来确认下：

mysql>select * from emp_date where separated between '2000-01-01' and '2004-12-31';

Empty set (0.00 sec)

同时检查emp_date表的记录数，确认受影响的仅是原来在p2分区中的记录：

mysql>select count(1) from emp_date;

+-----------+

| count(11) |

+-----------+

| 9 |

+-----------+

1 row in set (0.00 sec)

再次写入separated 日期在 '2000-01-01' 和 '2004-12-31' 之间的新纪录到 emp_date 表时，这些行会被保存在p3分区中，确认一下，首先检查 emp_date 表的记录分布，p3分区中仅有7条记录：

mysql>SELECT

-> partition_name part,

-> partition_expression expr,

-> partition_description descr,

-> table_rows

-> FROM

-> INFORMATION_SCHEMA.partitions

-> WHERE

-> TABLE_SCHEMA = schema()

-> AND TABLE_NAME='emp_date';

+------+-----------------+----------+------------+

+------+-----------------+----------+------------+

| p0 | YEAR(separated) | 1995 | 0 |

| p1 | YEAR(separated) | 2000 | 0 |

| p3 | YEAR(separated) | 2015 | 7 |

+------+-----------------+----------+------------+

3 rows in set (0.00 sec)

在 emp_date 表中写入一条separated 日期在 '2000-01-01' 和 '2004-12-31' 之间的新纪录：

mysql>insert into emp_date(id, ename, hired, separated, job, store_id) values

-> (7566, 'JONES', '1981-04-02', '2000-08-01', 'MANAGER', 20);

Query OK, 1 row affected (0.00 sec)

再次检查 emp_date 表的记录分布，发现p3分区的记录数增加为8条，确认新写入的记录写入p3分区：

mysql>SELECT

-> partition_name part,

-> partition_expression expr,

-> partition_description descr,

-> table_rows

-> FROM

-> INFORMATION_SCHEMA.partitions

-> WHERE

-> TABLE_SCHEMA = schema()

-> AND TABLE_NAME='emp_date';

+------+-----------------+----------+------------+

+------+-----------------+----------+------------+

| p0 | YEAR(separated) | 1995 | 0 |

| p1 | YEAR(separated) | 2000 | 0 |

| p3 | YEAR(separated) | 2015 | 8 |

+------+-----------------+----------+------------+

3 rows in set (0.00 sec)

删除LIST分区和删除RANGE分区使用的语句完全相同，只不过删除LIST分区之后，由于在LIST分区的定义中不再包含已经被删除了的分区的值列表，所以后续无法写入包含有已经删除了的分区的值列表的数据。

为一个RANGE或者LIST分区的表增加一个分区，可以使用ALTER TABLE ADD PARTITION 语句来实现。对于RANGE分区来说，只能通过ADD PARTITION方式添加新的分区到分区列表的最大一端，例如，给 emp_date 表增加p4分区，存放separated 日期在 '2015-01-01' 和 '2029-12-31' 之间的记录：

mysql>alter table emp_date add partition (partition p4 values less than (2030));

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>show create table emp_date\G

*************************** 1. row ***************************

Table: emp_date

Create Table: CREATE TABLE `emp_date` (

`id` int(11) NOT NULL,

`ename` varchar(30) DEFAULT NULL,

`hired` date NOT NULL DEFAULT '1970-01-01',

`separated` date NOT NULL DEFAULT '9999-12-31',

`job` varchar(30) NOT NULL,

`store_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (YEAR(separated))

(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB,

PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */

1 row in set (0.01 sec)

只能从RANGE分区列表最大端增加分区的话，否则会出现如下错误：

mysql>alter table emp_date add partition (partition p5 values less than (2025));

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

给LIST分区增加新的分区的方式也类似，以之前创建的 expenses 表为例，当前的 expenses 表结构为：

mysql>CREATE TABLE expenses (

-> expense_date DATE NOT NULL,

-> category INT,

-> amount DECIMAL (10,3)

-> )PARTITION BY LIST(category) (

-> PARTITION p0 VALUES IN (3, 5),

-> PARTITION p1 VALUES IN (1, 10),

-> PARTITION p2 VALUES IN (4, 9),

-> PARTITION p3 VALUES IN (2),

-> PARTITION p4 VALUES IN (6)

-> );

Query OK, 0 rows affected (0.04 sec)

为 expenses 表新增 p5分区存储 category 分类为 7 和 8 的记录：

mysql>alter table expenses add partition (partition p5 values in (7,8));

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

增加LIST分区的时候，不能添加一个包含现有分区值列表中的任意值的分区，也就是说对一个固定的分区键值，必须指定并且只能指定一个唯一的分区，否则会出现错误：

mysql>alter table expenses add partition (partition p6 values in (6,11));

ERROR 1495 (HY000): Multiple definition of same constant in list partitioning

MySQL也提供了在不丢失数据的情况下，通过重新定义分区的语句ALTER TABLE REORGANIZE PARTITION INTO重定义分区。仍以RANGE分区的emp_date 表为例，当前 emp_date 的表结构为：

mysql>show create table emp_date\G

*************************** 1. row ***************************

Table: emp_date

Create Table: CREATE TABLE `emp_date` (

`id` int(11) NOT NULL,

`ename` varchar(30) DEFAULT NULL,

`hired` date NOT NULL DEFAULT '1970-01-01',

`separated` date NOT NULL DEFAULT '9999-12-31',

`job` varchar(30) NOT NULL,

`store_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (YEAR(separated))

(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB,

PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */

1 row in set (0.00 sec)

计划拆分p3分区（2000~2015）为两个分区p2(2000~2005) 和 p3(2005~2015)：

mysql>alter table emp_date reorganize partition p3 into (

-> partition p2 values less than (2005),

-> partition p3 values less than (2015)

-> );

Query OK, 8 rows affected (0.03 sec)

Records: 8 Duplicates: 0 Warnings: 0

确认拆分之后emp_date表的表结构：

mysql>show create table emp_date\G

*************************** 1. row ***************************

Table: emp_date

Create Table: CREATE TABLE `emp_date` (

`id` int(11) NOT NULL,

`ename` varchar(30) DEFAULT NULL,

`hired` date NOT NULL DEFAULT '1970-01-01',

`separated` date NOT NULL DEFAULT '9999-12-31',

`job` varchar(30) NOT NULL,

`store_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (YEAR(separated))

(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THAN (2005) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB,

PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */

1 row in set (0.00 sec)

重新定义分区可以用来拆分一个RANGE分区为多个RANGE分区，也可以用来合并多个相邻RANGE分区为一个RANGE分区或者多个RANGE分区：

mysql>alter table emp_date reorganize partition p1,p2,p3 into (

-> partition p1 values less than (2015)

-> );

Query OK, 9 rows affected (0.05 sec)

Records: 9 Duplicates: 0 Warnings: 0

mysql>show create table emp_date\G

*************************** 1. row ***************************

Table: emp_date

Create Table: CREATE TABLE `emp_date` (

`id` int(11) NOT NULL,

`ename` varchar(30) DEFAULT NULL,

`hired` date NOT NULL DEFAULT '1970-01-01',

`separated` date NOT NULL DEFAULT '9999-12-31',

`job` varchar(30) NOT NULL,

`store_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (YEAR(separated))

(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (2015) ENGINE = InnoDB,

PARTITION p4 VALUES LESS THAN (2030) ENGINE = InnoDB) */

1 row in set (0.00 sec)

注意：重新定义RANGE分区的时候，只能够重新定义相邻的分区，不能跳过某个RANGE分区进行重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义分区来改变表分区的类型，例如，不能把RANGE分区变为HASH分区，也不能把HASH分区变成RANGE分区。

同样的，对LIST分区，也可以使用ALTER TABLE REORGANIZE PARTITION INTO语句重定义分区，例如，当前 expenses 表的分区如下：

mysql>show create table expenses\G

*************************** 1. row ***************************

Table: expenses

Create Table: CREATE TABLE `expenses` (

`expense_date` date NOT NULL,

`category` int(11) DEFAULT NULL,

`amount` decimal(10,3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LIST (category)

(PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB,

PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB,

PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB,

PARTITION p3 VALUES IN (2) ENGINE = InnoDB,

PARTITION p4 VALUES IN (6) ENGINE = InnoDB,

PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB) */

1 row in set (0.00 sec)

现在需要调整p4分区，使得p4分区包含值为6和11的记录，即 p4分区的定义为： PARTITION p4 VALUES IN (6,11)，之前我们单纯通过 ADD PARTITION 的方式是不可以的：

mysql>alter table expenses add partition (partition p6 values in (6,11));

ERROR 1495 (HY000): Multiple definition of same constant in list partitioning

可以变通的通过增加分区和重定义分区来实现，首先现增加不重复值列表的p6分区，包含值11：

mysql>alter table expenses add partition (partition p6 values in (11));

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>show create table expenses\G;

*************************** 1. row ***************************

Table: expenses

Create Table: CREATE TABLE `expenses` (

`expense_date` date NOT NULL,

`category` int(11) DEFAULT NULL,

`amount` decimal(10,3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LIST (category)

(PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB,

PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB,

PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB,

PARTITION p3 VALUES IN (2) ENGINE = InnoDB,

PARTITION p4 VALUES IN (6) ENGINE = InnoDB,

PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB,

PARTITION p6 VALUES IN (11) ENGINE = InnoDB) */

1 row in set (0.00 sec)

之后通过 REORGANIZE PARTITION 方式，重定义 p4,p5,p6 三个分区，合并p4和p6两个分区为新的p4分区，包含值6和11：

myql>alter table expenses reorganize partition p4,p5,p6 into (

-> partition p4 values in (6,11),

-> partition p5 values in (7,8)

-> );

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>show create table expenses\G

*************************** 1. row ***************************

Table: expenses

Create Table: CREATE TABLE `expenses` (

`expense_date` date NOT NULL,

`category` int(11) DEFAULT NULL,

`amount` decimal(10,3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LIST (category)

(PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB,

PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB,

PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB,

PARTITION p3 VALUES IN (2) ENGINE = InnoDB,

PARTITION p4 VALUES IN (6,11) ENGINE = InnoDB,

PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB) */

1 row in set (0.00 sec)

通过重定义分区之后，p4分区的值包含了6和11。类似的重定义RANGE分区，重新定义LIST分区的时候，只能够重新定义相邻的分区，不能跳过LIST分区进行重新定义，否则提示：

mysql>alter table expenses reorganize partition p4,p6 into (partition p4 values in (6,11));

ERROR 1519 (HY000): When reorganizing a set of partitions they must be in consecutive order

注意：类似重新定义RANGE分区，重新定义LIST分区的时候，只能够重新定义相邻的分区，不能跳过LIST分区进行重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义分区来改变表分区的类型，例如，不能把LIST分区变为RANGE分区，也不能把RANGE分区变成LIST分区。

6.3.2 HASH&KEY分区管理

在改变分区设置方面，HASH分区和KEY分区的表非常类似，所以这两种分区的管理合并在一起讨论。

不能使用RANGE或者LIST分区表中删除分区相同的方式，来从HASH或者KEY分区的表中删除分区，而是可以通过ALTER TABLE COALESCE PARTITION 语句来合并HASH分区或者KEY分区。例如，emp表按照store_id 分成了4个分区：

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY HASH (store_id) PARTITIONS 4;

Query OK, 0 rows affected (0.05 sec)

要减少HASH分区的数量，从4个分区变为2个分区，可以执行下面的ALTER TABLE 命令：

mysql>alter table emp coalesce partition 2;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>show create table emp\G

*************************** 1. row ***************************

Table: emp

Create Table: CREATE TABLE `emp` (

`id` int(11) NOT NULL,

`ename` varchar(30) DEFAULT NULL,

`hired` date NOT NULL DEFAULT '1970-01-01',

`separated` date NOT NULL DEFAULT '9999-12-31',

`job` varchar(30) NOT NULL,

`store_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY HASH (store_id)

PARTITIONS 2 */

1 row in set (0.00 sec)

COALESCE 不能用来增加分区的数量，否则会出现错误：

mysql>alter table emp coalesce partition 8;

ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead

要增加分区，可以通过ALTER TABLE ADD PARTITION 语句来实现，例如，当前emp表有2个HASH分区，现在增加8个分区，最终emp表一共有10个HASH分区：

mysql>alter table emp add partition partitions 8;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@localhost:test 22:34>show create table emp\G

*************************** 1. row ***************************

Table: emp

Create Table: CREATE TABLE `emp` (

`id` int(11) NOT NULL,

`ename` varchar(30) DEFAULT NULL,

`hired` date NOT NULL DEFAULT '1970-01-01',

`separated` date NOT NULL DEFAULT '9999-12-31',

`job` varchar(30) NOT NULL,

`store_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY HASH (store_id)

PARTITIONS 10 */

1 row in set (0.01 sec)

注意：通过ALTER TABLE ADD PARTITION PARTITIONS n语句新增HASH分区或者KEY分区的时候，是对原表新增n个分区，而不是增加到n个分区。

6.4 小结

本章重点介绍了MySQL的集中主要的分区类型、适用场景以及常规的管理维护命令，分区通过“分而治之”的方法管理数据库表，提高了数据处理的并行度从而能够提升性能。

mysql

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

开发篇-MySQL分区（三）

6.2.6 子分区

6.2.7 MySQL分区处理NULL值的方式

6.3 分区管理

6.3.1 RANGE&LIST分区管理

6.3.2 HASH&KEY分区管理

6.4 小结

评论