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