この記事はリンク情報システムの「TechConnect！2022年2月」のリレー記事です。
engineer.hanzomon のグループメンバによってリレーされます。
（リンク情報システムのFacebookはこちらから）
TechConnect！2022年2月のインデックスはこちら
はじめに
今回のアドベントカレンダーの2番目らしいです。
昨今、野球界では2番バッター最強説とかも出てきていることもあって、2番目ということに勝手にプレッシャーを感じてます。
なによりネタがピンポイントすぎる気がする、、、
今回は僕が関わっているシステムで課題だった処理時間の長さを解決してくれた救世主・パーティショニングを紹介したいと思います。
開発環境
開発言語:PHP7.3
MySQL:8.0.18
なんでパーティショニングが必要なのか？
DBにデータを登録していくともちろんデータが蓄積していく。
↓
そうすると探索するデータの対象が増えて処理に時間がかかってしまう。
↓
その他の応答時間も長くなってしまい、ユーザーに不快感を与えてしまう。
↓
ユーザーから問い合わせがくる。
↓
ヤバイ！
ということでパーティショニングを使ってみました！
ぱーてぃしょん？ぱーてぃしょにんぐ？
パーティショニングとは？
パーティショニングは、必要に応じて多くの部分を設定できるルールに従って、個々のテーブルの部分をファイルシステムに配分できるようにしています。それにより、テーブルの異なる部分が別個のテーブルとして別個の場所に格納されます。データを分割するためにユーザーが選択するルールはパーティショニング関数と呼ばれ、MySQL では法、範囲セットまたは値リストに対する単純な照合、内部ハッシュ関数、または線形ハッシュ関数が使用されます。関数は、ユーザーが指定したパーティショニングタイプに従って選択され、ユーザーが指定した式の値をパラメータとして取ります。この式には、使用されるパーティショニングのタイプに応じて、カラム値、1 つ以上のカラム値を操作する関数、または 1 つ以上のカラム値のセットを指定できます。
19.1 MySQL のパーティショニングの概要
くどくど書いてあるけど、要するに
1つのテーブルを仮想的に複数の区切りに分けられるということ！
図で示すとこんな感じ
パーティショニングの準備
１．使用するパーティショニングを選定する
一言にパーティショニングと言ってもいくつかの種類があります。今回はその中の２つを紹介します。
RANGEパーティショニング
指定された範囲に含まれるカラム値に基づいて、テーブルを分割する。
HASHパーティショニング
負の整数値にならないユーザー定義式に基づいてパーティションが選択される。
他にもパーティショニングがあるのでよかったら調べてみてください。
今回はRANGEパーティショニングを使用しました。
２．プライマリーキーを設定する
パーティショニングには前提があり、パーティショニングに使いたいカラムはPRIMARY KEYに含まれていなければいけないという条件があります。そのため、稼働中のDBに追加する場合は注意が必要です。
３．パーティションの追加SQL
パーティションを追加するSQLは以下のように記述します。
ALTER TABLE テーブル名
PARTITION BY RANGE COLUMNS(カラム名) (
PARTITION 各パーティショの名前 VALUES LESS THAN ('2022/1/1 00:00:00'))
このような感じで切りたいだけパーティションを作っていけます。
各パーティションごとに名前を付けることができるので、「P202201」のようにいつまでのパーティションかを区別できるのも便利な点です。
基本的にはこの3順序でパーティションが設定されます。
その他・注意点
パーティショニングを解除する
パーティショニングは以下のSQLで戻すことができます。
パーティショニングを解除してもデータは消えないので安心してください。
ALTER TABLE テーブル名 REMOVE PARTITIONING
パーティションの最大数
MySQL5.6.7以降は8192パーティションまで増加しています。
よっぽどのことがに限り使い切ることは無いと思いますが、注意しましょう！
パーティションの追加
既にパーティションが設定されているテーブルにパーティションを追加する場合は、今あるパーティションの後ろにしか追加できないので注意しましょう！
おまけ：実際にパーティションを使ってみた
実際に業務で利用しているデータのため、処理の詳細が書けないのでカットしようと思いましたが、ちょっとおもしろいデータがあるのでそれだけ紹介したいと思います。
それは年単位、月単位、週単位でパーティションを切った時の処理時間が摩訶不思議だったんです。
論より証拠ということで、それを示したのが下記の表です。
| 年単位 | 月単位 | 週単位 | |
|---|---|---|---|
| 処理時間 | 22秒 | 18秒 | 24秒 |
パーティションを細かくすれば比例して処理時間が短くなるかなと思いましたが、そんなことは無く週単位にすると逆に処理時間が長くなってしまったんです！
この回だけたまたまそうなっただけのような気もしますが、、、
本当なら何十回かデータを取って平均出すのがベターなんだろうけどそんな悠長なことをする時間が無かった、、、
まー、ぶっちゃけ20秒台になっただけで相当速くなっていたので、そこまで気にしてはないんですけどね。
これを見てみると、逆にパーティションの切り方でも処理が遅くなるのか？と思いました。
内部的にデータがわかれてしまうので、クエリによっては速度が遅くなる
MySQLのパーティショニングの使い方とエラー対策
まとめ
救世主・パーティショニングのおかげで処理速度の改善が図れました。
ありがとう！救世主・パーティショニング！
これからは長年使われそうなシステムについては、ちゃんとパーティションも考慮して設計しないとダメだなと感じました！
また書きます。