🍄

SQLアンチパターン　感想/要約

に公開
MySQL
SQL
設計
DB
アンチパターン
tech

感想

SQLアンチパターンを読んだのでまとめました。
今までなんとなくクエリを書いてたり、データベース設計を今まで勉強してこなかった人にはおすすめです。
会社で膨大なデータを扱う時に役に立ちそうな気がする。(私は読まずにやって苦労しました)

本書の悪いところはとっつきづらいこと。
クエリ文だけで説明する箇所がほとんどなのですぐに理解できないことがあった。

良いところは読んでいると頭に残りやすいこと。
コミュニケーションによってアンチパターンを特定する方法だったり、設計の悩みを一つ一つ潰してベストプラクティスを導いている。実際に開発している感覚になった。

興味があったら是非読んでみてください。

追記
著者の番外編幻の26章。結構重要だと思います。

ジェイウォーク（信号無視）

テーブル設計基本

アンチパターン

カンマ区切りのフォーマットで複数のデータを追加する...

例えば一つのIDカラムに3,4,5といったデータを追加することになる。

理由

  • データが収まらない
    • VARCHAR(100)のようにカラム文字数を制限するので、データを追加すると文字数を超える。
  • クエリ分の複雑化
    • 正規表現を使う必要が出てきて、SQLが読みづらくなる
    • COUNT, SUM, AVGが素直に使えない。
  • 更新が大変
    • UPDATEを使うと、他の変更したくないデータが消えてしまう。
    • 区切り文字そのものがデータとして使われた場合、データが壊れてしまう。
  • バリデーションが効かない
    • account_idは文字列になっているから

解決策

中間テーブル(交差テーブル)を使いましょう。

クエリ文をシンプルに組めるようにテーブル設計することができます。

ナイーブツリー（素朴な木）

  • ツリー状や階層的な構造を持ったデータを扱いたい
  • 組織図だったりTwitterのスレッド、レスのようなデータ

アンチパターン

  • 親データに依存させる。parent_id列を設定する。
comment_id parent_id speaker comment
1 NULL Nick 自転車買ったから見てくれ。
2 1 Ollie かっこいいね〜いくらしたの？
3 2 Nick 30万くらい！
4 1 Mike パーツ足りなくない？
5 4 Nick それ本当？全くわからない
6 5 Fran ペダルがないね
7 5 Ollie ライトがないかな
8 6 Nick ペダル買い忘れた...

理由

  • 階層が深い場合、テーブル結合をたくさんする必要がある。
  • 一部のデータを削除するとツリー、階層構造のデータが破綻するので、 依存する親データを毎回更新する必要がある。

解決策

下記のツリーモデルを採用する。

  • 経路列挙
    　- 先祖の系譜を文字列として格納する。(path)
    　-　ジェイウォークのアンチパターンに当てはまる
comment_id path speaker comment
1 1/ Nick 自転車買ったから見てくれ。
2 1/2/ Ollie かっこいいね〜いくらしたの？
3 1/2/3/ Nick 30万くらい！
4 1/4/ Mike パーツ足りなくない？
5 1/4/5/ Nick それ本当？全くわからない
6 1/4/5/6 Fran ペダルがないね
7 1/4/5/7 Ollie ライトがないかな
8 1/4/5/6/8 Nick ペダル買い忘れた...
  • 入れ子集合モデル
    • nsleftの値は下層ノードのnsleft, nsrightより値が小さい。
    • nsrightの値は下層ノードのnsleft, nsrightより値が大きい。
    • 非葉ノード(末端でない)データを削除してもデータの上下関係は変わらない。
    • データの挿入を頻繁にすることには向いていない
comment_id nsleft nsright speaker comment
1 1 16 Nick 自転車買ったから見てくれ。
2 2 5 Ollie かっこいいね〜いくらしたの？
3 3 4 Nick 30万くらい！
4 6 15 Mike パーツ足りなくない？
5 7 14 Nick それ本当？全くわからない
6 8 11 Fran ペダルがないね
7 12 13 Ollie ライトがないかな
8 9 10 Nick ペダル買い忘れた...
  • 閉包テーブルモデル
    　-　閉包テーブルを外部で持つ
    　　-　必ず自分自身を親子関係にして、間にいくつのものデータが有っても下層のデータであれば親子として記述する。
    　　-　階層構造の変更がしやすい。
    　　-　閉包テーブル 改のように親子の長さを記録しておけば、深さも検索条件に加えることができる。

※親と子の二列だけだと見栄えが悪いので折り返して表記しています。
閉包テーブル

親 子 親 子 親 子 親 子
1 1 1 7 4 5 5 7
1 2 1 8 4 6 5 8
1 3 2 2 4 7 6 6
1 4 2 3 4 8 6 8
1 5 3 3 5 5 7 7
1 6 4 4 5 6 8 8

※データ作成が手間なので一部だけ
閉包テーブル 改

親 子 長さ
4 4 0
4 5 1
4 6 2
4 7 2
4 8 3

テーブルには一長一短があるので、状況に合わせて使う。
閉包テーブルは面白かったので使ってみたい。

IDリクワイアド（とりあえずID）

サロゲートキーの扱い方

アンチパターン

  • 理由もなくテーブルにID列を設定する。

理由

  • 意図しない重複データが生成される。
  • 必要のないID列が多く生成されて、テーブルの設計意図がわからなくなる。

解決策

  • ORMではデフォルトでIDをサロゲートキーとして設定するので、上書きしてIDを使わないようにする。
  • 自然キーや複合キーを使う
    • 自然キーは一意の値になるか精査する
    • 複合キーは手間だけど使う

キーレスエントリ(外部キー嫌い)

アンチパターン

外部キーを使わない

理由

  • 外部キーを使わないと
    • 完璧なプログラムによって、データが壊れないようにする手間が発生する。
      • アプリケーション側で完璧に制御できても、DBに直接クエリを実行した場合に整合性が保証できなくなる。
    • データが壊れていないかチェックする仕組みが必要になる
      • 壊れていた場合の対応方法を決めないといけない。
  • 外部キーを使うとデータベース実行速度が遅くなる？
    • 確かに遅くなるが、外部キーの代わりの処理をするほうが、時間がかかる。

解決策

外部キーを使う

EAV（エンティティ・アトリビュート・バリュー）

可変属性を使いたい

アンチパターン

  • 属性を追加する度に新しい列を追加しないで行数を増やしていく
    • データ欠損によるnullデータも発生することがない
issue_id attr_name attr_value
1234 product app
1234 date_reported 2009-06-01
1234 version_affected 0.1
1234 description 決済の失敗

理由

  • 特定の属性を検索する時にWHEREを使うことになり冗長になる。

  • 必須属性(NOT NULL)が設定できないので、運用が複雑になる。

  • データ型が使えない

    • 例のテーブルではattr_valueはVARCARに設定しないといけないので、小数や日付のバリデーションがカラム定義によってできない、アプリケーション側で担保する必要がある。

  • 外部キーによる参照整合性が使えない

  • 一つの行として扱いたい場合、外部結合を繰り返す必要があり実行コストが増える。

    • 列が増えるたび悪化する
    issue_id product date_reported version_affected description
    1234 app 2009-06-01 0.1 決済の失敗

解決策

  • シングルテーブル継承
    • issue_typeはサブタイプでissueの種類を判定
    • サブタイプがすべてのカラムに対応しているわけではないので、NULLが多くなる
    • サブタイプの種類とサブタイプ固有の属性が少ない、アクティブ・レコードのときに使いたい
      • アクティブ・レコードが理解できていない...
issue_id issue_type date_reported version_affected description sponser
1 BUG 2009-06-01 0.1 決済の失敗 NULL
1 FEATURE 2009-06-01 NULL カート改善 Nick
  • 具象テーブル継承
    • サブタイプごとにテーブルを作成
    • シングルテーブル継承のようにサブタイプに存在しない属性値を入れる必要がない→NULLがなくなる
  • クラステーブル継承
    • サブタイプ固有の属性値をサブタイプごとに別テーブルに切り出し、固有でないものは基底テーブルとして切り出す。
    • サブタイプテーブルはissue_idを外部キーにする
  • 半構造化データ
    • LOB列(TEXT, CLOB, BLOB)を作ってXML、JSON形式でデータを追加する
    • 拡張性が高く、サブタイプの数が多かったり、頻繁に新しい属性を追加したいときに使う。とりあえずデータを入れることができる。
    • SQLだけではLOB列から特定の属性値を取り出すことはできない

ポリモーフィック関連

複数の親テーブルを参照する方法

外部キーは複数のテーブルを指定できない

アンチパターン

  • 複数のテーブルと関連をもたせるために、親テーブルの名前を格納するカラムを作成する。

理由

  • 結局外部キーは使えなくて参照整合性制約が定義できない。
  • かなりの確率で外部結合が必要になるが、複数の親テーブルがあるので結合してもデータが読み取れないケースが発生する
    • 行単位で別々の親テーブルを結合できないので、不一致が起きる。

解決策

  • 参照を逆転させる
  • 交差テーブルを使う
    • 親テーブルと子テーブルは交差テーブルに依存するようにする
  • 共通の親テーブル作成

マルチカラムアトリビュート（複数列属性）

ジェイウォークと同じで一つの列に複数の値を格納したいが、アンチパターンは違う

アンチパターン

  • 同じ意味合いのカラム名を複数追加する。今回はナンバリングされている
bug_id name tag1 tag2 tag3
1100 メール送信に失敗 error NULL NULL
1101 後払い処理の要望 request money NULL

理由

  • 検索するのが難しくなる
    • tag1, tag2, tag3全てに検索をかける必要がある
  • 更新が難しい
    • 値を変更したり、新規で追加(NULLを更新)するためにクエリが冗長になる
  • 一つの行に重複した値が入力される可能性がある(一意性の保証ができない)
  • カラムが増える可能性がある
    • tag4というカラムが増える
    • 事前にどれくらいカラムが必要か予測できない

解決

  • 従属テーブルを作る

メタデータトリブル（メタデータ大増殖）

アンチパターン

  • テーブルは行が少ないほうがスケーラビリティがいいと思って、行数を少なくする。
    • 正規化ではなく、属性ごとにテーブルを分割する。
    • 列を増やす。

理由

  • テーブルの増殖
    • テーブルの作成忘れによるエラー
    • テーブルの指定方法が複雑になる。
  • テーブル間の整合性を保つのが難しい
  • データの同期が難しい
  • テーブルを跨いだクエリを都度修正しなければならない

あんまりいいことないような...

解決策

  • 水平パーティショニング(シャーディング)
    • テーブルを行で分割する
    • テーブルは物理的に分割されるが、一つのテーブルを扱うようにSQLを実行できる。
    • https://pecopla.net/web-column/db-shard
    • mysqlでPARTITIONという機能が使える
  • 垂直パーティショニング
    • 列でテーブルを分割する
    • 利用頻度の低い列がある場合やTEXTやBLOBといった一部のデータサイズが大きくなる場合に使う。
  • 従属テーブルの導入

ラウンディングエラー(丸め誤差)

アンチパターン

FLOATデータ型を使う。

理由

FLOATだと丸め誤差が発生して正確な計算ができない。

解決策

  • NUMERICデータ型を使う。

サーティワンフレーバー(３１のフレーバー)

アンチパターン

  • 列の値を制限するためにCHECK制約で列定義(例: ENUM)を使う

理由

  • 列定義そのものが使いにくい
    • ENUM(列挙型)
      • mysqlだと非標準
      • データベースの中で仕様が統一されていない
    • DOMAIN(ドメイン), UDT(ユーザー定義型)
      • サポートするRDBMSは少ない
      • データベースの中で仕様が統一されていない
  • CHEKC制約が使いにくい
    • 追加、削除する構文がない
    • 新しい値セットで列を再定義するしかない
  • 値の廃止が困難

解決策

  • 列定義は参照テーブルに定義する
  • 参照テーブルと外部キーを使って列の値を制限する。

ファントムファイル(幻のファイル)

画像のような大容量メディアファイルを管理したい

アンチパターン

  • DBにはファイルパスを登録して、ファイルはファイルシステムに保存する

理由

  • クエリ文だけだとファイルを削除できない、アプリケーションでないと完全な削除ができない。
  • トランザクションの分離
    • ファイルを変更、削除した場合に参照エラーが起きる
    • ロールバック時にファイルだけが戻らない
  • ファイルにDBの権限が適用されない。
  • バックアップツールが使えない
  • ファイルパスは単なる文字列なので、ファイルが存在するか検証されない。更新もされない。

解決策

  • BLOB型を使う
    • BLOBは巨大なバイナリデータを格納することができる
    • バイナリなので画像として使いたいときはアプリケーションでエンコード、デコードが必要

インデックスショットガン(闇雲インデックス)

パフォーマンスを最適化したい。

アンチパターン

  • インデックスを闇雲に使う

理由

  • インデックスを全く定義する
    • テーブル更新回数<クエリ発行回数の場合、インデックスによるデメリット(オーバーヘッド)よりメリットが上回る
  • インデックスを多く定義する
    • 更新のときのオーバーヘッドを増やしてしまう。
  • インデックスが効果的に使われない
    • クエリでLIKE , ORがあれば使えない場合がある。

解決策

  • MENTORの原則に基づいてインデックスを使う
    • Meature（測定）
      • Mysqlだとスロークエリログを使う
    • Explain（解析）
      • EXPLAIN、クエリ実行計画を使って解析する。
    • Nominate（指名）
      • クエリ実行計画を頑張って読む
      • MySQL Query Analyzerの提案をもとに効果的じゃないインデックスをあぶり出す。
    • Test（テスト）
    • Optimize（最適化）
      • キャッシュに割り当てるメモリを調整する。
    • Rebuild（再構築）
      • ANALYZE TABLE,OPTIMIZE TABLEを使って定期的にメンテナンスする。

クエリアンチパターン

恐怖のunknown

NULLデータを扱い方

アンチパターン

  • NULLをそのまま式につかう
    • NULL + 10みたいなケース
  • NULLを許容する列を検索する
    • WHERE id = NULLのようなケース
  • プリペアドステートメントでNULLを使う
    • プリペアドステートメント ?は変数みたいな扱い
      • SELECT * FROM user WHERE id = ?
    • NULLでユーザーを検索する
      • SELECT * FROM user WHERE id = NULL
  • NULLを使わないようにする

理由

  • NULLで計算すると結果がNULL
  • NULLは比較できない
  • NULLが使えないと正しくデータが扱えない

解決策

  • スカラー式、論理式でNULLを使ったときの動作を覚えておく
  • IS NULLを使って検索する
  • NOT NULL制約を使う
    • データベース側でNULL値をコントロールさせる

アンビギュアスグループ(曖昧なグループ)

グループ内で最大値をもつ行を取得するとき、該当する行のグループ化されていない値を取得する時の解決方法。GROUP BYの使い方で注意することがある。

アンチパターン

  • GROUP BYで指定してないカラムを参照する

理由

通常下記のようなクエリはエラーになる。

  • SELECT product_id, MAX(date_reported) AS latest, bug_id
    FROM Bugs INNER JOIN BugsProducts USING (bug_id)
    GROUP BY product_id;

下記はbug_idでJOINしたあとのテーブルです。

product_id date_reported bug_id
1 2000-01-01 11
1 2000-02-02 22
10 2010-09-01 33
10 2010-01-01 44

これをproduct_idでグルーピングしてからdate_reportedの最新を取得すると下記のようにできると思いますが、現実はできません。

product_id date_reported bug_id
1 2000-02-02 11
10 2010-09-01 33

エラーが出る理由はこちらの記事が分かりやすかった

https://blog.capilano-fw.com/?p=7001

解決策

  • 関数従属性のある列のみにクエリを実行する
    • GROUP BYや集計関数が使われた集計関数だけをSELECT句に入れる。
  • 相関サブクエリを使う
  • 導出テーブルを使う
  • 外部結合を使う
  • 集計関数を使う
    • カラムが曖昧でなくなるから

一番難しかった章

ランダムセレクション

アンチパターン

  • データをランダムにソートして、ランダムな結果を返す。
    • ランダムな結果がほしい理由はコンテンツのローテーション表示やテストデータ生成など

理由

  • 通常のソートとは違ってインデックスの恩恵を受けられない
    • フルテーブルスキャンでソートすることになり、パフォーマンスが落ちる
  • 使うデータは一部だけなのに全体を一からソートするので、無駄多い。

解決策

  • 主キーが連番の場合
    • 主キーの数字をランダムに指定し抽出する
    • 欠番がある場合は欠番の穴の後ろにあるものを使う。
      • デメリットは欠番の分布によって選ばれる主キーに偏りがでる。
  • アプリケーション側で主キーを全て取得し、ランダムに選ぶ
    • データ量が多い場合はエラーが出るので避ける。
  • 主キーが連番でなく、かつ偏りなく選びたい
    • LIMITを用いて行数をランダムに指定する
  • SQL ServerやOracleにある最適化されたランダムセレクションの機能を使う。

プアマンズ・サーチエンジン(貧者のサーチエンジン)

アンチパターン

全文検索でLIKEや正規表現

理由

  • インデックスが使えず、フルテーブルスキャンになる。パフォーマンス低下
  • 意図しないパターンマッチが生じる
    • 単純な検索でも正規表現を考えて作る必要がある。

解決策

  • 全文検索エンジンを使う。できれば検索結果を保存する。
  • 転置インデックスの作成
    • ベンダー依存させない方法

スパゲッティクエリ

アンチパターン

一つのクエリで無理やり解決しようとして、複雑なクエリを書く。

理由

  • クエリが読みづらい
  • 間違った結果を出してしまう
  • クエリの修正、最適化コストが高くなる

解決策

  • クエリを分割する
    • UNIONを使って結果をまとめるのもあり

インプリシットカラム（暗黙の列）

アンチパターン

  • select * from tableのように *(ワイルドカード)やINSERT文で列名を明示的に指定しない
    • クエリを書く手間を省くために行う

理由

  • リファクタリングが難しくなる
  • INSERT
    • テーブル列が変更されると、誤った列に値が格納される
  • ワイルドカード
    • アプリケーション側でクエリ結果を添字で参照してた場合に誤った値が使われる
    • 必要のない値も取得するためパフォーマンスに影響がでる

解決策

  • 列名を明示的に指定する

リーダブルパスワード(読み取り可能パスワード)

アンチパターン

パスワードを平文で扱う

理由

  • パスワードが何かしらで公開されると悪用される
    • 暗号化してれば悪用されない

解決策

SQLインジェクション

アンチパターン

アプリケーション側で動的にクエリを組むが、未検証の入力をコードとして実行する。

task_id = '11'
sql = "select * from task where id = {}"
sql.format(task_id)

理由

task_id = '11; delete from task'
sql = "select * from task where id = {}"
sql.format(task_id)

普通はタスクをIDで検索するためのクエリ文なのに、下記のようにタスクテーブルを削除するクエリ文として悪用される。

select * from task where id = 11; delete from task

意図しないテーブル削除、変更がおきる。

解決策

  • プリペアドステートメントを使う
    • 値の挿入はSQL文の解釈とは別の言語処理が動くのでインジェクション対策になる。

シュードキー・ニートフリーク（疑似キー潔癖症）

DB技術に詳しくない人から欠損について指摘されたケース

アンチパターン

連番になっている疑似キーの欠損を埋める。

理由

  • 欠番を埋める
    • 自己結合クエリによりエラーを起こす場合がある。
  • ID振り直し
    • 欠番のキー特定とUPDATEの処理は競合状態を起こす。
  • データの整合性がとれなくなる。

解決策

疑似キーの欠損は埋めない。

  • 設計時にGUIDを使う
    • 欠番の不満が出ないことをメリットに挙げてるのは面白い。
    • 複数のDBで重複したGUIDが生成されない(確率的にほぼない)
      • IDだけでどのDB、テーブルのデータか判断できるのがメリットですかね？
  • 設計時に自然キーを使う
  • コミュニケーションで乗り切る
    • DBに詳しくない人にもわかるように技術的な説明をする
      • 行削除やロールバックで欠損が発生しているので問題なし。
    • コストとメリットが見合わないことを説明する

シー・ノー・エビル（臭いものに蓋）

データベース処理を簡潔なコードで書きたい

アンチパターン

  • データベースAPIの戻り値によって処理を制御しない
  • アプリケーションコードの点在するクエリしか読まない
    • 文字列を組み合わせるタイプのクエリ

理由

  • データベースに接続できない時やなにかしらのエラーが返ってきた時にアプリケーションコードが動作しなくなる
  • クエリが完成したときに予期しない空白文字やシンタックスエラーでクエリが実行できない

解決策

  • データベースAPIの戻り値によって例外処理を実装する
  • 実際に構築されたクエリを確認する
    • ログ出力されたクエリを確かめるのが楽そう

ディプロマティック・イミュニティ（外交特権）

開発においてベストプラクティスに努めようとする。

アンチパターン

DBに対してはベストプラクティスを適用しない

理由

  • DB管理者はチームを横断的にみる(インフラ、SRE的な立ち位置)ので特別扱いされる
  • アプリケーション開発と違ってDB用の高度なツールが少ない、知られていない
  • DBの運用は属人化しやすい

解決策

  • 文章化、バージョン管理、テスティングのベストプラクティスに努める。

個人的には組織整備が必要だと思う。

マジックビーンズ（魔法の豆）

MVCのModelを単純化するために、データベース設計を変える。

アンチパターン

Modelの大部分がアクティブレコードの基底クラスを継承する。

アクティブレコードとはデータサクセスパターンのひとつ。

ORMの機能の一つだと思う。

Ruby on RailslではActiveRecord(そのまま)として使われており、LaravelではEloquentに相当する。

SQL文でテーブル操作するのではなく、Classオブジェクトとして操作する？

理由

  • すこしでもテーブル定義を変更した場合、アプリケーションサイドの様々な箇所も変更する必要がある。
  • テーブルがCURDで操作できてしまうので、ロジックミスによる予期せぬデータ削除、更新などのトラブルが起きる。
  • CRUD以外のテーブル操作をするときにコードが複雑になる。
  • ユニットテストが難しくなる

解決策

  • GRASPに基づいて設計する
  • ドメインモデルを採用する
  • モデルとコントローラを疎結合にし、データアクセスを分離させる
    • スタブとモックを使ってテストしやすい

砂の城

心構え的なもの

アンチパターン

理由

  • 想定外のトラブルは必ず起きるから
  • データ採取による障害対応によってサービスに影響があるから

解決策

  • 障害に対する準備と対策をする
    • ベンチマーク、テスト環境構築、例外処理、バックアップ、高可用性、ディザスリカバリ
    • 運用ポリシーの策定

Discussion