云数据库 OceanBase中的分区管理

分区管理主要是指添加、删除、重新定义、合并或拆分已经存在的分区。所有这些操作都可以通过使用 ALTER TABLE 命令的分区扩展来实现。

RANGE分区



删除分区以及分区数据


[backcolor=transparent]语法 ALTER TABLE table_name DROP PARTITION partiton_name;

[backcolor=transparent]举例
使用下面的 CREATE TABLE 和 INSERT 语句创建了一个按照 RANGE 分区的表，并且已经插入了 10 条记录： mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
     -> PARTITION BY RANGE(YEAR(purchased))
     -> (
     -> PARTITION p0 VALUES LESS THAN (1990),
     -> PARTITION p1 VALUES LESS THAN (1995),
     -> PARTITION p2 VALUES LESS THAN (2000),
     -> PARTITION p3 VALUES LESS THAN (2005)
     -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tr VALUES
     -> (1, 'desk organiser', '2003-10-15'),
     -> (2, 'CD player', '1993-11-05'),
     -> (3, 'TV set', '1996-03-10'),
     -> (4, 'bookcase', '1982-01-10'),
     -> (5, 'exercise bike', '2004-05-09'),
     -> (6, 'sofa', '1987-06-05'),
     -> (7, 'popcorn maker', '2001-11-22'),
     -> (8, 'aquarium', '1992-08-04'),
     -> (9, 'study desk', '1984-09-16'),
     -> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.01 sec)

可以通过使用下面的命令查看那些记录已经插入到了分区 p2 中： mysql> SELECT * FROM tr
    -> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-----------+------------+
| id | name | purchased |
+------+-----------+------------+
| 3 | TV set | 1996-03-10 |
| 10 | lava lamp | 1998-12-25 |
+------+-----------+------------+
2 rows in set (0.00 sec)

要删除名字为 p2 的分区，执行下面的命令： mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)

记住下面一点非常重要：当删除了一个分区，也同时删除了该分区中所有的数据。可以通过重新运行前面的 SELECT 查询来验证这一点： mysql> SELECT * FROM tr WHERE purchased
     -> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)


删除数据：保留表的结构，包括分区


[backcolor=transparent]语法 TRUNCATE TABLE table_name

[backcolor=transparent]举例
接上面的例子，删除 tr 表的所有内部。 mysql> TRUNCATE TABLE tr;
Query OK, 0 rows affected (0.25 sec)

查看表的内容为空 mysql> select * from tr;
Empty set (0.00 sec)

查看表的数据结构 mysql> show create table tr;
+-------+------------------------------------------------------------
| Table | Create Table
+-------+------------------------------------------------------------
| tr | CREATE TABLE `tr` (
`id` int(11) DEFAULT NULL,
`name` VARCHAR(50) DEFAULT NULL,
`purchased` date DEFAULT NULL
)
/*!50100 PARTITION BY RANGE (YEAR(purchased))
     (PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
     PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
     PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */ |
+-------+------------------------------------------------------------
1 row in set (0.00 sec)


添加分区：为已经分区的表添加后续分区


[backcolor=transparent]语法 ALTER TABLE ... ADD PARTITION

[backcolor=transparent]举例
例如，假设有一个包含你所在组织的全体成员数据的分区表，该表的定义如下： CREATE TABLE members (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
)
PARTITION BY RANGE(YEAR(dob)) (
     PARTITION p0 VALUES LESS THAN (1970),
     PARTITION p1 VALUES LESS THAN (1980),
     PARTITION p2 VALUES LESS THAN (1990)
);

进一步假设成员的最小年纪是 16 岁。随着日历接近 2005 年年底，你会认识到不久将要接纳 1990 年（以及以后年份）出生的成员。可以按照下面的方式，修改成员表来容纳出生在 1990－1999 年之间的成员： ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

[backcolor=transparent]约束
对于通过 RANGE 分区的表，只可以使用 ADD PARTITION 添加新的分区到分区列表的高端。设法通过这种方式在现有分区的前面或之间增加一个新的分区，将会导致下面的一个错误： mysql> ALTER TABLE members ADD PARTITION (PARTITION p4 VALUES LESS THAN (1960));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition


分区扩展


分区可以通过 REOGANIZE PARTITION 来实现拆分或合并分区等。
[backcolor=transparent]语法 ALTER TABLE tblname REORGANIZE PARTITION partition_list INTO (partition_definitions)；

其中，tbl_name 是分区表的名称，partition_list 是通过逗号分开的、一个或多个将要被改变的现有分区的列表。partition_definitions 是一个是通过逗号分开的、新分区定义的列表，它遵循与用在“CREATE TABLE”中的 partition_definitions相同的规则。把多少个分区合并到一个分区或把一个分区拆分成多少个分区方面，没有限制。
[backcolor=transparent]举例 mysql> show create table members;
+---------+----------------------------------------------------------
| Table | Create Table
+---------+----------------------------------------------------------
| members | CREATE TABLE `members` (
`id` int(11) DEFAULT NULL,
`fname` VARCHAR(25) DEFAULT NULL,
`lname` VARCHAR(25) DEFAULT NULL,
`dob` date DEFAULT NULL
)
/*!50100 PARTITION BY RANGE (YEAR(dob))
     (PARTITION p0 VALUES LESS THAN (1970),
     PARTITION p1 VALUES LESS THAN (1980),
     PARTITION p2 VALUES LESS THAN (1990),
     PARTITION p3 VALUES LESS THAN (2000) */ |
+---------+----------------------------------------------------------
1 row in set (0.01 sec)

假定想要把表示出生在 1960 年前成员的所有行移入到一个分开的分区中。正如我们前面看到的，不能通过使用“ALTER TABLE ... ADD PARTITION”来实现这一点。但是，要实现这一点，可以使用 ALTER TABLE 上的另外一个与分区有关的扩展，具体实现如下： mysql> ALTER TABLE members REORGANIZE PARTITION p0 INTO (
     -> PARTITION s0 VALUES LESS THAN (1960),
     -> PARTITION s1 VALUES LESS THAN (1970)
     -> );
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table members; //查看结果
+---------+----------------------------------------------------------
| Table   | Create Table
+---------+----------------------------------------------------------
| members | CREATE TABLE `members` (
     `id` int(11) DEFAULT NULL,
     `fname` VARCHAR(25) DEFAULT NULL,
     `lname` VARCHAR(25) DEFAULT NULL,
     `dob` date DEFAULT NULL
     )
/*!50100 PARTITION BY RANGE (YEAR(dob))
     (PARTITION s0 VALUES LESS THAN (1960),
     PARTITION s1 VALUES LESS THAN (1970),
     PARTITION p1 VALUES LESS THAN (1980),
     PARTITION p2 VALUES LESS THAN (1990),
     PARTITION p3 VALUES LESS THAN (2000) */ |
+---------+----------------------------------------------------------
1 row in set (0.02 sec)

可以重新组织成员表的五个分区成两个分区，具体实现如下： mysql> ALTER TABLE members REORGANIZE PARTITION s0,s1,p1,p2,p3 INTO (
     -> PARTITION m0 VALUES LESS THAN (1980),
    -> PARTITION m1 VALUES LESS THAN (2000));
Query OK, 0 rows affected (0.73 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table members; //查看结果
+---------+----------------------------------------------------------
| Table   | Create Table
+---------+----------------------------------------------------------
| members | CREATE TABLE `members` (
    `id` int(11) DEFAULT NULL,
    `fname` VARCHAR(25) DEFAULT NULL,
    `lname` VARCHAR(25) DEFAULT NULL,
    `dob` date DEFAULT NULL
)
/*!50100 PARTITION BY RANGE (YEAR(dob))
    (PARTITION m0 VALUES LESS THAN (1980) ENGINE = InnoDB,
    PARTITION m1 VALUES LESS THAN (2000) ENGINE = InnoDB) */ |
+---------+----------------------------------------------------------
1 row in set (0.01 sec)


HASH分区



合并分区


[backcolor=transparent]语法 ALTER TABLE ... COALESCE PARTITION

[backcolor=transparent]举例
例如，假定有一个包含顾客信息数据的表，它被分成了 12 个分区。该顾客表的定义如下： CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY HASH( MONTH(signed) )
     PARTITIONS 12；

要减少分区的数量从 12 到 6，执行下面的 ALTER TABLE 命令： mysql> ALTER TABLE clients COALESCE PARTITION 6；
Query OK, 0 rows affected (0.02 sec)

[backcolor=transparent]约束
COALESCE 不能用来增加分区的数量，如果你尝试这么做，结果会出现类似于下面的错误： mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead


增加分区


[backcolor=transparent]语法 ALTER TABLE ... ADD PARTITION

[backcolor=transparent]举例
在表 clients 中，把原有 6 个分区，增加 18 个分区，更新为 24 个分区。 mysql> alter table clients add partition partitions 18;
Query OK, 0 rows affected (3.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table clients; //查看结果
+----------+---------------------------------------------------------|
|Table     | Create Table
+----------+---------------------------------------------------------|
clients2 | CREATE TABLE `clients` (
`id` int(11) DEFAULT NULL,
`fname` VARCHAR(30) DEFAULT NULL,
`lname` VARCHAR(30) DEFAULT NULL,
`signed` date DEFAULT NULL
) /*!50100 PARTITION BY HASH ( MONTH(signed))
    PARTITIONS 24 */ |
+----------+---------------------------------------------------------1
row in set (0.00 sec)

注释：“ALTER TABLE ... REORGANIZE PARTITION”不能用于按照HASH或HASH分区的表。