論理型の設定値を RDB に保存する場合の選択肢と各々のメリット・デメリット

2017-07-06

Rails
MySQL

ユーザごとに特定の機能に対して ON/OFF の設定値を持たせることはよくあると思います。
RDB にそのような設定情報を持たせる場合の選択肢として大きく次の 5 つが考えるんじゃないかと思います。

設定項目ごとにカラムを割り当てる
設定項目ごとにレコードを割り当てる（追記：アンチパターンという意見があるので最後に補足を書きました）
設定項目ごとにテーブルを割り当てる（自分は思い付かなかった）
設定項目ごとに 1 つの整数型カラムの 1 bit を割り当てる
1 つのカラムに JSON 等で全ての設定情報を持たせる

データベース理論的には 1, 2, 3 以外の選択肢はない気がしますが、実用上は 4, 5 も良い選択となることがあるので、メリット・デメリットを考えて選択する必要があると思います。

そんなわけで、それぞれの選択肢に関してメリット・デメリットを自分なりに考えてみました。

考慮すべき内容

まず、メリット・デメリットを考える上で、次のような内容を考慮する必要があるのかなと思います。

設定項目は増えたり減ったりするかどうか
設定項目名は変わる可能性があるかどうか
- locale で対応すべき問題な気がする…
スキーマの変更は容易かどうか
デプロイなしで設定項目の追加・削除や設定項目名の変更を行いたいかどうか
ON（または OFF）になっているレコードをインデックスを使って取得する必要があるかどうか
デフォルト ON の項目を追加する可能性があるかどうか
テーブルサイズを抑えたいかどうか
テーブルだけでデータの内容を理解できる形にしたいかどうか
テーブルの制約に頼りたいかどうか
アプリケーションコードをシンプルにしたいかどうか

早見表

それぞれの設計に対して主観で早見表を作ると次のようになりました。MySQL 5.7 を想定していますが、他の RDB でもだいたい同じ結果になると思います。
要件によっては要らない項目や重要度の高い項目もあるので、◯が多ければ良いというわけでは決してないです。

項目	1. カラム	2. レコード	3, テーブル	4. 整数型カラム	5. JSON
設定項目の追加	△	◎	◎	◯	◯
設定項目の削除	△	◎	◎	◯	△
設定項目名の変更	△	◎	△	◯	△
スキーマ変更の要否	要	否	否	否	否
デプロイなしの変更	△	◯	X	X	△
インデックスが使えるか	◯	◯	◯	△	X
デフォルト ON の項目の追加しやすさ	◎	◯	◯	△	△
テーブルサイズ	◎	◯	◯	◎	△
データの理解しやすさ	◎	◯	◯	△	◎
テーブルの制約	◯	◯	◯	X	X
アプリケーションコードのシンプルさ	◯	△	△	△	◯

それでは、user_settings というテーブルがあり、メールマガジンを受け取る、プッシュ通知を受け取るという 2 つの論理型設定情報を保持するケースを題材にそれぞれの観点で見ていきます。

1. 設定項目ごとにカラムを割り当てる

次のようなテーブル定義になると思います。

CREATE TABLE user_settings (
  user_id int unsigned NOT NULL PRIMARY KEY,
  subscribe_mailmagazine tinyint(1) DEFAULT 0 NOT NULL,
  receive_push_notification tinyint(1) DEFAULT 0 NOT NULL
);

設定項目の追加

カラムを追加で実現できます。

ALTER TABLE user_settings ADD receive_promotion_mail tinyint(1) DEFAULT 0 NOT NULL AFTER receive_push_notification;

設定項目の削除

カラムの削除で実現できます。

ALTER TABLE user_settings DROP receive_push_notification;

ただ、Rails の場合は LEFT JOIN が絡むと SELECT 文にキャッシュしているカラム名を列挙するので、削除前に見えなくしておかないとエラーになることがあります。
cf. Rails Migrations with Zero Downtime - via @codeship | via @codeship

設定項目名の変更

カラム名の変更で実現できます。

ALTER TABLE user_settings CHANGE receive_push_notification subscribe_push_notification tinyint(1) DEFAULT 0 NOT NULL;

ただ、たいていの場合は次のような手順が必要になると思います。Rails を想定していますが、他のフレームワークでも似たような手順になると思います。

別名カラムを追加
アプリケーション側で更新の際に既存のカラムと別名カラム両方に値を反映するようにする
既存のカラムの値を別名カラムに付与する
既存のカラムを削除
- 「設定項目の削除」参照

スキーマ変更の要否

いずれの作業を行うにしてもスキーマ変更が伴うので、スキーマ変更をするためにサービスを止める必要があるような場合は 1 カラムに設定値を持たせるのは厳しいかもしれません。もちろん追加・削除がなければ問題になりませんが。

デプロイなしの変更

「設定項目の削除」や「設定項目名の変更」でも言及しましたが、フレームワークがカラム情報をキャッシュするようになっていると色々不具合があると思います。デプロイとまではいかなくても、再起動はした方が良いでしょう。

インデックスが使えるか

2 値しかないのでカーディナリティは低いですが、一部のユーザしか設定を ON にしていないような選択性が高い場合はインデックスが効きます。

CREATE INDEX ix_subscribe_mailmagazine  ON user_settings (subscribe_mailmagazine);

-- メールマガジンを購読しているユーザの ID を取得
SELECT user_id FROM user_settings WHERE subscribe_mailmagazine = 1;

デフォルト ON の項目の追加しやすさ

追加するカラムのデフォルト値を 1 にすることで実現できます。

ALTER TABLE user_settings ADD receive_promotion_mail tinyint(1) DEFAULT 1 NOT NULL AFTER receive_push_notification;

テーブルサイズ

大量の設定項目があれば別ですが、tinyint(1) であれば 1 つの値を保持するのに 1 byte なので大した問題にはならなさそうです。

データの理解しやすさ

カラム名が内容を直接表しているのでわかりやすいですね。

テーブルの制約

不正な設定項目を追加できないかという観点だと、カラムに定義されている項目以外は設定しようがないです。

アプリケーションコードのシンプルさ

カラムの値を利用するだけなのでシンプルですね。

2. 設定項目ごとにレコードを割り当てる

次のようなテーブル定義になると思います。サロゲートキーを導入するか複合キーを主キーにするかはお好みで。

CREATE TABLE user_settings (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id int unsigned NOT NULL,
  user_setting_item_id tinyint unsigned NOT NULL,
  flag tinyint(1) DEFAULT 0 NOT NULL
);
CREATE UNIQUE INDEX ux_user_id_user_setting_item_id  ON user_settings (
  user_id,
  user_setting_item_id
);

設定項目名はアプリケーション側で

{ subscribe_mailmagazine: 1, receive_push_notification: 2 }

のような情報を持つか、次のようなテーブルを作成する必要があります。

CREATE TABLE user_setting_items (
  id tinyint(3) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL
);

user_settings の flag カラムをなくして、レコードが存在すれば ON、存在しなければ OFF とみなすことも考えられますが、ここでは flag カラムが存在し、user_setting_items テーブルを作成する前提にします。

設定項目の追加

user_setting_items にレコードを追加することで実現できます。

INSERT INTO user_setting_items (name) VALUES ('receive_promotion_mail');

設定項目の削除

user_settings から削除したい設定のレコードを削除し、user_setting_items からも削除します。user_setting_item_id にインデックスが張ってないととても重い処理になります。インデックスが張ってあっても、レコード数によっては負荷軽減のために一定量ずつ削除することになるかもしれません。

DELETE FROM user_settings WHERE user_setting_item_id = 2;
DELETE FROM user_setting_items WHERE id = 2;

設定項目名の変更

user_setting_items.name を更新することで実現できます。

UPDATE user_setting_items SET name = 'subscribe_push_notification' WHERE id = 2;

スキーマ変更の要否

スキーマ変更せずに設定項目の追加・削除、設定項目名の変更が可能です。

デプロイなしの変更

もちろんアプリケーションの実装次第ですが、上手く実装すればデプロイなしで設定項目の追加・削除、設定項目名の変更が可能です。

インデックスが使えるか

「1. 設定項目ごとにカラムを割り当てる」同様、カーディナリティは低いですが、選択性が高ければインデックスが使えます。

CREATE  INDEX ix_user_setting_item_id_flag  ON user_settings (
 user_setting_item_id,
 flag
);

-- メールマガジンを購読しているユーザの ID を取得
SELECT user_id FROM user_settings WHERE user_setting_item_id = 1 AND flag = 1;

デフォルト ON の項目の追加

項目を増やした上で、全ユーザのレコードを作成する必要があります。

INSERT INTO user_setting_items (name) VALUES ('receive_promotion_mail');
-- receive_promotion_mail の ID が 3 の場合
INSERT INTO user_settings (user_id, user_setting_item_id, flag) SELECT id, 3, 1 FROM users;

次の手順になると思います。

user_setting_items に項目を追加
アプリケーション側や RDB のトリガーで users レコードの追加の際に user_settings にもレコードを追加するように変更
user_settings に存在しないレコードだけ挿入

テーブルサイズ

サロゲートキーを導入すると ix_user_id_user_setting_item_id インデックスの容量が上乗せされるのと、設定項目が多くなると最大でユーザ数 x 設定項目の数だけレコードができるので、ユーザの増加ペースや設定項目の増加ペースはある程度考慮して採用した方が良いかもしれません。

データの理解しやすさ

user_setting_items があると user_setting_item_id がそれぞれ何を意味しているか容易に理解することができます。user_setting_items の内容を確認したり user_setting_items と JOIN しないといけないのが面倒と感じることもあるかもしれませんが。

テーブルの制約

不正な設定項目を追加できないかという観点では、参照整合性制約を入れることで解決します。

ALTER TABLE user_settings ADD CONSTRAINT fk_user_settings_user_setting_items
  FOREIGN KEY (user_setting_item_id)
  REFERENCES user_setting_items (id);

アプリケーションコードのシンプルさ

複数の設定値を参照する際に何も考えないと N+1 になる
「レコードが存在しない、または flag が 0 の場合に OFF とみなす」みたいな場合は ON にする場合にレコードを作成しなければならない

みたいなことがあり、若干扱いが面倒です。例えばこんな感じの module があると扱いやすいかもしれません。

3. 設定項目ごとにテーブルを割り当てる

CREATE TABLE user_subscribe_mailmagazines (
  user_id int unsigned NOT NULL PRIMARY KEY,
  flag tinyint(1) DEFAULT 0 NOT NULL
);
CREATE TABLE user_receive_push_notifications (
  user_id int unsigned NOT NULL PRIMARY KEY,
  flag tinyint(1) DEFAULT 0 NOT NULL
);

設定項目の追加

新しいテーブルの導入で実現できます。

CREATE TABLE user_receive_promotion_mails (
  user_id int unsigned NOT NULL PRIMARY KEY,
  flag tinyint(1) DEFAULT 0 NOT NULL
);

設定項目の削除

テーブルの削除で実現できます。

DROP TABLE receive_push_notifications;

設定項目名の変更

テーブル名の変更で実現できます。

RENAME TABLE user_receive_push_notifications TO subscribe_push_notifications;

ただ、現実的には次の手順になる気がします。

同じ定義で別名のテーブルを作成
アプリケーション側で更新の際に既存のテーブルと別名テーブル両方に値を反映するようにする
既存のテーブルの内容を別名テーブルにコピー
アプリケーション側で別名テーブルを参照するように変更
既存のテーブルを削除

スキーマ変更の要否

テーブルの作成と削除だけなので不要ですね。

デプロイなしの変更

普通の Rails のように 1 テーブル 1 モデルを割り当てて使う場合はアプリケーションコードの修正とデプロイが必須です。

インデックスが使えるか

「1. 設定項目ごとにカラムを割り当てる」とほぼ同じなので割愛します。

デフォルト ON の項目の追加しやすさ

テーブルを追加した上で、全ユーザのレコードを作成する必要があります。

CREATE TABLE user_receive_promotion_mails (
  user_id int unsigned NOT NULL PRIMARY KEY,
  flag tinyint(1) DEFAULT 0 NOT NULL
);
INSERT INTO user_receive_promotion_mails (user_id, flag) SELECT id, 1 FROM users;

次の手順になると思います。

テーブルを作成
アプリケーション側や RDB のトリガーで users レコードの追加の際に新しいテーブルにもレコードを追加するように変更
新しいテーブルにレコードが存在しないユーザのレコードを挿入

テーブルサイズ

user_id の分だけ余分に容量を使うことになります。

データの理解しやすさ

「1. 設定項目ごとにカラムを割り当てる」と同等の理解しやすさがあると思います。全ての設定項目を一度に閲覧したい場合は JOIN しないといけませんが。

テーブルの制約

不正な設定項目を追加できないかという観点ではテーブルが存在する項目以外は設定しようがないです。

アプリケーションコードのシンプルさ

1 SQL で全ての設定情報を取得するためには LEFT JOIN をしないといけない
「レコードが存在しない、または flag が 0 の場合に OFF とみなす」みたいな場合は ON にする場合にレコードを作成しなければならない

みたいな配慮が必要ですが、「2. 設定項目ごとにレコードを割り当てる」に比べると複雑な処理にならない気がします。

4. 設定項目ごとに 1 つの整数型カラムの 1 bit を割り当てる

Ruby だと flag_shih_tzu や bitfields がこの機能を提供しています。
テーブル定義は次のようになります。

CREATE TABLE user_settings (
  user_id int unsigned NOT NULL PRIMARY KEY,
  flags int unsigned NOT NULL
);

頑張れば項目名をテーブルで管理することもできますが、flag_shih_tzu のようにアプリケーションコードに次のような情報を持つことにします。

{
  1 => :subscribe_mailmagazine     # 1 bit 目
  2 => :receive_push_notification  # 2 bit 目
}

設定項目の追加

アプリケーションコードの変更で実現できます。

{
  1 => :subscribe_mailmagazine     # 1 bit 目
  2 => :receive_push_notification  # 2 bit 目
  3 => :receive_promotion_mail     # 3 bit 目
}

設定項目の削除

アプリケーションコードに変更を加え、削除する項目の値を初期化します。

{
  1 => :subscribe_mailmagazine     # 1 bit 目
}

-- 2 bit 目だと 1 << 1、3 bit 目だと 1 << 2
UPDATE user_settings SET flags = (flags & ~(1 << 1));

ただ、これには落とし穴があって、アプリケーション側でレコードを取得する際にロックしないと

アプリケーションがレコードを取得
設定項目削除のために UPDATE 発行
アプリケーションがレコードを更新

という手順によって 2 の変更がなかったことになる場合があります。
Rails の場合はレコードを取得する際に lock メソッドを使いましょう。

設定項目名の変更

アプリケーションコードの変更で実現できます。

{
  1 => :subscribe_mailmagazine       # 1 bit 目
  2 => :subscribe_push_notification  # 2 bit 目
}

スキーマ変更の要否

アプリケーションコードの変更だけで大丈夫なので、スキーマ変更は不要です。

デプロイなしの変更

アプリケーションコードを変更する必要があるのでデプロイ必須です。　

インデックスが使えるか

設定項目が少なければ IN 句に目的の設定項目の bit が立っている値を列挙することで一応使えます。flag_shih_tzu が追加する scope はデフォルトでこの挙動です。

CREATE  INDEX ix_flags  ON user_settings (flags);

-- メールマガジンを購読しているユーザの ID を取得
SELECT user_id FROM user_settings WHERE flags IN (1, 3);

ただ、項目数が 10 とかになると 2⁹ = 512 個の値を列挙しないといけないので、決して効率的とは言えません。
ビット演算を使うとインデックスは効かなくなります。

SELECT user_id FROM user_settings WHERE flags & (1 << 0) != 0;

デフォルト ON の項目の追加しやすさ

アプリケーションコードに変更を加え、追加する項目の bit が立つようにレコードを更新します。

{
  1 => :subscribe_mailmagazine     # 1 bit 目
  2 => :receive_push_notification  # 2 bit 目
  3 => :receive_promotion_mail     # 3 bit 目
}

-- 2 bit 目だと 1 << 1、3 bit 目だと 1 << 2
UPDATE user_settings SET flags = (flags | (1 << 2));

次の手順になると思います。

アプリケーションコードの設定項目を追加し、users レコードの追加の際の flags のデフォルト値も変更する
UPDATE クエリを発行

「設定項目の削除」で述べたようにレースコンディションを避けるための配慮が必要です。

テーブルサイズ

1 カラム（高々 8 bytes）しか使わないので最もテーブルサイズが小さくなると期待できます。

データの理解しやすさ

非常に理解しづらいです。何 bit 目が何の項目を表している覚えていたとしても bit 演算しないと常人には理解できません。bit と項目の対応付けがわからなければアプリケーションコードを読む必要があります。

テーブルの制約

テーブル側では何の制約も課すことができません。PostgreSQL であれば CHECK 制約で頑張ることもできるかもしれませんが。

アプリケーションコードのシンプルさ

flag_shih_tzu 等を使うと直観的に扱うことができます。自前で書いても意外にシンプルに書けるかもしれません。
cf. https://gist.github.com/abicky/af5cbe94253ed79b7a8556932834da43

また、1 カラムで複数の値を扱っているため、レースコンディションへの配慮が必要になってくるかもしれません。

5. 1 つのカラムに JSON 等で全ての設定情報を持たせる

次のように TEXT 型のカラムを用意し、JSON を保存します。

CREATE TABLE user_settings (
  user_id bigint unsigned NOT NULL PRIMARY KEY,
  settings text
)

Rails の ActiveRecord には serialize というメソッドがありますが、次のように定義するイメージです。

serialize :settings, JSON

JSON の形式として ON の項目だけを保持する配列か ON/OFF 両方保持するハッシュが考えられますが、ここでは配列で保持することにします。

なお、導入を検討する際には ActiveRecord serialize / store の甘い誘惑を断ち切ろう - Qiita にも目を通しておくと良いと思います。

設定項目の追加

アプリケーションコードに変更を加えて JSON に項目を追加します。

設定項目の削除

アプリケーションコードに変更を加えて、JSON に削除対象の項目が追加されないようにし、その後既存レコードの JSON の内容を修正します。
SQL で修正するなら次のようなクエリでしょうか。

UPDATE
  user_settings
SET
  settings = REPLACE(
               REPLACE(
                 REPLACE(settings, ',"receive_push_notification"', ''),
                 '"receive_push_notification",',
                 ''
               ),
               '"receive_push_notification"',
               ''
             )
FROM
  user_settings
;

また「4. 設定項目ごとに 1 つの整数型カラムの 1 bit を割り当てる」の「設定項目の削除」同様レースコンディションが起きる可能性があります。

設定項目名の変更

次のような手順になると思います。

アプリケーション側で更新の際に既存の項目と別名の項目に値を反映するようにする
既存のレコードの JSON に既存の設定内容と同じ値で別名の項目も追加する
アプリケーション側で別名の項目を参照するように変更
「設定項目の削除」の手順で既存の項目を削除

スキーマ変更の要否

TEXT 型にデータが入っているだけなのでスキーマ変更不要です。

デプロイなしの変更

いずれにしてもアプリケーションコードの変更が必要ですが、設定項目としてフリーキーワードを受け付けるようにすればデプロイなしで任意の項目を追加可能です。その場合、XSS 等の配慮も必要になってきます。
どうしても自由入力を受け付けるようにする場合は入力可能な文字を制限すると良い気がします。

インデックスが使えるか

全文検索で頑張れば使えるかもしれませんが、普通のインデックスは使えないですね。

デフォルト ON の項目の追加しやすさ

アプリケーションコードに変更を加え、追加する項目を JSON に含むようにした上で既存のレコードの JSON を修正します。
SQL で更新する場合は次のような感じでしょうか。

-- 部分一致する他の設定項目や "]" を含む設定項目がある場合はもっと複雑
UPDATE user_settings SET settings = CONCAT(REPLACE(settings, ']', ''), ',"receive_promotion_mail"]') WHERE settings NOT LIKE '%receive_promotion_mail%';

「設定項目の削除」で述べたようにレースコンディションを避けるための配慮が必要です。

テーブルサイズ

設定項目名を JSON に保存するため、設定項目名の長さの影響を大きく受けます。短い名前で項目数も大して多くなければ問題にならないかもしれません。

データの理解しやすさ

JSON なら人間でも読みやすいですね。項目数が多くなると大変ですが。

テーブルの制約

テーブル側では何の制約も課すことができません。PostgreSQL であれば CHECK 制約で頑張ることもできるかもしれませんが。

アプリケーションコードのシンプルさ

JSON はアプリケーションでも扱いやすい形式なので、シンプルな実装になるでしょう。
ただ、1 カラムで複数の値を扱っているため、レースコンディションへの配慮が必要になってくるかもしれません。

まとめ

細かいところまで色々挙げましたがそれぞれ次の箇所がネックになるんじゃないかと思います

設定項目ごとにカラムを割り当てる
- 0, 1 だけを持つカラムが設定項目の数だけできることに対する心理的障壁
- 項目を追加・削除する際にスキーマ変更を要する運用負荷
設定項目ごとにレコードを割り当てる
- レコード数が膨大になることに対する心理的障壁
- 導入時のアプリケーションコードを書く時の負荷
設定項目ごとにテーブルを割り当てる
- 設定項目の数だけテーブルができることに対する心理的障壁、モデル追加の面倒臭さ
設定項目ごとに 1 つの整数型カラムの 1 bit を割り当てる
- SQL を直接書かないといけない時の辛さ
- 既存のレコードを更新する際の運用負荷
- レースコンディション
1 つのカラムに JSON 等で全ての設定情報を持たせる
- 既存のレコードを更新する際の運用負荷
- レースコンディション

自分はこんな感じで上手くやってるという事例があれば教えてもらえると嬉しいです！

追記：EAV と「2. 設定項目ごとにレコードを割り当てる」について

「SQL アンチパターン」という本の 5 章に「EAV（エンティティ・アトリビュート・バリュー）」という章があり、「2. 設定項目ごとにレコードを割り当てる」がこの EAV に相当するという意見をちらほら見かけました。

僕は EAV に当たらないと思っています。EAV は attr_name, attr_value というカラムを設けることで、1 つのテーブルで様々な型の属性と値を扱うというものです。属性値が論理値に限定される（1/0 あるいはレコードの有無が true/false を意味する）場合は該当しません。

EAV がアンチパターンであるのは次の理由からです。

必須属性を設定できない
- 特定の属性にだけ NOT NULL 制約を課すということはできない（特定の属性だけレコードの存在を保証し、属性値が NULL でないことを保証することはできない）
SQL のデータ型を使えない
- 整数の属性も日付の属性も文字列の属性も全て自由度の高い文字列で格納しないといけない
参照整合性を強制できない
- 特定の属性の値に特定の値のみ付与するということができない
属性名を補わなければならない
- 属性名に制限を設けられないので、同じ属性を意図していても date_reported や report_date と異なる名前になることがある
行を再構築しなければならない
- 1 行に全ての属性が含まれるようにデータを取得するためには LEFT JOIN が必要
- 今回の話では「アプリケーションコードのシンプルさ」に近い話

設定項目ごとにレコードを割り当てる場合、EAV のデメリットのうち該当するのは 5 だけで、これは「3. 設定項目ごとにテーブルを割り当てる」も該当します。true が必須の項目は存在しないので 1 は該当しないし、1/0 だけなので 2 も 3 も該当しないし、今回言及したように参照整合性制約を課すことも可能なので 4 も該当しません。

EAV に近いのは「5. 1 つのカラムに JSON 等で全ての設定情報を持たせる」だと思います。

ところで、「SQLアンチパターン」はとても良い本なので読んだことのない方は一度読んでみると良いと思います！

Amazonで詳細を見る