并行查询（Parallel Query） - 内核功能| 阿里云

前提条件

PolarDB集群版本需为PolarDB MySQL 8.0且Revision version为8.0.1.0.5或以上，您可以通过查询版本号确认集群版本。

应用场景

并行查询适用于大部分SELECT语句，例如大表查询、多表连接查询、计算量较大的查询。对于非常短的查询，效果不太显著。

轻分析类业务
报表查询通常SQL复杂而且比较耗费时间，通过并行查询可以加速单次查询效率。
系统资源相对空闲
并行查询会使用更多的系统资源，只有当系统的CPU较多、IO负载不高、内存够大的时候，才可以充分使用并行查询来提高资源利用率和查询效率。
在离线混合场景
不同的业务用不同的连接地址，使用不同的数据库节点，避免互相影响。如果您为单节点的集群地址开启了并行查询，那么就会通过处理OLAP数据请求的地址对该单节点地址下的对应只读节点进行访问。

并行查询的使用方法

通过系统参数来控制并行查询
PolarDB通过Global全局参数max_parallel_degree来控制每一条SQL最多使用多少个线程并行执行，默认值是0，您可以在使用过程中随时修改该参数，无需重启数据库，具体操作请参见设置集群参数。

并行查询推荐设置以及相关说明如下：
- 并行度参数从低到高，逐渐增加，建议不要超过CPU核数的四分之一。集群的CPU内核数大于等于8才支持开启并行查询，小规格的集群不建议开启该参数。例如，刚开始使用并行查询时，设置max_parallel_degree参数为2，试运行一天后，如果CPU压力不大，可以往上增加；如果CPU压力较大，停止增加。
- max_parallel_degree参数为0时，表示关闭并行查询；max_parallel_degree参数为1时，表示开启并行查询，且并行度为1。
- 为了保证并行度参数max_parallel_degree也能兼容其它程序版本以及考虑到MySQL配置文件中该参数的默认配置，PolarDB在控制台上增加了前缀loose，因此参数名称是loose_max_parallel_degree，这样保证其他版本接受该参数时也不会报错，详情请参见Program Option Modifiers。
- 打开并行查询功能时，需要设置innodb_adaptive_hash_index参数为OFF，innodb_adaptive_hash_index参数开启会影响并行查询的性能。
除了Global集群级别，您也可以单独调整某Session内SQL查询的并行度。例如，通过Session级环境变量，加到JDBC的连接串配置中，则可以对某个应用程序单独设置并行度。
```
set max_parallel_degree = n 
```

通过Hint来控制并行查询

使用Hint语法可以对单个语句进行控制，例如系统默认关闭并行查询情况下，但需要对某个高频的慢SQL查询进行加速，此时就可以使用Hint对特定SQL进行加速。

您可以使用如下任意一种方式开启并行查询：

SELECT /*+PARALLEL(x)*/ ... FROM ...;   -- x >0

SELECT /*+ SET_VAR(max_parallel_degree=n) */  *  FROM ...   // n > 0

您可以使用如下任意一种方式关闭并行查询：

SELECT /*+NO_PARALLEL()*/ ... FROM ...;

SELECT /*+ SET_VAR(max_parallel_degree=0) */  *  FROM ...

Hint高级用法

并行查询提供了PARALLEL和NO_PARALLEL两种Hint。

通过PARALLEL Hint可以强制查询并行执行，同时可以指定并行度和并行扫描的表。
通过NO_PARALLEL Hint可以强制查询串行执行，或者指定不选择某些表作为并行扫描的表。

Hint语法如下所示：

/*+ PARALLEL [( [query_block] [table_name]  [degree] )] */
/*+ NO_PARALLEL [( [query_block] [table_name][, table_name] )] */

其中参数说明如下所示。


参数	说明
query_block	应用Hint的query block名称。
table_name	应用Hint的表名称。
degree	并行度。

示例：

SELECT /*+PARALLEL()*/ * FROM t1, t2; 
-- 当表记录数小于records_threshold_for_parallelism设置的行数 （ 默认10000行）时，会强制并行，
-- 并行度用系统默认max_parallel_degree， 如果max_parallel_degree > 0, 
-- 则打开并行，如果max_parallel_degree等于0时，依旧时关闭并行。

SELECT /*+PARALLEL(8)*/ * FROM t1, t2; 
-- 强制并行度8并行执行， 
-- 当表记录数小于records_threshold_for_parallelism设置的行数 （ 默认10000行）时，会强制并行，
-- 并行度设置max_parallel_degree为8。

SELECT /*+ SET_VAR(max_parallel_degree=8) */  *  FROM ...   
-- 设置并行度max_parallel_degree为8， 
-- 当表记录数小于records_threshold_for_parallelism设置的行数（如20000行）时，会自动关闭并行。

SELECT /*+PARALLEL(t1)*/ * FROM t1, t2; 
-- 选择t1表并行, 对t1表执行/*+PARALLEL()*/ 语法

SELECT /*+PARALLEL(t1 8)*/ * FROM t1, t2; 
-- 强制并行度8且选择t1表并行执行，  对t1表执行/*+PARALLEL(8)*/语法

SELECT /*+PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1); 
--强制subquery并行执行， 并行度用系统默认max_parallel_degree， 
-- 如果max_parallel_degree > 0, 则打开并行，max_parallel_degree等于0时，依旧时关闭并行

SELECT /*+PARALLEL(@subq1 8)*/ SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1); 
--强制subquery并行执行， 并行度设置max_parallel_degree为8

SELECT SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+PARALLEL()*/ SUM(t1.a) FROM t1); 
--强制subquery并行执行， 
-- 并行度用系统默认max_parallel_degree， 
-- 如果max_parallel_degree > 0, 则打开并行，max_parallel_degree等于0时，依旧时关闭并行

SELECT SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+PARALLEL(8)*/ SUM(t1.a) FROM t1); 
--强制subquery并行执行， 设置并行度max_parallel_degree为8

SELECT /*+NO_PARALLEL()*/ * FROM t1, t2; 
-- 禁止并行执行

SELECT /*+NO_PARALLEL(t1)*/ * FROM t1, t2; 
-- 只对t1表禁止并行， 当系统打开并行时， 有可能对t2进行并行扫描，并行执行

SELECT /*+NO_PARALLEL(t1, t2)*/ * FROM t1, t2; 
-- 同时对t1和t2表禁止并行

SELECT /*+NO_PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1); 
--禁止subquery 并行执行

SELECT SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+NO_PARALLEL()*/ SUM(t1.a) FROM t1); 
 --禁止subquery 并行执行

说明对于不支持并行的查询或者并行扫描的表，PARALLEL Hint不生效。

并行子查询的选择方式（并行子查询详细信息请参见并行执行计划中的子查询支持部分）也可以通过Hint来进行控制，语法及说明如下：

/*+ PQ_PUSHDOWN [( [query_block])] */ 对应的子查询会选择push down的并行子查询执行策略
/*+ NO_PQ_PUSHDOWN [( [query_block])] */ 对应的子查询会选择shared access的并行子查询执行策略

示例：

#子查询选择push down并行策略
EXPLAIN SELECT /*+ PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ qb_name(qb1) */ a FROM t1);

#子查询选择shared access并行策略
EXPLAIN SELECT /*+ NO_PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ qb_name(qb1) */ a FROM t1);
#不加query block进行控制
EXPLAIN SELECT * FROM t2 WHERE t2.a =
                 (SELECT /*+ NO_PQ_PUSHDOWN() */ a FROM t1);

通过设置阈值来控制优化器是否选择并行执行
PolarDB提供了两个阈值来控制优化器是否选择并行执行，SQL语句只要满足其中任意一个条件，优化器就会考虑并行执行。
- records_threshold_for_parallelism
  若优化器估算出语句中存在扫描记录数超过该阈值的表，优化器会考虑选择并行执行计划。默认值为10000。若您的业务量较小或复杂查询业务并发较低，您可以选择将该阈值设置为2000或以上。
  
  说明上文提到的扫描记录数是根据对应表的统计信息进行估算得出的值，可能存在一定的误差。
- cost_threshold_for_parallelism
  若优化器估算查询的串行执行代价超过该阈值，优化器会考虑选择并行执行计划。默认值为50000。

表 1. 系统参数
参数名	级别	描述
max_parallel_degree	Global、Session	单个查询的最大并行度，即最多使用多少个Worker进行并行执行。取值范围：[0-1024] 默认值：0，表示关闭并行计算。说明 PolarDB优化器可能会对主查询和子查询分别并行执行，如果同时并行执行，它们的最大Worker数不能超过max_parallel_degree的值，整个查询使用的Worker数为主查询和子查询使用的Worker数之和。
parallel_degree_policy	Global	设置单个查询的并行度配置策略，取值范围如下： `TYPICAL`（默认）：PolarDB选择查询并行度时不会考虑数据库负载（如CPU使用率等），而尽可能与`max_parallel_degree`设置的并行度保持一致。 `AUTO`：PolarDB会根据数据库负载（如CPU使用率等）来决定是否禁止并行查询计划，并会根据查询代价选择并行度。 `REPLICA_AUTO`：仅只读节点会根据数据库负载（如CPU使用率等）决定是否禁止并行查询计划，并会根据查询代价选择并行度，而主节点不会选择并行执行计划。说明更多关于并行度配置策略的详细介绍，请参见并行度控制策略。
records_threshold_for_parallelism	Session	若优化器估算出语句中存在扫描记录数超过该阈值的表，优化器会考虑选择并行执行计划。取值范围：[0-18446744073709551615] 默认值：10000。说明若您的业务量较小或复杂查询业务并发较低，您可以选择将该阈值设置为2000或以上。
cost_threshold_for_parallelism	Session	若优化器估算查询的串行执行代价超过该阈值，优化器会考虑选择并行执行计划。取值范围：[0-18446744073709551615] 默认值：50000。

表 2. 状态变量
变量名	级别	描述
Parallel_workers_created	Session、Global	从Session启动开始，生成Parallel Worker的个数。
Gather_records	Session、Global	Gather记录总数。
PQ_refused_over_memory_soft_limit	Session、Global	由于内存限制没有启用并行的查询数。
PQ_refused_over_total_workers	Session、Global	由于总Worker数限制没有启用并行的查询数。
Total_used_query_memory	Global	当前总的已使用的查询内存（Virtual Memory）。
Total_running_parallel_workers	Global	当前正在运行的Parallel Worker的数目。

性能指标

本次测试将使用TPC-H生成100 GB数据来测试PolarDB MySQL 8.0集群的性能指标。测试用的PolarDB集群规格为32核256 GB，并行度max_parallel_degree分别设置为32和0，具体测试步骤请参见并行查询性能（OLAP）。

通过以上测试结果图得出结论，TPC-H中95%的SQL可以被加速，且在被加速的SQL语句中，有70%的SQL语句的执行速度是未使用并行查询的8倍以上。

并行执行计划

以下为您介绍EXPLAIN执行计划输出中与并行查询相关的内容。

并行扫描
在并行扫描中，每个Worker并行独立扫描数据表中的数据。Worker扫描产生的中间结果集将会返回给Leader线程，Leader线程通过Gather操作收集产生的中间结果，并将所有结果汇总返回到客户端。
多表并行连接
并行查询会将多表连接操作完整的下推到Worker上去执行。PolarDB优化器只会选择一个自认为最优的表进行并行扫描，而除了该表外，其他表都是一般扫描。每个Worker会将连接结果集返回给Leader线程，Leader线程通过Gather操作进行汇总，最后将结果返回给客户端。
并行排序
PolarDB优化器会根据查询情况，将ORDER BY下推到每个Worker里执行，每个Worker将排序后的结果返回给Leader，Leader通过Gather Merge Sort操作进行归并排序，最后将排序结果返回到客户端。
并行分组
PolarDB优化器会根据查询情况，将GROUP BY下推到Worker上去并行执行。每个Worker负责部分数据的GROUP BY。Worker会将GROUP BY的中间结果返回给Leader，Leader通过Gather操作汇总所有数据。这里PolarDB优化器会根据查询计划情况来自动识别是否需要再次在Leader上进行GROUP BY。例如，如果GROUP BY使用了Loose Index Scan，Leader上将不会进行再次GROUP BY；否则Leader会再次进行GROUP BY操作，然后把最终结果返回到客户端。
并行聚集
并行查询执行聚集函数下推到Worker上并行执行。并行聚集是通过两次聚集来完成的。第一次，参与并行查询部分的每个Worker执行聚集步骤。第二次，Gather或Gather Merge节点将每个Worker产生的结果汇总到Leader。最后，Leader会将所有Worker的结果再次进行聚集得到最终结果。
子查询支持
在并行查询下子查询有四种执行策略：
- 在Leader线程中串行执行
  当子查询不可并行执行时，例如在2个表JOIN，在JOIN条件上引用了用户的函数，此时子查询会在Leader线程上进行串行查询。
- 在Leader上并行执行（Leader会启动另一组Worker）
  生成并行计划后，在Leader上执行的计划包含有支持并行执行的子查询，但这些子查询不能提前并行执行（即不能采用Shared access)。例如，当前如果子查询中包括window function，子查询就不能采用Shared access策略。
- Shared access
  生成并行计划后，Worker的执行计划引用了可并行执行的子查询，PolarDB优化器会选择先提前并行执行这些子查询，让Worker可以直接访问这些子查询的结果。
- Pushed down
  生成并行计划后，Worker执行计划引用了相关子查询，这些子查询会被整体推送到Worker上执行。

并行执行计划示例

以下示例中使用pq_test表进行并行查询测试。

表结构如下：

mysql> SHOW CREATE TABLE pq_test\G
*************************** 1. row ***************************
       Table: pq_test
Create Table: CREATE TABLE `pq_test` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `help_topic_id` INT(10) UNSIGNED NOT NULL,
  `name` CHAR(64) NOT NULL,
  `help_category_id` SMALLINT(5) UNSIGNED NOT NULL,
  `description` TEXT NOT NULL,
  `example` TEXT NOT NULL,
  `url` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21495809 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

表大小如下：

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: pq_test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 20064988
 Avg_row_length: 1898
    Data_length: 38085328896
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 21495809
    Create_time: 2019-07-30 01:35:27
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

使用如下查询SQL：

SELECT COUNT(*) FROM pq_test;

通过EXPLAIN语句查看不使用并行查询的情况，查询语句如下：

SET max_parallel_degree=0; EXPLAIN SELECT COUNT(*) FROM pq_test\G

查询结果如下：

*************************** 1. row ***************************
           Id: 1
  Select_type: SIMPLE
        Table: pq_test
  Partitions: NULL
         Type: index
Possible_keys: NULL
          Key: PRIMARY
      Key_len: 8
          Ref: NULL
         Rows: 20064988
     Filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.03 sec)

通过EXPLAIN语句查看使用并行查询的情况，查询语句如下：

EXPLAIN SELECT COUNT(*) FROM pq_test\G

查询结果如下：

*************************** 1. row ***************************
           Id: 1
  Select_type: SIMPLE
        Table: &lt;gather2&gt;
   Partitions: NULL
         Type: ALL
Possible_keys: NULL
          Key: NULL
      Key_len: NULL
          Ref: NULL
         Rows: 20064988
     Filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           Id: 2
  Select_type: SIMPLE
        Table: pq_test
   Partitions: NULL
         Type: index
Possible_keys: NULL
          Key: PRIMARY
      Key_len: 8
          Ref: NULL
         Rows: 10032494
     Filtered: 100.00
        Extra: Parallel scan (2 workers); Using index
2 rows in set, 1 warning (0.00 sec)

说明

上述并行计划中包含了Gather操作，该操作负责汇总所有Worker返回的中间结果。
从执行计划输出的Extra信息中可以看到pq_test表使用了Parallel scan（并行扫描）策略，期望用2个Workers来并行执行。

通过带有子查询的EXPLAIN语句查看使用并行查询的情况，查询语句如下：

EXPLAIN SELECT a, (select sum(t2.b) from t2 where t2.a = t1.b) FROM t1 WHERE (a, b) IN (SELECT b, MAX(a) FROM t2 GROUP BY b)\G

查询结果如下：

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <gather1>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Parallel scan (1 workers); Using where
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Parallel pushdown; Using where
*************************** 4. row ***************************
           id: 3
  select_type: SUBQUERY
        table: <gather3>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Shared access; Using temporary
*************************** 5. row ***************************
           id: 3
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Parallel scan (1 workers); Using temporary
5 rows in set, 2 warnings (0.02 sec)

从上述结果可以看出：

select_type为SBUQUERY的子查询中，Extra显示Parallel pushdown，表示该子查询使用了Parallel pushdown策略，即整个子查询被整个下推到Worker去执行。
select_type为DEPENDENT SUBQUERY的子查询中，Extra显示Shared access，表示该子查询使用了Shared access策略，即PolarDB优化器选择提前并行执行该子查询并将执行结果Share给所有Worker。

前提条件

应用场景

并行查询的使用方法

相关参数和变量

性能指标

并行执行计划

并行执行计划示例