云数据库 MySQL 加速 ETL 回写

云数据库 MySQL 可通过只读分析引擎加速数据查询并回写至读写实例，使用此功能，您可以在只读分析引擎中使用列存加速 INSERT…SELECT… 语句中的 SELECT 语句查询，并将结果通过内部网络将数据传回读写实例并写入目标表。详细的功能描述请参考 加速 ETL 回写。
使用场景
注意：
因为只读分析引擎为异步复制模式，所以当只读分析引擎存在延迟时查询的结果与读写实例中存在一定的时差。此功能仅适用于对数据延迟不敏感的场景。
当前仅支持 INSERT...SELECT... 加速，暂不支持 UPDATE...SELECT... 以及 DELETE...SELECT... 等场景加速。
推荐在以下场景使用此功能：当查询条件复杂、SQL 语句执行时间较长但查询结果集的数据量较小时。在这种场景下，使用此功能可以显著提高性能，因为只读分析引擎会加速 SELECT 查询的执行效率。
使用此功能并不是在所有场景下都能带来性能收益，在某些场景下性能可能会下降。例如：
INSERT…SELECT… 语句中的 SELECT 查询比较简单时，从只读分析引擎读取数据并回写到读写实例会带来额外的网络开销。对比直接从读写实例读取数据的优势并不明显。
INSERT…SELECT… 语句中的 SELECT 查询结果集数据量大的情况下，此时主要的性能瓶颈在于结果集通过网络传输并写入读写实例的过程。此场景无法使用此功能来优化性能。
前提条件
只读分析引擎实例和读写实例的运行状态均为正常运行中。
执行的用户需拥有对应对象的 INSERT 权限。
仅支持 INSERT…SELECT… 的 SQL 执行场景。
功能参数说明
参数名
参数说明
默认值
参数值
libra_etl_to_rw
控制是否执行回写功能。
off
on：开启回写能力，表示执行回写。
off：关闭回写能力。当值为 off 时，执行 INSERT…SELECT… 语句时会报错权限不足。
libra_concurrent_etl
控制是否在回写读写实例时开启并发写入。
off
on: 开启并发回写。并发回写能提升结果回写效率，但是不能保证多线程间写入的事务一致性。
off：关闭并发回写。设置为此值时，回写至读写实例的数据将在一个事务中完成。
libra_etl_concurrency
控制回写读写实例的并发线程数。只有在 libra_concurrent_etl 设置为 on 时才会生效。
1
取值范围：[1 - 读写实例 CPU 核数]，可设置值为整数，最大值为读写实例的 CPU 核数。
注意：
使用多并发回写时执行速度更快，但是并发数量过高会导致读写实例无法承受其写入压力而使得实例性能受损。
执行示例
1. 使用客户端工具登录至只读分析引擎。例如使用 MySQL 客户端通过数据库账号密码登录至只读分析引擎。
2. 执行如下 SQL，即可开启加速 ETL 回写功能（通过只读分析引擎回写读写实例功能）。
通过在 seesion 中设置参数执行：
set libra_etl_to_rw=on;
set libra_concurrent_etl=on;
set libra_etl_concurrency=4;
INSERT INTO database1.table1 select a.t1,b.t2,a.t2,b.t3 from a,b where a.t1=b.t1 and a.t5='x';
set libra_concurrent_etl=off;
set libra_etl_to_rw=off;
通过在 SQL 中添加 Hint 参数执行：
INSERT /*+ SET_VAR(libra_etl_to_rw=ON) SET_VAR(libra_concurrent_etl=ON) SET_VAR(libra_etl_concurrency=4)*/ INTO database1.table1 select a.t1,b.t2,a.t2,b.t3 from a,b where a.t1=b.t1 and a.t5='x';
注意：
如果使用 mysql 客户端执行“添加 Hint 参数的方法执行”，需要在 MySQL 客户端登录只读分析引擎时添加 -c 参数，否则 hint 将不会生效。
性能对比
测试环境
主实例：16核64GB。
只读分析引擎：16核64GB。
测试数据
使用 TPC-H 10GB数据集。
测试复杂 SQL 及结果
set libra_etl_to_rw=on;
set libra_concurrent_etl=off;
set libra_etl_concurrency=1;
﻿
create table t1 (
    supp_nation VARCHAR(50),
    cust_nation VARCHAR(50),
    l_year INT,
    revenue DECIMAL
);
﻿
INSERT INTO t1 SELECT
supp_nation, cust_nation, l_year, sum(volume) as revenue 
from (select n1.n_name as supp_nation, n2.n_name as cust_nation, 
             extract(year from l_shipdate) as l_year, 
             l_extendedprice * (1 - l_discount) as volume 
      from supplier, lineitem, orders, customer, nation n1, nation n2 
      where s_suppkey = l_suppkey 
        and o_orderkey = l_orderkey 
        and c_custkey = o_custkey 
        and s_nationkey = n1.n_nationkey 
        and c_nationkey = n2.n_nationkey 
        and ((n1.n_name = 'JAPAN' and n2.n_name = 'INDIA') 
              or (n1.n_name = 'INDIA' and n2.n_name = 'JAPAN')) 
        and l_shipdate between '1995-01-01' and '1996-12-31') as shipping 
group by supp_nation, cust_nation, l_year 
order by supp_nation, cust_nation, l_year; 
查询结果为4行数据时，测试结果如下（单位：秒）。
只读实例 SELECT 的时间
未开启回写功能
在读写实例执行 INSERT…SELECT… 的时间
开启回写功能
在只读分析引擎执行 INSERT…SELECT… 的时间
0.57
232.77
0.61
set libra_etl_to_rw=on;
set libra_concurrent_etl=off;
set libra_etl_concurrency=1;
﻿
CREATE TABLE t2
 (
    p_brand VARCHAR(10),
    p_type VARCHAR(25),
    p_size INTEGER,
    supplier_cnt INTEGER
);
﻿
INSERT INTO t2 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行数据时，测试结果如下（单位：秒）。
只读实例 SELECT 的时间
未开启回写功能
在读写实例执行 INSERT…SELECT… 的时间
开启回写功能
在只读分析引擎执行 INSERT…SELECT… 的时间
0.15
8.77
0.61
测试简单 SQL 及结果
set libra_etl_to_rw=on;
set libra_concurrent_etl=on;
set libra_etl_concurrency=8;
﻿
CREATE TABLE IF NOT EXISTS lineitem ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL , primary key(L_ORDERKEY, L_LINENUMBER));
﻿
INSERT INTO lineitem_t SELECT
    *
from
    lineitem;
查询结果为59986051行数据时，测试结果如下（单位：秒）。
执行并发
未开启回写功能
在读写实例执行 INSERT…SELECT… 的时间
开启回写功能
在只读分析引擎执行 INSERT…SELECT… 的时间
不开启并发
622.7
1441.02
并发数为8
﻿
259.20
并发数为16
﻿
181.79
并发数为32
﻿
185.64

参数名	参数说明	默认值	参数值
libra_etl_to_rw	控制是否执行回写功能。	off	on：开启回写能力，表示执行回写。 off：关闭回写能力。当值为 off 时，执行 `INSERT…SELECT…` 语句时会报错权限不足。
libra_concurrent_etl	控制是否在回写读写实例时开启并发写入。	off	on: 开启并发回写。并发回写能提升结果回写效率，但是不能保证多线程间写入的事务一致性。 off：关闭并发回写。设置为此值时，回写至读写实例的数据将在一个事务中完成。
libra_etl_concurrency	控制回写读写实例的并发线程数。只有在 libra_concurrent_etl 设置为 on 时才会生效。	1	取值范围：[1 - 读写实例 CPU 核数]，可设置值为整数，最大值为读写实例的 CPU 核数。注意：使用多并发回写时执行速度更快，但是并发数量过高会导致读写实例无法承受其写入压力而使得实例性能受损。

只读实例 SELECT 的时间	未开启回写功能在读写实例执行 INSERT…SELECT… 的时间	开启回写功能在只读分析引擎执行 INSERT…SELECT… 的时间
0.57	232.77	0.61

执行并发	未开启回写功能在读写实例执行 INSERT…SELECT… 的时间	开启回写功能在只读分析引擎执行 INSERT…SELECT… 的时间
不开启并发	622.7	1441.02
并发数为8			259.20
并发数为16			181.79
并发数为32			185.64

加速 ETL 回写

本页目录：

使用场景

前提条件

功能参数说明

执行示例

性能对比

测试环境

测试数据

测试复杂 SQL 及结果

测试简单 SQL 及结果