『SQLアンチパターン』を読んだ①「I部 データベース論理設計のアンチパターン」
前の職場で読書会があったものの何回かしか参加できず通読できなかった『SQLアンチパターン』を読んでいる。
毎日1〜2個読んで、本文や調べたことをnotionにまとめていたのだけど、せっかくなのでその一部をこちらにも転記する。
引用の域を超えてしまうので本の内容をまとめた部分は端折って、感想とその周辺の最低限の内容だけを転載した(ら元のメモの2割くらいになった)。
1〜8章で共通していた視点
- メタデータとデータを分けて考える
- 参照整合性・一意性・データ型はデータベース側で保証する
- 外部キー制約やユニーク制約をちゃんと貼る、貼れるように設計する
- インデックス等のオーバーヘッドより、余計なSELECTやアプリケーション上の操作のほうが無駄
- 常にアプリケーションコードを介してデータ操作が行われるとは限らない
- 将来的に増えるもの・変わりうるものを想定して決める
- テーブルやカラムの数には上限がある
- 個別のテーブル名やカラム名を列挙するようなクエリは保守がしにくい
各章
1. ジェイウォーク(信号無視)
- 交差テーブル、solidusで慣れっこだぜ
- 「ベンダー中立」データベースによって挙動や構文が異なるとそのDBベンダーにロックインされてしまうのでよくない、なるほど
2. ナイーブツリー(素朴な木)
- 経路列挙モデル
WHERE '1/4/6/7/' LIKE c.path || '%'
で親を取得||
何かと思ったらORではなくCONCAT()
として使えるらしい- https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html
SET sql_mode = 'PIPES_AS_CONCAT';
- WHERE句の右辺側にカラムを持ってくるのなるほど……
- 閉包テーブル:1つのパスを表現するテーブルを作る
- 先祖IDカラムと子孫IDカラムを持つ
- 自己参照(先祖IDと子孫IDが同一)のレコードも入れる
- 「ノード1が親、ノード2が子」のパスは
先祖ID = 1
子孫ID = 2
- けっきょく交差テーブルということか
- 関係性も1つのオブジェクト、みたいにとらえるとイメージしやすいかも
- solidusでやたら中間テーブルあって大変な思いをしたけど、その分柔軟だったな
- 先祖IDカラムと子孫IDカラムを持つ
3. ID リクワイアド(とりあえず ID)
- アンチパターン:元のモデルに、主キーになりうる意味のある値(自然キー)が存在する場合にも、全てのテーブルの主キーに
id
列を用いる- Railsだとそうするしな〜 🤔
- ↓言われてみれば確かにそう
主キーは「あるテーブル上の 1 つの行」を識別するためのも のなので、主キーの列名でもそのテーブルの種類が分かるようにしておくべきです。そうすれば「bug_id = 1」という情報だけで、データベース上の 1 行を特定することができます。
- 主キーの概念とデータ型の概念を切り離し、適切なカラムを主キーにする
- 主キーは数値型じゃないと駄目、インクリメンタルじゃないと駄目、ということはない
- なるほどたしかに〜〜〜
- 解決策:主キーにはわかりやすい名前をつけること
そういえばRailsでも変えられる……。ただbug.bug_id
はちょっと気持ち悪い気もする
class Bug < ActiveRecord::Base self.primary_key = 'bug_id' end
4. キーレスエントリ(外部キー嫌い)
- MyISAM、外部キー制約無いんだ……
- カスケード更新 のオプション(MySQL)
- 外部キーを指定する際に、参照先のテーブル(のレコード)の更新時・削除時の挙動が指定できる
- MySQLリファレンスより
RESTRICT
← 参照オプションが指定されていない場合のデフォルト- 親テーブルの更新・削除操作を拒否する
CASCADE
ON UPDATE CASCADE
親テーブルの該当レコーとの該当カラムが更新された場合、子テーブルのレコードのカラムも同じ値に更新するON DELETE CASCADE
親テーブルの該当レコードが削除された場合、子テーブルのレコードも削除する
SET NULL
- 親テーブルの該当レコードが更新または削除された場合、子テーブルのレコードの当該カラムをNULLにする
- 子テーブル側のカラムがNULLを許容している必要がある
NO ACTION
SET DEFAULT
5. EAV(エンティティ・アトリビュート・バリュー)
- アンチパターン:属性テーブル
- IssuesテーブルとIssuesAttributesテーブルを作る
- IssuesテーブルはIDだけを持つ
- IssuesAttributesはIssuesテーブルのIDと、属性名と、値を持つ
- Issue has many IssuesAttributes の関係
- IssuesAttributesの1レコードはIssueの1属性を表す
- やばそう
- へー
導入時の初期設定が不要で、裏でデータベースを使っているようなソフトウェアパッケージの多くは、 カスタマイズしやすいように、EAV 設計を採用しています
- IssuesテーブルとIssuesAttributesテーブルを作る
7. マルチカラムアトリビュート(複数列属性)
- 解決策の従属テーブル、solidusのaddressesが近かったかも
8. メタデータトリブル(メタデータ大増殖)
レコードが大きくなると必ずパフォーマンスが低下すると思いこみ、とにかくレコードが増えないようにメタデータを設計してしまう
- しかし実際は、毎年データも増えカラムも追加されるテーブルではいずれパフォーマンスが低下する
- たしかに
MySQLの水平パーティショニング(シャーディング)
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.2 パーティショニングタイプ
- パーティション中のレコードをさらにパーティショニングすることも可能とのこと
- RANGEパーティショニング
- リファレンス
PARTITION BY RANGE (expr) ( PARTITION p0 VALUES LESS THAN (n), ...)
expr
には整数値を示す式が入る- 整数型のカラムを指定するのはRANGE COLUMNSパーティショニング
expr
の値がどの範囲に含まれるかによって、入るパーティションが直接指定される
- LISTパーティショニング
- リファレンス
PARTITION BY LIST (expr) ( PARTITION p0 VALUES IN (n1, n2, ...), ...)
expr
には整数値を示す式が入る- 整数型以外のカラムを指定するのはLIST COLUMNSパーティショニング
expr
の値がどのリストに含まれるかによって、入るパーティションが直接指定される
- HASHパーティショニング
- KEYパーティショニング
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.2 パーティショニングタイプ
- 垂直パーティショニング
MySQLは垂直パーティショニングやってくれない? 謎
MySQL 8.0 does not support vertical partitioning, in which different columns of a table are assigned to different physical partitions. There are no plans at this time to introduce vertical partitioning into MySQL.
「LOB型のカラムは多くのデータベース製品が自動でパーティショニングする」そうなのか
ただし*
でSELECTすると、全カラムを取るために↑のパーティショニングが無駄になるとのこと