こんにちは、ECナビ事業本部の佐々木です。

今回は、MySQLのパーティション周りについて書きたいと思います。
ただ「いまさらパーティションの話？」と言われてしまうとそれまでなのですが、それぞれにタイプによりどう違うがあるのか曖昧な部分もあり、改めて調べてみようかと思った次第です。

では、調べていくに当たり、まずは MySQLのパーティショニングタイプについて、軽くふれておきます。大まかな種類としては、以下。

HASH
- カラム値またはカラム値に対してユーザ定義式から整数結果を算出し、パーティションを選択する。
KEY
- 指定カラム/カラムセットに対してMySQLで提供される関数を使って整数結果を算出し、パーティションを選択する
RANGE
- 範囲によってパーティションの選択をする。例えば、～2015年, ～2016年, ～2017年 ... といった年数で分ける場合など。
LIST
- 連続しない値によるパーティションの選択をする。例えば、'Tokyo', 'Osaka', 'Kyoto' ... といった都道府県で分ける場合など。

より詳しくは MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.2 パーティショニングタイプを参照してもらう事にし、今回はこの中の（似ている） HASH/KEYパーティショニングにフォーカスをあて、違いがあるのか検証していきたいと思います。

ちなみに HASH/KEYパーティショニングをフォーカスした理由は、調べていたら LINEAR HASH/KEY というタイプがあり、単に興味をもった程度とお考えください。

検証方法

検証方法としては、下のようなテーブルに対して user_id をパーティションの判定カラムとして利用し、「INSERT処理」, 「SELECT処理」, 「パーティションの追加/削除」を調べてみます。

対象テーブル

+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| user_id    | int(11)  | NO   | MUL | NULL    |       |
| action_id  | int(11)  | NO   |     | NULL    |       |
| created_at | datetime | NO   |     | NULL    |       |
| updated_at | datetime | NO   |     | NULL    |       |
+------------+----------+------+-----+---------+-------+

検証内容
- INSERT処理
  - 総登録レコード数 10,000,000 に対し、登録レコード 100,000 レコード単位に 1レコードのINSERT処理時間を計測（実際は100回ほど実行した平均）。
  - user_id については、 1~100,000 からランダム値を算出する。
- SELECT処理
  - 総登録レコード数 10,000,000 に対し、登録レコード 100,000 レコード単位に 1クエリのSELECT処理時間を計測（実際は100回ほど実行した平均）。
  - クエリとしては、user_id はランダムで SELECT COUNT(*) FROM sample_table WHERE user_id = ? を実行
- パーティションの追加/削除
検証環境
- Amazon RDS
  - MySQL 5.6.22
  - db.t2.micro

なお、検証で利用したテーブルには、PRIMARY KEY を付けてないのですが、理由としてはMySQLの制約である下記を意識しないでシンプルに考えたかったからです。（業務上テーブル作成する場合、PRIMARY KEY を設定していることが大多数でしょうが...)

テーブルのすべての一意キーは、テーブルのパーティショニング式内のすべてのカラムを使用する必要があります

MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.6.1 パーティショニングキー、主キー、および一意キー

HASH, LINEAR HASH の比較

HASHパーティショニングは、「int型カラム」や「整数値を返す定義式」を使ってどのパーティションに格納されるかを決めますが、今回は単純にするために「int型カラム」を使って検証してみます。

テーブルスキーマ

-- PARTITION BY HASH
CREATE TABLE `sample_table` (
  `user_id` int(11) NOT NULL,
  `action_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (user_id)
PARTITIONS 4;

-- PARTITION BY LINEAR HASH
CREATE TABLE `sample_table` (
  `user_id` int(11) NOT NULL,
  `action_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LINEAR HASH (user_id)
PARTITIONS 4;

INSERT処理
SELECT処理 (※y軸は、0始まりではありません)
パーティションの追加/削除

-- PARTITION BY HASH
mysql> ALTER TABLE sample_table ADD PARTITION PARTITIONS 2;
Query OK, 10000000 rows affected (2 min 12.09 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sample_table COALESCE PARTITION 2;
Query OK, 10000000 rows affected (1 min 50.51 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

-- PARTITION BY LINEAR HASH
mysql> ALTER TABLE sample_table ADD PARTITION PARTITIONS 2;
Query OK, 4999451 rows affected (1 min 1.93 sec)
Records: 4999451  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sample_table COALESCE PARTITION 2;
Query OK, 4999451 rows affected (59.21 sec)
Records: 4999451  Duplicates: 0  Warnings: 0

「INSERT/SELECT処理」については、ほぼ違いがありませんでしたが、「パーティションの追加/削除」において LINEAR HASH は全レコードに影響されるわけではなく、一部のレコードのみ影響があるようです。

ただ、どのレコードに影響するかまでは調べきれていませんが、恐らくこの辺が絡んでいるのではないかと考えています。

線形ハッシュによるパーティショニングの利点は、パーティションの追加、削除、マージ、および分割の速度が向上することです。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.2.4.1 LINEAR HASH パーティショニング

KEY, LINEAR KEY の比較

KEYパーティショニングは、どのパーティションに格納するかはMySQLの内部ハッシュ関数を利用するため、int型カラム以外に varchar型カラムを利用できるようです。

ただ、HASHパーティショニングとも比較できるように、今回はint型カラムを使って検証してみます。

テーブルスキーマ

-- PARTITION BY KEY
CREATE TABLE `sample_table` (
  `user_id` int(11) NOT NULL,
  `action_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (user_id)
PARTITIONS 4;

-- PARTITION BY LINEAR KEY
CREATE TABLE `sample_table` (
  `user_id` int(11) NOT NULL,
  `action_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LINEAR KEY (user_id)
PARTITIONS 4;

INSERT処理
SELECT処理 (※y軸は、0始まりではありません)
パーティションの追加/削除

-- PARTITION BY KEY
mysql> alter table sample_table add PARTITION PARTITIONS 2;
Query OK, 10000000 rows affected (2 min 9.24 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> alter table sample_table COALESCE PARTITION 2;
Query OK, 10000000 rows affected (1 min 52.38 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

-- PARTITION BY LINEAR KEY
mysql> alter table sample_table add PARTITION PARTITIONS 2;
Query OK, 3952544 rows affected (52.77 sec)
Records: 3952544  Duplicates: 0  Warnings: 0

mysql> alter table sample_table COALESCE PARTITION 2;
Query OK, 3952544 rows affected (46.42 sec)
Records: 3952544  Duplicates: 0  Warnings: 0

ほぼ、HASHパーティショニングと同じ結果。

(LINEAR) HASH, KEY の比較

「INSERT/SELECT処理」（上記を参照）については大きな違いは見れませんでしたが、LINEAR HASH/LINEAR KEY の「パーティションの追加・削除」結果を見ると影響されている件数に違いがあるようです。ということで、各パーティションの件数を調べてみました。

HASHパーティショニング

mysql> SELECT partition_method, partition_name, table_rows
FROM information_schema.partitions WHERE table_name = 'sample_table';
+------------------+----------------+------------+
| partition_method | partition_name | table_rows |
+------------------+----------------+------------+
| HASH             | p0             |    2490789 |
| HASH             | p1             |    2348067 |
| HASH             | p2             |    2350906 |
| HASH             | p3             |    2445973 |
+------------------+----------------+------------+
4 rows in set (0.00 sec)

KEYパーティショニング

mysql> SELECT partition_method, partition_name, table_rows
FROM information_schema.partitions WHERE table_name = 'sample_table';
+------------------+----------------+------------+
| partition_method | partition_name | table_rows |
+------------------+----------------+------------+
| KEY              | p0             |     802179 |
| KEY              | p1             |    3141243 |
| KEY              | p2             |    4227594 |
| KEY              | p3             |    1802493 |
+------------------+----------------+------------+
4 rows in set (0.00 sec)