腾讯云TDSQL-C MySQL版支持的语句场景和受限场景

本文介绍并行查询能力支持的语句场景和受限场景。

兼容语句场景

TDSQL-C MySQL 版已经实现了具备如下特征的 SQL 语句的并行查询处理，并在逐渐完善更多的功能场景。对于单表扫描：支持全表扫描、索引扫描、索引范围扫描、索引 REF 查询等扫描类型的正序、逆序扫描。对于多表连接：支持 Nested Loop Join 算法以及 Semi Join、Anti Join、Outer Join 等连接类型。对于子查询：支持 derived table 的并行。对于数据类型：支持带多种数据类型的查询，包括整型数据、字符型数据、浮点型数据、时间类型数据、以及（有运行时大小限制的）溢出类型数据。普通运算符和函数原则上不限。聚合函数支持 COUNT/SUM/AVG/MIN/MAX。支持 UNION/UNION ALL 查询。支持 traditional（默认格式）、json 和 tree 三种 EXPLAIN 格式。对应语句的性能提升
SF=100，Dop=16设置下，加速比如下。SUM/AVG/COUNT 等聚合函数。

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 '93' day GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus ;

未开启并行查询执行时间为1376.96秒，开启并行查询执行时间为107.25秒，加速倍数可以达到12.84倍。order by/group by 分组/排序语句。

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 '93' day GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus ;

未开启并行查询执行时间为1376.96秒，开启并行查询执行时间为107.25秒，加速倍数可以达到12.84倍。Join/Between/In 语句

select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#12' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 6 and l_quantity <= 6 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#13' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 10 and l_quantity <= 10 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#24' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 21 and l_quantity <= 21 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ); 

未开启并行时查询时间为20.55秒，开启并行查询后加速时间为1.87秒，加速倍数可以达到11倍。

受限场景

TDSQL-C MySQL 版并行查询能力不支持的场景如下。

除了通过 并行查询兼容语句场景 可以查询到语句是否被执行并行查询外，您还可以通过查看并行查询执行计划与查看线程工作状态查询，详情参见 查看并行查询。
官网1折活动，限时活动，即将结束，速速收藏
同尘科技为腾讯云授权服务中心。
购买腾讯云产品享受折上折，更有现金返利。同意关联立享优惠