Movatterモバイル変換

和田卓人 (@t_wada)Apr 20, 2013 @ デブサミ再演SQLアンチパターン(拡大版)

和田卓人id: t-wada@t_wadagithub: twada

1. アンチパターンとは2. 本書のダイジェスト3. おわりにAgenda

アンチパターンとはべからず集あるある集だけでは無い

0. 名前1. 目的2. アンチパターン3. アンチパターンの見つけ方4. アンチパターンを用いても良い場合5. 解決策名前重要!!本書のアンチパターンの構成

例: ナイーブツリー(素朴な木)名付けの例

パターン名が英語そのままカタカナ表記であるのは、目次を見ただけではビックリするポイントですね。ただ、チーム内で相談するときなどに目立つ名前が付いているのはむしろありがたいですし、何よりなんかカッコよくておもしろかったです!http://d.hatena.ne.jp/moro/20130205/1360044434なぜカタカナ!?

0. 名前1. 目的2. アンチパターン3. アンチパターンの見つけ方4. アンチパターンを用いても良い場合5. 解決策実例: ナイーブツリー(素朴な木)

目的: 階層構造を格納し、クエリを実行する

アンチパターンとは何でしょうか。それは、問題の解決を意図しながらも、しばしば他の問題を生じさせてしまうような技法を指します。─ Bill Karwinよかれと思って裏目に出てしまうもの

CREATE TABLE Comments (comment_id SERIAL PRIMARY KEY,parent_id BIGINT UNSIGNED,comment TEXT NOT NULL,);親idが入るアンチパターン: 常に親のみに依存する

SELECT c1.*, c2.*, c3.*, c4.*FROM Comments c1 -- 1階層目LEFT OUTER JOIN Comments c2ON c2.parent_id = c1.comment_id -- 2階層目LEFT OUTER JOIN Comments c3ON c3.parent_id = c2.comment_id -- 3階層目LEFT OUTER JOIN Comments c4ON c4.parent_id = c3.comment_id -- 4階層目アンチパターンにより起こること素朴すぎる故にアンチパターン

直面している問題の種類や、メンバー間の会話での何気ない言葉が、そこにアンチパターンがあるかもしれないことに気づくヒントになります。─ Bill Karwin

アンチパターンの見つけ方「このツリーでは、深さを何階層までサポートすればいい？」「ツリー型のデータ構造を扱うコードなんて二度と書きたくないな」「ツリーの中で孤児になった行をきれいにするために、定期的にスクリプトを実行しなければ」

WITH CommentTree(comment_id, bug_id, parent_id, author, comment, depth)AS (SELECT *, 0 AS depth FROM CommentsWHERE parent_id IS NULLUNION ALLSELECT c.*, ct.depth+1 AS depth FROM CommentTree ctJOIN Comments c ON ct.comment_id = c.parent_id)SELECT * FROM CommentTree WHERE bug_id = 1234;アンチパターンを用いても良い場合共通テーブル式(CTE：common table expression)を使って再帰クエリを書ける場合

本書にはアンチパターンを適用しても良い状況の説明もあって好感が持てます。 (略) この本は単なる「べからず集」ではなく「パターン本」だからです。コンテキストや制約が異なれば導かれる解法も異なるというわけです。アンチパターンを用いても良い場合http://yojik.hatenablog.jp/entry/2013/02/13/235729

解決策: 代替ツリーモデルを使用するcomment_id path 発言者コメント1 1/ Fran このバグの原因は何かな？2 1/2/ Ollie ヌルポインターのせいじゃないかな？3 1/2/3/ Fran そうじゃないよ。それは確認済みだ。4 1/4/ Kukla 無効なインプットを調べてみたら？5 1/4/5/ Ollie そうか、バグの原因はそれだな。6 1/4/6/ Franよし、じゃあチェック機能を追加してもらえるかな？7 1/4/6/7/ Kukla 了解。修正したよ。策1: 経路列挙 (Path Enumeration)

2. 入れ子集合NestedSet3. 閉包テーブルClosure Table解決策: 代替ツリーモデルを使用する

設計テーブル数子へのクエリ実行ツリーへのクエリ実行挿入削除参照整合性維持隣接リスト 1 簡単難しい簡単簡単可能再帰クエリ 1 簡単簡単簡単簡単可能経路列挙 1 簡単簡単簡単簡単不可入れ子集合 1 難しい難しい難しい難しい不可閉包テーブル 2 簡単簡単簡単簡単可能解決策: 代替ツリーモデルを使用する解決策の比較表

4つの部25のパターンを一巡り

1. 論理設計2. 物理設計3. クエリ4. アプリケーション

1. ジェイウォーク（信号無視）2. ナイーブツリー（素朴な木）3. IDリクワイアド（とりあえずID）4. キーレスエントリ（外部キー嫌い）5. EAV（エンティティ・アトリビュート・バリュー）6. ポリモーフィック関連7. マルチカラムアトリビュート（複数列属性）8. メタデータトリブル（メタデータ大増殖）論理設計のアンチパターンざわ…ざわ…

CREATE TABLE Products (product_id SERIAL PRIMARY KEY,product_name VARCHAR(1000),account_id VARCHAR(100));ジェイウォーク (信号無視)‘111,222,333’カンマ区切りで多対多関連を格納してしまう

解決策: 交差テーブルを作成するCREATE TABLE Contacts (product_id BIGINT UNSIGNED NOT NULL,account_id BIGINT UNSIGNED NOT NULL,PRIMARY KEY (product_id, account_id),FOREIGN KEY (product_id) REFERENCES Products(product_id),FOREIGN KEY (account_id) REFERENCES Accounts(account_id));

CREATE TABLE Comments (comment_id SERIAL PRIMARY KEY,parent_id BIGINT UNSIGNED,comment TEXT NOT NULL,);親idが入るナイーブツリー (素朴な木)

CREATE TABLE BugsProducts (id SERIAL PRIMARY KEY,bug_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,UNIQUE KEY (bug_id, product_id),FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),FOREIGN KEY (product_id) REFERENCES Products(product_id));考え無しにとりあえず主キーを“id”にしてしまうID リクワイアド (とりあえずID)

解決策: 状況に応じて適切に調整するわかりやすい列名 (id より bug_id)SELECT * FROM Bugs INNER JOIN BugsProducts USING (bug_id);規約に縛られない自然キーと複合キーの活用同じ名前ならUSINGが使える考えた結果の“id”ならそれで良し

CREATE TABLE Bugs (-- 他の列. . .reported_by BIGINT UNSIGNED NOT NULL,status VARCHAR(20) NOT NULL DEFAULT 'NEW',FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),FOREIGN KEY (status) REFERENCES BugStatus(status));キーレスエントリ(外部キー嫌い)外部キーを定義せず,整合性をアプリで保つ→アプリ以外からゴミデータが入る

CREATE TABLE Bugs (-- 他の列. . .reported_by BIGINT UNSIGNED NOT NULL,status VARCHAR(20) NOT NULL DEFAULT 'NEW',FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),FOREIGN KEY (status) REFERENCES BugStatus(status));解決策: 外部キー制約を宣言するきちんと外部キーを定義する

CREATE TABLE Issues (issue_id SERIAL PRIMARY KEY);CREATE TABLE IssueAttributes (issue_id BIGINT UNSIGNED NOT NULL,attr_name VARCHAR(100) NOT NULL,attr_value VARCHAR(100),PRIMARY KEY (issue_id, attr_name),FOREIGN KEY (issue_id) REFERENCES Issues(issue_id));EAV（エンティティ・アトリビュート・バリュー）FK,名前,値FK,名前,値……動的な項目を格納したい

解決策: サブタイプのモデリングを行うシングルテーブル継承具象テーブル継承クラステーブル継承シリアライズ LOBPofEAA 読むべし

CREATE TABLE Comments (comment_id SERIAL PRIMARY KEY,issue_type VARCHAR(20),issue_id BIGINT UNSIGNED NOT NULL,author BIGINT UNSIGNED NOT NULL,comment_date DATETIME,comment TEXT,FOREIGN KEY (author) REFERENCES Accounts(account_id));ポリモーフィック関連'Bugs' または'FeatureRequests' が入る

解決策:関連(リレーションシップ)を単純化するCREATE TABLE BugsComments (issue_id BIGINT UNSIGNED NOT NULL,comment_id BIGINT UNSIGNED NOT NULL,PRIMARY KEY (issue_id, comment_id),FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),FOREIGN KEY (comment_id) REFERENCES Comments(comment_id));CREATE TABLE FeaturesComments (issue_id BIGINT UNSIGNED NOT NULL,comment_id BIGINT UNSIGNED NOT NULL,PRIMARY KEY (issue_id, comment_id),FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id),FOREIGN KEY (comment_id) REFERENCES Comments(comment_id));基底テーブルが必要ならPofEAA の継承マッピング普通に交差テーブル

CREATE TABLE Bugs (bug_id SERIAL PRIMARY KEY,description VARCHAR(1000),tag1 VARCHAR(20),tag2 VARCHAR(20),tag3 VARCHAR(20));マルチカラムアトリビュート（複数列属性）空いている列に値が入る

解決策: 従属テーブルを作成するCREATE TABLE Tags (bug_id BIGINT UNSIGNED NOT NULL,tag VARCHAR(20),PRIMARY KEY (bug_id, tag),FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id));SELECT * FROM BugsINNER JOIN Tags AS t1 USING (bug_id)INNER JOIN Tags AS t2 USING (bug_id)WHERE t1.tag = 'printing' AND t2.tag = 'performance';複数の列ではなく複数の行に格納すること

CREATE TABLE Bugs_2008 ( . . . );CREATE TABLE Bugs_2009 ( . . . );CREATE TABLE Bugs_2010 ( . . . );メタデータトリブル（メタデータ大増殖）年が変わる毎にテーブルや列を追加している

解決策: パーティショニングと正規化を行うCREATE TABLE Bugs (bug_id SERIAL PRIMARY KEY,-- 他の列. . .date_reported DATE) PARTITION BY HASH ( YEAR(date_reported) )PARTITIONS 4;水平パーティショニング(シャーディング)

09. ラウンディングエラー（丸め誤差）10. サーティワンフレーバー（31のフレーバー）11. ファントムファイル（幻のファイル）12. インデックスショットガン（闇雲インデックス）物理設計のアンチパターン否定意見多し

ALTER TABLE Bugs ADD COLUMN hours FLOAT;ALTER TABLE Accounts ADD COLUMN hourly_rate FLOAT;SELECT hourly_rate * 1000000000 FROM Accounts WHEREaccount_id = 123;結果: 59950000762.939ラウンディングエラー（丸め誤差）少数値を表すために浮動小数点数を使ってしまう

解決策: NUMERIC データ型を使用する精度(桁の総数)スケール(小数点以下に格納できる桁数)ALTER TABLE Bugs ADD COLUMN hours NUMERIC(9,2);ALTER TABLE Accounts ADD COLUMN hourly_rate NUMERIC(9,2);SELECT hourly_rate * 1000000000 FROM Accounts WHEREaccount_id = 123;結果: 59950000000

CREATE TABLE Bugs (-- 他の列. . .status VARCHAR(20) CHECK (status IN('NEW','IN PROGRESS','FIXED')));サーティワンフレーバー（31のフレーバー）許可する値を列定義で指定する→ 許可値を取得できない

解決策: 限定する値をデータで指定するCREATE TABLE BugStatus (status VARCHAR(20) PRIMARY KEY);INSERT INTO BugStatus (status) VALUES ('NEW'),('IN PROGRESS'), ('FIXED');CREATE TABLE Bugs (-- 他の列. . .status VARCHAR(20),FOREIGN KEY (status) REFERENCES BugStatus(status)ON UPDATE CASCADE);外部キーで整合性保証

CREATE TABLE Screenshots (bug_id BIGINT UNSIGNED NOT NULL,image_id BIGINT UNSIGNED NOT NULL,screenshot_path VARCHAR(100),caption VARCHAR(100),PRIMARY KEY (bug_id, image_id),FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id));ファントムファイル（幻のファイル）物理ファイルの使用を必須と思い込む

解決策: 必要に応じてBLOB型を採用するCREATE TABLE Screenshots (bug_id BIGINT UNSIGNED NOT NULL,image_id BIGINT UNSIGNED NOT NULL,screenshot_image BLOB,caption VARCHAR(100),PRIMARY KEY (bug_id, image_id),FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id));整合性、トランザクション、ロック、権限管理がメリット

CREATE TABLE Bugs (bug_id SERIAL PRIMARY KEY,date_reported DATE NOT NULL,summary VARCHAR(80) NOT NULL,status VARCHAR(10) NOT NULL,hours NUMERIC(9,2),INDEX (bug_id),INDEX (summary),INDEX (hours),INDEX (bug_id, date_reported, status));インデックスショットガン（闇雲インデックス）闇雲にインデックスを定義してしまう

解決策: 「MENTOR」の原則に基づいて効果的なインデックス管理を行うMeasure (測定)Explain (解析)Nominate (指名)Test (テスト)Optimize (最適化)Rebuild (再構築)

13. フィア・オブ・ジ・アンノウン（恐怖のunknown）14. アンビギュアスグループ（曖昧なグループ）15. ランダムセレクション16. プアマンズ・サーチエンジン（貧者のサーチエンジン）17. スパゲッティクエリ18. インプリシットカラム（暗黙の列）クエリのアンチパターン様々な解決策

CREATE TABLE Bugs (bug_id SERIAL PRIMARY KEY,-- 他の列. . .assigned_to BIGINT UNSIGNED NOT NULL,hours NUMERIC(9,2) NOT NULL,FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id));INSERT INTO Bugs (assigned_to, hours) VALUES (-1, -1);SELECT AVG( hours ) AS average_hours_per_bug FROM BugsWHERE hours <> -1;フィア・オブ・ジ・アンノウン（恐怖のunknown）NULLを嫌う → おかしなことに

解決策: NULL を一意な値として使うSELECT * FROM Bugs WHERE assigned_to IS NULL;SELECT * FROM Bugs WHERE assigned_to IS NOT NULL;SELECT * FROM Bugs WHERE assigned_to IS NULL ORassigned_to <> 1;SELECT * FROM Bugs WHERE assigned_to IS DISTINCT FROM 1;IS DISTINCT FROM ならプリペアドステートメントにも使える!

SELECT p.product_id,MAX(b.date_reported) AS latest,b.bug_idFROM Bugs b INNER JOIN BugsProducts p USING (bug_id)GROUP BY p.product_id;アンビギュアスグループ（曖昧なグループ）MAX(date_reported)のbug_idが返るとは限らない

解決策: 曖昧でない列を使うSELECT m.product_id, m.latest, MAX(b1.bug_id) ASlatest_bug_idFROM Bugs b1 INNER JOIN(SELECT product_id, MAX(date_reported) AS latestFROM Bugs b2 INNER JOIN BugsProducts USING (bug_id)GROUP BY product_id) mON b1.date_reported = m.latestGROUP BY m.product_id, m.latest;相関サブクエリ導出テーブルJOIN の使用などの手段がある(例は導出テーブル)

SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;ランダムセレクション非決定性を持つ式によってソートを行ってしまう

解決策: 特定の順番に依存しないSELECT b1.*FROM Bugs AS b1INNER JOIN (SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROMBugs)) AS bug_id) AS b2 ON b1.bug_id >= b2.bug_idORDER BY b1.bug_idLIMIT 1;様々なテクニック(例は欠番の穴の後にある番号を取得するSQL)

SELECT * FROM BugsWHERE description LIKE '%one%';プアマンズ・サーチエンジン（貧者のサーチエンジン）あいまい検索にパターンマッチ述語を使用してしまう

解決策: 適切なツールを使用する1. ベンダー拡張の全文検索機能2. サードパーティーの全文検索エンジン3. 転置インデックスの自作SQL だけでがんばらない

SELECT p.product_id,COUNT(f.bug_id) AS count_fixed,COUNT(o.bug_id) AS count_openFROM BugsProducts pINNER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED'INNER JOIN BugsProducts p2 USING (product_id)INNER JOIN Bugs o ON p2.bug_id = o.bug_id AND o.status = 'OPEN'WHERE p.product_id = 1GROUP BY p.product_id;スパゲッティクエリ複雑な問題をワンステップで解決しようとする

解決策: 分割統治を行う(SELECT p.product_id, 'FIXED' AS status, COUNT(f.bug_id) AS bug_countFROM BugsProducts pLEFT OUTER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED'WHERE p.product_id = 1GROUP BY p.product_id)UNION ALL(SELECT p.product_id, 'OPEN' AS status, COUNT(o.bug_id) AS bug_countFROM BugsProducts pLEFT OUTER JOIN Bugs o ON p.bug_id = o.bug_id AND o.status = 'OPEN'WHERE p.product_id = 1GROUP BY p.product_id)ORDER BY bug_count DESC;ワンステップずつ解く必要なら UNION 等

SELECT * FROM Bugs;インプリシットカラム（暗黙の列）タイプ数を減らしたい欲望に負ける→不要な列まで取得したり、定義変更がバグの元になる

解決策: 列名を明示的に指定するSELECT bug_id, date_reported, summary, description, resolution,reported_by, assigned_to, verified_by, status, priority, hoursFROM Bugs;INSERT INTO Accounts (account_name, first_name, last_name, email,password_hash, portrait_image, hourly_rate)VALUES ('bkarwin', 'Bill', 'Karwin', 'bill@example.com',SHA2('xyzzy', 256), NULL, 49.95);誤りの防止(フェイルファースト)必要な列だけ指定

19. リーダブルパスワード（読み取り可能パスワード）20. SQLインジェクション21. シュードキー・ニートフリーク（疑似キー潔癖症）22. シー・ノー・エビル（臭いものに蓋）23. ディプロマティック・イミュニティ（外交特権）24. マジックビーンズ（魔法の豆）25. 砂の城奥野さん書き下ろし！アプリケーションのアンチパターン

CREATE TABLE Accounts (account_id SERIAL PRIMARY KEY,account_name VARCHAR(20) NOT NULL,email VARCHAR(100) NOT NULL,password VARCHAR(30) NOT NULL);INSERT INTO Accounts (account_id, account_name, email, password)VALUES (123, 'billkarwin', 'bill@example.com', 'xyzzy');リーダブルパスワード（読み取り可能パスワード）パスワードを平文で格納してしまう

解決策: ソルトをつけてパスワードハッシュを格納する<?php$stmt = $pdo->query("SELECT saltFROM AccountsWHERE account_name = 'bill'");$row = $stmt->fetch();$salt = $row[0];$hash = hash('sha256', $password . $salt);$stmt = $pdo->query("SELECT (password_hash = '$hash') AS password_matchesFROM Accounts AS aWHERE a.account_name = 'bill'");$row = $stmt->fetch();if ($row === false) {// アカウント'bill'は存在しない} else {$password_matches = $row[0];if (!$password_matches) {// パスワードが間違っている}}ソルトとハッシュ

<?php$project_name = $_REQUEST["name"];$sql = "SELECT * FROM Projects WHERE project_name = '$project_name'";↓http://bugs.example.com/project/view.php?name=O'Hare↓SELECT * FROM Projects WHERE project_name = 'O'Hare';SQLインジェクション未検証の入力をクエリにつなげて実行してしまう

解決策: 誰も信用してはならない<?php$sql = "UPDATE AccountsSET password_hash = SHA2(?, 256)WHERE account_id = ?";$stmt = $pdo->prepare($sql);$stmt->bindValue(1, $_REQUEST["password"], PDO::PARAM_STR);$stmt->bindValue(2, $_REQUEST["userid"], PDO::PARAM_INT);$stmt->execute();あえて言うなら、第一にプリペアドステートメントを使うべし

bug_id status product_name----------------------------1 OPEN Open RoundFile2 FIXED ReConsider4 OPEN ReConsiderUPDATE Bugs SET bug_id = 3 WHERE bug_id = 4;シュードキー・ニートフリーク（疑似キー潔癖症）気になるので欠番の隙間を埋めてしまう

解決策: 疑似キーの欠番は埋めない解決すべき大きな問題が残っています。それは、疑似キーの欠番を埋めろという上司からの要求を、どうやって断るのかという問題です。これは技術ではなく、コミュニケーションの問題です

<?php$pdo = new PDO("mysql:dbname=test;host=db.example.com","dbuser", "dbpassword");$sql = "SELECT bug_id, summary, date_reported FROM BugsWHERE assigned_to = ? AND status = ?";$stmt = $pdo->prepare($sql);$stmt->execute(array(1, "OPEN"));$bug = $stmt->fetch();シー・ノー・エビル（臭いものに蓋）戻り値や例外を無視する

解決策: エラーから優雅に回復する<?phptry {$pdo = new PDO("mysql:dbname=test;host=localhost","dbuser", "dbpassword");① } catch (PDOException $e) {report_error($e->getMessage());return;}$sql = "SELECT bug_id, summary, date_reported FROM BugsWHERE assigned_to = ? AND status = ?";② if (($stmt = $pdo->prepare($sql)) === false) {$error = $pdo->errorInfo();report_error($error[2]);return;}③ if ($stmt->execute(array(1, "OPEN")) === false) {$error = $stmt->errorInfo();report_error($error[2]);return;}④ if (($bug = $stmt->fetch()) === false) {$error = $stmt->errorInfo();report_error($error[2]);return;}戻り値や例外をチェック

ディプロマティック・イミュニティ（外交特権）DBだけは特別扱いし、やらなくて良いと考えがち文書化バージョン管理テスティング

解決策: 包括的に品質問題に取り組む文書化バージョン管理テスティング例外は無い!!テーブル定義のバージョン管理にはmigration が便利

<?phpclass CustomBugs extends BaseBugs{public function assignUser(Accounts $a){$this->assigned_to = $a->account_id;$this->save();mail($a->email, "バグ担当に任命されました","あなたはバグ #{$this->bug_id} の修正担当に任命されました。");}}$bugsTable = Doctrine_Core::getTable('Bugs');$bugsTable->find(1234);$bug->assigned_to = $user->account_id;$bug->save();マジックビーンズ（魔法の豆）モデルがアクティブレコードそのものなので、ドメインロジックを迂回できる

解決策: モデルがアクティブレコードを「持つ」ようにする

砂の城 (奥野さん書き下ろし)「データサイズが一ヶ月で3倍になった」「データベースへの更新でデッドロックが起きる。製品のバグじゃないか？」「問題を解決するためにベンダーが要求しているデータは本番環境の負荷が高すぎるので採取できない」「最近マシンをアップグレードした。だから性能の問題とは無縁だろう」想定不足

ベンチマークテスト環境の構築例外処理バックアップ高可用性ディザスタリカバリ運用ポリシーの策定解決策: インシデントを想定し、備える

この本の素晴らしいところは、よく見る「悪い」方法を「悪いこと」としてまとめてくれたことです。http://bleis-tift.hatenablog.com/entry/2013/02/14/SQLアンチパターン「悪いこと」をまとめた意義

「あーはいはいインデックスショットガン乙」Explain の結果も見ないでインデックス貼りまくる奴いるよねーーー「マルチカラムアトリビュートとか 10 年前に通ったわー」http://yoshiori.github.com/blog/2013/02/10/sql-antipatterns/アンチパターン名で議論できるようになる

この問題！進研ゼミでやったところだ！http://yojik.hatenablog.jp/entry/2013/02/13/235729アンチパターンを共有しよう!

ご清聴ありがとうございました本書のハッシュタグは#sqlap です!ぜひ読んでみてください

Movatterモバイル変換

Change Language

SQLアンチパターン - 開発者を待ち受ける25の落とし穴 (拡大版)

Embed presentation

Recommended

More Related Content

What's hot

Similar to SQLアンチパターン - 開発者を待ち受ける25の落とし穴 (拡大版)

More from Takuto Wada

SQLアンチパターン - 開発者を待ち受ける25の落とし穴 (拡大版)