Alibaba Cloud の POLARDB を試してみる（３）MySQL 8.0 互換版でパラレルクエリを試す

今年もアドベントカレンダーの季節がやってきました。

というわけで、この記事は MySQL Advent Calendar 2019 3 日目のエントリとなります。

昨日は meijik さんでした。

• MySQLクライアントでの非同期処理概略（キムラデービーブログ）

そして、以下の記事の続きでもあります。

• Alibaba Cloud の POLARDB を試してみる（１）MySQL 8.0 互換版起動編
• Alibaba Cloud の POLARDB を試してみる（２）MySQL 8.0 互換版のバッファプールまわりを中心に

※本日の同日エントリもあります（Qiita Advent Calendar の仕様変更に振り回された結果…）。

• 
  Alibaba Cloud の POLARDB を試してみる（４）PostgreSQL 11 互換版起動＋パラレルスキャン編

今回は、Alibaba Cloud の ApsaraDB for POLARDB MySQL 8.0 互換版の注目の機能、**パラレルクエリ（Parallel Query）**を軽く試してみます。

パラレルクエリとは

簡単にいってしまうと**「1 つの SQL の処理を内部的に分割して並列に行うもの」**です。

近年、PostgreSQL ではバージョンアップのたびにパラレルクエリの対象となる SQL が増えてきていますが、MySQL（8.0）では主キーに対するCOUNT(*)など非常に限られた SQL のみの対応にとどまっていました。

• MySQL 8.0.14でSELECT COUNT(*)が加速する！- 「innodb_parallel_read_threads」検証その１（なからなLife／atsuizo さん）

そんなこともあって、MySQLer おなじみの Percona Live 2019 でも 2 日目のキーノートで取り上げられたようです。

• Percona Live 2019 in Texas Austin 現地レポ(Session Day 2) – Keynote（スマートスタイル TECH BLOG）

Alibaba Cloud のドキュメントでは、以下のページで説明されています。

• Parallel query（POLARDB MySQL Database）
• Examples of parallel query（POLARDB MySQL Database）
• Restrictions on parallel query（POLARDB MySQL Database）

試してみた

インスタンスとして、（テスト用を除いて最小の）4 Core 16 GB（polar.mysql.x4.large）を選択して試しました。

結果として、小さなインスタンスでもそれなりに効果があることがわかりました。

データ準備

以下のような定義のテーブルに、前回同様 100 万行INSERTしました。

テーブル定義

mysql> CREATE DATABASE pqtest;
Query OK, 1 row affected (0.00 sec)

mysql> USE pqtest;
Database changed
mysql> CREATE TABLE pqtest1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, dummy_str1 VARCHAR(512) NOT NULL, val1 INT NOT NULL, val2 INT NOT NULL);
Query OK, 0 rows affected (0.80 sec)

データINSERT（部分）

SET AUTOCOMMIT=0;
INSERT INTO pqtest.pqtest1 SET dummy_str1 = REPEAT('a', 512), val1 = FLOOR(1 + (RAND() * 1000)), val2 = FLOOR(1 + (RAND() * 1000));
INSERT INTO pqtest.pqtest1 SET dummy_str1 = REPEAT('a', 512), val1 = FLOOR(1 + (RAND() * 1000)), val2 = FLOOR(1 + (RAND() * 1000));
INSERT INTO pqtest.pqtest1 SET dummy_str1 = REPEAT('a', 512), val1 = FLOOR(1 + (RAND() * 1000)), val2 = FLOOR(1 + (RAND() * 1000));
（中略）
INSERT INTO pqtest.pqtest1 SET dummy_str1 = REPEAT('a', 512), val1 = FLOOR(1 + (RAND() * 1000)), val2 = FLOOR(1 + (RAND() * 1000));
COMMIT;

INSERT結果（部分）

mysql> SELECT * FROM pqtest1 ORDER BY id LIMIT 10;
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------+
| id | dummy_str1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | val1 | val2 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------+
|  1 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  332 |  440 |
|  2 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  202 |  691 |
|  3 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  848 |  165 |
|  4 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  281 |  912 |
|  5 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  714 |  834 |
|  6 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |   26 |  629 |
|  7 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |   68 |  450 |
|  8 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |   48 |  888 |
|  9 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  294 |  807 |
| 10 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |  151 |  335 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------+
20 rows in set (0.00 sec)

テスト１：COUNT(*)・SUM()・AVG()

まずはパラレルクエリ無効で実行してみます。

パラレル無効

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (9.04 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (0.41 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 912368
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

1 回目がバッファプールに載っていない状態、2 回目が載っている状態です。

次は 2 並列のパラレルクエリを試してみます。

2並列

mysql> SET max_parallel_degree = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET force_parallel_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (5.25 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (0.21 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1\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: 912368
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 456184
     filtered: 100.00
        Extra: Parallel scan (2 workers)
2 rows in set, 1 warning (0.00 sec)

実行計画が変わり、2 倍近く高速になりました。

続いて 4 並列。

4並列

mysql> SET max_parallel_degree = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> SET force_parallel_mode = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (3.51 sec)

mysql> SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1;
+----------+-----------+-----------+
| COUNT(*) | SUM(val1) | AVG(val2) |
+----------+-----------+-----------+
|  1000000 | 500579322 |  500.0565 |
+----------+-----------+-----------+
1 row in set (0.22 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val1), AVG(val2) FROM pqtest1\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: 912368
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 228092
     filtered: 100.00
        Extra: Parallel scan (4 workers)
2 rows in set, 1 warning (0.00 sec)

バッファプールに載っていない状態ではさらに速くなりました。

GROUP BY・ORDER BY・LIMIT

続いて、COUNT(*)・SUM()にGROUP BY・ORDER BY・LIMITを組み合わせてみます。

まずはパラレルクエリ無効で。

パラレル無効

mysql> SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20;
+----------+-----------+
| COUNT(*) | SUM(val2) |
+----------+-----------+
|     1019 |    515266 |
|      979 |    492835 |
|     1026 |    503475 |
|      969 |    471603 |
|      989 |    494306 |
|      989 |    490951 |
|      996 |    507320 |
|      954 |    465757 |
|      983 |    485429 |
|     1017 |    509723 |
|     1045 |    515420 |
|      983 |    496242 |
|     1002 |    499199 |
|     1004 |    521288 |
|     1006 |    503223 |
|     1015 |    517678 |
|     1058 |    523834 |
|     1032 |    498823 |
|      932 |    473861 |
|     1014 |    507929 |
+----------+-----------+
20 rows in set (10.15 sec)

mysql> SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20;
+----------+-----------+
| COUNT(*) | SUM(val2) |
+----------+-----------+
|     1019 |    515266 |
|      979 |    492835 |
|     1026 |    503475 |
|      969 |    471603 |
|      989 |    494306 |
|      989 |    490951 |
|      996 |    507320 |
|      954 |    465757 |
|      983 |    485429 |
|     1017 |    509723 |
|     1045 |    515420 |
|      983 |    496242 |
|     1002 |    499199 |
|     1004 |    521288 |
|     1006 |    503223 |
|     1015 |    517678 |
|     1058 |    523834 |
|     1032 |    498823 |
|      932 |    473861 |
|     1014 |    507929 |
+----------+-----------+
20 rows in set (0.83 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 912368
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

今度はパラレルクエリを 4 並列だけ試してみます。

4並列

mysql> SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20;
+----------+-----------+
| COUNT(*) | SUM(val2) |
+----------+-----------+
|     1019 |    515266 |
|      979 |    492835 |
|     1026 |    503475 |
|      969 |    471603 |
|      989 |    494306 |
|      989 |    490951 |
|      996 |    507320 |
|      954 |    465757 |
|      983 |    485429 |
|     1017 |    509723 |
|     1045 |    515420 |
|      983 |    496242 |
|     1002 |    499199 |
|     1004 |    521288 |
|     1006 |    503223 |
|     1015 |    517678 |
|     1058 |    523834 |
|     1032 |    498823 |
|      932 |    473861 |
|     1014 |    507929 |
+----------+-----------+
20 rows in set (3.34 sec)

mysql> SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20;
+----------+-----------+
| COUNT(*) | SUM(val2) |
+----------+-----------+
|     1019 |    515266 |
|      979 |    492835 |
|     1026 |    503475 |
|      969 |    471603 |
|      989 |    494306 |
|      989 |    490951 |
|      996 |    507320 |
|      954 |    465757 |
|      983 |    485429 |
|     1017 |    509723 |
|     1045 |    515420 |
|      983 |    496242 |
|     1002 |    499199 |
|     1004 |    521288 |
|     1006 |    503223 |
|     1015 |    517678 |
|     1058 |    523834 |
|     1032 |    498823 |
|      932 |    473861 |
|     1014 |    507929 |
+----------+-----------+
20 rows in set (0.39 sec)

mysql> EXPLAIN SELECT COUNT(*), SUM(val2) FROM pqtest1 GROUP BY val1 ORDER BY val1 LIMIT 20\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: 912368
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: SIMPLE
        table: pqtest1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 228092
     filtered: 100.00
        Extra: Parallel scan (4 workers); Using temporary; Using filesort
2 rows in set, 1 warning (0.00 sec)

先の例と同じような結果になりました。

注意点

先に記した Alibaba Cloud のドキュメント 3 つ目 のページに書かれていますが、以下のようなケースではパラレルクエリが適用されないか、並列処理の効果が発揮されません。

• テーブル内の行数が 20,000 未満
• システムテーブルや一時テーブル
• SELECT... FOR UPDATE・SELECT... FOR SHARE
• フルテキストインデックスでの検索
• ストアドプロシージャや UDF
• 再帰 CTE やウィンドウ関数
• GIS / XML 関数
• GROUP BY WITH ROLLUP
• ロック関数
• B+ ツリー以外のインデックスでの検索やインデックスマージ
• トランザクション分離レベルSERIALIZABLEでの SQL 実行
• CPU コアの 4 倍以上のスレッドを使うパラレルクエリ

次回は…

LIKE検索など別ケースのパラレルクエリを試す予定です。

---

MySQL Advent Calendar 2019 明日（4 日目）は tmtms さんです。

• MySQL Parameters を拡張した（＠tmtms のメモ）