技术解读｜Klustron Mirror表功能介绍和使用示例

<!--StartFragment--> # 文章背景 介绍 Mirror 表的功能和具体使用场景，并演示如何创建 Mirror 表，以及如何使用 Mirror 表加速一个分析型的查询场景。所有的操作都通过命令行的方式进行展现。   ## **1**.Mirror 表的作用 在 OLAP 的现实场景中有一些数据表具有如下特征： * 数据量比较小，例如部门信息表、业务代码表等等。 * 数据变化不频繁。 * 这表经常会和大表（比如订单表）做 join。 对于具有上面特征的表，在 Klustron 中用户可以把他们定义为 Mirror  表（镜像表），以便实现更好的查询性能。Klustron 会确保一个集群的 Mirror 表在每一个存储节点中都有一份相同的数据。 在执行对 Mirror 表的插入、更新、删除操作时，Klustron 的计算节点会自动对 Mirror 表在每一个存储节点的数据做相应的插入、更新、删除操作，并且这些操作运行在同一个全局事务中，因而具有 ACID 特性。 当新增一个 Storage Shard，Klustron 会自动把系统中所有的 Mirror 表都会复制到新 Shard 上，在这个复制的过程中，这些 Mirror 表仍然可以被读写，不过新增 Mirror 表的操作会阻塞直到复制全部完成。 Mirror 表与分片存储的大表的 join 就总是可以下推到存储节点执行，同时这确保了这两个表的 join 是由多个存储节点并行执行的，从而达到更好的查询执行性能。 在 OLAP 应用中，维度表通常符合 Mirror 表的特征，适合定义为 Mirror 表。这样在一个 OLAP 的星形连接查询中，多个 Mirror 表与一个事实表的的 join，实际上也是并行地运行在多个存储节点上的，因为这个事实表会分片存储到多个存储节点上。 另外，两个或者多个 Mirror 表的 join，也总是可以下推到某一个存储节点上去执行，也可以在一定程度上提升查询性能。 ## 2.测试环境 由于测试是在虚拟机上进行的，文章中的执行时间只能用作在相同环境下不同场景之间的对比参考。测试集群相关虚拟机的配置信息如下：  ## 3. 具体案例 首先通过 pgsql 客户端登录计算节点 192.168.40.152，创建用户和数据库 ``` psql -h 192.168.40.152 -p 47001 postgres create user kunlun_test with password 'kunlun'; create database testdb owner kunlun_test; grant all privileges on database testdb to kunlun_test; \\q ``` 以用户 kunlun_test 登录 testdb，创建测试表 sales_order 和 product: ``` psql -h 192.168.40.152 -p 47001 -U kunlun_test testdb create table sales_order( order_number INT NOT NULL AUTO_INCREMENT, customer_number INT NOT NULL, product_code INT NOT NULL, order_date DATETIME NOT NULL, entry_date DATETIME NOT NULL, order_amount DECIMAL(18,2) NOT NULL) partition by range(order_date); create table sales_order_p0 partition of sales_order for values from ('2021-01-01') to ('2021-04-01'); create table sales_order_p1 partition of sales_order for values from ('2021-04-01') to ('2021-07-01'); create table sales_order_p2 partition of sales_order for values from ('2021-07-01') to ('2021-10-01'); create table sales_order_p3 partition of sales_order for values from ('2021-10-01') to ('2022-01-01'); create table product ( product_code INT NOT NULL AUTO_INCREMENT, product_name VARCHAR(128) NOT NULL, product_category VARCHAR(256) NOT NULL, PRIMARY KEY (product_code)) ; ``` 分别创建存储过程，然后使用存储过程往 sales_order 表中装载 10000 行记录，往 product 表中装载 1000 行记录。 ``` create or replace procedure generate_order_data() AS \$\$ DECLARE v_customer_number integer; v_product_code integer; v_order_date date; v_amount integer; start_date date := to_date('2021-01-01','yyyy-mm-dd'); i integer :=1; BEGIN while i<=10000 loop v_customer_number := FLOOR(1+RANDOM()*6); v_product_code := FLOOR(1+RANDOM()*500); v_order_date := to_date('2021-01-01','yyyy-mm-dd') + CAST(FLOOR(RANDOM()*365) AS INT); v_amount := FLOOR(1000+RANDOM()*9000); insert into sales_order VALUES (i,v_customer_number,v_product_code,v_order_date,v_order_date,v_amount); commit; i := i+1; end loop; END; \$\$ LANGUAGE plpgsql; create or replace procedure generate_product_data() AS \$\$DECLARE v_product_name varchar(128); i integer :=1; BEGIN while i<=1000 loop case mod(i,3) when 0 then v_product_name := 'Hard Disk '||i; INSERT INTO product VALUES (i,v_product_name,'Storage'); when 1 then v_product_name := 'LCD '||i; INSERT INTO product VALUES (i,v_product_name,'Monitor'); when 2 then v_product_name := 'Paper'||i; INSERT INTO product VALUES (i,v_product_name,'Paper'); end case; commit; i := i+1; end loop; END; \$\$ LANGUAGE plpgsql; set statement_timeout 0; call generate_order_data(); call generate_product_data(); ``` 创建索引，并收集统计信息 ``` create index sales_order_idx1 on sales_order(product_code,order_amount); create index product_idx1 on product(product_name,product_code) analyze sales_order; analyze product; ``` 查看表 product 和 sales_order 在存储节点的分布情况。 ``` select * from pg_shard; \\d+ product ```  可以发现 product 存放于 shard 的 id 为6，即 shard\_1 存储节点。而 sales_order 的分区则分布于 shard\_1 和 shard\_2 两个存储节点。 ``` select relname table_name ,reltuples num_rows, name shard_name from pg_class t1,pg_shard t2 where t1.relshardid= t2.id and t1.reltype<>0 and t1.relname  like 'sales%'; ```  查看下面获取所有硬盘总销售数据 SQL 的执行计划 ``` explain select sum(order_amount) from sales_order t1, product t2 where t1.product_code = t2.product_code andproduct_name like 'Hard Disk%' ; ```  首先在计算节点查询优化器选择 Nested Loop 方式进行 join, 选择 product 表为驱动表，计算节点发送下面的 Remote SQL 到驱动表所在的shard\_1上获取满足条件的记录到计算节点。 ``` SELECT `t2`.`product_code` FROM `testdb_\$\$_public`.`product` as t2 WHERE (`t2`.`product_name` like"Hard Disk%") ``` 然后将获取到驱动表满足条件记录的 product_code 组成下面的 Remote SQL ``` SELECT `t1`.`order_amount`,`t1`.`product_code` FROM `testdb_\$\$_public`.`sales_order_p0` as t1 WHERE (?= `t1`.`product_code`) ``` 分别发送到 sales_order 4 个分区所在的 shard\_1 和 shard\_2 执行，最后将每个分区的返回的order_mount进行汇总。 SQL的执行时间为4.59s ``` testdb=>\\timing ontestdb=> select sum(order_amount) from sales_order t1, product t2 where t1.product_code = t2.product_code and product_name like 'Hard Disk%' ; sum ------------- 17975514.00(1 row) Time: 4590.807 ms (00:04.591) ``` 将 product 设置为 Mirror 表，装载数据库重新进行测试。 其中 with (shard=all) 的子句说明将表创建为 Mirror 表，顾名思义其会存放在所有的存储节点上。 ``` drop table product; create table product ( product_code INT NOT NULL AUTO_INCREMENT, product_name VARCHAR(128) NOT NULL, product_category VARCHAR(256) NOT NULL, PRIMARY KEY (product_code)) with (shard=all); call generate_product_data(); create index product_idx1 on product(product_name,product_code); analyze product; ``` Product 表对应的 shard id 是一个很大的值，说明其是 Mirror 表  重新查看测试 SQL 的执行计划 ``` explain select sum(order_amount) from sales_order t1, product t2 where t1.product_code = t2.product_code andproduct_name like 'Hard Disk%' ; ```  可以看到下面的 Remote SQL 被推送到 sales_order 所有分区的存储节点执行。 ``` SELECT sum(var\$\$0) FROM ( `testdb_\$\$_public`.`product` as t2 join lateral (SELECT `t1`.`order_amount` asvar\$\$0,`t1`.`product_code` as  var\$\$1 FROM  `testdb_\$\$_public`.`sales_order_p0`  as t1 WHERE (`t2`.`product_code` = `t1`.`product_code`)) tmp\$\$2 on (`t2`.`product_name` like "Hard Disk%")) WHERE (var\$\$1 = `t2`.`product_code`)  ``` 最后的结果才在计算节点汇总(Finalize Aggregate)。相应的 SQL 执行也变快了,从之前的 4.59 秒降低到 1.46 秒。说明了 Mirror 表合理的使用，能够提升某些分析型场景的性能。 ``` testdb=>\\timing on testdb=> select sum(order_amount) from sales_order t1, product t2 where t1.product_code = t2.product_code and product_name like 'Hard Disk%' ; sum ------------- 17975514.00(1 row) Time: 1459.327 ms (00:01.459) ``` **同时欢迎大家扫码👇添加小助手（备注：加入Klustron技术交流群）欢迎大家在交流群共同探讨更多问题及主题。**  <!--EndFragment--> ````