hysryt commented 5 years ago

データをカンマ区切りで保持することのデメリット

JSONやserialize化したデータを保持する場合も含む。

検索がしづらくなる
- インデックスが使えない
- パターンマッチングを使うと標準SQLではなくなる
そのデータを基にしたJOINがしづらくなる
データに対して集約クエリが使えない（データの個数を集計するためにCOUNT()を使うなどができない）
データの更新が手間
外部キーを使って参照整合性を保つことができない
データに関わらず、カラムの型が文字列になる

hysryt commented 5 years ago

行同士に親子関係を持たせるには parent_id では不十分

掲示板のスレッドなど。親となる行のIDを parent_id で持つ方法がよく使われるが、これでは不十分なケースがある。

親と子ならよいが、親と子と孫など、階層が深い場合、SELECTする際に階層を追うにつれてその分テーブルを結合しなくてはならない。SELECT文のカラム数も階層を追うごとに増えてゆく。
子孫全てを削除する場合、子孫全てのIDを取得してから DELETE を発行する、という手間がかかる。

ただし、WITH 句が使える場合はその限りではない。 WITH 句は PostgreSQL8.4、MySQL8.0、Oracle11g などで使用可能。 MySQL5.7以下や、SQLite では使用できない。

WITH 句以外の方法として、以下の3つがある。

経路列挙（Path Enumeration）
入れ子集合（Nested Set）
閉包テーブル（Closure Table）

経路列挙

ルートからの自分までの階層を行ごとに保存する方法。

id	path	comment
1	1/	親
2	1/2/	子
3	1/3/	子2
4	1/2/4/	孫

メリット

path に対して LIKE で検索をかけることによって子孫全てを取得可能
同様に、先祖全てを取得することも可能

デメリット

外部キーを使って参照整合性を保つことができない
そのデータを基にしたJOINがしづらい

など、カンマ区切りでデータを保持するのと同じデメリットがある。

入れ子集合

各行に、nsleft と nsright という2つの数値型カラムを持たせる方法。 id と nsleft, nsright は全くの無関係。 nsleft と nsright は以下のルールによってつける。

nsleft < nsright
親の nsleft < 子の nsleft
子の nsright < 親の nsright

id	nsleft	nsright	comment
1	1	8	親
2	2	5	子
3	6	7	子2
4	3	4	孫

メリット

ある行の子孫全てを取り出したい場合は、nsleft がその行の nsleft より大きく nsright より小さい物を検索すれば良い。
親、子、孫が存在するとき、子を削除すれば孫はそのまま子にシフトされる

デメリット

子を追加する際、関連する他の行の nsleft と nsright を再計算する必要がある。　（「達人に学ぶDB設計徹底指南書」にこの弱点を克服する方法があるとのこと）
データを見るだけでは親子関係がわかりづらい

閉包テーブル

親子関係を別テーブルに分けて持つ方法。閉包テーブルには自分自身と、全ての祖先との親子関係を登録する。

id	comment
1	親
2	子
3	子2
4	孫

ancestor（先祖）	descendant（子孫）
1	1
1	2
2	2
1	3
3	3
1	4
2	4
4	4

メリット

シンプルかつわかりやすい

デメリット

階層が深くなればなるほど追加した時に増える行が多くなる
掲示板のスレッドのような、親から子孫への順番が重要になる場合、parent_idも必要な気がする

hysryt commented 5 years ago

「とりあえず id 列を作って主キーにする」のはやめる

他に主キーとして使える列があるのであればそちらを使用する。ない場合のみ、id列を擬似キーとして追加し、主キーとする。

命名と言う観点から見ても、id より product_id の方が良い

JOIN するときに USING が使える
名前から関連を推測できるツールを使うときに便利

hysryt commented 5 years ago

外部キー制約は積極的に使用するべき

参照整合性を保証できる
カスケード更新が可能（ON UPDATE CASCADE, ON DELETE SET DEFAULT）

hysryt commented 5 years ago

属性名、属性値の列を作らない

↓こういうの EAVテーブル（Entity Attribute Value テーブル）と呼ぶ。 WordPressの wp_options テーブルが主な例。

entity_id	meta_key	meta_value
1	'color'	'#00000'
1	'font-size'	'16px'

デメリット

属性値列のデータ型が無いに等しい（全て文字列）
属性名の打ち間違いが起こる可能性がある。
ユニークキー制約、外部キー制約を使用できない。
上の例の場合、entity_id で entity テーブルと結合した際、一つのentity行に複数行が結合されることになる。これの処理をアプリ側で for を回してデータを整理する必要がある。

基本的に1データに対して1列用意した方がいい

原因

EAVテーブルが生まれる原因はいくつかある。

メタデータの拡張性が求められる場合（頻繁に属性が増減する）
「期間限定商品」のように、商品でありつつも、独自のメタデータ（期間の開始日、終了日）が必要となる場合。

対策1：シングルテーブル継承

メタデータを全て列にする使わない場合はnullを入れる名前はエンタープライズアプリケーションアーキテクチャパターンから

product_id	product_name	color	size	type	start_date	end_date
1	'期間限定Tシャツ'	'red'	'M'	'期間限定'	2019-08-01	2019-09-01
2	'普通のTシャツ'	'white'	'S'	'普通'	null	null

商品の種別を区別する列が必要になる。商品の区別が増えるほど列が増えてしまう。

対策2：具象テーブル継承

メタデータが必要なテーブルを別テーブルに分ける名前はエンタープライズアプリケーションアーキテクチャパターンから

product_id	product_name	color	size
2	'普通のTシャツ'	'white'	'S'

product_id	product_name	color	size	start_date	end_date
1	'期間限定Tシャツ'	'red'	'M'	2019-08-01	2019-09-01

全ての商品データを取得する場合は UNION ALL を使う必要がある。検索が多い場合は不向き。

対策3：クラステーブル継承

メタデータだけテーブルを分ける名前はエンタープライズアプリケーションアーキテクチャパターンから

product_id	product_name	color	size
1	'期間限定Tシャツ'	'red'	'M'
2	'普通のTシャツ'	'white'	'S'

product_id	start_date	end_date
1	2019-08-01	2019-09-01

おそらく一番良い方法。

対策4：半構造化データ

メタデータをJSONなどにまとめて保持する

product_id	product_name	color	size	meta
1	'期間限定Tシャツ'	'red'	'M'	JSONデータ
2	'普通のTシャツ'	'white'	'S'	からのJSONデータ

SQLによる検索ができなくなるが、メタデータの属性が頻繁に増減する場合に有効。

hysryt commented 5 years ago

「ポリモーフィック関連」は作らない

ポリモーフィック関連とは、ある列が別の異なる2つのテーブルの主キーを参照すること。

外部キーは一つのテーブルしか参照できない
どちらのテーブルを参照しているかという列が必要になる

フレームワークなどではポリモーフィック関連が使用されていることがある。これはフレームワーク内部で参照整合性を保証しているため、ある程度信頼できる。

対策1：各テーブルとの間に関連テーブルを挟む

参照整合性を保証できる

対策2：各テーブルに共通の親テーブルを作成する

オブジェクト指向の親クラスのようにする。親テーブルの主キーを外部キーとして参照すれば参照整合性を保証できる。

hysryt commented 5 years ago

同じ意味合いのデータを複数持つ場合は従属テーブルを作成する

そのまま

hysryt commented 5 years ago

データ件数が多い場合はパーティショニングを行う

水平パーティショニング

テーブルを行で分割する。 シャーディング とも呼ばれる。 DBMSの機能によって行うが、SQL標準には定義されていないため、DBMSごとに方法が異なる。

MySQLでのパーティショニング

CREATE Bugs (
  ...
) PARTITION BY HASH ( YEAR(date_reported) )
PARTITIONS 4;

パーティションを指定して検索することで高速化が見込める

SELECT * FROM Bugs PARTITION (HASH ( YEAR(some_date) ))

垂直パーティショニング

テーブルを列で分割する。 DBMSに専用の機能はないため、手動で別テーブルを作成する。サイズの大きいBLOB型やTEXT型の列を別テーブルとすることが多い。

hysryt commented 5 years ago

誤差を許容できない場合はFLOAT型は使わない

FLOAT型は浮動小数点数であり、誤差が発生する。誤差が許容できない場合は NUMERIC または DECIMAL 型（固定小数点数）を使用する。

hysryt commented 5 years ago

列に入力する値が限定されているときは別テーブルを作成する

別テーブルを作成し、入力可能な値を行として追加する。元のテーブルから外部キーとして参照することで、CHECK制約を用いず、入力値を制限できる。

hysryt commented 5 years ago

画像をデータベースに格納するかどうか

画像データをデータベース内に格納する方法
画像データをファイルシステムで保持する方法

どちらにも一長一短ある

データベース内に格納するメリット

画像の削除、更新をトランザクション内で行えるため、整合性を保つことができる。
ロールバックも可能。
画像へのアクセス制御もDBMSのアクセス権限をそのまま使用できる。

ファイルシステムに保持するメリット

データベースと画像ファイルを分離できる。画像ファイルは基本的にデータサイズが大きいため、データベースの肥大化を防ぐことができる。
データベースの肥大化を防ぐことができるため、データベースのバックアップも高速化に行える。
画像の編集が容易。

hysryt commented 5 years ago

インデックスを適切に使う

「MENTOR」の原則に基づいてインデックスを作成する

Measure（測定）

SQLクエリの実行時間を測定する。実行頻度が高く、実行時間が長いクエリは最適化するメリットが大きい。実行時間が長いが実行頻度が低い場合はメリットが少ないかもしれない。クエリパフォーマンスの測定中はキャッシュを無効化にする。

Explain（解析）

対象のクエリが遅くなっている原因を解析する。データベースはクエリ実行計画（QEP）と呼ばれる機能でどのインデックスを使用するかを判断している。 QEPの取得方法はDBMSごとに異なる。

Nominate（指名）

インデックスを使用していない部分を探し、インデックスを作成する。

Test（テスト）

作成したインデックスが有効な効果をもたらしているかテストする。

Optimize（最適化）

キャッシュメモリサイズを調整する。

Rebuild（再構築）

インデックスは長期にわたって使用していると不均衡になってくるため、定期的に再構築を行う。

補足

ANSI SQL 規格にはインデックスに関する記述は存在しない。ベンダがそれぞれ独自に実装しているため、インデックスを有効に活用するには使用しているDBMSのドキュメントをよく読む必要がある。

hysryt commented 5 years ago

NULLも適切に使う

hysryt commented 5 years ago

GROUP BY 句で指定してない列を SELECT 句で指定してはならない

どの値が取得されるか曖昧なため。

id	name	season	price
1	りんご	冬	100
2	ぶどう	秋	500
3	なし	秋	400
4	すいか	夏	300

season でグループ分けし、グループの中で一番 price が高いものの name を取得する

SELECT season, MAX(price), name
FROM fruit
GROUP BY season

これではだめ name は一意に決まらない

対応策.1 相関サブクエリを使用する

WHERE 句でサブクエリを使用して、取得する値を一意にする。

SELECT season, price, name
FROM fruit as f1
WHERE price = (
  SELECT MAX(price)
  FROM fruit as f2
  WHERE f2.season = f1.season
)

サブクエリで同じ season のもののうち、一番高い price を取得している。外側の SELECT で、取得した price と同じもののみを選択している。

ただし相関サブクエリは行毎に実行されるため、パフォーマンスはあまり良くない。

対応策.2 導出テーブルを使用する

FROM句でサブクエリを使用して、取得する値を一意にする。

SELECT f1.season, f1.price, f1.name
FROM fruit as f1
INNER JOIN (
  SELECT season, MAX(price) AS max_price
  FROM fruit
  GROUP BY season
) f2
ON f1.season = f2.season
  AND f1.price = f2.max_price

導出テーブルはサブクエリは1度実行されるだけなので、相関サブクエリよりもパフォーマンスが良い。ただし一時テーブルを作成するコストがかかるため、場合によってはさらにパフォーマンスの改善ができる。

対応策3. テーブルのJOINのみでなんとかする

SELECT f1.season, f1.price, f1.name
FROM fruit as f1
INNER JOIN fruit as f2
ON f1.season = f2.season
  AND f1.price < f2.price
WHERE f2.season IS NULL

パフォーマンスは良いが、SQL文が複雑になりやすい。（保守性が下がる）テーブルの結合数によってはパフォーマンスが下がる可能性もあるので、実際に測定した方が良い。

解説

| id | name | season | price | |-|-|-|-| |1|りんご|冬|100| |2|ぶどう|秋|500| |3|なし|秋|400| |4|すいか|夏|300| ```sql SELECT f1.season, f1.price, f1.name FROM fruit as f1 ``` | f1.id | f1.name | f1.season | f1.price | f2.id | f2.name | f2.season | f2.price | |-|-|-|-|-|-|-|-| |1|りんご|冬|100|1|りんご|冬|100| |1|りんご|冬|100|2|ぶどう|秋|500| |1|りんご|冬|100|3|なし|秋|400| |1|りんご|冬|100|4|すいか|夏|300| |2|ぶどう|秋|500|1|りんご|冬|100| |2|ぶどう|秋|500|2|ぶどう|秋|500| |2|ぶどう|秋|500|3|なし|秋|400| |2|ぶどう|秋|500|4|すいか|夏|300| |3|なし|秋|400|1|りんご|冬|100| |3|なし|秋|400|2|ぶどう|秋|500| |3|なし|秋|400|3|なし|秋|400| |3|なし|秋|400|4|すいか|夏|300| |4|すいか|夏|300|1|りんご|冬|100| |4|すいか|夏|300|2|ぶどう|秋|500| |4|すいか|夏|300|3|なし|秋|400| |4|すいか|夏|300|4|すいか|夏|300| ```sql SELECT f1.season, f1.price, f1.name FROM fruit as f1 INNER JOIN fruit as f2 ``` | f1.id | f1.name | f1.season | f1.price | f2.id | f2.name | f2.season | f2.price | |-|-|-|-|-|-|-|-| |1|りんご|冬|100|1|りんご|冬|100| |2|ぶどう|秋|500|2|ぶどう|秋|500| |2|ぶどう|秋|500|3|なし|秋|400| |3|なし|秋|400|2|ぶどう|秋|500| |3|なし|秋|400|3|なし|秋|400| |4|すいか|夏|300|4|すいか|夏|300| ```sql SELECT f1.season, f1.price, f1.name FROM fruit as f1 INNER JOIN fruit as f2 ON f1.season = f2.season ``` | f1.id | f1.name | f1.season | f1.price | f2.id | f2.name | f2.season | f2.price | |-|-|-|-|-|-|-|-| |1|りんご|冬|100|null|null|null|null| |2|ぶどう|秋|500|null|null|null|null| |3|なし|秋|400|2|ぶどう|秋|500| |4|すいか|夏|300|null|null|null|null| ```sql SELECT f1.season, f1.price, f1.name FROM fruit as f1 INNER JOIN fruit as f2 ON f1.season = f2.season AND f1.price < f2.price ``` | f1.id | f1.name | f1.season | f1.price | f2.id | f2.name | f2.season | f2.price | |-|-|-|-|-|-|-|-| |1|りんご|冬|100|null|null|null|null| |2|ぶどう|秋|500|null|null|null|null| |4|すいか|夏|300|null|null|null|null| ```sql SELECT f1.season, f1.price, f1.name FROM fruit as f1 INNER JOIN fruit as f2 ON f1.season = f2.season AND f1.price < f2.price WHERE f2.season IS NULL ```

対応策.4 グループ内のすべてのデータを連結して一つのデータとして扱う

SELECT season, MAX(price), GROUP_CONCAT(name)
FROM fruit
GROUP BY season

hysryt commented 5 years ago

ランダムに行を取得

テーブルの中からランダムに1行取得する方法のアンチパターンとして、以下のようなものがある。

SELECT *
FROM product
ORDER BY RAND()
LIMIT 1;

「ランダム値で行をソートし一番上に来たものを返す」というクエリ。悪いのはランダム値でソートをしている部分。インデックスのない列でのソートはコストがかかる。行数が増えれば増えるほど、そのコストは高くなる。

ランダムに関する処理はアプリケーション側で行う方が良い場合が多い。今回の場合は、

product テーブルの主キーを全て取得
アプリケーション側でランダムに一つを選ぶ
選んだ主キーをもとにデータを取得

と言う流れで行うのが良い。

1の時点で全てのデータを持って来ればクエリの実行は1回で済むが、メモリを多く使用することになるため好ましくない。

あるいは以下の方法もある。

product テーブルの行数を取得
行数内でランダム値を1つ取得
ランダム値をオフセットとして1行取得

これは1の時点で行数しか取得していないので先ほどの例よりも効率がよい。ただしSQLの記述方法がDBMSによって異なる為、DBMSに依存したくない場合は使えない。また、ランダムで複数件取得したい場合はデータが偏ってしまう。

hysryt / wiki

SQLアンチパターン #121

データをカンマ区切りで保持することのデメリット

行同士に親子関係を持たせるには parent_id では不十分

経路列挙

メリット

デメリット

入れ子集合

メリット

デメリット

閉包テーブル

メリット

デメリット

「とりあえず id 列を作って主キーにする」のはやめる

外部キー制約は積極的に使用するべき

属性名、属性値の列を作らない

デメリット

原因

対策1：シングルテーブル継承

対策2：具象テーブル継承

対策3：クラステーブル継承

対策4：半構造化データ

「ポリモーフィック関連」は作らない

対策1：各テーブルとの間に関連テーブルを挟む

対策2：各テーブルに共通の親テーブルを作成する

同じ意味合いのデータを複数持つ場合は従属テーブルを作成する

データ件数が多い場合はパーティショニングを行う

水平パーティショニング

MySQLでのパーティショニング

垂直パーティショニング

誤差を許容できない場合はFLOAT型は使わない

列に入力する値が限定されているときは別テーブルを作成する

画像をデータベースに格納するかどうか

データベース内に格納するメリット

ファイルシステムに保持するメリット

インデックスを適切に使う

Measure（測定）

Explain（解析）

Nominate（指名）

Test（テスト）

Optimize（最適化）

Rebuild（再構築）

補足

NULLも適切に使う

GROUP BY 句で指定してない列を SELECT 句で指定してはならない

対応策.1 相関サブクエリを使用する

対応策.2 導出テーブルを使用する

対応策3. テーブルのJOINのみでなんとかする

対応策.4 グループ内のすべてのデータを連結して一つのデータとして扱う

ランダムに行を取得