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

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

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

本期收费试验地址

本期教学视频地址

前置筹备

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

分区治理测试

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

1. 筹备测试表。

执行如下 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. 查看表的构造以及拓扑信息。

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. 分区合并。

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;

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

4. 分区决裂。

在下面的例子中咱们对表 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. 分区迁徙。

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

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';

6. 批改分区值。

对于 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);

7. 重命名分区。

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

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

alter table t4 rename partition p2 to p20;

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

alter tablegroup tg4 rename partition p3 to p30;

分区热点散列测试

1. 筹备测试表。

执行如下 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);

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

-- 查看一下默认主键拆分的 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. 筹备测试数据。

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. 变更表组。

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;

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

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

原文链接

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