LoginSignup
3

More than 3 years have passed since last update.

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

Last updated at Posted at 2019-12-02

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

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

昨日は meijik さんでした。

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

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

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

パラレルクエリとは

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

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

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

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

試してみた

インスタンスとして、(テスト用を除いて最小の)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)

テスト1: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 BYORDER BYLIMIT

続いて、COUNT(*)SUM()GROUP BYORDER BYLIMITを組み合わせてみます。

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

パラレル無効
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 UPDATESELECT... FOR SHARE
  • フルテキストインデックスでの検索
  • ストアドプロシージャや UDF
  • 再帰 CTE やウィンドウ関数
  • GIS / XML 関数
  • GROUP BY WITH ROLLUP
  • ロック関数
  • B+ ツリー以外のインデックスでの検索やインデックスマージ
  • トランザクション分離レベルSERIALIZABLEでの SQL 実行
  • CPU コアの 4 倍以上のスレッドを使うパラレルクエリ

次回は…

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


MySQL Advent Calendar 2019 明日(4 日目)は tmtms さんです。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3