　最近、この説明を複数回したので記事にする。

要約

　普段は 今北産業 派なのだが、3行考えるのが面倒なため、今後は大人の表現を使う。

「今北産業」をスタートアップ語にすると「マジ価値サマリー」になるらしい
ちなみにここだけの話ですが、大人語にすると「要約」になります pic.twitter.com/Q8SflvBX7c
— ところてん (@tokoroten) 2022年1月24日

画面に表示したい順(以下、表示順）は振る舞いの属性なので分ける
似たような振る舞いに関わる属性は別テーブルにわけると良い

　普通に正規化しましょうって話。

表示順をカラムを追加して表現する

　よくあるテーブルは画面情報と合わせて表示順カラムがあるパターン。こういうテーブルを作って 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

表示順はテーブルを分ける

　そこでテーブルは分けましょうという話になる。シンプルに交差テーブルを作れば良い。

f:id:Soudai:20220127112641p:plain

　検索クエリはこのような形になる。

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さんが以下のようなテーブルを作って対応している。

github.com

　これはラストクリックが取りたい、ラストログインが知りたい、など多くのケースで応用が効く。

参考情報 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

まとめ

　イージーとシンプルは違う。テーブルはシンプルに、スコープは小さくして振る舞いは閉じる。アプリケーションの設計と本質は変わらないし、そのために正規化がある。

そーだいなるらくがき帳

そーだいが自由気侭に更新します。

表示順という属性を別テーブルに分ける

要約

表示順をカラムを追加して表現する

表示順はテーブルを分ける

これって検索でも応用できるのでは？

参考情報 2022/01/27 14:20 追記

まとめ