MariaDB(MySQL)でのパーティショニング

MAGAZINE

ルーターマガジン

インフラ/運用

MariaDB(MySQL)でのパーティショニング

2022.04.08

Pocket

パーティショニングとは

レコード数が膨大な DB だと，データを参照したり，データベースの内容を更新する際に非常に時間がかかります．これは，DB を複数の「パーティション」に分割して管理することで解消することができます．このパーティション分割による管理を「パーティショニング」といいます．パーティショニングは，見かけ状の DB の状態は保存したまま，物理空間だけ分離します．これにより，必要なパーティション（物理空間）のみを指定してデータ探索をすることができます．

例として，創業100年の会社の直近1年分の売り上げデータを分析したいという状況を想定します．この会社の売り上げデータは1つのテーブルで管理されており，データを取得するためには過去100年分全てのデータを走査する必要があります．これを1年ごとのパーティションで分割管理し，対象のパーティションのみを選択して操作すると，探索領域は単純に 1/100 となります．

このようにして，パーティショニングは検索コストの削減を実現します．

MariaDB(MySQL)によるパーティショニング

本記事では MariaDB を用いてパーティショニングの手順を紹介していきます．(MySQLと互換性がない場合もあります．)

パーティショニングの種類

最初にパーティショニングの種類について説明していきます．MariaDB の Knowledge Base では，サポートするパーティショニングの種類について以下のように書かれています．

MariaDB supports the following partitioning types:MariaDB supports the following partitioning types:
・RANGE
・LIST
・RANGE COLUMNS and LIST COLUMNS, HASH COLUMNS
・HASH
・KEY
・LINEAR HASH, LINEAR KEY
・SYSTEM_TIME
https://mariadb.com/kb/en/partitioning-types-overview/

これらのうち，以下の３つが一般に用いられます．

RANGE
- 値の範囲に合致するレコードごとに分割
- ex) 時間や日付の範囲指定で分割して使用
LIST
- リストに割り振られた値に合致するレコードごとに分割
- ex) 曜日ごとに分割して使用
HASH
- 定められた数のパーティションに均等に分割
- ex) 統計的分析をする際に使用

パーティショニングにおける制約

パーティショニングにおいて注意すべき制約としては，主に以下の２つが挙げられます．

パーティション分割に使用するキーはプライマリキー（主キー）に含まれていなければならない
RANGE でのパーティション分割に使用するキーは int, date, datetime 型でなければならない

パーティショニング実行例

以降の手順では以下の partition_table を対象とします．

Field	Type	Null	Key	Default	Extra
id	int(11) unsigned	NO	PRI	NULL	auto_increment
name	varchar(255)	YES		NULL
created_at	datetime	NO	PRI	NULL

partition_table

生成された日付(created_at)によってパーティショニング(RANGE分割)する例を示していきます．

手順

1. パーティションに使用するキーを主キーに設定

> CREATE TABLE partition_table (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  created_at datetime NOT NULL,
  PRIMARY KEY (id,created_at)
)

- 既存のテーブル（id のみが主キー）をパーティション分割する場合は，一度主キーを解放して id と created_at を複合主キーとして設定する必要あり

2. パーティショニング（月単位）

> ALTER TABLE partition_table PARTITION BY RANGE COLUMNS(created_at) (
  PARTITION p2101 VALUES LESS THAN ('2021-02-01 00:00:00'),
  PARTITION p2102 VALUES LESS THAN ('2021-03-01 00:00:00'),
  PARTITION p2103 VALUES LESS THAN ('2021-04-01 00:00:00'),
  PARTITION p2104 VALUES LESS THAN ('2021-05-01 00:00:00'),
  PARTITION p2105 VALUES LESS THAN ('2021-06-01 00:00:00'),
  PARTITION p2106 VALUES LESS THAN ('2021-07-01 00:00:00'),
  PARTITION p2107 VALUES LESS THAN ('2021-08-01 00:00:00'),
  PARTITION p2108 VALUES LESS THAN ('2021-09-01 00:00:00'),
  PARTITION p2109 VALUES LESS THAN ('2021-10-01 00:00:00'),
  PARTITION p2110 VALUES LESS THAN ('2021-11-01 00:00:00'),
  PARTITION p2111 VALUES LESS THAN ('2021-12-01 00:00:00'),
  PARTITION p2112 VALUES LESS THAN ('2022-01-01 00:00:00'),
  PARTITION p9999 VALUES LESS THAN MAXVALUE
);

3. パーティション確認

> SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='partition_table';

+----------------+-----------------+----------------+----------------------------+------------+
| TABLE_SCHEMA   | TABLE_NAME      | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+----------------+-----------------+----------------+----------------------------+------------+
| partation_test | partition_table | p2101          |                          1 |          0 |
| partation_test | partition_table | p2102          |                          2 |          0 |
| partation_test | partition_table | p2103          |                          3 |          0 |
| partation_test | partition_table | p2104          |                          4 |          0 |
| partation_test | partition_table | p2105          |                          5 |          0 |
| partation_test | partition_table | p2106          |                          6 |          0 |
| partation_test | partition_table | p2107          |                          7 |          0 |
| partation_test | partition_table | p2108          |                          8 |          0 |
| partation_test | partition_table | p2109          |                          9 |          0 |
| partation_test | partition_table | p2110          |                         10 |          0 |
| partation_test | partition_table | p2111          |                         11 |          0 |
| partation_test | partition_table | p2112          |                         12 |          0 |
+----------------+-----------------+----------------+----------------------------+------------+

その他のパーティション操作

- パーティショニング（曜日単位）
```
> ALTER TABLE partition_table PARTITION BY LIST(WEEKDAY(created_at)) (
  PARTITION pMon VALUES IN (0),
  PARTITION pTue VALUES IN (1),
  PARTITION pWed VALUES IN (2),
  PARTITION pThu VALUES IN (3),
  PARTITION pFri VALUES IN (4),
  PARTITION pSat VALUES IN (5),
  PARTITION pSun VALUES IN (6)
);
```
- パーティション追加
  - LIST の場合
```
> ALTER TABLE partition_table ADD PARTITION (PARTITION pExc VALUES IN (7));
```
  - RANGE の場合
```
> ALTER TABLE partition_table REORGANIZE PARTITION p9999 INTO (
    PARTITION p2201 VALUES LESS THAN ('2022-02-01 00:00:00'),
    PARTITION p9999 VALUES LESS THAN MAXVALUE
);
```
    - 単純に ADD PARTITION で追加すると，末尾に追加される．
    - → RANGE パーティションは先に定義された範囲から順に評価していくため，追加したパーティションの前に p9999 パーティションで全て振り分けられる．
    - そのため，RANGE では上記の方法で最大値のパーティションを更新する必要がある．
- パーティション解除→元の設定に戻す
```
> ALTER TABLE partition_table REMOVE PARTITIONING;
```
  - データはそのままで，パーティションの設定のみ消える．
  - パーティション削除
```
> ALTER TABLE partition_table DROP PARTITION p21xx;
```
    - 該当パーティションが消える．
    - パーティションの中身を削除
```
> ALTER TABLE partition_table TRUNCATE PARTITION p21xx;
```
      - 該当パーティションのレコードが消える．

実行時間の検証

まず，先ほどの partition_table に大量のレコードをインサートします．
以下のようにして，プロシージャを使い各パーティションに１万レコードずつ保存します．

> delimiter //
> CREATE PROCEDURE insert_loop(in i int)
    BEGIN
        DECLARE cnt int DEFAULT 0;
        WHILE cnt < i DO
            SET cnt = cnt + 1;
            INSERT INTO partition_table (name, created_at) VALUES
                ('Jan','2021-01-15 12:00:00'),
                ('Feb','2021-02-15 12:00:00'),
                ('Mar','2021-03-15 12:00:00'),
                ('Apr','2021-04-15 12:00:00'),
                ('May','2021-05-15 12:00:00'),
                ('Jun','2021-06-15 12:00:00'),
                ('Jul','2021-07-15 12:00:00'),
                ('Aug','2021-08-15 12:00:00'),
                ('Sep','2021-09-15 12:00:00'),
                ('Oct','2021-10-15 12:00:00'),
                ('Nov','2021-11-15 12:00:00'),
                ('Dec','2021-12-15 12:00:00');
        END WHILE;
    END //
> delimiter ;
> CALL insert_loop(10000);

この DB から 5 月土曜日のデータを参照したいものとして，以下のクエリを実行します．

パーティション未選択

> SELECT * FROM partition_table WHERE WEEKDAY(created_at) = 5;
...
10000 rows in set (0.038 ± 0.003 sec)

パーティション選択

> SELECT * FROM partition_table PARTITION(p2105) WHERE WEEKDAY(created_at) = 5;
...
10000 rows in set (0.008 ± 0.002 sec)

以上から，パーティショニングによって実行時間の削減が実現できることが分かります．

終わりに

本記事ではパーティショニングによる利点のみを述べましたが，デメリットもあるため，DB の利用環境に適した使い方が推奨されます．

Pocket

CONTACT

お問い合わせ・ご依頼はこちらから