本文介绍如何使用EXPLAIN语句查看执行计划输出中与并行查询相关的内容。

查询用表

本文示例中使用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查询语句

  • 通过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: <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)
    从上述结果可以看出:
    • 上述并行计划中包含了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_typeSBUQUERY的子查询中,Extra显示Parallel pushdown,表示该子查询使用了Parallel pushdown策略,即整个子查询被整个下推到Worker去执行。
    • select_typeDEPENDENT SUBQUERY的子查询中,Extra显示Shared access,表示该子查询使用了Shared access策略,即PolarDB优化器选择提前并行执行该子查询并将执行结果Share给所有Worker。