MySQLのパーティションニングにより処理速度の改善

## 概要
Mysqlパーティショニングについて、今まで触れる機会はあったが実際に作成したことがなかったので、実際に作成して動作を確認してみた

- ドキュメント
https://dev.mysql.com/doc/refman/8.0/ja/partitioning.html

## パーティショニングとは
- テーブルを内のデータを分割して保持する機能
- 分割して管理することで以下のメリットが得られる

### レスポンス向上
- パーティションプルーニングにより、一致する値がない可能性があるパーティションをスキャンしないことによる処理速度の向上が見込める
  - https://dev.mysql.com/doc/refman/8.0/ja/partitioning-pruning.html

### 処理の並列化

- MySQLでは現在実装されていないとのこと。オラクル等の場合は、処理の並列化による処理速度の向上が見込める

> 通常はパーティショニングに関連付けられるその他の利点を次に示します。これらの機能は MySQL パーティショニングに現在実装されていませんが、オラクルの優先事項リストの上位にあります。

>SUM()、COUNT() などの集約関数を含むクエリーを簡単に並列化できます。そのようなクエリーの簡単な例として、SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id; を挙げられます。「並列化」することで、クエリーを各パーティションで同時に実行してから、すべてのパーティションで取得される結果を単に集約するだけで最終的な結果が得られることを意味します。

>複数のディスクにデータシークを分散することによって、クエリーのスループットが向上します。

>MySQL パーティショニング開発は継続されているため、このセクションおよび章が更新されているかどうかを頻繁にチェックしてください。

- https://dev.mysql.com/doc/refman/8.0/ja/partitioning-overview.html

### メンテナンス性の向上
- 分割したテーブル他院射手の追加や削除、更新等を行うことができるのでメンテナンス性が向上する

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

- 詳細は公式ドキュメントを参照。今回は一番よく使われるであろうRANGEパーティションを利用してみた

https://dev.mysql.com/doc/refman/8.0/ja/partitioning-types.html

- RANGE
- LIST
- HASH
- KEY

## パーティションテーブルの作成

### 実際にパーティション化したテーブルを作成してみる

- パーティションを行うために指定するキーは、主キーの一部である必要がある

> テーブルのパーティション化式で使用されるすべてのカラムは、主キーを含め、テーブルに含まれる可能性のあるすべての一意キーの一部である必要があります。 つまり、次の SQL ステートメントで作成されたこのようなテーブルはパーティション化できません

- テーブル作成時にパーティションの作成を行う

```sql
mysql> CREATE DATABASE sample character SET utf8mb4;
mysql> CRERATE TABLE plogs (
         id INT NOT NULL AUTO_INCREMENT,
         created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
         updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
         PRIMARY KEY (id, created_at)
       )
       PARTITION BY range columns(created_at) (
         PARTITION p202105 VALUES LESS THAN ('2021-06-01 00:00:00'),
         PARTITION p202106 VALUES LESS THAN ('2021-07-01 00:00:00'),
         PARTITION p202107 VALUES LESS THAN ('2021-08-01 00:00:00'),
         PARTITION p202108 VALUES LESS THAN ('2021-09-01 00:00:00'),
         PARTITION latest VALUES LESS THAN MAXVALUE
       );
```

- テストデータの作成

```sql
mysql> delimiter //
mysql> create procedure create_test_data_plogs(in i int, m datetime)
       begin
         declare cnt int default 0;
         declare v datetime;
         while cnt < i do
           set cnt = cnt + 1;
           set v = m + interval cnt second;
           insert into plogs(created_at, updated_at) values(v, v);
         end while;
       end
       //
mysql> delimiter ;
mysql> call create_test_data_plogs(100000, 20210901);
mysql> call create_test_data_plogs(100000, 20210801);
mysql> call create_test_data_plogs(100000, 20210701);
mysql> call create_test_data_plogs(100000, 20210601);
mysql> call create_test_data_plogs(100000, 20210501);
```


### 処理速度比較用テストデータ（非パーティション化テーブル）の作成

- 処理速度の比較用に、パーティションを利用しない場合のテーブルを作成

```sql
mysql> CRERATE TABLE logs (
         id INT NOT NULL AUTO_INCREMENT,
         created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
         updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
         PRIMARY KEY (id, created_at)
       )
```

- テストデータの作成

```sql
mysql> delimiter //
mysql> create procedure create_test_data_logs(in i int, m datetime)
       begin
         declare cnt int default 0;
         declare v datetime;
         while cnt < i do
           set cnt = cnt + 1;
           set v = m + interval cnt second;
           insert into logs(created_at, updated_at) values(v, v);
         end while;
       end
       //
mysql> delimiter ;
mysql> call create_test_data_logs(100000, 20210901);
mysql> call create_test_data_logs(100000, 20210801);
mysql> call create_test_data_logs(100000, 20210701);
mysql> call create_test_data_logs(100000, 20210601);
mysql> call create_test_data_logs(100000, 20210501);
```

## 速度の検証

- パーティションのキーである `created_at`（非インデックス）を条件に検索
- パーティションプルーニングにより、一致する値がない可能性があるパーティションを検索しないことで、処理時間の高速化が見込める

https://dev.mysql.com/doc/refman/8.0/ja/partitioning-pruning.html

```sql
mysql> SELECT * FROM plogs WHERE created_at BETWEEN "2021-06-01 00:00:00" AND "2021-06-02 00:00:00";

86400 rows in set (0.09 sec)
```

- 実行計画を確認すると、partitions の項目に検索対象となったパーティションが表示されていることが確認できる

```sql
mysql> EXPLAIN SELECT * FROM plogs WHERE created_at BETWEEN "2021-06-01 00:00:00" AND "2021-06-02 00:00:00";
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | plogs | p202106    | ALL  | NULL          | NULL | NULL    | NULL | 100405 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
```

### パーティションなしの場合

- 全てのレコードに対する探索処理が走るので、パーティショニングを行った場合よりも処理時間が遅くなっていることが確認できる

```sql
mysql> SELECT * FROM logs WHERE created_at BETWEEN "2021-06-01 00:00:00" AND "2021-06-02 00:00:00";

86400 rows in set (0.41 sec)
```

- 実行計画でもrowsがテーブルに存在するレコード数に増えていることが確認できる

```sql
mysql> EXPLAIN SELECT * FROM logs WHERE created_at BETWEEN "2021-06-01 00:00:00" AND "2021-06-02 00:00:00";
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | logs  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 501579 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```

## パーティションの管理

### パーティションの追加 

- https://dev.mysql.com/doc/refman/5.6/ja/partitioning-management-range-list.html
- alter table を使用してパーティションの追加が可能

```sql
mysql> ALTER TAVLE plogs ADD PARTITION (PARTITION p202109 VALUES LESS THAN ('2021-10-01 00:00:00'));
```

- すでに既存のパーティションに割り当てられている値を、ほかのパーティションに移動することはできないので以下の
  - 既存のパーティションを一旦別テーブルにコピー → 古いテーブルを作成しテーブル名を変更する 等で対応する必要がある

```sql
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
```

- mysqlではありがたいことにパーティションを再定義する方法がある
    - 既存のパーティションを、分割可能
    - 再編成中はロックが発生する

```sql
mysql> ALTER TABLE plogs REORGANIZE PARTITION latest INTO (
    PARTITION p201009 VALUES LESS THAN ('2021-10-01 00:00:00'),
    PARTITION latest VALUES LESS THAN MAXVALUE
);
```

### パーティションの自動追加

- 今回のような日付を使用した Range パーティション等では、一定間隔でパーティションを自動作成してくれる機能等あるとありがたいがない
- トリガーやストアドファンクション等を使って自動的にパーティションを作成するようにすると良いかもしれない

参考: https://qiita.com/yancya/items/960180ed1d6faa510d65


### パーティションの削除

- パーティションを削除すると、データも一緒に削除される

```sql
mysql> ALTER TABLE plogs DROP PARTITION p202109;
```

## 終わりに
- このほかにもパーティションに使用するキーを主キーと完全に一致させて場合に処理速度がどうなるか試してみたが、主キーで検索するような場合はパーティショニングをおこなった場合の方が処理時間がかかることが確認できた・・・!

## 参考
- https://www.oracle.com/technetwork/jp/ondemand/database/db-technique/d-12-disk-1484778-ja.pdf
- https://atmarkit.itmedia.co.jp/ait/articles/0701/25/news139.html
- https://hit.hateblo.jp/entry/%3Fp%3D62
- https://qiita.com/hmatsu47/items/354f979cde6ad91bcc6b
- https://qiita.com/hmatsu47/items/b81ba1cdc8cfeb8a6ee7
- https://qiita.com/taroshin/items/608076c9f8e09497c4b1
- https://moneyforward.com/engineers_blog/2015/09/16/mysql-partitioning/