使用列存索引加速INSERT SELECT类ETL任务-云原生数据库 PolarDB-阿里云

PolarDB支持ETL（Extract Transform Load）功能，开启该功能后，您可以在RW节点上使用列存索引，RW节点的SQL语句中的SELECT请求会发送至只读列存节点并使用列存索引功能进行加速查询，读取数据后，PolarDB会通过内部网络将数据传回RW节点并写入目标表。

技术原理

从只读列存节点读取数据并写入表

前提条件

集群版本需满足以下条件之一：

PolarDB MySQL版8.0.1版本且Revision version为8.0.1.1.29及以上。
PolarDB MySQL版8.0.2版本且Revision version为8.0.2.2.12及以上。

您可以通过查询版本号来确认集群版本。

使用限制

该功能仅适用于以下两种SQL语句：

CREATE TABLE table_name [AS] SELECT ...
INSERT ... SELECT ...

使用方法

您可以在数据库中通过设置下表中的参数值来选择是否从只读列存节点读取数据，以及在读取数据时是否需要压缩文件。

参数

说明

etl_from_imci

是否从只读列存节点读取数据。取值如下：

ON：从只读列存节点读取数据。
OFF（默认）：不从只读列存节点读取数据。
FORCED：取消事务限制。

etl_from_imci_compress

从只读列存节点读取数据时，是否需要压缩文件。取值如下：

ON：从只读列存节点读取数据时，压缩文件。
OFF（默认）：从只读列存节点读取数据时，不压缩文件。

在修改参数值时，您可以将参数值设置为Global级别、Session级别或语句级别，以etl_from_imci参数为例：

设置为Global级别。
```
SET GLOBAL etl_from_imci = ON;
```
设置为Global级别后，当前集群的所有CREATE TABLE new_tbl [AS] SELECT ..和INSERT ... SELECT ...请求都会从只读列存节点读取数据。
设置为Session级别。
```
SET etl_from_imci = ON;
```
设置为Session级别后，仅当前Session下的CREATE TABLE new_tbl [AS] SELECT ..和INSERT ... SELECT ...请求会从只读列存节点读取数据。
设置为语句级别。
您可以通过HINT语法为某条SQL语句选择是否从只读列存节点读取数据。示例如下：
```
CREATE TABLE t2 SELECT /*+ SET_VAR(etl_from_imci=ON) */ * from t1 where 'A' = 'a';
```

当etl_from_imci参数设置为ON，即选择从只读列存节点读取数据，数据读取完成并传回RW节点时，您可以通过SHOW processlist 命令查看processlist状态，此时，processlist状态应为ETL FROM IMCI。

使用说明

当查询条件复杂，SQL语句执行时间较长但查询的结果集数据量较小时，开启ETL功能可以明显提升性能。

开启ETL功能并不是在所有场景下都能带来性能收益，某些场景下性能可能会下降。例如：

当查询比较简单时，从远程的只读列存节点读取数据会引入额外的网络传输和结果集解析开销，性能可能会下降。
当查询的结果集数据量比较大时，从只读列存节点读取数据并传入RW节点，以及在RW节点上将数据写入表成为主要瓶颈，使用该功能会导致性能下降。

性能比对

使用TPCH-10 GB数据集测试较复杂的查询。示例如下：

查询结果为1行数据。

select
    sum(l_extendedprice * l_discount) as revenue
from
    lineitem
where
    l_shipdate >= date '1994-01-01'
    and l_shipdate < date '1994-01-01' + interval '1' year
    and l_discount between .06 - 0.01 and .06 + 0.01
    and l_quantity < 24

查询结果为1行数据时，查询时间对比如下（单位：秒）：

只读列存节点查询时间	未开启ETL功能，在RW节点执行`CREATE TABLE new_tbl [AS] SELECT ..`语句的时间	开启ETL功能后，在RW节点执行`CREATE TABLE new_tbl [AS] SELECT ..`语句的时间	未开启ETL功能，在RW节点执行`INSERT ... SELECT ...`语句的时间	开启ETL功能后，在RW节点执行`INSERT ... SELECT ...`语句的时间
0.05	>60	0.17	>60	0.08

查询结果为4行数据。

select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
    l_shipdate <= date '1998-12-01' - interval '90' day
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus

查询结果为4行数据时，查询时间对比如下（单位：秒）：

只读列存节点查询时间	未开启ETL功能，在RW节点执行`CREATE TABLE new_tbl [AS] SELECT ..`语句的时间	开启ETL功能后，在RW节点执行`CREATE TABLE new_tbl [AS] SELECT ..`语句的时间	未开启ETL功能，在RW节点执行`INSERT ... SELECT ...`语句的时间	开启ETL功能后，在RW节点执行`INSERT ... SELECT ...`语句的时间
0.58	>60	0.64	>60	0.58

查询结果为27840行数据。

select
    p_brand,
    p_type,
    p_size,
    count(distinct ps_suppkey) as supplier_cnt
from
    partsupp,
    part
where
    p_partkey = ps_partkey
    and p_brand <> 'Brand#45'
    and p_type not like 'MEDIUM POLISHED%'
    and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
    and ps_suppkey not in (
        select
            s_suppkey
        from
            supplier
        where
            s_comment like '%Customer%Complaints%'
    )
group by
    p_brand,
    p_type,
    p_size
order by
    supplier_cnt desc,
    p_brand,
    p_type,
    p_size

查询结果为27840行数据时，查询时间对比如下（单位：秒）：

只读列存节点查询时间	未开启ETL功能，在RW节点执行`CREATE TABLE new_tbl [AS] SELECT ..`语句的时间	开启ETL功能后，在RW节点执行`CREATE TABLE new_tbl [AS] SELECT ..`语句的时间	未开启ETL功能，在RW节点执行`INSERT ... SELECT ...`语句的时间	开启ETL功能后，在RW节点执行`INSERT ... SELECT ...`语句的时间
0.55	>60	0.92	>60	0.82