MySQLのパーティショニングの使い方とエラー対策
MySQL（※1）にはパーティショニング機能があります。
これは、一つのTableで膨大なデータを管理しなければいけない時に使える手法で、論理的に1つに見えるTableを、内部的にはわけて管理（＝パーティショニング）することができます。
しかしMySQLの公式ページが一番詳しいものの、いまいち分かりづらかったり、かといって日本語でまとまった情報がなかったりしますので、構築時に困りがちです。
例えば、パーティショニングで作成方法を間違えると下記のようなエラーがでます。
A UNIQUE INDEX must include all columns in the table’s partitioning function
この対処は後述しますが、そもそもパーティショニングの概念をおさえておくと早いと思いますので、ここで簡単にまとめておきます。
※1 正確にはOracleなど他のRDBMSにもあります。
目次
パーティショニングとは
冒頭でも触れましたが、パーティショニングは膨大なデータ量を1つのTableで扱いたい時に使える手法です。
そもそも1つのTableに数百万行といった量のデータが入る場合、ちょっと速度や容量などが怖いですよね。しかし、QA Heatmap Analyticsもそうですが、ウェブ行動データのような膨大なデータを長期間扱おうとすれば、どうしてもTableサイズは膨大になってきます。
こういった時に、例えば毎月ごとにTable名をわけて管理することが考えられます。
例えば202001_log、202002_log、202003_log、、といったTableをどんどん作っていくという手法です。そうすれば1つのTableサイズは小さくて済みます。
しかし、この手法では同じ意味のTableが無駄に多数できてしまい、月またぎのSQLも難しくなってきて、管理もしづらくなってしまいます。このような時にパーティショニングが有効です。
パーティショニングは、内部的にはTableがわかれるものの、Table名は一つです。例えば先ほどの例では以下のようになります。
Table名：log
※内部パーティション
p202001
p202002
p202003
このようにすると、SQLはもちろんTable「log」に対して行えるので月またぎのSQLも今までどおりシンプルになりますし、場合によってパーティションを指定して検索することもできます。
またDropもパーティション単位で行えますので、膨大なデータを管理する場合に検討の余地のある方法になります。
パーティショニングの使い道。メリットとデメリット
パーティショニングのメリットは、前出のように膨大なデータを管理する場合に管理がしやすくなることです。具体的には下記のメリットがあります。
- パーティション単位でデータを移行する、Dropするなどの操作が簡単にできる
- 内部的には分かれているものの、1つのTableとして扱えるのでSQLはいままで通り使える
- データが小分けに管理されるので、それを意識したクエリであれば速度が早くなる
デメリット
一方で、デメリットとして
- 内部的にデータがわかれてしまうので、クエリによっては速度が遅くなる
- 後からTable構造を変更しようとすると大変なので、初期設計が重要となる
- クエリーキャッシュがサポートされないなど、いくつかの制約がでる
- パーティショニングに対して慣れていないプログラマに啓蒙が必要となる
- パーティション全体を通してユニークなキーは作成することができない
総じて、やはり管理ルールが変わりますから、今までとまったく一緒というわけにはいきません。ですから、やはり膨大なデータを扱いたいときに採用を検討すると良いと思います。
制約については公式ページが詳しいです。
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html
パーティショニングの作成方法
パーティショニングの作り方は、CREATE TABLE時にやる方法と、後からALTER TABLEでやる方法の二種類があります。
具体的なクエリは後述しますが、例えばlogファイルで行う場合は、月ごとにわけることが一般的で、コツとしては最初から一気に未来日付分まで作ってしまう方がよいです。
例えば2038年くらいまで作っておけば、あとはMySQLがデータインサート時に勝手に振り分けてくれるので、しばらく日付判定なども不要で楽です。
パーティショニングとユニークキーについて
これが最も大切な考え方です。
仮に下記のようなTableがあったとします。
CREATE TABLE ids_log (
uid INT AUTO_INCREMENT,
another_id INT UNIQUE NOT NULL,
insert_time DATETIME NOT NULL
)another_idがユニークになっています。
このようなTableで、仮にinsert_timeで判定し、各月ごとにパーティションを作ったとします。この状況で問題となる例をあげます。
まず最初に2020年1月にanother_id = 5の人をINSERTしたとします。
その3ヶ月後に、今度はanother_id = 5 の人に重複INSERTがあったとします。
重複と簡単にいいましたが、実はここがパーティショニングのデメリットとなる部分です。結論からいうと、パーティショニングを使う場合「another_idという単一カラムのみで重複を判断することはできません。つまりanother_id = 5を全パーティションをまたいでユニークにはできません。
その理由ですが、そもそもMySQLのパーティショニングとは、各パーティションごとにTable（インデックス付き）が作成されるようなイメージです。そうするとanother_id = 5というデータだけを挿入する場合、どのパーティションに入れてよいかわからないと思います。従って、another_id = 5は、必ず日時(insert_time)とセットで挿入の指示が必要です。
この現象をMySQL側からみると、ユニークなのは「another_idと日時(insert_time)の複合の組み合わせ」ということになります。逆にいえばanother_id=5だけではユニークではありませんし、ユニークにすることができません。従って、パーティショニング時にユニークキーを使う場合は、必ず複合キー、今回でいえば日時（insert_time)と組み合わせないといけないのです。
別の言い方をすると、「パーティションでユニークを使いたいなら、必ず、あるカラムとパーティションで使うカラムとの複合インデックス（複合キー）を作らないといけない。」ということになります。
パーティションを作成する時にエラーが出ることがありますが、その殆どがこの問題です。
そもそもユニークキー、プライマリーキー（主キー）、インデックスって？
念のため、ユニークキーやインデックスなどについてもここで簡単に整理しておきますね。
そもそもMySQLなどのRDBMSではカラムにいろいろな属性などをつけれますが、大雑把に以下にわかれます。
- インサート時に簡単に判断できる条件・・・NOT NULL、CURRENT TIMESTAMPなど
- そもそも索引（インデックス）がないとMySQL側で判断が付かない条件・・・UNIQUE（固有性）
- 検索に便利な管理手法・・・INDEX（索引）。キーとも言われる。
並列ではなく、後者2つが特別なんですよね。
まずユニークは、重複を許さないということです。
ここで重要なのは、重複を許さないということは、MySQLは自動的に索引（インデックス）を作る必要があるということです。
そうでなければ、毎回、全件検索しなくてはいけなりません。
ということで、ユニークキーを作るとは、ユニークを担保するためにキー＝インデックスが自動で作成される、という意味です。
プライマリーキーは、ユニークキーの特殊形態です。
Nullを許可しないこと、および各Tableで1つしか作れないこと以外はユニークキーと一緒なので、ユニークキーの一種だと思って問題ないでしょう。
最後にインデックスは、上記でも書きましたが「索引を作る」ことです。
索引を作ると、クエリで意識すれば当然検索が早くなりますし、必要なデータだけとってくるので、メモリの使用量も抑えやすくなります。
ユニークキー（主キー含む）を作れば、自動でインデックスが作成されます。もちろん手動でINDEXを作ることもできます。
詳しくはこのあたりが参考になると思います。
複合キーって？
複合キー ＝ 複合インデックス。つまり、二つ以上（複合）のカラムを結合して索引を作ることです。
パーティショニングでは、この複合キーをうまく使う必要があります。
例えば、社員番号と入社日のTableがあったとします。
ここで例えば入社日でパーティションをわけた場合、いつものように社員番号だけにユニーク属性をつけることはできません。
なぜなら、社員番号だけにユニーク属性をつけると、索引が自動で作成されますが、それだけだと、どのパーティションにその社員番号が格納されているかわからなくなるからです。
これを解決するために、社員番号と入社日をあわせて複合キーを作る必要があります。そうすれば総合的なインデックスが作成されるので、MySQLとしても社員番号を指定された時に、どのパーティションに格納されているか、また重複があるかどうか瞬時にわかるからです。
パーティションとインデックスは関係ない？
はい。インデックスは関係ないです。
インデックスただの索引ですし、もちろん重複もOK（管理しない）です。ですからパーティションにかかわらず自由につけることができますし、複合にする必要もありません。
そこは勘違いしないようにしましょう。
パーティションの最大数は8192
MySQL 5.6.7 以降は、最大8192 パーティションまで作成できます。
CREATE PARTITIONの例
create table table
(
id int auto_increment,
aid varchar(28) not null,
primary key (id, update_date),
hizuke date not null,
unique key (aid, update_date)
)
partition by range COLUMNS(hizuke) (
partition p202001 values less than ('2020-01-01 00:00:00'),
partition p202002 values less than ('2020-02-01 00:00:00'),
);hizukeで判断するパーティションを後半につけてコミットします。
ユニークが必要な箇所はすべてhizukeと複合キーにします。
前出のようにインデックスはパーティションと無関係なので、Table作成後に自由につけることができます。
ALTER PARTITIONの例
alter table table partition by range COLUMNS(hizuke) (
partition p202001 values less than ('2020-01-01 00:00:00'),
partition p202002 values less than ('2020-02-01 00:00:00'),
);ALTERの場合もCREATEの時とほとんど構文は変わりません。
A UNIQUE INDEX must include all columns in the table’s partitioning functionへの対処
前出しましたが、ユニークキーはパーティションではとても重要です。
全てのユニークキーは、パーティション全体でユニークになるよう複合キーにしなくてはいけません。
例えば下記のようなTableでinsert_timeを判断してパーティションを作るとします。
CREATE TABLE ids_log (
uid INT AUTO_INCREMENT,
another_id INT UNIQUE NOT NULL,
syain_id INT UNIQUE NOT NULL,
insert_time DATETIME NOT NULL
)上記では、uid(auto_incre,ent),another_id、syain_idの3つは全てユニークになっています。
この時、必ず下記3つの複合キーを作る必要がある（作らないと、パーティションをまたがったユニークが担保できないから）ということになります。
UNIQUE KEY (uid, insert_time),
UNIQUE KEY (another_id, insert_time),
UNIQUE KEY (syain_id, insert_time)
パーティショニングの速度アップのコツ
パーティションへのクエリを速くするには、当然、分かれている単位でクエリを出すのが得策です。
例えば、毎月のパーティションを作ったならば、その一ヶ月で収まるような範囲でクエリを投げれば、速くなることは想像しやすいですよね。
これはパーティションプルーニングと言われています。
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-pruning.html
また、インデックスの仕組みを考えれば、DBにとっては索引で収まる範囲でのクエリを投げてもらうと、いち早く目的のデータを探し出し、オンメモリすることができます。
この時はメモリ使用量も全件検索と比べてはるかに小さくなりますから、サーバー側でSwapが発生する確率も低くなり、パフォーマンスを維持しやすくなります。
そう考えると変にSQLのWHERE句で細かく指定をして全件検索をさせるより、インデックスをうまく使って一部をメモリにとってきて、あとはプログラム側で配列処理した方が速くなることも多いです。
そのような考え方で運用するようにすると、うまく活用できると思います。
まとめ
以上、パーティショニングについてまとめてみました。
うまく使えば膨大なテーブルの管理がしやすくなるのでおすすめです。
最後に、パーティショニングは公式ページが一番詳しいので、参考URLを貼っておきます。
https://dev.mysql.com/doc/refman/5.6/ja/partitioning.html
