关于数据库:实践教程之-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_tg1create table t1 (    a int) partition by key(a) partitions 5 tablegroup=test_tg1;-- 创立表t2, 让它和t1一样绑定到表组test_tg1create 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分区的表t4create 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分区的表t5create 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;

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

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

原文链接

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