先日、第 17 回 PostgreSQL アンカンファレンス@オンライン の中で、うっかり

「MySQL ではパーティショニングで速くなるケースが思ったよりも少ないんですよね」

的なことを口走ったところ、

ポスグレよりはマシだと思ってました（笑）
— 明治㌠から㍽㍼㍻経て令和 (@meijik) September 24, 2020

プロの方に見つかってしまいました（笑…いや怖い）。

実際のところ、自分で使っていても（DELETEよりパーティションのDROPが速いとか）限られたケース以外で高速化した実感がないのですが、~~恐る恐る~~あらためて実験してみました。

【注】 今回は更新性能については触れません（特定の条件でパーティショニングを使うと更新処理が速くなることは確認済みです）。

実験内容

MySQL 8.0.21 on Windows を使用
RANGEパーティショニングで、区分キー（パーティションキー）としてDATETIME列を使って以下の範囲に分割
- 2014 年以前
- 2015 年
- 2016 年
- 2017 年
- 2018 年
- 2019 年
- 2020 年以降
比較用に非パーティショニングテーブルも用意し、DATETIME列のインデックスを作成
以下の 2 つの条件で絞り込み
- DATETIME列のみを条件に絞り込み（範囲検索）
- DATETIME列＋非インデックス列（長いVARCHAR文字列）を条件に絞り込み（同上）

テーブル定義

※パーティショニング
mysql> CREATE DATABASE partition_test;
Query OK, 1 row affected (0.02 sec)

mysql> USE partition_test;
Database changed
mysql> CREATE TABLE log_record (
    ->   log_id INT NOT NULL AUTO_INCREMENT,
    ->   log_datetime DATETIME NOT NULL,
    ->   log_text VARCHAR(500) NOT NULL,
    ->   PRIMARY KEY (log_id, log_datetime)
    -> )
    -> PARTITION BY RANGE (YEAR(log_datetime)) (
    ->   PARTITION pl VALUES LESS THAN (2015),
    ->   PARTITION p2015 VALUES LESS THAN (2016),
    ->   PARTITION p2016 VALUES LESS THAN (2017),
    ->   PARTITION p2017 VALUES LESS THAN (2018),
    ->   PARTITION p2018 VALUES LESS THAN (2019),
    ->   PARTITION p2019 VALUES LESS THAN (2020),
    ->   PARTITION pu VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.58 sec)

※非パーティショニング
mysql> CREATE DATABASE no_partition_test;
Query OK, 1 row affected (0.02 sec)

mysql> USE no_partition_test;
Database changed
mysql> CREATE TABLE log_record (
    ->   log_id INT NOT NULL AUTO_INCREMENT,
    ->   log_datetime DATETIME NOT NULL,
    ->   log_text VARCHAR(500) NOT NULL,
    ->   PRIMARY KEY (log_id),
    ->   KEY (log_datetime)
    -> );
Query OK, 0 rows affected (0.12 sec)

※これらのテーブルに 655,360 行のデータを突っ込んで試しました（なお、VARCHAR列には各行 480 文字入っています）。

非パーティショニングテーブルで絞り込み

mysql> USE no_partition_test;
Database changed

※DATETIME 列のみを条件に絞り込み
mysql> SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999';
+----------+
| COUNT(*) |
+----------+
|    15706 |
+----------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log_record
   partitions: NULL
         type: range
possible_keys: log_datetime
          key: log_datetime
      key_len: 5
          ref: NULL
         rows: 31666
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

※DATETIME 列＋非インデックス列（長い VARCHAR ）を条件に絞り込み
mysql> SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a';
+----------+
| COUNT(*) |
+----------+
|     4756 |
+----------+
1 row in set (2.95 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log_record
   partitions: NULL
         type: range
possible_keys: log_datetime
          key: log_datetime
      key_len: 5
          ref: NULL
         rows: 31666
     filtered: 33.33
        Extra: Using index condition; Using where; Using MRR
1 row in set, 1 warning (0.00 sec)

こんな感じで、いずれのケースでもインデックスを使ってレンジスキャンしています（後者では Index Condition Pushdown と Multi-Range Read が使われている）。

パーティショニングテーブルで絞り込み

mysql> USE partition_test;
Database changed

※DATETIME 列のみを条件に絞り込み
mysql> SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999';
+----------+
| COUNT(*) |
+----------+
|    15706 |
+----------+
1 row in set (0.16 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log_record
   partitions: p2019
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 9
          ref: NULL
         rows: 165079
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

※DATETIME 列＋非インデックス列（長い VARCHAR ）を条件に絞り込み
mysql> SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a';
+----------+
| COUNT(*) |
+----------+
|     4756 |
+----------+
1 row in set (0.15 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log_record
   partitions: p2019
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 165079
     filtered: 3.70
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

EXPLAINの結果に違いはあるものの、どちらも「2019 年のパーティション内をフルスキャン」しているような状態になっており、結果として非インデックス列（長いVARCHAR）が絞り込み条件に付加されている場合は非パーティショニングテーブルと比べて大幅に高速化しているものの、区分キー（DATETIME列）のみを絞り込み条件とした場合は、かえって遅くなってしまっています。

なお、PostgreSQL 12 にて区分キー（TIMESTAMP列）のみを絞り込み条件として類似の実験をしてみたときには、大幅ではないものの 1.5 倍程度高速になりました。

パーティショニングテーブルにインデックスを加えてみる

遅くなった原因が「実行計画がパーティション内の（主キーの）フルスキャンに変わったこと」であるのなら、区分キーの列をインデックスとして追加すれば良いのでは？ということで…。

インデックス追加

mysql> USE partition_test;
Database changed
mysql> ALTER TABLE log_record ADD KEY (log_datetime);
Query OK, 0 rows affected (4.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

※DATETIME 列のみを条件に絞り込み
mysql> SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999';
+----------+
| COUNT(*) |
+----------+
|    15706 |
+----------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log_record
   partitions: p2019
         type: range
possible_keys: PRIMARY,log_datetime
          key: log_datetime
      key_len: 5
          ref: NULL
         rows: 29182
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

※DATETIME 列＋非インデックス列（長い VARCHAR ）を条件に絞り込み

mysql> SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a';
+----------+
| COUNT(*) |
+----------+
|     4756 |
+----------+
1 row in set (0.16 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM log_record WHERE log_datetime BETWEEN '2019-04-01 00:00:00' AND '2019-05-31 23:59:59.999' AND log_text > 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: log_record
   partitions: p2019
         type: ALL
possible_keys: log_datetime
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 165079
     filtered: 5.89
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

今度は区分キー（DATETIME列）のみを条件とした場合でもインデックスが使われ（レンジスキャン）、遅くなりませんでした。

MySQL でパーティショニングを使う例を見ていると、区分キーのインデックスを作成していないケースが割と多いのですが、絞り込み条件による速度低下を防ぐ目的でインデックスを作成しておいたほうが良さそうですね。

この結果を見ると、

インデックスを使って緩く絞り込んだ後に、インデックスを使わずに必要行を抽出（フィルタリング）する

よりも、

パーティショニングによって刈り込んだ後に、対象パーティション内をフルスキャンする

ほうが効率的なケースで、パーティショニングのほうが高速化することがわかります。

反対に、

インデックスのみで十分に絞り込める

ようなケースでは、パーティショニングの効果が出ない（薄い）ことが分かります。

（PostgreSQL 12 との比較では、PostgreSQL のほうは「インデックスで十分に絞り込める場合でもパーティショニングの効果はあるものの効果自体は控えめ」なので、プロの方の話は嘘ではありません。「効果が出る範囲」と「効果の程度」の違いの話です。）

なお、今回のケースでは非インデックス列（長いVARCHAR）を絞り込み条件として付加しましたが、通常は数値列または短い文字列で絞り込むケースが多いと思います。SELECTによる射影（取得）列次第ですが、パーティショニングを使うのではなく複合インデックスを作成しカバリングインデックス化することで（インデックスのみで十分に絞り込み＋必要列の抽出ができるようにして）対処できるケースもあるでしょう。

（わたしが普段使っているときのデータ量が少ないだけかもしれませんが、インデックスの木構造の階層が深くなることによるオーバーヘッドもそんなに感じないんですよね…特に参照のみに限れば。）

そうなるとやはり、パーティショニングを使って高速化できるケースは意外と少な…あ、プロの方に怒られる（笑）。

10/02 追記：
下のコメントのやり取りもついでに見ていただくと良いです。COUNT(*)ではなく列値を射影（取得）するケースでは結果が変わります。

10/04 追記：

続きのエントリを書きました。

MySQL のパーティショニングで速くなる？ならない？問題、実験の続き

MySQL のパーティショニングで速くなる？ならない？問題、あらためて実験してみた

実験内容

非パーティショニングテーブルで絞り込み

パーティショニングテーブルで絞り込み

パーティショニングテーブルにインデックスを加えてみる