SQLアンチパターン
素朴な木
再帰クエリ構文
確かにコメント欄のリプライなど、ツリー構造になっているデータをどのようにDBに格納する?というのはパッと考えて思いつかなかった
オーバーエンジニアリングに注意しよう、のコラムはまさにありそうだなという感じ
データセンターの設備管理アプリケーションの開発
線路列挙のデータ
UNIXのパスの表現と同じような形式
コメントがどのようなツリー構造の中にあるかをパスの形で表現する
弱点はジェイウォークのアンチパターンに陥る危険性があること
挿入できる文字列(=パス)の長さに制限がある
挿入されるパスの正確な形式や、既存ノードに正しく対応していることなどを担保できない
入れ子集合
各ノードを集合と見なして、nsleftとnsrightの値を使って下層のノードの集合を表現する
各ノードにnsleftとnsrightの値を割り当てるが、その値は要素のIDとは関係ないので注意
これ知らなかった、かつ直感的にわかりづらいmochi5o.icon
閉包テーブル
これも知らなかった
隣接するノード以外にもツリー全体のパスを格納する
パスを格納する、といっても自分自身(先祖)と自分の子孫に当たるノードの組み合わせを保持する
先祖と子孫の関係性を全て閉包テーブルに保持する
コメント6の先祖を探すには、子孫にコメントID6を持つノードを探せば良い
閉包テーブルで関係性を削除したとしても元のコメントテーブルのデータは消えないのがポイント
例えば人事異動などで配置図が変わるときなどは社員自体は消えないが関係性が削除され新たに作成される必要がある
ノードが複数のツリーへ所属することができる
とりあえずID
idカラムによる主キーが必要になるシーン以外で無闇にidカラムによる主キー(擬似キー)を使わないこと
テーブル内でユニークな値として自然キーが存在する場合
フレームワークで標準的にidカラムが付与されるので思考停止になっている感は確かにあるmochi5o.icon
複合キーを使っている場合はidカラムが意味をなさない
ORMを使う場合は、規約に沿ってテーブルを作成する必要があるので必ずしもこのアンチパターンを回避する必要はない
ORMを使っていてもidカラムの名前を書き換えることはできるので検討する
主キーは制約であり、データ型ではない
単一カラムや複数カラムを主キーとして用いることもできるし、AutoIncrementのカラムを主キーにもできるし、ユニークな文字列カラムを主キーにすることもできる
基本的に分かりやすいカラム名をつけること!
idだけだと分かりづらいケースもあるので思考停止にならないこと
外部キー嫌い
データベースに関する全てのコードが完ぺきであることは、まずあり得ないと考えていいでしょう
心当たりがありすぎるmochi5o.icon
ポカヨケ(poka-yoke)という「ミス防止」を意味する日本発の言葉が紹介されていたけど知らないmochi5o.icon
古いDBMSでなかったらカスケード処理が使えるはずなので、外部キー使いたくないなってなることがあんまりないかもmochi5o.icon
メタデータ大増殖
モデリングの観点から単一データベースでの運用が正しいと思われるケースでも、スケーラビリティを考えると単一データベースでの管理はリスクがあるということを認識すること
コラム内では上記のようにデータベースという記載だったが前後の文面から考えるとテーブルの話をしているかもしれない?
このコラムの後テーブル分割の話が続いていたmochi5o.icon
水平パーティショニング(シャーディング)
テーブルを行で分割する
テーブルの分割を行えるが、論理的に一つのテーブルを管理しているように扱うことができる
MySQL標準の機能ではないが各製品でパーティショニング機能を提供しているとのこと
垂直パーティショニング
テーブルを列で分割する
例えばデータ量の大きいtextデータ型のカラムを分割しておくことで、このカラムを参照する必要がない場合のクエリが高速になるなどのメリットがある
丸め誤差
floatとdoubleの使い分け、正直まだ曖昧mochi5o.icon
FLOAT, DOUBLE, REAL, PRECISIONなどのデータ型を用いると、「完全に正確な値」は期待できない
NUMERIC型もしくは DECIMAL型を使うこと
これらは同じデータ型(DECと省略されることも)
MENTOR
Measure, Explain, Nominate, Test, Optimize, Rebuildの頭文字
スロークエリログ
設定値long_query_timeのデフォルトは10秒(MySQL, PostgreSQL)
NULLを含む列のデータを他の列と連結するとNULL