避けるべきSQLアンチパターン

 (datamethods.substack.com)
54 ポイント 投稿者 GN⁺ 2025-10-19 | 5件のコメント | WhatsAppで共有
  • SQLアンチパターンは、クエリやデータパイプラインの保守を難しくし、想定以上に遅い性能を招く
  • CASE WHENの乱用、インデックス列への関数適用、SELECT *、DISTINCTの乱用、ネストしたビューとサブクエリ、深い依存構造などが代表例
  • 問題の多くは、速度や締め切りの圧力による場当たり的な解決策から生じ、長期的にはデータの信頼性と開発速度の両方を損なう
  • 解決策として、明確なJOIN定義、ディメンションテーブルの活用、不要なネストの除去、定期的なビュー整理などが必要
  • SQLは単なるスクリプトではなく、チーム単位で管理される本番コードとして扱うべきであり、可読性を考慮した初期設計が手戻りを減らす

紹介

  • 今日は、SQLアンチパターンの中でもよく発生し、影響の大きい事例をいくつか中心に説明する
  • こうした問題は、データの信頼性低下やクエリ開発速度の低下といった悪循環を引き起こす
  • 以下の一覧はすべての事例を網羅するものではなく、さらに深く理解したい場合は Bill Karwinの著書 をおすすめする

過度に複雑なCASE WHEN文

  • 大規模システムでは、状態コード（例: 1=在庫切れ）を人が読める形に変換するため、CASE WHEN文がよく使われる
  • ダッシュボードやレポートを素早く開発するために、1つのViewにだけそのCASE WHENロジックを追加するのは、長期的にはアンチパターンに当たる
  • これは重複ロジックのコピー＆ペーストや解釈の不一致を招き、クエリ環境全体を雑然としたものにする
  • 解決策は、状態コードを変換するディメンションテーブル（dimension table）または共通ビューを別途作成し、再利用性を確保すること

インデックス列での関数使用

  • WHERE UPPER(name) = 'ABC' のように、インデックスが張られた列に関数を適用すると、インデックスの効率が失われる
    • SQL Serverなどでは、その結果 不要なフルテーブルスキャン（full table scan） が発生する
  • 解決策は、関数適用後の列を別途インデックス化するか、入力値を変換してクエリ条件を単純化すること

ビューでのSELECT *使用

  • ビュー開発時に SELECT * を使うと便利に見えるが、構造（スキーマ）が変更されるとビューが簡単に壊れることがある
  • 不要な列まで含まれて 意図しない依存関係や性能問題 が生じるため、列は明示的に選択すべき

DISTINCT乱用による重複の“解決”

  • 誤ったJOINによって重複結果が発生したとき、SELECT DISTINCT で一時的に対処するのは、データ整合性の問題を隠すことになる
    • 根本原因はJOIN条件の不完全さや、関係定義（1:1、1:Nなど）の誤りにある
    • 正しい解決策は、JOINロジックを補強して関係定義を明確にし、集計やレポートの前に関係の整合性を確保すること

ビューのネスト（Excessive View Layer Stacking）

  • 複数チームが既存ビューを再利用しながら新しいビューを積み重ね続けると、依存チェーンが複雑化し、性能が急激に低下する
    • デバッグが難しくなり、クエリの拡張が「考古学的発掘」レベルで困難になる
  • 定期的に変換ロジックを フラット化（flatten） し、複雑な演算は 明確なベースビューやテーブルとしてマテリアライズ（materialize） する戦略が必要

過度に深いサブクエリ

  • 3〜4段階以上に 深くネストしたサブクエリ は、可読性を下げ、デバッグを難しくする
    • 5000行を超えるサブクエリを扱う事例もある
  • CTE（Common Table Expression） を活用すると、論理的な段階分けがしやすくなり、クエリの 明確さ（readability） が高まる

結論

  • SQL は表面的には単純に見えるが、システムが大きくなるほど複雑性が増大する性質がある
  • 多くの アンチパターン は悪意からではなく、「早く結果を出す」ための妥協（速度、締め切り、その場しのぎ） の結果として生まれる
  • SQLをコードのように管理（バージョン管理、コードレビュー、明確な設計） すれば、長期的に生産性と信頼性を同時に確保できる
  • 初期設計に数分投資して、明確さと一貫性を意識すれば、今後の手戻りや混乱を大きく減らせる

関連記事

5件のコメント

 
aer0700 2025-10-20

今は時間がないから、とりあえず目先の火だけ消して後で書き直そうと言っていたものが、積み重なると恐ろしいクエリ地獄になりますよね。私もいくつも作ってきました。あれらを書き直す「後で」なんて永遠に来ないと分かっていながら、そうしてしまうのです。
 
firefoxsaiko123 2025-10-20

うっ…
 
ilikeall 2025-10-20

「ほとんどの問題は、速度や締め切りのプレッシャーによる場当たり的な解決策から生まれる」
うう..
 
GN⁺ 2025-10-19
Hacker Newsのコメント
  • クエリでDISTINCTが使われているのを見ると、書いた人がデータモデルや集合論をきちんと理解していないか、あるいはその両方を分かっていないのではと疑ってしまう
    • 場合によっては、DISTINCTは過度に正規化されていないスキーマの兆候でもありうる。たとえば、都市名の重複記録を防ぐためだけに、わざわざaddresses_citiesのようなテーブルを作る必要はないと思う
    • 自分の経験もほぼ同じ。ただ最近、すべてのJOINが正しく書かれていても、CTEの中にDISTINCTを入れたら性能が大きく改善したケースがあった。レコードの一意性が保証されると、クエリプランナーが最適化しやすくなるようだ
    • クエリで結果が最大1件しか返らない想定だったのでLIMIT 1を追加したところ、同じように良くないというフィードバックを受けたことがある。でも大きなテーブルでは、目的のレコードが見つかった後でもDBがテーブル全体を探索し続ける傾向がある (sqlite, mysql, postgresql のいずれでも)
    • SELECT x FROM t クエリでDISTINCTを安全に省略してよいかどうか、どう判断できるのかと尋ねたい。tのスキーマでxにPRIMARYやUNIQUE制約があるのを見たとしても、誰かがすぐにUNIQUE制約を外してしまうかもしれない。すると重複が発生し、なぜそうなったのか悩むことになる。SQLは集合(set)言語ではなくbag言語だ。実行時にrel tと属性xを見つけたら、そのまま返すだけ。重複が出ることもあるし、型が変わることもある。もしSetが欲しいなら、必ずDISTINCTを明示しなければならない。クエリプランナーは実行時にUNIQUEやPRIMARYならdeduplicationをしないようになる
    • Cypherではむしろ逆だ。neo4jで複雑なデータを扱うと、重複ノードが本当に簡単に結果へ混じるのでDISTINCTが必須になる。特に可変長リレーションを使うとき、DISTINCTがないと遅くなり重複も増える
  • DISTINCTなしで正しいクエリ構造を設計する方法について、約9000字のチュートリアルを2部構成で書いてみた
    https://kb.databasedesignbook.com/posts/systematic-design-of-join-queries/
    • 良い記事だ。ブックマークした。それに、これが実際には本でもあると気づいた
  • あまり言及されていないものの一つが、まさに「存在しないもの」を探すクエリだ。たとえば != や NOT IN (...) を使う場合は、ほとんどが非効率になる（ただし他の条件で結果集合が十分に絞られているなら問題ないこともある）。それに、DBがnullをどう扱うかを理解するのは重要だ。nullと空文字列が同じなのか、null == null なのかは、DBごとに異なることがある
    • null処理とインデックスに関していうと、自分が使ってきたDBはnull値をインデックスしないので、WHERE col IS NULL のクエリはcolにインデックスがあっても非効率に動く。本当に必要なら、colのnull有無を示す char(1) や bit カラムを作ってそのフィールドにインデックスを張る方法を勧める
    • != や NOT IN (...) がほぼ常に非効率だと言っていたけれど、なぜなのか気になる。右辺が定数ならハッシュテーブルのルックアップになって一般には効率的そうだし、もっと効率のよい代替があるのか知りたい
  • 挙げられている「アンチパターン」がすべて本当にアンチパターンだとは思わない。クエリ条件がインデックスに合っていない問題は、結局のところインデックスの仕組みの理解不足に由来する。そして、ここで触れられている問題のかなりの部分は、SQLそのものよりもデータベースのスキーマ設計に近い話だ。DISTINCTが必要になるなら、主キー設計が正しくないのかもしれない。viewを積み重ねすぎるのも、結局は基本テーブル設計が悪いということだ。良いDBモデリングがあれば、問題の多くは事前に防げる
  • こうした「アンチパターン」は、実際にはSQL言語設計の限界（あるいは未設計）から生じた単なる回避策にすぎない。自分はSQLデータベース上で動く新しい言語を作っていて、こうした問題それぞれにもっと良い代案を用意したいと思っている。まだ未完成で文書も不足しているが、興味があれば https://lutra-lang.org でフィードバックがほしい
    • 「SQLデータベース」という表現は曖昧だ。SQLは関係型だけでなく非関係型DBにも実装例がある。専門家たちはSQLの問題をずっと以前から認識していて、Chris DateやHugh DarwenのTutorial Dのような代替案もあった。それでも、何十年にもわたって蓄積されたSQLコードとツールのために代替は定着できなかった。自分はSQLのおかげで何十年も雇用の安定と継続的な収入を得てきたので、より良い言語が必要だとは思う一方で、この状況を前向きにも見ている
    • プロジェクトは見た目が良さそうだ。もっと完成度が上がったらぜひ追っていきたい
  • SQLを単なるクエリ言語ではなく、本物のプログラミング言語として認識しないことこそ最大のアンチパターンだ。コードスタイルは一貫したインデントにし、論理的に関連する部分ごとにまとめることを勧める。サブクエリはCTEに置き換えるのを推奨する。効果的にコメントを残すのも重要だ。自分のスタイル例はこちら: https://bentilly.blogspot.com/2011/02/sql-formatting-style.html
    • こういうコードスタイル論争は、適切なリンターツールがないとほとんど意味がないと思う
  • クエリ速度を上げ、サーバー資源の使用量を減らすうえで最大の秘訣は、クエリをよりsargableにすることだった
    https://en.wikipedia.org/wiki/Sargable
    https://www.brentozar.com/blitzcache/non-sargable-predicates/
    • 「sargable」という単語が実際にどのコミュニティで使われているのか気になる。20年以上SQLを扱ってきたが、マニュアルでもStack OverflowでもHNでもほとんど見たことがない。どのDBや会社、あるいは特定のオープンソースコミュニティでよく使われるのか知りたい
    • sargableの語源を調べていたら、このStackOverflowの回答が参考になった https://dba.stackexchange.com/a/217983
      「sargable」という単語は「Search ARGument ABLE」のかばん語（portmanteau）だ
  • CASE WHEN文の多用という問題の多くは、UDF(User Defined Function)でロジックを一か所に集約すれば解消できる
    インデックス列に関数を使うのは、クエリがsargableでない兆候だ
    DISTINCTの乱用の代わりに、JOIN由来のファンアウトの中からテーブル粒度に合わせてde-dupeするクエリとしては 
    ROW_NUMBER() OVER (PARTITION BY <grain> ORDER BY <deterministic sort>) = 1
    のような形が有用だ。一部のDBではQUALIFYという機能があり、クエリがずっときれいになる
    sargableの説明
    QUALIFY in Redshift
    • sargableでない問題は、expression indexで簡単に解決できる。少なくともsqliteではそう思う
  • viewを入れ子で使うことがむしろ必要な場面もある。うちのPOSソフトウェアでは、トランザクションを一度にきれいに見られるバックボーンviewを作るために、入れ子のviewを多用している。そうしないと、各テーブルごとにwhere句を別々に書き、void/返品/取消といった各種条件を毎回処理しなければならず、変更があれば何十ものview/procedureをすべて直す羽目になる。うちのケースでは入れ子viewの活用のほうがずっと実用的だ
  • インデックス列に関数を使う問題は、もっと明確な説明が必要だ。インデックスされた列に関数が入るとインデックス効率が失われ、実際にはフルスキャンになって遅くなる。これは自分で痛い目を見て身につけた
    • 関連して有名な文書がある https://use-the-index-luke.com/sql/where-clause/obfuscation
    • 提示されている解決策（例: UPPER(name) 列へのインデックス追加）は、少なくともMS SQL Serverでは最善ではない。他のDBでの対応は分からないが、もっと良い解決策は、最初からCOLLATEを使って大文字小文字を区別しない計算列を作ることだ 
      ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;
      （好みに応じて調整）
    • 関連ブログには誤字がある。1行目は大文字で書くべきだ。すでに関数適用後のデータに対してインデックスを張っているなら、クエリ時にフルスキャンにはならない。もちろんこの例では、そもそも大文字小文字を区別しないcollationを使うほうが良いが、一般論としてはこのアイデア自体は妥当だ
    • 「これは自分で経験して学んだ」は、SQL開発者たちのモットーみたいなものだ。いずれにせよSQLはかなり長いあいだ安定して変化してきたので、こうした落とし穴を前もって知っておくことは長く役に立つ
 
ahwjdekf 2025-10-21

最も重要なものが抜けていますね。

  • ORMを使うこと