この記事は個人ブログと同じ内容です
www.ritolab.com
パーティショニングは MySQL 5.1 から利用できますが、パーティショニングとは何者なのか。MySQL 8.0 でパーティショニングを理解していきます。
パーティショニング
パーティショニングでは、指定したルールに従いテーブルの各行をパーティション分割したファイルシステムに配分します。
これによって同じテーブルのレコードでもそれぞれ配分された箇所が内部的にはあたかも別のテーブルのように個別の場所に格納されます。
テーブルは 1 つで操作もこれまでと変わりませんが、ファイルシステムにおける内部的なデータの持ち方としてはパーティション分割されたそれぞれの場所に保存されていきます。
こうすることでパーティショニングすべき状況と合致する場合はデータの読み出しをはじめとした操作を効率的に行うことができ、クエリ実行時のパフォーマンスを向上させることができます。
dev.mysql.com
環境
MySQL 8.0 にて操作を行います。ストレージエンジンは InnoDB です。
MySQL 8.0 のパーティショニング方式
MySQL 8.0 でのパーティショニングは「水平パーティショニング」という方式が採用されています。これは、テーブルの各レコードを異なる物理パーティションに割り当てる方式です。
一方で、「垂直パーティショニング」と呼ばれる、カラムベースで異なる物理パーティションに割り当てる方式がありますが、これについては MySQL 8.0 ではサポートされていません。
対応ストレージエンジン
MySQL 8.0 でのパーティショニングついて対応しているストレージエンジンは InnoDB と NDB のみです。その他（MyISAM など）のストレージエンジンではパーティショニングを利用できません。
テーブルのパーティション分割を行う際に、パーティションキーとして指定するすべてのカラムは、主キーを含めテーブルに含まれる可能性のあるすべての一意キーの一部である必要があります。
つまり、パーティション分割を行う際に指定するカラムは、そのテーブルにプライマリーキー、もしくはユニークキーを設定するのであればそれを構成するカラムである必要があります。（逆にこれらのキーを設定しない場合はその制約を受けません）
パーティショニングタイプ
MySQL 8.0 で利用可能なパーティショニングのタイプは、大きく分けて 4 つのタイプ +α があります。
RANGE パーティショニング
パーティショニング時に指定したカラムの範囲に含まれる値（整数）に基づいて行をパーティションに割り当てます。
- ある数値を収録しているカラムの数値範囲（〜1000000、〜2000000, ... ）
- ある日付を収録しているカラムの年範囲（〜2022, 〜2023, ...）
CREATE TABLE `logs` (
`logged_at` datetime NOT NULL,
.,
.,
.
)
PARTITION BY RANGE (year(`logged_at`)) (
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
RANGE COLUMNS パーティショニング
DATE 型・DATETIME 型のカラムをパーティショニングキーとしてパーティション分割したい場合は RANGE COLUMNS パーティショニングを用います。
CREATE TABLE `employees` (
`hired` date NOT NULL,
.,
.,
.,
)
PARTITION BY RANGE COLUMNS(hired) (
PARTITION p202201 VALUES LESS THAN ('2022-02-01'),
PARTITION p202202 VALUES LESS THAN ('2022-03-01'),
PARTITION p202203 VALUES LESS THAN ('2022-04-01'),
PARTITION p202204 VALUES LESS THAN ('2022-05-01'),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
LIST パーティショニング
パーティショニング時に指定したリストに基づいて行をパーティションに割り当てます。
- あるグループを識別するための番号リストによるパーティション（A=3,5,6,9,17, B=1,2,10,11,19,20, ...）
CREATE TABLE `members` (
`name` varchar(255) NOT NULL,
`country_id` smallint unsigned NOT NULL
)
PARTITION BY LIST (`country_id`) (
PARTITION pAfricaZone VALUES IN (247,213,244,256,20,251,291,233),
PARTITION pAmericaZone VALUES IN (1,53,54,57,297,503,598,593),
PARTITION pAsiaZone VALUES IN (66,81,94,357,850,886,963),
PARTITION pEuropeZone VALUES IN (7,39,44,353,355,372,380,998),
PARTITION pOceaniaZone VALUES IN (61,64,672,677,683,685,687,690)
);
LIST COLUMNS パーティショニング
文字列や DATE 型などのリストでパーティション分割したい場合は LIST COLUMNS パーティショニングを用います。
CREATE TABLE `residents` (
`name` varchar(255) NOT NULL,
`prefecture` varchar(255) NOT NULL
)
PARTITION BY LIST COLUMNS(prefecture) (
PARTITION pHokkaidoRegion VALUES IN ('北海道'),
PARTITION pTohokuRegion VALUES IN ('青森県','岩手県','宮城県','秋田県','山形県','福島県'),
PARTITION pkantoRegion VALUES IN ('東京都','茨城県','栃木県','群馬県','埼玉県','千葉県','神奈川県'),
PARTITION pChubuRegion VALUES IN ('新潟県','富山県','石川県','福井県','山梨県','長野県','岐阜県','静岡県','愛知県'),
PARTITION pKinkiRegion VALUES IN ('京都府','大阪府','三重県','滋賀県','兵庫県','奈良県','和歌山県'),
PARTITION pChugokuRegion VALUES IN ('鳥取県','島根県','岡山県','広島県','山口県'),
PARTITION pShikokuRegion VALUES IN ('徳島県','香川県','愛媛県','高知県'),
PARTITION pKyushuRegion VALUES IN ('福岡県','佐賀県','長崎県','大分県','熊本県','宮崎県','鹿児島県','沖縄県')
);
HASH パーティショニング
指定したパーティション数に基づいて、行をパーティションに均等に割り当てます。
RANGE や LIST では集めたいものでパーティショニングしていましたが、HASH パーティショニングにおいては、指定値のハッシュに基づいて均等にパーティションに配分されていきます。
CREATE TABLE `diaries` (
`create_date` date NOT NULL,
`title` varchar(255) NOT NULL,
`content` text NOT NULL
)
PARTITION BY HASH (to_days(`create_date`)) PARTITIONS 3;
指定するカラムの値、または式の返り値は整数である必要があります。（上記の例では DATE 型を整数に変換（to_days()）した値を指定している）
例えばパーティションを 3 つに分割するとして、振り分け先パーティションの決定は以下のように行われます。
2022-03-01 -> MOD(TO_DAYS('2022-03-01'),3) -> 1
2022-03-02 -> MOD(TO_DAYS('2022-03-02'),3) -> 2
2022-03-03 -> MOD(TO_DAYS('2022-03-03'),3) -> 0
2022-03-04 -> MOD(TO_DAYS('2022-03-04'),3) -> 1
2022-03-05 -> MOD(TO_DAYS('2022-03-05'),3) -> 2
2022-03-06 -> MOD(TO_DAYS('2022-03-05'),6) -> 0
.
.
.
このように剰余計算で配分先パーティションを決定しているため、値が y=ax のような直線的、比例的に増加していくものであると配分が均等になり良いです。
LINEAR HASH パーティショニング
CREATE TABLE `diaries` (
`create_date` date NOT NULL,
`title` varchar(255) NOT NULL,
`content` text NOT NULL
)
PARTITION BY LINEAR HASH (year(`create_date`)) PARTITIONS 3;
HASH パーティショニングとの違いは配分するパーティションを決定するための算出方法です。
HASH パーティショニングは剰余で算出していた一方で、LINEAR HASH パーティショニングではビット論理積を使ったアルゴリズムを利用するので計算が速いという利点があります。
// 配分先パーティションの決定
V = POWER(2, CEILING(LOG(2,3))) = 4 // パーティション数（=4）以上の 2 の累乗を算出
N = YEAR('2022-03-01') & (V（=4） - 1)
= 2022 & 3
= b'11111100110' & b'11'
= 2 // パーティション p2 へ格納
2022 年のレコードであれば パーティション p2 へ配分されることを実際に insert して確認してみます。
INSERT INTO diaries (create_date, title, content) VALUES ('2022-03-01', 'test', 'test');
mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'diaries';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 1 |
+----------------+------------+
パーティション p2 へ配分されることを確認できました。
ちなみに計算上パーティション数以上の数値になってしまうことがあり、その場合は算出された値を元に更に処理が行われます。
// 算出された値がパーティション数以上の場合
V = POWER(2, CEILING(LOG(2,3))) = 4
N = YEAR('2023-03-01') & (V(=4) - 1)
= 2023 & 3
= b'11111100110' & b'11'
= 3 // パーティション数以上であるのでこの値を元に更に計算
N' = N(=3) & (V(=4)/2 - 1)
= 3 & 1
= b'11111100111' & b'1'
= 1 // パーティション p1 へ格納
2023 年のレコードであれば パーティション p1 へ配分されることを実際に insert して確認してみます。
INSERT INTO diaries (create_date, title, content) VALUES ('2023-03-01', 'test', 'test');
mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'diaries';
+
| PARTITION_NAME | TABLE_ROWS |
+
| p0 | 0 |
| p1 | 1 |
| p2 | 1 |
+
パーティション p1 へ配分されることを確認できました。
この再計算が行われることについては、V としてパーティション数以上の 2 の累乗を算出している関係上発生しています。
---
P = 3（パーティション数）
V = POWER(2, CEILING(LOG(2, P))) = 4
---
つまり、
- P=2 なら V=2
- P=3 なら V=4
- P=4 なら V=4
- P=5 なら V=8
といった具合になるので、パーティション数が 5 の場合には初回の計算時点では N=0-8 の値が算出されてくることになります。そして、値が 5 以上であれば再計算が走ります。（しかも 5-8 で再計算すると N=0-1 にしかならないので配分されるパーティションに偏りがでます）
ただしこれは回避可能で、それにはシンプルにパーティション数を 2 の累乗で設定すればパーティション数よりも大きな V にはならないため、再計算を回避でき、一度の計算で算出しきれるようになります。(P= 2|4|8|16|32|64|128|256|512|1024)
KEY パーティショニング
こちらも HASH パーティショニング同様、指定したパーティション数に基づいて、行をパーティションに均等に配分します。
HASH パーティショニングでは、判定する元となる値の算出をユーザー側で定義（DATE型を整数に変換したり）しましたが、KEY パーティショニングの場合は MySQL 側で提供されます。
パーティショニングキーを指定しない場合はプライマリキー、ユニークキーの順で存在するカラムがパーティショニングキーとして利用されますが、パーティショニングキー（カラム）を指定する場合も含めて、制約がいくつかあります。
- プライマリキーがある場合は、その一部かすべてを構成しているカラムであること。
- プライマリキーは無くてユニークキーがある場合はユニークキーがパーティショニングキーとして使われる。
- この場合も同じく、カラムがユニークキーの一部かすべてを構成しているカラムであること。
- ユニークキーの場合、NOT NULL 制約がついていること。
CREATE TABLE `players` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)
PARTITION BY KEY () PARTITIONS 10;
サブパーティショニング（複合パーティショニング）
パーティション分割したものを更にパーティション分割するというものです。
RANGE または LIST でパーティション化されたテーブルに対して、 HASH もしくは KEY でのサブパーティショニングが可能です。
CREATE TABLE `logs` (
`logged_at` datetime NOT NULL,
.,
.,
.,
.
)
PARTITION BY RANGE (year(`logged_at`))
SUBPARTITION BY HASH (to_days(`logged_at`)) (
PARTITION p2019 VALUES LESS THAN (2020) (
SUBPARTITION s0 ENGINE = InnoDB,
SUBPARTITION s1 ENGINE = InnoDB
),
PARTITION p2020 VALUES LESS THAN (2021) (
SUBPARTITION s2 ENGINE = InnoDB,
SUBPARTITION s3 ENGINE = InnoDB
),
PARTITION p2021 VALUES LESS THAN (2022) (
SUBPARTITION s4 ENGINE = InnoDB,
SUBPARTITION s5 ENGINE = InnoDB
),
PARTITION p2022 VALUES LESS THAN (2023) (
SUBPARTITION s6 ENGINE = InnoDB,
SUBPARTITION s7 ENGINE = InnoDB
),
PARTITION pmax VALUES LESS THAN MAXVALUE (
SUBPARTITION s8 ENGINE = InnoDB,
SUBPARTITION s9 ENGINE = InnoDB
)
);
パーティショニングでのパフォーマンス向上を試す
パーティショニングを行い、パフォーマンスがどれくらい向上するのかをみてみます。
CREATE TABLE `logs` (
`logged_date` datetime NOT NULL
)
PARTITION BY RANGE (year(`logged_date`)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023)
);
RANGE パーティショニングを採用しました。ここに約 2400 万件のレコードを挿入して、それぞれのパーティションに配分してあります。
+--------------------+
| count(logged_date) |
+--------------------+
| 24300030 |
+--------------------+
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 8086082 |
| p2021 | 8086074 |
| p2022 | 8086026 |
+----------------+------------+
他に、パーティションなしと、パーティション無しだがインデックスありのパターンで 3 回試して比較してみます。
-- パーティションなし・インデックスなし
mysql> SELECT logged_date FROM logs WHERE logged_date >= '2022-01-01 00:00:00' AND logged_date <= '2022-12-31 23:59:59';
-> 8100010 rows in set (15.18 sec)
-- パーティションなし・インデックスあり
mysql> SELECT logged_date FROM logs WHERE logged_date >= '2022-01-01 00:00:00' AND logged_date <= '2022-12-31 23:59:59';
-> 8100010 rows in set (6.37 sec)
-- パーティションあり
mysql> SELECT logged_date FROM logs PARTITION (p2022);
-> 8100010 rows in set (4.98 sec)
パーティションもインデックスも無いテーブルに比べて、インデックスだけ張ったテーブルでは約 2.4 倍早く、パーティションありだと約 3 倍早くなりました。
パーティション無くてもインデックス張っていればそれなりですが、パーティションあると更に早くなっています。
以下は実行計画です。
-- パーティションなし・インデックスなし
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | logs | NULL | ALL | NULL | NULL | NULL | NULL | 23707015 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
-- パーティションなし・インデックスあり
+----+-------------+----------+------------+-------+----------------------------+----------------------------+---------+------+----------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+----------------------------+----------------------------+---------+------+----------+----------+--------------------------+
| 1 | SIMPLE | logs | NULL | range | our_logs_logged_date_index | our_logs_logged_date_index | 5 | NULL | 11708590 | 100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+----------------------------+----------------------------+---------+------+----------+----------+--------------------------+
-- パーティションあり
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | logs | p2022 | ALL | NULL | NULL | NULL | NULL | 8086026 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
注目すべきは rows（黄色の部分）で、やはりパーティションで区切られている分、スキャンしようとしている行数も比較的少ないです。
パーティションなし・ありで比較すると、パーティションありは、インデックスなしの約 34%、インデックスありの約 70 % ほどでした。
次に、これらのレコードを削除してみます。
-- パーティションなし・インデックスなし
DELETE FROM your_logs WHERE logged_date >= '2022-01-01 00:00:00' AND logged_date <= '2022-12-31 23:59:59';
-> 8100010 rows affected (44.61 sec)
-- パーティションなし・インデックスあり
DELETE FROM our_logs WHERE logged_date >= '2022-01-01 00:00:00' AND logged_date <= '2022-12-31 23:59:59';
-> 8100010 rows affected (6 min 38.57 sec)
-- パーティションあり
ALTER TABLE my_logs DROP PARTITION p2022;
-> 0 rows affected (0.17 sec)
パーティションなしの DELETE ステートメントは 1 行ずつ削除していくのでやはり時間がかかりますが、パーティションありの DROP PARTITION は TRUNCATE TABLE ステートメント的に処理してくれるため早いです。
パーティションなし・ありで比較すると、インデックスもないテーブルの 263 倍早く、インデックスありの方は index 再構築入る分更に遅いので、それに比べると 2345 倍早い。という結果になりました。
まとめ
大規模データを扱う時にとても有利になりそうなパーティショニング。概念を知っておくことは大切だなと思います。
今回で全てに触れたわけではないので、またの機会に更に潜ってみたいと思います。
dev.mysql.com
現在 back check 開発チームでは一緒に働く仲間を募集中です!!
herp.careers
https://herp.careers/v1/scouter/GMnGlADgFBtQherp.careers
herp.careers
herp.careers
herp.careers
herp.careers
herp.careers
