皆さんこんにちは、エンジニアの西尾です。
新しい機能・サービスを開発する際、私は特にデータベース設計に気をつかいます。
データベースはシステムの土台です。 土台が不安定だと、その上に積み上げていくアプリケーションコードがいびつなものになり、つらい思いをします。 また、一度動き出してしまったシステムのデータベース設計を変えるのは、容易なことではありません。
データベース設計には”これだ！”という正解はないと思っています。 サービスの特徴、システムの性質、toB向け/toC向け、Readが多い・少ない、Writeが多い・少ない。 その他もろもろの背景により、データベース設計の仕方も変わってきます。
このテーブルは正規化していないから駄目だ、この設計はいわゆるポリモーフィック関連だから使ってはいけない、などということはありません。 アンチパターンと呼ばれるものも時と場合によっては正解になります。
今回は、食べチョクのデータベースを設計する際に気をつけていることを共有いたします。 なお、食べチョクではRDBとしてMySQL5.7を利用しています。
- 1. 制約をつける
- 2. 適切なデータ型を使う
- 3. 三角関係のリレーションを持つテーブルは(できるだけ)作らない
- 4. 一時的なレコードと永続化が必要なレコードを同じテーブルには入れない
- 5. 定期的に性能を見て設計を検討し直す
- 6. インデックスの性質を知り、適切に貼る
- 7. 正規化が必要なところ、不要なところを見極めてテーブル設計をする
- 8. リレーショナルデータベース(RDB)が苦手な表現を理解し設計する
- まとめ
1. 制約をつける
データベース設計において重要なのは、いかにして不整合を起こさないようにするかです。
「データを引いてみたら関連先のレコードが無くなっている」、「このレコードはユーザーごとに1つだけ持つはずだけど、2レコードある」など。 不整合は往々にして発生します。
データを挿入・更新・削除してもよいかのチェックはアプリケーションレベルで防ぐだけではなく、可能ならばデータベースレベルで行います。 そのために、以下制約をつける努力をします。
1.1 外部キー制約をつける
外部キー制約は、可能な限りつけるようにしています。 DBが別れている場合、外部キーはもちろん貼れないのですが、そうでない場合はとにかく何も考えず貼っています。
テスト時のテストデータが入れにくいから貼りたくない、とかいってる場合じゃないです。本番環境で不整合が起こる方が怖いですよね。
テストデータ入れるだけなら、 SET FOREIGN_KEY_CHECKS=0; とかでレコードいれればよいだけですし(本番環境ではやらないでください)。
1.2 ユニークキー制約をつける
ユニークキーも可能な限りつけるようにしています。
例えば以下のような注文(orders)テーブルと、支払い(payments)テーブルがあるとして、 注文に対する支払いは1つしか存在しないことがわかっている場合。
CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `orders_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) ENGINE=InnoDB ROW_FORMAT=dynamic DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE `payments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) NOT NULL, `transaction_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `payments_order_id_fk` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ) ENGINE=InnoDB ROW_FORMAT=dynamic DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
このときは、paymentsテーブルのorder_idにユニークキー制約をつけます。
CREATE UNIQUE INDEX `index_payments_on_order_id` ON `payments`(`order_id`);
そうすれば、注文に対する有効な支払いがなぜか2件できてしまっていた、というバグを未然に防ぐことができます。
別のパターンも見てみます。 例えば、paymentsテーブルに以下のようにactiveというカラムを足します。 有効な支払いは1つ(active=1の場合)だけだが、支払いを変更したなどで履歴情報として過去の支払いをactive=0として持っておきたい場合。
CREATE TABLE `payments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(1) NOT NULL DEFAULT '1', `order_id` int(11) NOT NULL, `transaction_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `payments_order_id_fk` (`order_id`), CONSTRAINT `payments_order_id_fk` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
この場合、(active, order_id)にユニークキー制約を貼るわけにはいきません。
支払いを2回変更したら、active=0が2レコードできてしまうのですが、そういうレコードは入れられません。
この場合は以下2つのうち、どちらかで修正します。
そもそもactiveとかいうカラムを持たず、別途payment_historiesなどのテーブルを作り、そちらにレコードを移す
履歴として取っておく必要があるのなら、元のテーブルにレコードを残しておくのではなく、別のテーブルに移動させます。 こちらの設計の方がスマートです。元のユニークキー制約を残したまま、レコードの行数も抑えられるし、paymentsを引くときの余計な条件式も減らせます。
activeをNULL許容にして、過去データは active=NULL としてデータを入れる
NULLは必ずユニーク扱いになります。そのため、activeカラムをnull許可することで、(active, order_id)にユニークキーを貼っていても、 (MySQLでは)以下のようにデータを入れることが可能です。
INSERT INTO payments(active, order_id, transaction_code, created_at) VALUES (1, 1, "AAA1", "2020-06-15 00:00:00"); INSERT INTO payments(active, order_id, transaction_code, created_at) VALUES (NULL, 1, "AAA2", "2020-06-15 00:00:00"); INSERT INTO payments(active, order_id, transaction_code, created_at) VALUES (NULL, 1, "AAA3", "2020-06-15 00:00:00");
この設計は賛否両論あると思いますが、これはNULLの正しい使い方ではないかと思っています。
別テーブルに分けるか、NULL許容して入れるか、実装コストを踏まえて選択しています。
1.3 NOT NULL制約をつける
なるべくNULLが入らないようにテーブルを設計しています。 例えば以下のようなテーブルがあったとします。
CREATE TABLE `something` ( `id` int(11) NOT NULL AUTO_INCREMENT, `note` varchar(255), `created_at` datetime NOT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB ROW_FORMAT=dynamic DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
このテーブルからnoteが空のレコードを抽出する場合、どういうSQLを書くでしょうか。
SELECT * FROM something WHERE note = "";
いやいや、もしかしたらnoteにはNULLが入っているかもしれません。
SELECT * FROM something WHERE note IS NULL OR note = "";
NULLが入っていることで、SQLが複雑化する可能性があります。 また、SQLの書き方を間違えると意図したレコードを抽出できない可能性があります。 そのため、極力NULLを入れなくて良い箇所はNOT NULL制約を入れるようにしています。
ただし、なんでもかんでもNULLを排除すれば良いというわけではありません。 例えば、以下のようにuser_idを持つテーブルがあるとします。
CREATE TABLE `something` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11), `created_at` datetime NOT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB ROW_FORMAT=dynamic DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
なんとしてもNULLを入れたくないのか、user_idデータが無い場合はuser_id=0としてレコードを作成する、
といったように無理やりダミーレコードをいれてNULL回避をする方がいます。
データが存在しないのならば、素直にNULLを入れるべきです。
余談ですが、datetime型に0000-00-00 00:00:00を入れるとえらい目にあうので注意が必要です。 ( 参照: https://soudai.hatenablog.com/entry/2018/05/12/191050 )
2. 適切なデータ型を使う
MySQL5.7では、https://dev.mysql.com/doc/refman/5.7/en/data-types.html にあるデータ型を利用できます。
本来数値しか入らないレコードはint型を使うなど、適切な型を設定するよう努力します。 数値カラムに全角数字が入っていた、そんなことはデータ型を正しく選択していれば起こり得ないことです。 なんでもかんでもvarcharで定義しないよう注意します。
以下、型を選ぶ時に気をつけていることを記載します。
- bool値を入れるカラムには tinyint(1)を利用する
- 長くない可変文字列を入れる場合(大体1024文字より下くらいかな？)はvarcharを利用します
- 長い可変文字列を入れる場合はtext型を利用する、またtext型を使う場合はテーブルのROW_FORMATがDynamic(Barracuda)であることを確認する
- MySQL5.6まではデフォルトのフォーマットはCompact(Antelope)でしたが、これは1レコードあたり8KBまでしかデータを入れることができません。
- テキスト型を使うと8KB制限を突破してしまうこともあるため、テーブルのフォーマットがDynamic(Barracuda)であることを確認します。
- 整数値を入れる場合はint型かbigint型を使う
- float型は使わない
- 精度のトラブルに巻き込まれたくないためfloatは使いません。多くの場合、doubleかdecimalで問題ありません。
- 金額情報など、精度を求められる小数値にはdecimalを使う
- doubleも小数点以下の精度に悩まされることがあります。金額を扱う、精度が必要な計算は必ずDecimalを利用します。
- 日付を入れる場合はDATE型を使う
- 商品のお届け日など、日付を入れる場合は DatetimeやTimestamp型ではなくDATE型を使うようにします。DatetimeやTimestampはタイムゾーンの影響を受けるためです。
- JSON型を使ったら負け
注意点として、数字に見えるデータを入れる際、必ずしもint型が正解ではない可能性もあります。 郵便番号(例: 0100011) とか、電話番号(例: 08000000000) は一見数値に見えますが、これはvarcharで保存すべきです。 なぜなら、数値の場合は先頭の0の値が抜け落ちてしまうためです。
3. 三角関係のリレーションを持つテーブルは(できるだけ)作らない
三角関係という表現が正しいかはさておき、下図ERのようなテーブルは(できるだけ)作らないようにしています。
この構造では、注文からグループを引く場合に注文 → ユーザー → グループというたどり方をした場合と、注文 → グループと
直接レコードを引いた際に違うレコードが取れる可能性はあります。
例えば、ユーザーが所属するグループが途中で変わった場合は、整合性が崩れる可能性があります。
三角関係にならないよう、以下のように設計を修正します。
性能問題により、注文からグループテーブルへのショートカットを貼る場合もあります。 その場合、ユーザーが所属するグループが変わった場合は、注文レコードが保持しているグループへのリレーションも貼り直す必要があります。 整合性が崩れやすいので、このような設計はできるだけしないようにしています。
4. 一時的なレコードと永続化が必要なレコードを同じテーブルには入れない
一時的に利用するレコードと、永続化すべきレコードは同じテーブルに入れないようにしています。
例えば、以下のような受注(orders)テーブルがあるとします。 これは SpreeというオープンソースのECプラットフォームのテーブル設計です。 （実際はもう少し複雑なのですが、簡略化しています）
Spreeにはカート機能があるのですが、ユーザーが一時的にカートに入れた注文も、その後実際に受注したデータも同じordersテーブルに入っています。
カートに入っている未発注の注文は、注文ステータスをcartという状態にして、関連テーブルと共にレコードを挿入してます。
確かにカートの情報をそのままordersに入れれば実装上は楽なのですが、この設計は性能に問題がでる可能性があります。
ユーザーがカートに入れたまま放置したら、ordersテーブルには不要なレコードがたまり続けることになります。
構造が同じでも意味が違うデータは分けて管理すべきだと、私は思います。 その方がレコードの行数も少なく抑えられて、注文テーブルを引くコストが下がります。
5. 定期的に性能を見て設計を検討し直す
データベースは生き物です。
日々データ量が増え続けていき、設計時には思いもよらないようなパフォーマンス問題にぶち当たることがあります。 一度設計したら終わりではなく、日々パフォーマンスを監視し、重いクエリがあればチューニングが必要です。 ときにはテーブル構造自体を見直す必要があるかもしれません。
昔はSlowQueryを吐き出してそれを見る、ということをしていましたが、 最近では性能を監視するためのツールが充実しています。 おすすめのツールはNewRelicとAWSのRDSパフォーマンスインサイトです。
NewRelicでは、下図のようにパフォーマンスを可視化してくれます。 例えば、以下を見るとDeliveryPossibilityテーブルを引くのに時間がかかっていますね。パフォーマンスチューニングが必要そうです。
AWSのパフォーマンスインサイトは、下図のように更に細かい単位でパフォーマンスの可視化をしてくれます。
6. インデックスの性質を知り、適切に貼る
パフォーマンスが出ないクエリは、インデックスを貼れば早くなる！わけではありません。 インデックスは銀の弾丸ではなく、性質を知り適切に貼る必要があります。
6.1 カーディナリティを考慮してインデックスを貼る
MySQLでインデックスを利用する場合、多くはBツリーインデックスを利用するかと思います。 Bツリーだろうがハッシュだろうが、カーディナリティの低いカラムにインデックスを張っても性能はでません。
カーディナリティは、入っている値の種類がどのくらいあるかを表しています。
例えば、レコードが1千万行あるテーブルがあり、activeという1と0の値を取るカラムがあるとします。
このactiveカラムのカーディナリティは2であり、ここにindexを貼っても多くの場合クエリは早くなりません。
逆にカーディナリティが1千万のカラム（すなわち重複の無いデータ）にindexを貼れば、早くデータを引いてこられる可能性があります。
カーディナリティを確認するには、show indexクエリを利用します。
以下のテーブルでは複合indexとして(faxable_type, faxable_id)にインデックスを張ってます。
faxable_typeはカーディナリティ2であり、(ユニークキー制約がある場合は別として）ここに貼る意味はあまりなさそうですね。
faxable_idはカーディナリティが高く、このカラムにインデックスを貼るのは正解です。
6.2 インデックスが効いているかをExplainで見る
インデックスが効いているかはExplainを使えばわかります。
Explainの解説をするとそれだけで長文がかけてしまうのでここでは省略します。
クエリにexplainをかけてtypeを見る、possible_keysに意図したインデックスが入っているか、引いてくるレコードの行(rows)が少なくなっているか(フルスキャンしていないか)、Using Indexがでているか、コストが高くないかなどを確認します。
MySQL Workbenchを使うと、グラフィカルにExplain結果が見られるのでおすすめです。
6.3 インデックスを貼る順序に気をつける
複合インデックスの場合は、インデックスを貼るカラムの順序も重要です。
(column1, column2, column3) という順に貼ったindexと、(column3, column2, column1) のように貼ったインデックスは別のものとして扱われます。
前者のindexを貼った場合、条件に(column1), (column1, column2), (column1, column2, column3)が入っている場合のみ、インデックスが有効です。
where column3=xxxxx のようにクエリを書いてもindexは使われないことに注意します。
6.4 インデックスショットガンをしない
インデックスを全部のカラムに貼ったら早くなるんじゃないか。 誰しもが思う疑問かもしれませんが、そんなに都合良くは行きません。
まずMySQLのインデックスは1クエリにつき基本的に1つしか利用できません。 (インデックスが1つだけ使われるということで、1つのカラムにだけ効く、という意味ではありません。複合インデックスは有効です。)
index1, index2をいい感じに合体して高速なクエリを作り出してくれる、そんな高度な機能は... インデックスマージという機能はなくはないのですが、基本は1つだけ使うと考えて良いでしょう。
またインデックスを貼るとテーブルの更新処理が遅くなり、ディスク容量も膨れ上がります。 それはそのとおりで、通常のレコードとは別にインデックス用のデータを作るわけですから、遅くなります。
すべてのカラムにインデックスを貼る、インデックスショットガンはしないようにします。インデックスを貼るにもコストがかかるのです。
7. 正規化が必要なところ、不要なところを見極めてテーブル設計をする
MySQLのjoinはとても遅いです。Oracleなら高速で返ってくるであろうクエリも、MySQLでは遅いです。
toC向けのRead負荷が高いページを表示するのに、joinを多用したクエリを実行するべきではありません。 アクセス負荷の高いページではjoinしたら負けです。理想は1テーブルのみのアクセスです。
そういう場合は、各種テーブルのデータをサマった正規化していないテーブルを別途作成し、そのテーブルにのみアクセスするようにします。 いわゆるマテビュー(マテリアライズド・ビュー)のようなものを別途作成することで、アクセスを高速にさばくことができます。
逆にtoB向けのページでは、できるだけ正規化したテーブルにアクセスするようにします。 こちらは整合性の崩れた（キャッシュのような)レコードにアクセスしてデータが間違っていた、という事件が起きるほうがリスクが高いです。
正規化する/しないはアクセスの性質・サイトの性質に合わせて柔軟に検討します。
8. リレーショナルデータベース(RDB)が苦手な表現を理解し設計する
RDBは万能ではありません。RDBでは表現が難しいデータ構造・表現も存在します。 以下はRDBで扱いにくいものの一例です。
- ツリー構造など階層を持ったデータ構造
- カラムが動的に変化するデータ構造
- GIS(地理情報)を扱う場合
- 全文検索が必要な場合
- データ量が多すぎる場合(アクセスログデータを入れて計算するなど)
ツリー構造(階層構造)を持ったデータは頻出ですが、RDBでは扱いにくいものの部類にはいります。 そのため、ツリーを表現するための設計パターンが存在します。 隣接リスト・Nested Set・クロージャーテーブルなどなど。どれも一長一短があります。それぞれの設計の利点欠点を理解し、適切なデータ構造を選びます。
カラムが動的に変化する構造も苦手です。GISも苦手、全文検索も苦手です。データ量が多すぎる場合もRDBでは対応できないかもしれません。
RDBに向いていないデータを入れる場合は、素直にRDB以外を選択したほうが良いでしょう。
まとめ
今回はデータベース設計を行う際に気をつけていることをまとめました。
システムの土台となるデータベースの設計は、とても重要です。 DBの寿命はアプリより長い といわれていますが、そのとおりだと思います。
あとでつらい思いをしないためにも、今後も慎重にテーブル設計をしていきたいものです。
