PolarDB MySQL 8.0重磅推出并行查询框架,当您的查询数据量到达一定阈值,就会自动启动并行查询框架,从而使查询耗时指数级下降。

在存储层将数据分片到不同的线程上,多个线程并行计算,将结果流水线汇总到总线程,最后总线程做些简单归并返回给用户,提高查询效率。

并行查询(Parallel Query)利用多核CPU的并行处理能力,以8核 32G配置为例,示意图如下所示。

示意图

应用场景

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

  • 轻分析类业务

    报表查询通常SQL复杂而且比较耗费时间,通过并行查询可以加速单次查询效率。

  • 系统资源相对空闲

    并行查询会使用更多的系统资源,只有当系统的CPU较多、IO负载不高、内存够大的时候,才可以充分使用并行查询来提高资源利用率和查询效率。

  • 在离线混合场景在离线混合场景

    不同的业务用不同的连接地址,使用不同的数据库节点,避免互相影响。您可以在只读地址(AP地址)上开启并行查询。

并行查询的使用方法

  • 通过系统参数来控制并行查询

    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配置文件的约定俗成, 阿里云在控制台上增加了前缀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; 
    -- 设置force_parallel_mode为true(表示当表记录数小于20000行时,依旧会强制并行),
    -- 并行度用系统默认max_parallel_degree, 如果max_parallel_degree > 0, 
    -- 则打开并行,如果max_parallel_degree等于0时,依旧时关闭并行
    
    SELECT /*+PARALLEL(8)*/ * FROM t1, t2; 
    -- 强制并行度8并行执行, 
    -- 并设置force_parallel_mode为true(表示当表记录数小于20000行时,依旧会强制并行),
    -- 并行度设置max_parallel_degree为8
    
    SELECT /*+ SET_VAR(max_parallel_degree=8) */  *  FROM ...   
    -- 设置并行度max_parallel_degree为8, 
    -- 但force_parallel_mode为false(表示当表记录数小于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优化器可能不选择并行执行查询,如果希望优化器忽略代价,尽可能选择并行计划,可以设置如下参数:
    set force_parallel_mode = on
    说明 这是一个调试参数,不建议在生产环境中使用。由于并行查询使用场景的限制,有些情况下即便设置了该参数,优化器也可能不选择并行。

相关参数和变量

表 1. 系统参数
参数名 级别 描述
max_parallel_degree Global、Session

单个查询的最大并行度,即最多使用多少个worker进行并行执行。

  • 取值范围:[0-1024]
  • 默认值:0,表示关闭并行计算。
说明 PolarDB优化器可能会对主查询和子查询分别并行执行,如果同时并行执行,它们的最大worker数不能超过max_parallel_degree,整个查询使用的worker数将会是主查询和子查询使用的worker数之和。
force_parallel_mode Session

强制PolarDB优化器忽略代价,尽可能的使用并行查询。

  • 取值范围:[ON, OFF]
  • 默认值:OFF
注意 这是个调试参数,打开后,PolarDB优化器会尽可能的选择并行查询,但不能保证一定使用并行查询。
表 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生成100G数据来测试PolarDB MySQL 8.0集群的性能指标。测试用的PolarDB集群规格为32核256GB,并行度max_parallel_degree分别设置为32和0,具体测试步骤请参见并行查询性能(OLAP)

性能指标

通过以上测试结果图得出结论,TPC-H中95%的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条件上引用了用户的function,此时子查询会在leader线程上进行串行查询。

    • 在Leader上并行执行(Leader会启动另一组worker)

      生成并行计划后,在Leader上执行的计划包含有支持并行执行的子查询,但这些子查询不能提前并行执行(即不能采用Shared access)。例如,当前如果子查询中包括window function就采用这样执行策略。

    • 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输出如下:
    mysql> SET max_parallel_degree=0; EXPLAIN SELECT COUNT(*) FROM pq_test\G
    Query OK, 0 rows affected (0.02 sec)
    *************************** 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输出如下:
    mysql> EXPLAIN SELECT COUNT(*) FROM pq_test\G
    *************************** 1. row ***************************
               Id: 1
      Select_type: SIMPLE
            Table: <gather2>
       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)

    从该EXPLAIN可以看到,并行计划中包含Gather操作,该操作负责汇总所有Worker返回的中间结果。另外,从执行计划输出的Extra信息中看到:pq_test表使用了Parallel scan(并行扫描),期望用2个Workers来并行执行。

  • 带子查询的语句使用并行查询的EXPLAIN输出如下:
    mysql> 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的2个子查询,Extra分别标识了Parallel pushdown和Shared access,分别表示2个子查询的并行执行策略,一个是整个下推到Worker去执行,一个是提前并行执行并Share执行结果给所有worker。