Open Source Web Development Tutorials - Dev Shed

MySQLクエリー最適化およびスキーマ設計
（2009/01/26公開）

スキーマ設計

　データベースの基本設計概念は、スキーマで表される。その中にデータベースの構造にかかわる重要な指針が含まれる。リレーショナルデータベースにはテーブルが含まれ、各テーブル内でフィールドも指定される。もちろん、それら相互のリレーションシップも含まれる。

　有用なスキーマを設計するのは極めて重要なことだ。とはいえ、使いやすさはデータベース管理者が設計するときに考慮に入れる要素の1つにすぎない。もちろん、使いやすさは欠かせない。でないとデータベースとは呼べない。だが、同じほど重要な要素がもう1つあることを忘れてはいけない。パフォーマンスだ。データベース管理業務に携わるようになって日の浅い管理者の多くは、スキーマが使いやすければその設計でよいと考える。これは賢いアプローチではない。

　むしろ、スキーマを設計しながら、実現するパフォーマンスも念頭に置く必要があると筆者は考える。データベース管理者が責任を持つパフォーマンス指針には、クエリーが実行される方法、ディスク入出力、MySQLおよびDBMSが機能する仕様（限界、特徴、可能性など）などが関連している。

　データベースの正規化は実に驚くべき成果を挙げるが、そのためには一般的なガイドラインに従う必要がある。要約すると、まず正規化し、あとで非正規化して行く、ということだ。ぜひとも避けていただきたいのは、極端に正規化すること、正規化をやり過ぎるということだ。ある程度シンプルにするが、必要以上にはそうしない。必要以上にシンプルにすると、データベース全体が複雑になり過ぎ、結局はパフォーマンスが低下する。

　前述のとおり、スキーマを設計するときにクエリー（少なくとも要件に含まれるもの）を考慮に入れること。データタイプは必要以上に大げさにしない。可能な限り、smallintあるいはtinyintさえ使おう。何にでも対応できるからと言ってbigintに飛び付いてはいけない。これに伴うパフォーマンスヒットは割に合わない。

　スキーマを設計する際にはいくつかの質問に対する答えを出す必要がある。しかし、ここでも、どれが「必要な」質問なのかを吟味すべきだ。いくつかの例を挙げる。データはどこから来るか。このデータベースでユーザーは何を成し遂げたいか。どんなファクトを測定する必要があるか。時間の経過に伴って容量が変化するか。ファクトテーブルファミリーが必要か。さらに詳しくは、このガイドを参照いただきたい。

　また、データベース管理者は、IPv4アドレスを符号なしデータタイプ（int）として保存することを勧めている。特定の要件に対してデータタイプを割り当てたり指定したりする前に必ず熟考すること。例えば、保存量を減らすためにシンプル化する方法があるか、と問う。

　最後に、重要なこととして、「大きな」テーブルを複数の「小さな」テーブルに分割するのは費用効果の高い次善策である。大きなテーブルとは、行や列が多いものを指す。行や列が何十もある文字通り大きなテーブルを作成するより、むしろ読者のニーズを細かい項目に適応させ、さまざまなテーブルに分割するとよい。こうした努力は、パフォーマンスや明りょう度の向上という形で十分に報われる。

　技術的な詳細は割愛するが、テーブルをパーティションすると、パフォーマンスが向上する。なぜなら、バッファページに不必要なデータ（巨大なテーブル全体を保存している、など）が詰まっていないからだ。そのため、ディスク入出力も最小化されたり、またご想像のとおり、シークタイムが短縮されるゆえに応答時間が改善したりする。

　それから、先ほども触れたが、明りょう度という利点がある。巨大なテーブルのトラブルシューティングを行っているところを想像していただきたい。筆者はそんなのご免だ。問題の原因がどのテーブルにあるかがすぐ分かって、そのテーブルに集中すれば済むほうがどれほどよいだろう。