最近、この説明を複数回したので記事にする。
要約
普段は 今北産業 派なのだが、3行考えるのが面倒なため、今後は大人の表現を使う。
「今北産業」をスタートアップ語にすると「マジ価値サマリー」になるらしい
— ところてん (@tokoroten) 2022年1月24日
ちなみにここだけの話ですが、大人語にすると「要約」になります pic.twitter.com/Q8SflvBX7c
- 画面に表示したい順(以下、表示順)は振る舞いの属性なので分ける
- 似たような振る舞いに関わる属性は別テーブルにわけると良い
普通に正規化しましょうって話。
表示順をカラムを追加して表現する
よくあるテーブルは画面情報と合わせて表示順カラムがあるパターン。
こういうテーブルを作って SELECT * FROM items ORDER BY display_order_number;
で表示順に取り出すパターン。
表示順を変更したい場合は display_order_number
の値をUPDATEさせることで調整する。
item_id | name | display_order_number |
---|---|---|
1 | hoge | 1 |
2 | fuga | 2 |
3 | foo | 3 |
この設計で問題が出るパターンは ここから仕様を追加していくと破綻する ことにある。 例えば以下のような仕様を追加したらどうなるだろうか。
- itemにはカテゴリが複数あり、カテゴリ毎に表示したい順番がある
- itemが表示するページが複数あり、ページによって表示したい順番が違う
- itemは相当数のデータがあり、更に多くのテーブルの外部キー制約の親テーブルとして設定されている。
1と2は類似のパターンだがそれぞれが追加された場合に皆さんならどうするだろう?「よっしゃ!必要な分だけカラムを追加するぞ!」と考えることだけは絶対に辞めて欲しい。本当に辞めて欲しい。 3のパターンは昔、ユーザ情報の設計の際にも説明したが親テーブルに対する更新は正しくロックを取る必要がある。 そして正しくロックを取ると更新が直列になるので場合によってはパフォーマンスのボトルネックになる。
この3のパターンを防ぐために以下のようにデータを100刻みで保存し、itemの追加の際は101を設定するなどのパターンも見るがこれはロジックがどんどん難しくなっていくので同じように破綻する。
item_id | name | display_order_number |
---|---|---|
1 | hoge | 100 |
2 | fuga | 200 |
3 | foo | 300 |
表示順はテーブルを分ける
そこでテーブルは分けましょうという話になる。 シンプルに交差テーブルを作れば良い。
検索クエリはこのような形になる。
SELECT * FROM items INNER JOIN items_categories_order ico on items.item_id = ico.item_id WHERE ico.category_id = ? ORDER BY ico.display_order_number
前述であった表示場所の場合はcategoryの代わりに表示枠を管理するテーブルを作れば良い。 また必要に応じてユニーク制約を設定することで表示場所の重複を防ぐこともできる。
これって検索でも応用できるのでは?
検索も振る舞いの一つなので似たような設計ができる。 例えばisucon11の予選で出てくる 状態の履歴から最新の状態が欲しい 場合に最新の状態だけ保存したテーブルを用意し、JOINすることで問題を解決できる。 Window関数でも対応できるが、データが多い場合やパラレルクエリの無いRDBMSの場合など、多くのケースでパフォーマンスでメリットがあり、実際に自分のチームではyoku0825さんが以下のようなテーブルを作って対応している。
これはラストクリックが取りたい、ラストログインが知りたい、など多くのケースで応用が効く。
参考情報 2022/01/27 14:20 追記
MySQLの質問が来ていたので実際のMySQLの実行計画を記載する。
-- 14件登録済み CREATE TABLE `categories` ( `category_id` int(11) NOT NULL AUTO_INCREMENT, `name` text COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`category_id`), UNIQUE KEY `categories_category_id_uindex` (`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci -- 93827件登録済み CREATE TABLE `items_categories_order` ( `item_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `display_order_number` int(11) NOT NULL, PRIMARY KEY (`item_id`,`category_id`,`display_order_number`), KEY `items_categories_order_categories_category_id_fk` (`category_id`), CONSTRAINT `items_categories_order_categories_category_id_fk` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`), CONSTRAINT `items_categories_order_items_item_id_fk` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci -- 32214件登録済み CREATE TABLE `items` ( `item_id` int(11) NOT NULL AUTO_INCREMENT, `name` text COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`item_id`), UNIQUE KEY `items_item_id_uindex` (`item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
EXPLAIN SELECT * FROM items INNER JOIN items_categories_order ico on items.item_id = ico.item_id WHERE ico.category_id = 1 ORDER BY ico.display_order_number
実行計画
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | ico | NULL | ref | PRIMARY,items_categories_order_categories_category_id_fk | items_categories_order_categories_category_id_fk | 4 | const | 6600 | 100 | Using where; Using index; Using filesort |
1 | SIMPLE | items | NULL | eq_ref | PRIMARY,items_item_id_uindex | PRIMARY | 4 | common-web.ico.item_id | 1 | 100 | NULL |
まとめ
イージーとシンプルは違う。 テーブルはシンプルに、スコープは小さくして振る舞いは閉じる。 アプリケーションの設計と本質は変わらないし、そのために正規化がある。