This topic describes how to execute the EXPLAIN statement to view information about parallel queries in execution plans.

The table used in the test

In the following examples, the pq_test table is used to test parallel queries.

  • Schema:
    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)
  • Table size:
    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 statement:
    SELECT COUNT(*) FROM pq_test;

EXPLAIN statements

  • To view the result when the parallel query is not used, execute the following EXPLAIN statement:
    SET max_parallel_degree=0; EXPLAIN SELECT COUNT(*) FROM pq_test\G

    The following result is returned:

    *************************** 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)
  • To view the result when the parallel query is used, execute the following EXPLAIN statement:
    EXPLAIN SELECT COUNT(*) FROM pq_test\G

    The following result is returned:

    *************************** 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)
    The result indicates the following information:
    • The EXPLAIN output shows that the parallel plan includes a Gather operation. Gather is implemented to gather the partial results that are produced by all workers.
    • In addition, information in the Extra field shows that a parallel scan is performed on the pq_test table. This plan includes a simultaneous scan. In this case, you can perform a simultaneous scan by using two workers.
  • The EXPLAIN statement returns the following result when the parallel query is used for a statement that includes subqueries:
    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

    The following result is returned:

    *************************** 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)
    The result indicates the following information:
    • For the subquery whose select_type is SBUQUERY, the Extra field displays the Parallel pushdown policy. In the Parallel pushdown policy, the subquery is sent to workers for parallel processing.
    • For the subquery whose select_type is DEPENDENT SUBQUERY, the Extra field displays the Shared access policy. In the Shared access policy, the PolarDB optimizer runs the parallel subquery in advance, and the result is shared among workers.