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

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

というわけで、この記事は **[MySQL Advent Calendar 2019](https://qiita.com/advent-calendar/2019/mysql)** 3 日目のエントリとなります。

昨日は meijik さんでした。

 - **[MySQLクライアントでの非同期処理概略](http://blog.kimuradb.com/?eid=877580)（キムラデービーブログ）**

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

 - **[Alibaba Cloud の POLARDB を試してみる（１）MySQL 8.0 互換版起動編](https://qiita.com/hmatsu47/items/661f9b7d7d0ed376e464)**
 - **[Alibaba Cloud の POLARDB を試してみる（２）MySQL 8.0 互換版のバッファプールまわりを中心に](https://qiita.com/hmatsu47/items/da024a9de6236d761cdc)**

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

 - **[
Alibaba Cloud の POLARDB を試してみる（４）PostgreSQL 11 互換版起動＋パラレルスキャン編](https://qiita.com/hmatsu47/items/ec485766341c79bdce99)**

今回は、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」検証その１](https://atsuizo.hatenadiary.jp/entry/2019/01/23/112608)（なからなLife／atsuizo さん）**

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

 - **[Percona Live 2019 in Texas Austin 現地レポ(Session Day 2) – Keynote](https://www.s-style.co.jp/blog/2019/06/4421/)（スマートスタイル TECH BLOG）**

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

 - **[Parallel query](https://www.alibabacloud.com/help/doc-detail/128615.htm)（POLARDB MySQL Database）**
 - **[Examples of parallel query](https://www.alibabacloud.com/help/doc-detail/137453.htm)（POLARDB MySQL Database）**
 - **[Restrictions on parallel query](https://www.alibabacloud.com/help/doc-detail/138803.htm)（POLARDB MySQL Database）**

# 試してみた

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

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

## データ準備

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

```sql:テーブル定義
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)
```

```sql:データ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;
```

```sql: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()`

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

```sql:パラレル無効
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 並列のパラレルクエリを試してみます。

```sql: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 並列。

```sql: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`を組み合わせてみます。

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

```sql:パラレル無効
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 並列だけ試してみます。

```sql: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 つ目](https://www.alibabacloud.com/help/doc-detail/138803.htm) のページに書かれていますが、以下のようなケースではパラレルクエリが適用されないか、並列処理の効果が発揮されません。

 - テーブル内の行数が 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](https://qiita.com/advent-calendar/2019/mysql)** 明日（4 日目）は tmtms さんです。

 - **[MySQL Parameters を拡張した](https://tmtms.hatenablog.com/entry/201912/mysql-params)（＠tmtms のメモ）**