关于数据库:实践教程之-PolarDBX-分区管理

PolarDB-X 为了不便用户体验，提供了收费的试验环境，您能够在试验环境里体验 PolarDB-X 的装置部署和各种内核个性。除了收费的试验，PolarDB-X 也提供收费的视频课程，手把手教你玩转 PolarDB-X 分布式数据库。

本期试验将领导您如何进行 PolarDB- X 分区治理。

本期收费试验地址

本期教学视频地址

假如曾经依据前一讲内容实现了 PolarDB- X 的搭建部署，应用 PolarDB-X Operator 装置 PolarDB-X，并且能够胜利链接上 PolarDB- X 数据库。

本步骤将带您体验 PolarDb- X 数据库中的分区治理能力。

执行如下 SQL 语句，创立测试数据库 part_manage 并创立测试表。

-- 创立测试库
create database part_manage mode='auto';
use part_manage;
-- 创立一个表组
create tablegroup test_tg1;
-- 创立表 t1 并绑定到表组为 test_tg1
create table t1 (a int) partition by key(a) partitions 5 tablegroup=test_tg1;
-- 创立表 t2, 让它和 t1 一样绑定到表组 test_tg1
create table t2 (a int) partition by key(a) partitions 5 tablegroup=test_tg1;
-- 创立表 t2，不指定表组
create table t3 (a int) partition by key(a) partitions 5;
-- 手工绑定 t3 到表组 test_tg，成果和在创立时指定是一样的 
alter table t3 set tablegroup=test_tg1 force;
-- 创立 range 分区的表 t4
create table t4 (a int) partition by range(a) (partition p1 values less than(100),
                         partition p2 values less than(500),
                         partition p3 values less than(1000));
-- 创立 list 分区的表 t5
create table t5 (a int) partition by list(a) (partition p1 values in (1,2,3,4,5),
                         partition p2 values in (6,7,8,9),
                         partition p3 values in (10,11,12,13,14));
-- 拆分形式创立表 orders，order_details 默认将按主键 hash 拆分
create table orders(order_id bigint primary key auto_increment, 
           customer_id varchar(64) default null, create_time datetime not null,
           update_time datetime not null);
create table order_details(order_detail_id bigint primary key auto_increment, 
           order_id bigint not null, customer_id varchar(64) default null,
           create_time datetime not null, update_time datetime not null);
-- 查看一下 orders 表的拆分形式
show full create table orders;

2.1 执行如下 SQL 语句，查看表构造。

-- set show_hash_partitions_by_range=true 而后执行 show full create table 能够将 hash 分区的表各个分区的 hash 空间展现进去
set enable_set_global=true;
set global show_hash_partitions_by_range=true;
show full create table t1;

2.2 执行如下 SQL 语句，查看以后库中的所有的表组信息。

-- 精简模式 show tablegroup; 
-- 详情模式 show full tablegroup;

2.3 执行如下 SQL 语句，查看表的拓扑信息，包含各个分区的物理散布。

show topology from t1;

3.1 从下面的 show full tablegroup 能够看到，表 t1、t2、t3 都在表组 test_tg1 中，在做分区合并前，执行如下 SQL 语句，咱们先看看他们之间的 join 能不能下推。

explain select * from t1,t2 where t1.a=t2.a;
explain select * from t1,t3 where t3.a=t1.a;

返回后果如下，此时 t1、t2 以及 t1、3 的 join 是能够间接下推到存储节点执行的。

3.2 执行如下 SQL 语句，将 t1 的分区 p2 和 p3 合并。

alter table t1 merge partitions p2,p3 to p3;

3.3 执行如下 SQL 语句，查看在合并结束后 t1、t2 以及 t1、3 的 join 是否能持续下推。

explain select * from t1,t2 where t1.a=t2.a; 
explain select * from t1,t3 where t3.a=t1.a;

返回后果如下，合并 p2、p3 后，t1 和 t2、t1 和 t3 的 join 不再下推，因为 t1 的分区形式和 t2、t3 的不统一了，表组也不一样了

3.4 目前 t2 和 t3 还在在同一个表组 test_tg2 中, t2 和 t3 的 join 是能够下推的，如果想维持这种稳固的下推关系又想合并 t2 的 p2 和 p3 分区，应该怎么做？

您能够执行表组级别的分区合并，将表组内所有的表都同步执行雷同的表更。例如执行如下 SQL 语句，会对 test_tg2 的所有表（t2、t3）都执行合并 p2、p3 分区的操作

alter tablegroup test_tg1 merge partitions p2,p3 to p3;

3.5 原来 t2、t3 的 join 能够下推的，咱们在看看执行了表组级别的分区合并之后是否能持续下推。

explain select * from t2,t3 where t3.a=t2.a;

返回后果如下，执行了表组级别的分区合并之后还能持续下推。实质起因是表组内所有的表都做了雷同的变更，不同表的名称雷同的分区的定义和物理地位都是保持一致的，他们表组也还是雷同的。

在下面的例子中咱们对表 t1 做了分区合并操作，在上面的例子中咱们对其执行分区决裂操作。

4.1 执行如下 SQL 语句，在决裂前咱们查看各个分区的外部 hash 空间。

set enable_set_global=true; set global show_hash_partitions_by_range=true; show full create table t1;

返回后果如下，能够看到 p3 的 hash 空间范畴是 [-5534023222112865481, 1844674407370955163)。

4.2 执行如下 SQL 语句，咱们对 p3 执行决裂，并查看决裂后的成果如何。

alter table t1 split partition p3;  
show full create table t1;

返回后果如下，能够看到决裂后相当于将 p3 按 hash 空间均匀划分成两个新分区 p6、p7。

4.3 对于决裂，PolarDB- X 也反对表组级别的分区变更。

执行如下 SQL 语句，对 test_tg1 表组的分区变更。

alter tablegroup test_tg1 split partition p3;

4.4 对于 hash 形式的分区表，咱们在决裂的时候能够不指定新分区的任何信息，默认依照待决裂的分区的 hash 空间二等份决裂成两个新分区，对于 range 或者 list 拆分形式的分区表，对其分区决裂须要指定新分区的残缺分区信息。例如执行如下 SQL 语句，别离对 range 分区表 t4 和 list 分区表 t5 执行决裂操作。

alter table t4 split partition p3 into (partition p30 values less than(600),
                                        partition p31 values less than(800),
                                        partition p32 values less than(1000));
alter table t5 split partition p2 into (partition p2_0 values in(6), partition p2_1 values in(7,8), partition p2_2 values in(9));

所谓分区迁徙，就是将表的分区从一个存储节点迁徙到另一个存储节点，以此达到数据平衡或者数据隔离的成果。所以在迁徙前，咱们须要晓得以后实例有哪些存储节点。

5.1 执行如下 SQL 语句，查看以后实例的存储节点。

show storage;

返回后果如下，其中 INST_KIND=META_DB 的节点只承当存储 GMS 信息，不承当普通用户表的存储工作，所有这个实例中，能够保留用户表信息的存储节点有两个，别离是 polardb-x-9f8v-dn-0 和 polardb-x-9f8v-dn-1。

5.2 接下来咱们须要通过 show topology from #tb 的命令查看表的各个分区的物理散布。例如执行如下 SQL 语句，表查看 t1 的物理拓扑信息。

show topology from t1;

返回后果如下，能够看到 t1 的分区 p1 在存储节点 polardb-x-9f8v-dn- 0 上。

5.3 执行如下 SQL 语句，将 t1 的分区 p1 的存储节点迁徙到 polardb-x-9f8v-dn-1。

-- 理论执行过程，请依据用户的实例的存储节点信息，填写正确的存储节点 alter table t1 move partitions p1 to 'polardb-x-9f8v-dn-1'; show topology from t1;

5.4 分区迁徙也反对表组级别的操作，语法如下。

-- 请依据理论状况，填写正确的存储节点信息 alter tablegroup test_tg1 move partitions p4 to 'polardb-x-9f8v-dn-1';

对于 list/list column 分区，PolarDB- X 反对在线批改（减少或者删除分区值）各个分区的定义（也就是分区值）。

6.1 以表 t5 为例，执行如下 SQL 语句，批改前先查看各个分区定义。

show create table t5;

6.2 执行如下 SQL 语句，咱们对表 t5 的分区 p3 减少两个值 20 和 21。

alter table t5 modify partition p3 add values(20,21);

6.3 执行如下 SQL 语句，查看变更后表 t5 的分区定义。

show create table t5;

6.4 相应的，PolarDB- X 也反对删除局部分区值。执行如下 SQL 语句，执行如下 SQL 语句，咱们对表 t5 的分区 p3 删除两个值 20 和 21。

alter table t5 modify partition p3 drop values(20,21);

分区批改也反对表组级别的操作，实例的具体操作如下。

6.5 执行如下 SQL 语句，查看 t5 的表组名称。

show full create table t5

返回后果如下，能够看到 t5 的表组名称为 tg4。

6.6 执行如下 SQL 语句，表组级别的减少分区值。

alter tablegroup tg4 modify partition p3 add values(20,21);

6.7 执行如下 SQL 语句，表组级别的删除分区值。

alter tablegroup tg4 modify partition p3 drop values(20,21);

PolarDB- X 也反对对已有分区进行重命名操作。

执行如下 SQL 语句，对表级别重命名分区。

alter table t4 rename partition p2 to p20;

执行如下 SQL 语句，对表组级别的重命名分区。

alter tablegroup tg4 rename partition p3 to p30;

执行如下 SQL 语句，创立测试数据库 part_manage 并创立测试表。

-- 创立测试库
create database hot_key_test mode='auto';
use hot_key_test;
-- 拆分形式创立表 orders，order_details 默认将按主键 hash 拆分
create table orders(order_id bigint primary key auto_increment, 
           customer_id varchar(64) default null, create_time datetime not null,
           update_time datetime not null);
create table order_details(order_detail_id bigint primary key auto_increment, 
           order_id bigint not null, customer_id varchar(64) default null,
           create_time datetime not null, update_time datetime not null);

-- 查看一下默认主键拆分的 orders 和 order_details 表的拆分形式
set enable_set_global=true;
set global show_hash_partitions_by_range=false;
show full create table orders;
show full create table order_details;

3.1 热点值指的是某个 key 的某个值的行数或者写入量十分多，称为 Big Key, PolarDB-X 反对热点辨认及热点散列。

执行如下 SQL 语句，以 orders、order_details 为了例子，咱们人为的造一下数据，让 order_id=88。和 order_id=null 的数据特地多 (表的总数据量大略 100W 行左右)。

insert into orders values (null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now());
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into orders select null,case cast(rand()*2 as signed) when 0 then concat("",cast(rand()*10000 as signed)) when 1 then 88 else null end ,now(),now() from orders;
insert into order_details select null, order_id,customer_id,now(),now() from orders order by rand();

3.2 执行如下 SQL 语句，当初咱们看看这两个表的各个分区的数据分布状况。

set names utf8mb4;
analyze table orders;
analyze table order_details;
select table_group_name,table_name,index_name,partition_name,table_rows,percent from information_schema.table_detail where table_schema='hot_key_test' and table_name = 'orders';
select table_group_name,table_name,index_name,partition_name,table_rows,percent from information_schema.table_detail where table_schema='hot_key_test' and table_name = 'order_details';

返回后果如下，因为这两个表都是默认主键 hash 拆分的，所以各个分区的数据分布还是挺平衡的：

3.3 因为业务须要依据 orders 和 order_details 须要按一下关联查问统计订单信息, 查问的 SQL 如下。

select count(1) from orders a join order_details b on a.order_id=b.order_id and a.customer_id=b.customer_id;

3.4 执行如下 SQL 语句，查看上一步 SQL 语句的执行打算。

explain select count(1) from orders a join order_details b on a.order_id=b.order_id and a.customer_id=b.customer_id;

3.5 执行如下 SQL 语句，查看具体执行工夫。

select count(1) from orders a join order_details b on a.order_id=b.order_id and a.customer_id=b.customer_id ;

3.6 因为因为 orders 和 order_details 表都是按主键拆分的，没有在 customer_id，order_id 纬度拆分，所以下面的 SQL 须要将两个表的数据拉到 CN 上做 JOIN，而后在聚合。为了进步以上 SQL 的查问效率，咱们在别离在 orders 表和 order_details 表创立两个聚簇索引，索引按 key(customer_id，order_id) 拆分。

create clustered index clustered_idx_customer_id on orders(customer_id,order_id);
create clustered index clustered_idx_customer_id on order_details(customer_id,order_id);

3.7 索引创立好后，咱们看看这个查问语句变成了一条在索引表的一个分片上做下推的 join 查问，执行效率将失去很大的进步。咱们能够看看这个 SQL 的执行打算。

explain select count(1) from orders a join order_details b on a.order_id=b.order_id and a.customer_id=b.customer_id;

3.8 执行如下 SQL 语句，查看具体执行工夫。

select count(1) from orders a join order_details b on a.order_id=b.order_id and a.customer_id=b.customer_id where a.customer_id=1;

返回后果如下，从执行工夫看，如同并没有什么改善，为什么呢？

3.9 因为全局索引表是依照 customer_id 维度拆分的，数据分布很不平均，咱们通过一下 SQL 看看它的数据分布状况。

select table_group_name,table_name,index_name,partition_name,table_rows,percent from information_schema.table_detail where table_schema='hot_key_test' and table_name = 'orders';
select table_group_name,table_name,index_name,partition_name,table_rows,percent from information_schema.table_detail where table_schema='hot_key_test' and table_name = 'order_details';

返回后果如下，能够看到两个索引表的 p6 和 p12 都有热点数据。

3.10 咱们通过以下 SQL 查查看这个热点 key 是多少，

select count(*),customer_id from orders group by customer_id order by count(*) desc limit 10;
select count(*),customer_id from order_details group by customer_id order by count(*) desc limit 10;

返回后果如下，能够看到 customer_id=88 和 customer_id=null（匿名用户），合乎咱们造数据时的要求。

3.11 发现了热点 key，咱们能够应用 PolarDB- X 提供的热点散列能力对其打散。

执行如下 SQL 语句，咱们别离对 orders、order_details 表的索引进行散列。

-- 将 customer_id=null 的数据依照表的第二个拆分列 order_id 的 hash 空间拆分为 16 个分区
alter table orders.clustered_idx_customer_id split into partitions 16 by hot value (null);
-- 将 customer_id=88 的数据依照表的第二个拆分列 order_id 的 hash 空间拆分为 16 个分区
alter table orders.clustered_idx_customer_id split into partitions 16 by hot value (88);
-- 将 customer_id=88 的数据依照表的第二个拆分列 order_id 的 hash 空间拆分为 16 个分区
alter table order_details.clustered_idx_customer_id split into partitions 16 by hot value (88);
-- 将 customer_id=null 的数据依照表的第二个拆分列 order_id 的 hash 空间拆分为 15 个分区
alter table order_details.clustered_idx_customer_id split into partitions 15 by hot value (null);

3.12 散列实现后咱们再看看这两个表的数据分布状况，索引表的数据曾经很平衡了（因为分区太多，咱们只截了要害局部的图）。

select table_group_name,table_name,index_name,partition_name,table_rows,percent from information_schema.table_detail where table_schema='hot_key_test' and table_name = 'orders';
select table_group_name,table_name,index_name,partition_name,table_rows,percent from information_schema.table_detail where table_schema='hot_key_test' and table_name = 'order_details';

3.13 执行如下 SQL 语句，咱们再看看当初这个查问语句的执行打算。

explain select count(1) from orders a join order_details b on a.order_id=b.order_id and a.customer_id=b.customer_id;

返回后果如下，能够看到这个 sql 曾经不是一个下推的 join，而是一个 BKAJoin，效率必定是没有下推的高。之所以不能下推，是因为这两个索引的表组不统一了，从下面的截图能够看到 orders.clustered_idx_customer_id 的表组是 tg8，order_details.clustered_idx_customer_id 的表组是 tg7。

4.1 为了让下面的两个索引组织到一个雷同的表组中，咱们手工创立一个表组 tg_idx_customer_id，而后别离将他们通过以下命令绑定到雷同的表组。

create tablegroup tg_idx_customer_id;
alter table orders.clustered_idx_customer_id set tablegroup=tg_idx_customer_id;
alter table order_details.clustered_idx_customer_id set tablegroup=tg_idx_customer_id force;

4.2 执行如下 SQL 语句，查看之前的查问语句的执行打算。

explain select count (1) from orders a join order_details b on a.order_id=b.order_id and a.customer_id=b.customer_id;

返回后果如下，此时之前的查问语句又能够变成可下推的 join 查问了。

4.3 执行如下 SQL 语句，查看查问语句的执行工夫。

select count (1) from orders a join order_details b on a.order_id=b.order_id and a.customer_id=b.customer_id;

返回后果如下，能够看到执行工夫也缩小了不少。

点击立刻收费试用云产品开启云上实际之旅！

原文链接

本文为阿里云原创内容，未经容许不得转载。

关于数据库:实践教程之-PolarDBX-分区管理

前置筹备

分区治理测试

1. 筹备测试表。

2. 查看表的构造以及拓扑信息。

3. 分区合并。

4. 分区决裂。

5. 分区迁徙。

6. 批改分区值。

7. 重命名分区。

分区热点散列测试

1. 筹备测试表。

2. 执行如下 SQL 语句，查看表的构造。

3. 筹备测试数据。

4. 变更表组。

Just My Socks（注册教程内含优惠码）

关于数据库:实践教程之-PolarDBX-分区管理

前置筹备

分区治理测试

1. 筹备测试表。

2. 查看表的构造以及拓扑信息。

3. 分区合并。

4. 分区决裂。

5. 分区迁徙。

6. 批改分区值。

7. 重命名分区。

分区热点散列测试

1. 筹备测试表。

2. 执行如下 SQL 语句，查看表的构造。

3. 筹备测试数据。

4. 变更表组。

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）