https://www.slideshare.net/hironorimiura/sql-34880588

SQLアンチパターン メンター用資料

    1. SQLアンチパターン メンター用資料 VOYAGE GROUP システム本部 ERDG 三浦 裕典@hironomiu
  2. 2. 0章 この資料について • この資料について • O’ReillyのSQLアンチパターン勉強会用のメンター用として作成 • 書籍と合わせて利用するための資料です • 個人の主観が大きく入っています • サンプルデータベースについて • SQLアンチパターン書籍中 xxi ~ xxiii 参照 • サンプルコード • http://www.oreilly.co.jp/books/9784873115894/ • bksqla-code.tgz • その他 • MySQLを前提に記載しています（Oracleも場合により）
  3. 3. 0章 はじめに • xviii 世の中のアンチパターンの多くにはユーモラスで刺激的なタイトルがつい ています。 • 「黄金のハンマー(Golden Hammer)」 • 「車輪の再発明(Reinventing the Wheel)」 • 「委員会による設計(Design by Committee)」 • その他は wikipedia:アンチパターン 参照 • xix 本書のテーマはSQLとリレーショナルデータベースであり、他の代替手段 ではありません。
  4. 4. 1章ジェイウォーク • Gist • ジェイウォーク（題名）の由来 • 交差テーブル（交差点）ー＞ 無視してカンマ区切りのデータの作成（信号無視）＝ ジェイウォーク • 1.2 アンチパターンを実践するDDLの流れ？ • テーブルのカラムをINTからVARCHARに変更することによるデータの洗い替え方法を考える • サービス無停止(サービス稼働中)で実行可能か？ • 1.2.1 regexpやfind_in_setの実行計画 • 等価性による比較が出来なくなるデメリットとは？ • 一つはインデックスで実現されているキーからの導出が困難
  5. 5. 1章ジェイウォーク • 1.2.2 例のSQLが直積となっているイメージが湧くか？（Products × Accounts） • クロス積の記載改修済み(product_idで絞り込み後Accountsのレコード数分、正規表現で突きあわせ) • 1.2.3 実装しているSQLはどういう意図のSQL？ • 1.2.4 • remove.phpを参照しアカウントの更新、削除がキーで導出出来るケースとどれだけ差がある？ • 業務でこの変更(追加、更新、削除)処理をしようした場合どこらへんに気をつけることがある？ • ロストアップデートの危険 • どうやって回避する？ • プログラム的にはproduct_id = 123はプレースフォルダを使うべきだがサンプルコードの意味が大きいので割愛 • 1.2.5 bananaの妥当性はどのようにする？一般的にはどのように担保する？（制約） • 1.2.6 個々の入力値に区切り文字が含まれる場合は？
  6. 6. 1章ジェイウォーク • 1.3 アンチパターンの再確認 • アンチパターンを用いても良い場合以外、基本交差テーブルを作成すること • 1.4 アンチパターンを用いても良い場合 • 業務でアンチパターンの利用例はある？ • リスト内の各要素への個別アクセスが不要 ＝ 導出することが無い（text型のデータなど） • 1.5 交差テーブルを作成する • 交差テーブルとは多対多の関連を表現するテーブル ＝ 連関エンティティ • 1.5.7 交差テーブルの他のメリットについて考えてみましょう • 例えば、連絡先が追加された日付を記録し • 閑話 FKをつける際に気をつける事 • 親、子のカラム定義は厳密に同じ内容を指定すること（null ,not null,正負など）
  7. 7. 2章ナイーブツリー • Gist • 2.1 階層構造とは • 組織図 • 商品カテゴリ • 掲示板（この章は掲示板を前提に記されている） • ネットワーク図ではありません（循環しない等） • 隣接リスト（＋再起クエリ） • 再起クエリ WITH句、CONNECT BY PRIOR構文 • http://www.oracle.com/technetwork/jp/articles/otnj-sql-image7-1525406-ja.html • 親キー ＝ 子キー のデータを挿入した場合どのような問題が生じるか（循環＝無限ループ • 親子関係のSQLは直感的にわかりやすい
  8. 8. 2章ナイーブツリー • 2.2 なぜ隣接リストはアンチパターンなのか？ • 2.2.2 それは、すべての子孫を取得するクエリ • そもそも1回のSQLで任意の階層データ全てを取得する要件はどのようなものか？ • コメント数のようなcountがしずらい(か？)(数件 ~ 数万 ~ 数千億) • 2.4 アンチパターンを用いてもよい場合 • 隣接リスト設計は、アプリケーションで求められているタスクに適している状態とは？ • 2.5 解決策 • 隣接モデルでは要件が満たせない場合に選択 • パフォーマンス • ノード変更の柔軟性
  9. 9. 2章ナイーブツリー • 2.5.1 経路列挙 • パンくずリスト • ジェイウォークと同様の問題点を抱える • 存在しない親、不正な文字列が挿入された場合（外部キーによる 整合性が保証出来ない） • 挿入出来る文字数の限界 • pathに自分のIDを入れる理由は？
  10. 10. 2章ナイーブツリー • 2.5.2 入れ子集合 • P25 しかし入れ子集合では、直近の親の取得などの、隣接リストでは簡単に実行できるクエリの一部が複雑になっ てしまいます。 • SQLが直感的にわかりずらい • 参照整合性により親子関係のキーを制限できない • 参考 SQLで木と階層構造のデータを扱う（１）―― 入れ子集合モデル • http://www.geocities.jp/mickindex/database/db_tree_ns.html • 業務で実装実績あり • SQLは複雑になるが大規模な親子データを「塊」としてを扱う場合にパフォーマンスの観点で有利 • 参考 発展系 入れ子区間集合 • http://gihyo.jp/dev/serial/01/sql_academy2/000601
  11. 11. 2章ナイーブツリー • 2.5.3 閉包テーブル • ジェイウォークの解決版？ • TreePaths ＝ 交差テーブル（連関エンティティ） • 10階層目にレコードを挿入する場合55行TreePathsには挿入？ • 自分のIDを入れる理由？ • 参照整合性により親子関係のキーを制限できる
  12. 12. 2章ナイーブツリー • 2.5.4 比較表から適所を考える（隣接リスト、経路列挙、入れ子集合、閉包テーブル） • 自分から親をたどる • 自分から子をたどる • どれが自分の親か？（複数の先祖（親、祖父、祖祖父）がいる場合） • メンテナンス性 • 挿入（末端に追加、途中に追加） • 削除（自身のみ削除、自身から子孫についても削除） • 更新（自身の更新、属する親の変更、自身から子孫についても従属） • 親子キーの整合性の保証（参照整合性）
  13. 13. 3章IDリクワイアド • 3.1 目的：主キーの規約を確立する • 擬似キー • 代理キー • 35P 主キーは必要か？ • リレーショナルモデルでの集合ではなく物理実装されたRDBMSにてレコードを一意にする方法は？ • RowID、PK、etc • 主キー(ナチュラルキー)を更新(変更)したいケースが存在するか • カラムデータの変更に対する弾力性 • マスタテーブルなどでサロゲートキーをPKにするケースで多い • 余談 パーティションテーブル、主キー = パーティションキーの場合、内部ではどのような動きが発生しているか
  14. 14. 3章IDリクワイアド • 3.2 なぜこれがアンチパターンなのか考える • 特定のプラットフォームでの仕様による考慮（Innodb クラスタインデックス） • http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-index-types.html • RDBMSでの行の一意方法（rowid;Oracle, PK:MySQL Innodb） • 実演 userテーブルに100万レコード で UUIDのようなuser_id 主にインデック スのスプリットについて考える • サロゲートキーでのinsert • user_idをPKにしてinsert
  15. 15. 3章IDリクワイアド • 論理設計にてマスタ、トランザクション（＋サマリー）の観点でIDを考える • 3.2.1 冗長なキーが作成されてしまう • マスタテーブルを例にメリット、デメリット（変更に対する弾力性、親子関係（外部整 合性制約）によるデータの妥当性） • 3.2.2 重複行を許可してしまう • Unique制約で対応する場合、IDは無駄になるか？ • 3.2.3 キーの意味 テーブル名＋カラム名、カラム名だけで意味をもたす？（3.5.1も同様 ） • P39 シーケンスについて（auto_increment、Oracle sequence、などなど）