ゲームを題材に学ぶ 内部構造から理解するMySQL

第1回 DBサーバの構造を知ろう!

この記事を読むのに必要な時間:およそ 4 分

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

ゲーム開発におけるRDBMSの役割

 RDBMS(Relational DataBase Management System)は業務システム(以下,業務系)で多くの実績を作ってきました。一方でゲーム分野(以下,ゲーム系)では,RDBMSはほとんど使われていませんでした。ネットゲームではなく,スタンドアローンのゲームが中心だったので,プレーンなテキストやバイナリ形式でデータを保存していたからです(スタンドアローン環境向けのSQLiteもまだ普及していませんでした⁠⁠。しかし,ネットゲームの流行に伴い,現在ではゲーム系でもLAMP環境注1で開発されることが多くなっています。ゲーム系の開発でRDBMSが使われ始めたころは,O/Rマッパー(Object Relational Mapper。以下,ORM)が広く使われていましたから,最初からORMを利用して手続き型(オブジェクト指向)言語の感覚でRDBMSをストレージのように使うことができました。そのためRDBMSを深い理解をしないまま使っているエンジニアも多いのではないでしょうか。

注1
Linux,Apache HTTP Server,MySQL,PHPを組み合わせたソフトウェア開発環境の総称です。

 話が変わりますが,以前『Software Design』誌で連載していた「RDB性能トラブルバスターズ奮闘記」を再構成した『SQLの苦手を克服する本』という書籍が2019年8月に発刊されます。この連載や書籍では業務系でのトラブルを中心に解説しましたので,ゲーム系で重要な事柄について言及していないこともあります。そのため本稿ではゲーム系のエンジニアがRDBMSについて勘違いしていることが多いポイントについて,MySQLを中心に解説します注2。Software Designの連載でもそうでしたが,本稿は概念的に理解すること,それが目的ですので,詳しいコマンドの解説は省いています。また,業務系やWebサービスを作られているエンジニアも対象にしています。

注2
とくに断りがない限りInnoDBを対象とします。

 本稿のサンプルはMySQL 8.0.15を利用しました。サンプルソースのデータは,MySQLのサンプルデータベース注3 world databaseを使います(件数などはデータを取得した時期によって前後する可能性があるのでご了承ください⁠⁠。

注3
MySQLのWebサイトからダウンロードしてください。

I/Oの単位

 SQLは抽象度が非常に高いため,RDBMSの低レイヤの部分は隠蔽されていて,その深い部分のしくみを知らなくても使えます。しかし,ゲーム系のエンジニアは低レイヤの仕事をこなすことが多いので,本稿ではそうした部分から解説します。

 抽象度が高いSQLをさらにORMでラップする場合を想定してみましょう。このとき,コード上はレコード単位で処理されているように感じるかもしれません。しかし,実際には,RDBMSはページまたはブロックと呼ばれる決まった大きさ(以下,ページ)の単位ごとにデータを読み書きします。RDBMSのページのイメージは図1のようになっています。

図1 RDBMSのページのイメージ

RDBMSのページのイメージ

 この図を見ればわかると思いますが,RDBMSのI/Oの最小単位はレコード単位ではなくページ単位なのです。なお,MySQLの場合,デフォルトのページサイズは16KBとなっているうえ,ほとんどのシステムでデフォルトのページサイズのままMySQLが使われているようです。

 もちろん,Oracle Database(以降,Oracle)などテーブルスペース(表領域)ごとにページサイズを変更できるRDBMSはあります。しかし,MySQLの場合,インスタンスを作りなおさなければページサイズも変更できません。そのため,Amazon Relational Database Service(以下,RDS)のようなインスタンスを共有するサービスでMySQLを利用する場合,ページサイズを変更することはできません。

 いずれにしても,主記憶装置がHDDである場合,このような「可変長のレコードをいかに効率よく扱うか」という課題を解決するために作られたのが,ページ単位のI/Oという処理構造なのです。

 本稿とは直接関係のない話ですが,HDDの存在を意識する必要のないインメモリDB注4ではレコード単位でI/Oができます。このインメモリDBのI/Oは,キャッシュヒット率が99%もあるようなRDBMSのI/Oよりも10倍以上速くなるということが多いです。その理由はメモリのI/Oが高速だからということではありません。RDBMSのキャッシュヒット率が99%であるなら,HDDのI/O速度は残りの1%だけなので,速度差にはほとんど関係ないはずです。本当の理由は,インメモリDBのI/O単位はレコード単位であるため,ページ単位でI/Oが起こるRDBMSに比べ,無駄なI/Oが軽減されるということにあります。

注4
HDDディスクやSSDなどのストレージデバイスではなく,メインメモリ上でデータを保存するデータベースのことです。

MyISAMについて

 MySQLでは,テーブルごとにストレージエンジンを切り替えることができます。デフォルトはInnoDBですが,MyISAMというISAM形式のストレージエンジンも利用されることが多いです。この,ISAM(Indexed Sequential Access Method)という形式は,COBOL時代から使われている古典的なデータ保存形式です。ISAM形式では,ページ単位でデータが保存されるInnoDBと違って,固定長のシーケンシャルファイルとしてデータが保存されます。MyISAMでは可変長のレコードも扱えるように拡張はされていますが,レコード長を超えたデータは,別のエリアに保存するという方式になっています。つまり,レコードのフラグメンテーションが起きるのです。

 また,MyISAMの更新処理には,

  • トランザクション処理がない
  • テーブルロックがかかってしまう

などといった制約があるため,ゲーム系のような,マルチユーザ環境で激しく更新処理が行われるシステムへの利用は困難です。しかし,更新が行われないマスタデータに利用すると高速化できる可能性があります。MyISAMを利用するときは,構造をよく理解して利用してください。

プライマリーキー(クラスタードインデックス)

 MySQLでは,プライマリーキーはデフォルトで図2のようなクラスタードインデックスという形式で格納されます。

図2 クラスタードインデックス

図2 クラスタードインデックス

 クラスタードインデックスにおいては,最上層の(1)はルートと呼ばれます。中間の(2)はブランチと呼ばれます。図2では1段しかありませんが,データが増えれば複数段になります。また,⁠3)の最下層はリーフと呼ばれます。クラスタードインデックスではこの(3)の部分が実データになっています。

 リスト1のSQLのように,WHERE句にプライマリーキーを指定してデータを1件だけ読み取るときを考えてみましょう。

リスト1 プライマリーキーを指定

SELECT * FROM city WHERE ID = 1532;

 仮にレコード長が30Byteしかない場合であっても,クラスタードインデックスでは少なくとも(1⁠⁠,⁠2⁠⁠,⁠3)の3ページ(16KB×3=48KB)ぶん読み込む必要があります。

 クラスタードインデックスは実データがキーの順番に並んで保存されます。それを利用したお勧めのテーブル設計があります(ただし,コーディング量は増えます⁠⁠。

 それは,⁠プライマリーキーに複合キーを指定する」というものです。あくまで筆者は「プライマリーキーに複合キー注5やナチュラルキー注6を使わないほうが良い」という立場ですが,ゲーム系でMySQLを利用するなら,あえて複合キーにするほうがI/O処理が効率的になる可能性が高いです。たとえば,ユーザがキャラクターを複数所持していることを想定するとリスト2のようなテーブルとプライマリーキーになります。

注5
複合キーとは,2つ以上のカラムから構成されるようなプライマリーキーのことです。
注6
ナチュラルキーとは,すでに存在する項目のキーが指定されたプライマリーキーのことです。

リスト2 プライマリーキーに複合キーを指定するテーブル設計の例

CREATE TABLE user_characters(
     user_id int NOT NULL DEFAULT 0           COMMENT 'ユーザID'
   , user_character_id int NOT NULL DEFAULT 0 COMMENT 'ユーザキャラクターID'
   , character_id int NOT NULL DEFAULT 1      COMMENT 'キャラクターID'
-- …(中略)…
   , PRIMARY KEY CLUSTERED(user_id, user_character_id )
)   COMMENT = 'ユーザ所有キャラクター';

 こちらの例では,ユーザIDをプライマリーキーの最初に配置することで,ユーザID順に並んで保存されます。そのため,user_charactersのユーザの情報は,同じページに配置される可能性が高くなり図3上⁠,I/Oが軽減されるというわけです。

図3 プライマリーキーを複合キーにした場合とサロゲートキーにした場合の違い

図3 プライマリーキーを複合キーにした場合とサロゲートキーにした場合の違い

 逆に,user_charactersテーブルにAUTO_INCREMENTを指定したサロゲートキーを利用したり,キャラクターIDをユーザIDより先にプライマリーキーとして配置したりするときを考えてみましょう。こういった場合,特定のユーザが10個のキャラクターを所持していると,user_charactersのデータは10ページに分散されて保存される可能性が高くなります図3下⁠。すなわち,ユーザIDをプライマリーキーの先頭に配置したときと比べると,所持キャラクターを取得する処理で数倍以上の負荷がかかることになります。

 I/O性能を考えるのであれば,同時に使うことが多いデータはできる限り同じページに保存されるように工夫しましょう。

著者プロフィール

生島勘富(いくしまさだよし)

株式会社ジーワンシステム 代表取締役。
フリーランスのエンジニアを経て,2003年に株式会社ジーワンシステムを創業する。その後,プレイングマネージャーとして多くのシステム開発に従事し,現在ではデータベースを中心としたコンサルティングを行っている。
メール:info@g1sys.co.jp
Webサイト:http://www.g1sys.co.jp/
ブログ:https://sikushima.hatenablog.com/ Twitter:@Sikushima