- 🙅♂️ 複数の値をカンマ区切りで表現する
- 交差テーブルを作ろう
- 🙅♂️ 再帰構造の表現に parent_id を使う
- 経路列挙
- パス形式の値を持たせる
- 入れ子集合
- 深さを数値で持たせる
- 閉包テーブル
- 関連を別テーブルに持たせる
- 🤔 1-n の2テーブル作ったらダメなんかな？
- 🙅♂️ 何でもかんでも擬似キーを使う
- 分かりやすい列名にする
- 規約に縛られすぎない
- 自然キーと複合キーを活用する
- 🤔 サロゲートキーは命綱みたいなとこある
- とりあえず付けとけは思考停止だったかもなぁ
- 🙅♂️ 外部キーを使わない
- 外部キー制約を使う
- ミスを未然に使う
- 🙅汎用的な属性テーブルを使う
- シングルテーブル継承
- type カラムを作って、一つのテーブルで表現する
- 具象テーブル継承
- サブタイプごとにテーブルを作成する
- クラステーブル継承
- 基底のテーブルとサブタイプのテーブルを作る
- 半構造化データ
- LOB 列に XML や JSON で格納する
- 🤔基本はクラステーブル継承で、必要に応じて半構造化データを使うのがよさそう
- 🙅♂️ 二重目的の外部キー
- type 列を用意した判定
- 交差テーブルを作る
- 必要に応じて Unique 制約を付与
- 共通の親テーブルを作る
- 🙅♂️ 複数の列を定義する
- tag1, tag2, tag3,,みたいなの
- 従属テーブルを作る
- 意味合いが異なるなら互換性がある値でもよい
- 報告者アカウント、担当者アカウント、テストアカウント、みたいなの
- 🙅♂️ メタデータのテーブルや列をコピーする
- bugs_2018, bugs_2019,,みたいなの
- パーティショニングを行う
- 従属テーブルを使う
- アーカイブが目的なら許容される
- 🤔 スケーラビリティが目的ならパーティショニングするよりかクラウドで従属テーブルがよさげ
- アーカイブも BigQuery とかに逃せば
- 🙅♂️ FLOAT データ型を使用する
- 無限精度の値がある
- 丸めが避けられない
- NUMERIC または DECIMAL を用いる
- 精度とスケールを指定できる
- 🙅♂️ 値の制約を列定義で行う
- CHECK 制約や ENUM データ型
- 限定する値はデータで指定する
- ステータスマスタみたいなものを作る
- 🙅♂️ DB にはパスを格納し、実体は別にある
- BLOB 型を必要に応じて採用する
- 🤔 筆者は自分が少数派であることを認識している
- 🤔 言われるとなるほど確かにと思うこともある
- パスは型による保証がない
- トランザクションが分離
- ロールバック
- アクセス権限
- etc
- 🙅♂️ インデックスを闇雲に貼る、あるいは貼らない
- 😲 インデックスは SQL 規格にない
- MENTOR の原則に基づいてインデックスを管理する
- Mesure (測定)
- スロークエリログを見る
- キャッシュは無効化する
- アプリケーションのプロファイリングをする
- Explain (解析)
- 実行計画を取得する
- Nominate (指名)
- 実行計画からネックを特定する
- カバーリングインデックス
- 全ての列をインデックスにすることでテーブルアクセスを不要にする
- 😳 やんちゃだ。。
- Test (テスト)
- 変更の効果を計測する
- Optimize (最適化)
- キャッシュメモリの量を設定する
- MySQL はインデックスをあらかじめキャッシュにロードできる
LOAD INDEX INTO CACHE
- Mesure (測定)
- Rebuild (再構築)
- インデックスのメンテナンス
- MySQL なら ANALIZE TABLE
- 実行頻度に正解はない
- 🙅♂️ NULL を一般値として扱う
- 🙅♂️ 一般値を NULL として扱う
- NULL は FALSE や 0 や空文字ではない
- 🙅♂️ NULL を使いたくないがために "unknown" のような特殊値を使う
- NULL を使うこと自体がアンチパターンなわけではない
- NULL を一意な値として使う
- NULL が意味をなさない場合には NOT NULL 制約を宣言する
- COALESCE 関数を使う
- NULL の代わりに動的なデフォルト値を取得できる
- 🙅♂️ 非グループ化列を参照する
- 関数従属性のある列のみにクエリを実行する
- 基本はこれで、後は必要に応じて考えればいい気がする
- 🙅♂️ データをランダムにソートする
- インデックスが使えないのでテーブルスキャンが走る
- 1 と最大値の間のランダムなキー値を選択する
- 連番が前提ならこれ
- 欠番の穴の後にあるキー値を選択
- 欠番があるならこれ
- キー値のリストを取得した後、ランダムに選択
- クエリが二回になる
- 🤔 全件取得なので微妙
- オフセットを用いてランダムに行を選択
- 🤔 これならまぁ。。
- ベンダー依存の解決策
- サンプル取得の機能が用意されている製品がある
- 🙅♂️ 全文検索にパターンマッチ述語を使用する
- LIKE や正規表現は重い
- ベンダー拡張を使う
- 🤔 PostgresSQL の TSVECTOR は便利そう
- サードパーティーのサーチエンジンを使う
- Sphinx Search
- Apache Lucene
- 🤔 Elastic Search 使えば良さそう
- 🙅♂️ 複雑な問題をひとつのクエリで解決しようとする
- リレーションを持たないテーブルだとデカルト積が発生する
- 分割統治を行う
- クエリを分ける
- UNIONを用いる
- CASE 式と SUM 関数を組み合わせる
- SQL を生成する SQL を書く
- 節約の原則
- まったく同じ予測をする２つの競合する理論があるときは、単純な方が優れている
- 🙅♂️ 列名を省略する
- ワイルドカードなど
- 明示的に指定する
- ワイルドカードを使えない局面はいずれ訪れる
- 🙅♂️ パスワードを平文で格納する
- ソルトを付けてパスワードハッシュを格納する
- MD5 は使わない
- ソルトを付けて辞書攻撃を防ぐ
- クエリからパスワードを隠す
- ハッシュ化してからクエリを発行する
- パスワードをリカバリーせず、リセットする
- "あなたが読み取れるものは、攻撃者にも読み取れます"
- 🙅♂️ 動的 SQL に未検証の入力を使用する
- 入力のフィルタリング
- プリペアドステートメントの使用
- 動的値を引用符で囲う
- プリペアドステートメントは万能ではない
- IN 句で使えない
- IN 述語のパラメータ化
- 識別子、予約語等では使えない
- IN 句で使えない
- ユーザーの入力をコードから隔離
- 識別子と入力値の紐付けをクエリの外で行う
- コードレビュー
- 🙅♂️ 擬似キーの欠番を埋める
- 欠番は埋めない
- 行番号と擬似キーは別物
- ROW_NUMBER 関数や LIMIT 句を使う
- 整数の擬似キーは枯渇するか？
- 毎秒１個としても 32 ビットなら 136 年かかる
- 64 ビットなら毎秒 100 万個でも 58 万 4542 年かかる
- GUID （グローバル識別子） も擬似キーに使える
- 事実上一意
- 16 バイトなので、サイズが大きい
- 意味のあるキーは自然キーにする
- 🙅♂️ 肝心な部分を見逃す
- データベース API の戻り値を無視する
- アプリケーションコード内の SQL しか読まない
- 戻り値と例外をチェックする
- 実際に構築された SQL クエリを使用する
- 🙅♂️ SQL を特別扱いする
- 包括的に品質問題に取り組む
- 文書化
- バージョン管理
- テスティング
- 複数のブランチ
- アプリケーションの各ブランチ向けにそれぞれデータベースインスタンスを構築する
- 🙅♂️ モデルがアクティブレコードそのもの
- データベーススキーマに強く依存する
- ドメインモデル貧血症をもたらす
- モデルがアクティブレコードを持つようにする
- 🙅♂️ 想定不足
- 本番と同じテスト環境を１セット用意するのが理想
- 例外処理を実装する
- デッドロックやロック待ちタイムアウトはどの製品でも起こる
- バックアップ
- 高可用性
- ディザスタリスカバリ
- 運用ポリシーの策定
- 高可用性の限界を超えた障害
- 問題の調査
- 性能の劣化