DB設計 メモ

インデックスを作成するときはカーディナリティを考える。
カーディナリティは特定の列が、どのくらいの種類の多さを持つか、ということを表す概念。
このカーディナリティの高い列にインデックスを作る。
目安は、全体のレコード数の5%程度に絞り込めるだけのカーディナリティがあるかどうか。

STI (Single Table Inheritance): 単一テーブル継承
CTI (Class Table Inheritance): クラステーブル継承

STI か？ CTI か？

物理設計にかかる要件で、例えば串刺し検索がある場合には STI の方を、そうでない場合は CTI を採用するといった決定をする。

参考: t-wadaさんとのSQLアンチパターン勉強会 | FiNC Developers Blog

JOIN に関して

• JOIN は必要最低限
• INDEX を適切に活用する
• JOIN するテーブルは小さくしてから JOIN する
• 複雑なクエリになった場合は View を活用する

テーブルに状態を持たせることについて

削除フラグなどテーブルに状態を持たせると以下のデメリットがある。

• クエリの複雑化
• UNIQUE 制約が使えない
• カーディナリティが低くなる

このアンチパターンを回避するには、「事実のみを保存する」ことが大切。主に以下の方法がある。

• 「削除済み」のためのテーブルを用意し、データを移す
• トリガーを使う
• View を使う

参考: SQLアンチパターン 幻の第26章「とりあえず削除フラグ」

トリガーは非常に危険なので、使うべきではない。代わりにストアドプロシージャを使うべき。

トリガーは、データベースのカスタマイズに役立ちますが、必要な場合のみ使用してください。トリガーを過剰に使用すると相互依存関係が複雑になる可能性があり、大規模なアプリケーションでは管理が困難

「WHERE と ORDER BY の、どちらに向けた高速化インデックスなのか？」意識した方がいい。

• WHERE句で十分絞り込める場合は WHERE 狙い
• WHERE句がほとんど機能しないような場合は ORDER BY 狙い

参考: MySQL の WHERE狙いのキー（インデックス）と、ORDER BY狙いのキーによるSQLの高速化

ポリモーフィック関連

子テーブルが複数の親テーブルを持つような設計。子テーブルの属性によって紐づく親テーブルが変わる。外部キーを宣言できないことから、アンチパターンとして紹介されている。

解決策としては、交差テーブルや共通の親テーブルを用意することで、データの整合性を保証する。

参考: SQLアンチパターン勉強会　第6回：ポリモーフィック関連

一時テーブルとは、「一時的なデータ」を保持するためのテーブル。「テンポラリテーブル」とも呼ぶ。標準SQLに準拠した機能である。
このタイプのテーブルは、決められたスパンにおいてしかデータを保存せず、そのスパンが終了するとデータは自動的に削除される。そのスパンは、トランザクションであったりセッションであったり、複数から選べる。必要なときだけデータを保持して、使い終われば自動的に空になる、名前のとおり「使い捨ての」テーブル。
この一時テーブルは、「データマート」を作るために使われることもしばしば。実データを保持するため、SELECT文のコストが上昇せず、かといって永続的にデータを保持するわけではないので、ストレージの節約になる。

要は、データ量が多かったり複雑な処理をする際、１つのクエリで結果を得るのが難しかったり、処理が重くなりがちになる。そこで一旦、一時テーブルを作り対象となるデータを整理し、それを基に最終目的のデータを取得した方が処理が重くならない。

あるDBから別のDB にデータを移したいときに、一度データを整形したいときとかに

JSON 型
ビジネス要因で完全に構造が確定していない場合は有効。

Redshift で IDENTITY を使っても、IDの順序は保証されない。
分散環境である事が影響しており、必ずしも意図した形でのソート順やルール通りの採番はなされない模様。

• cf: Amazon Redshift: 分散ノード環境下でのIDENTITY機能の挙動について
• cf: 【AWS】Amazon Redshift ～ 自動採番 / IDENTITY ～

DynamoDB はテーブル単位から利用が開始され、テーブル→項目→属性と設計する。