三十八、MySQL基础系列笔记之MySQL分区管理
MySQL提供了添加、删除、重庆义、合并、拆分、交换分区的命令用于管理分区的操作，这些命令都使用alter table实现。
RANGE与LIST分区管理
对于添加、删除、重定义，RANGE与LIST是相似的。
删除分区
命令：alter table 表名 drop partition
创建分区表并写入数据
# 创建分区表
create table demo10(
id int not null,
name varchar(20) not null
)partition by range (id) (
partition p0 values less than (10),
partition p1 values less than (30),
partition p2 values less than maxvalue
);
# 写入数据
insert into demo10 values (1,'lisi'),(2,'wangwu'),
(10,'zhoaliu'),(13,'sunwang'),(31,'meili');
id为1，2的数据写入到了p0分区，10，13的数据写入到了p1分区；31的数据写入到p2分区。
删除p2分区
mysql> alter table demo10 drop partition p2;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
注意：
1）删除分区后，并没有显示实际从表中删除了多少数据，并非没有数据被删除，而只要在p2分区中的数据全部被删除了。
2）不同的是，删除分区后，LIST分区中不同包含已经被删除了的分区值列表，因此无法写入含有已经删除了的分区的值列表数据。
新增分区
命令：alter table 表名 add partition
alter table demo10 add partition (partition p4 values less than maxvalue);
mysql> insert into demo10 values (33,'zi');
Query OK, 1 row affected (0.00 sec)
LIST分区
create table demo11(
id int
)partition by list(id) (
partition p0 values in (1,2),
partition p1 values in (3,4)
);
# 新增list分区
mysql> alter table demo11 add partition(partition p2 values in(4,5));
ERROR 1495 (HY000): Multiple definition of same constant in list partitioning
# 成功
mysql> alter table demo11 add partition(partition p2 values in(5,6));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
注意：LIST分区，第一次新增(4,5)时失败，因为4已经存在了。因此可以看出，新增LIST分区时，必须指定且只能指定一个唯一的分区。
重定义分区
命令：alter table 表名 reorganize partition into
以之前创建的emp表为列：
Create Table: CREATE TABLE `emp` (
`id` int NOT NULL,
`name` varchar(30) DEFAULT NULL,
`store_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`store_id`)
(PARTITION p0 VALUES LESS THAN (15) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (35) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (55) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
下面案例中对p3分区进行了重新定义，将其拆分成了2个分区。
alter table emp reorganize partition p3 into (
partition p3 values less than(65),
partition p4 values less than maxvalue
);
既然可以拆分那么也可以合并，如p0为15,p1为35，那么合并之后是35，而非50
alter table emp reorganize partition p0,p1 into (
partition p1 values less than(35)
);
HASH与KEY分区管理
HASH分区与KEY分区类型，管理操作也是类似。RANGE与LIST可以删除分区，不同的是HASH或KEY分区不能从表中删除分区，而是合并分区。
命令：alter table 表名 coalesce partition
案例：创建一张表hash分区数量为4的表，然后将分区修改为2个
先来创建一个HASH分区
create table demo12 (
id int not null,
name varchar(20) not null
)partition by hash(id) partitions 4;
修改分区为2个
alter table demo12 coalesce partition 2;
# 增加分区会报错
mysql> alter table demo12 coalesce partition 9;
ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead
coalesce不能用来增加分区，否则会报错。
交换分区
命令：ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt
含义：将分区表pt中的一个分区或者子分区p中的数据和普通表nt中的数据进行交换。
交换分区必须满足如下条件：
- 表nt不能是分区表。由于交换分区不能通过分区对分区的方式进行，若需求如此，可用一个普通表作为中间表，通过交换两次分区来实现；
- 表nt不能是临时表；
- 表pt和nt的结构完全一致（包括索引名称、索引列），表分区除外；
- 表nt上不能有外键，也不能有其他表的外键依赖nt；
- nt表的所有数据，应在分区p定义的范围内。若数据都在限定范围内可通过
WITHOUT VALIDTION来跳过逐行验证。
交换分区案例
1）创建分区表并插入数据
CREATE TABLE demo13(
id INT NOT NULL,
name VARCHAR(20)
)PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN(50),
PARTITION p1 VALUES LESS THAN(100),
PARTITION p2 VALUES LESS THAN(150),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO demo13 VALUES(160,'lisi'),(180,'wangwu'),(15,'meili'),(2021,'ziru');
2）创建普通表
CREATE TABLE demo14 LIKE demo13;
3）将demo14分区表修改为非分区表
命令：ALTER TABLE 表名 REMOVE PARTITIONING
ALTER TABLE demo14 REMOVE PARTITIONING;
# 查看结果,已经不是分区表
mysql> show create table demo14;
+--------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------+
| demo14 | CREATE TABLE `demo14` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
4）查看demo13分区表中的数据分布
mysql> SELECT partition_name,table_rows FROM information_schema.partitions WHERE table_name='demo13';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
4 rows in set (0.00 sec)
5）执行交换分区并查看交换之后的数据分布
# 开始交换
ALTER TABLE demo13 EXCHANGE PARTITION p0 WITH TABLE demo14;
# 查看分区表数据分布
mysql> SELECT partition_name,table_rows FROM information_schema.partitions WHERE table_name='demo13';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
4 rows in set (0.00 sec)
# 查看非分区表
mysql> select * from demo14;
+----+-------+
| id | name |
+----+-------+
| 15 | meili |
+----+-------+
1 row in set (0.00 sec)
交换分区的使用，可以方便地完成对包含大量数据的分区、子分区的备份，迁移等工作。
使用交换分区注意点：
- 交换分区不会触发任何被交换的表或分区上的触发器；
- 表中自增列的值会被重置；
- 交换分区的命令中，IGNORE关键字不会产生影响。
2021-01-16