第7回　パーティショニング

　本記事は、『Software Design 2019年8月号』の第2特集「ゲームを題材に学ぶ内部構造から理解するMySQL」をWeb掲載用に再編集したものです。
　本記事のテーマを、より基本的なところから丁寧に解説した『SQLの苦手を克服する本　データの操作がイメージできれば誰でもできる』が2019年8月26日に発売されました。本記事と併せてご活用ください。

効果的な開発のためのテーブルテクニック

　ここまでの連載ではコーディングの話が続きましたが、今回はゲーム系の開発で効果を出しやすいテクニックをもう1つ紹介します。それは、テーブルのパーティショニングです。かつて、Oracle Databaseなどの商用DBにおいてパーティショニング機能はEnterprise Edition限定の機能でした。これはライセンス費が1,000万円超するほど高価で、ユーザもあまり多くありませんでした。そのため、このテクニックの利用はあまり進んでいません。しかし、データ量が多いシステムでは非常に有効ですので、ぜひ、理解して利用してください。

パーティショニングとは

　パーティショニングとは、大きいテーブルを特定のカラムのデータによって、内部的に小さいテーブルに分けることです。同じテーブル名でアクセスしても、WHERE句などによって抽出条件がある場合、分割された小さなテーブル（パーティション）や、分割された小さなインデックスのうち必要なものにだけアクセスすることになります。そのため、多くの処理で抽出条件に指定されるカラム（ゲーム系ではユーザIDなど）がある場合、レスポンスやサーバの負荷削減に効果が高い機能です。MySQLでは、サブパーティションまで作成でき、MySQL 5.6.7以降ではサブパーティションと合わせて最大8,192個、それより古いバージョンでは1,024個作る（分割する）ことができます。

MySQLの制限

　MySQLでは、パーティショニングキーになるカラムは、プライマリーキー、または、ユニークキーの一部である必要があります。また、「⁠外部キー制約がついているテーブルはパーティショニングができない」という制限があります。この制限によって生じるメリットとデメリットをよく検討して利用してください。

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

　おもなパーティショニングの種類としては、次の4つがあります。

ハッシュパーティショニング（ハッシュ）

キーパーティショニング（Key）

レンジパーティショニング（Range）

リストパーティショニング（List）

　「⁠ハッシュパーティショニング」とは、整数値のカラムの値を分割する個数で割った余りの値で分割するパーティショニングのことです。おそらくゲーム系ではこのパーティショニングが一番利用されるので、あとで詳しく解説します。

　「⁠キーパーティショニング（Key⁠）⁠」とは、プライマリーキーに含まれるカラム、あるいは行全体を内部ハッシュ関数で生成した値により分割するパーティショニングのことです。パーティショニングキーが整数値型以外のとき利用します。

　「⁠レンジパーティショニング（Range⁠）⁠」とは、カラムの範囲を指定して分割するパーティショニングのことです。たとえば、ゲーム内部にチャットルームがあるとき、チャットの発言日時を必要な範囲で（日別、週別、月別など）分割します。（⁠HDDに余裕があるなら）すべての発言を残しておいても、WHERE句に発言日時での絞り込みを入れておけば、最新の発言しか入っていない小さな内部テーブルのみにアクセスします。また、保持期限で削除したい場合、DELETE文で削除するとインデックスの更新処理などが必要になるため非常に時間がかかりますが、レンジパーティショニングにしておけば、不要なパーティションをDROP文で捨て去ることができるため高速で処理できます。

　「⁠リストパーティショニング（List⁠）⁠」とは、明示的に存在するカラムの値ごとに分割するパーティショニングのことです。たとえば、多言語対応するゲームで、マスタテーブルにLanguageというカラムがあったとすると、このLanguageカラムがあるテーブルすべてをリストパーティショニングで分割します。こういうゲームの場合、Languageカラムにインデックス（複合キーを含む）を付けたり、最悪なものとしてはテーブル名にxxxxxx_jp、xxxxxx_enなどという対応をしていたりするということが結構あります。言語の種類は数が少ない（カーディナリティが低い）ため、インデックスはあまり効きません。オプティマイザーがインデックスの利用を非効率と判断する可能性も高く、せっかくインデックスを作っていても利用されないかもしれません。また、テーブル名を変えると、文字列連結をしてSQL文を構築する必要が出るため好ましくありません。しかし、リストパーティショニングにしておけばWHERE句に「Languege = 'jp'」などの絞り込みがあるだけで必要最小限のデータのみにアクセスできます。

ユーザIDでハッシュパーティショニング

　RDBMSを利用するほとんどのゲームは、ユーザごとのデータにアクセスすることが非常に多いと思われます。たとえば、ユーザがガチャを引いてキャラクターを集めていくというゲームで、キャラクターに対してアイテムを複数所持していくとします。この場合、第1回で解説したとおり、リスト1、図1下のようにあえてプライマリーキーをナチュラルキーにし、プライマリーキーの先頭にuser_idを追加することにより、特定のユーザが所有しているキャラクターやアイテムのデータが、ほぼ同じページに保存されることになります。

　それに加えて、ユーザIDごとに、それぞれ100個のパーティショニングをすることになります。この場合のハッシュパーティショニングでは、剰余、つまり、ユーザIDの下2桁ごとに分かれますので、ほぼ均等に100分の1になります。

　この状態でデータを入力してリスト2のSQLの実行計画をビジュアルではなくデータグリッド（Tabular Explain）で確認すると、図2のようにそれぞれのテーブルではなく、p34パーティションだけにアクセスしてデータを取得していることがわかります。処理のイメージとしては図3のようになります。

リスト2　3つのテーブルからuser_id = 41234のデータを取得する

SELECT *
FROM
    users a
    INNER JOIN user_characters b
        ON a.user_id = b.user_id
    INNER JOIN character_items c
        ON b.user_id = c.user_id
        AND b.user_character_id = c.user_character_id
WHERE
    a.user_id = 41234;

　つまり、今回は100個に分割しましたので、リスト2のSQLはユーザ数が100分の1だったとき（100倍のデータ量になっても、第1回で解説したとおりインデックスのブランチの深さは100倍にはならない）と同等のコストで処理できます。

パーティショニングの注意点

　ここまでで、ハッシュパーティショニングについて解説してきました。このパーティショニングでは、パーティショニング数の剰余で保存されるパーティションが決まりますから、同じキーが含まれるテーブルは必ず同じ個数でパーティショニング（リスト1の「PARTITIONS n」のnを同じ値にする）を行ってください。

　また、パーティショニングキー（ユーザID）以外で抽出しようとすると、100個に分割する場合、100個のパーティションを確認することになってしまいます。たとえば、ユーザグループ（ギルドやクランなどと呼ばれるもの）を作ってマルチユーザでプレイするゲームにおいて、ユーザグループごとの処理が増える場合を想定しましょう。このとき、ユーザグループIDがパーティショニングキーに入っていないと、ユーザグループごとの処理の際、人数の倍数に近いコストがかかります。このような場合は、ユーザグループでパーティショニングを行うほうが良いかもしれません。

　やみくもにパーティショニングキーを設定するとたいへんなデメリットもありますから注意してください。