ハクソク

世界を動かす技術を、日本語で。

SQLアンチパターン

219日前原文(datamethods.substack.com)

概要

  • SQLアンチパターン は保守性やパフォーマンス低下の原因
  • CASE WHENの乱用 や インデックスを活かせない記述 がよく見られる
  • **SELECT ** *や SELECT DISTINCT の誤用も注意点
  • ビューの多重化 や ネストされたサブクエリ の過剰利用はパフォーマンス悪化の要因
  • 設計段階での工夫 と チームでのベストプラクティス共有 が重要

よくある高インパクトSQLアンチパターン

  • 大規模CASE WHEN文 のビュー内限定実装

    • アプリケーションのステータスコードを 巨大なCASE WHEN で英語化するケース
    • 個別ビュー 内でのみ変換ロジックを記述しがち
    • 他開発者が コピペ や ロジック未使用 となり、 データ整合性の崩壊 を招く
    • ディメンションテーブル や 共通ビュー で一元管理推奨

  • インデックスを活かせないWHERE句

    • 例： WHERE UPPER(name) = 'ABC'
    • インデックスが効かず 全件スキャン 発生
    • 解決策：
      • 比較値を 小文字 や 正規化 して保存
      • UPPER(name) のインデックス付与

  • **SELECT ** *の安易な使用

    • スキーマ変更時に ビューが壊れる リスク
    • 不要なカラム まで取得しパフォーマンス低下

  • SELECT DISTINCT による重複排除

    • 不完全なJOIN や 誤ったテーブル関係 が原因の重複を 一時的に隠す
    • 根本原因を放置し メトリクス不整合 や 集計ミス を招く
    • 適切な JOIN条件 の見直しが必須

  • ビュー多重化（View on View）

    • 初期は モジュール化・整理 のつもりで実装
    • 時間経過とともに 依存関係が複雑化
    • パフォーマンス劣化 や デバッグ困難化
    • 一定期間ごとに ロジックのフラット化 や マテリアライズ を推奨

  • ネストされたサブクエリの過剰利用

    • サブクエリの多重ネストで 可読性・保守性低下
    • 例： 3～4層 のサブクエリ＋ 5000行超のSQL
    • CTE（WITH句） 活用で読みやすさ向上

SQLアンチパターンへの対処とチーム開発

  • SQLはシンプルに見えて、システム規模拡大で 複雑化
  • アンチパターンは 納期優先や小さな妥協 が積み重なって発生
  • 生産コード同様、SQLも 共有・バージョン管理・レビュー・最適化 が重要
  • 設計段階の工夫 が 後工程の手戻り防止 につながる
  • Bill Karwin著 "SQL Anti-patterns" の参照推奨

Hackerたちの意見

インデックスで関数を使う部分は、もっと明確で深い説明が必要だと思う。インデックスで関数を使うと、データのフルスキャンになっちゃうんだよね。クエリランナーがすべての行と列に関数を実行しなきゃいけないから、インデックスの利点が消えちゃう。残念ながら、これは痛い目を見て学んだよ！

└

このトピックに関する有名なドキュメント - https://use-the-index-luke.com/sql/where-clause/obfuscation

└

「残念ながら、これは痛い目を見て学んだよ！」…これ、SQL開発者のモットーみたいだね。一方で、結構安定した言語（方言のファミリー？）みたいだから、落とし穴を見つけるのは長い目で見れば役立つよね。

└

提示された解決策（インデックス付きのUPPER(name)カラムを作る）は、少なくともMS SQL Serverでは最良の方法じゃないと思う。他のデータベースでも同じようにサポートされてるかは分からないけど、より良い解決策はケースインセンシティブな計算カラムを作ることだね：ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;（お好みで調整してね）

└

ブログにタイプミスがあるよ。最初の行は大文字にする必要がある: > query WHERE name = ‘ABC’ > create an indexed UPPER(name) column ポイントは、インデックス自体がすでに関数が適用されたデータにあるってこと。だから、元のクエリのようにフルスキャンにはならないんだ。もちろん、この特定の例では最初から大文字小文字を区別しない照合を使いたいだけなんだけど、一般的な概念としては有効だよ。

重複を「修正」するためにDISTINCTを使いすぎる どんな時にクエリでDISTINCTを見かけると、すぐにそのクエリを書いた人がデータモデルを十分に理解していないか、集合論を理解していないか、もっと言うとその両方だと思っちゃう。

└

それ、ほぼいつも私もそう感じるよ。ただ最近、正しい結合があっても、CTE内でDISTINCTを追加することでパフォーマンスが劇的に上がることがあるって学んだんだ。レコードの一意性が保証されていると、クエリプランナーが何らかの最適化をするんじゃないかな。

└

わからないけど、「どのZIPコードに顧客がいるの？」って知りたいのは普通のことだと思うよ。

└

それは単に、過剰に正規化されていないスキーマの指標かもしれないね（重複する都市がアドレステーブルに書き込まれないように、わざわざaddresses_citiesテーブルを作る必要があるの？）。

└

それについては一般化しすぎない方がいいと思う。普段どのクエリを読んでいるかによるんじゃないかな。

└

私の経験では、データベースの設計に問題があることが多いのは、クエリを書く人のせいと同じくらいです。

└

IDを取得するためのクエリのように、最大でも1つの結果を返すことを期待してLIMIT 1を追加したら、似たような嫌なことを言われたことがあるよ。でも、大きなテーブル（少なくともSQLite、MySQL、そして多分PostgreSQLでも）では、指定したレコードが見つかった後も、データベースはテーブル全体を検索し続けるんだ。

└

CoddのリレーショナルモデルをSQLのタブ型モデルより信じるべきだね。

└

Cypherでは全く逆なんだ。今、neo4jで複雑なデータを扱ってるんだけど、見た目は完璧なクエリがなぜこんなに遅いのか不思議に思ってたら、DISTINCTを使うのを忘れてたことに気づいた。特に可変長のリレーションシップを使うと、結果に重複ノードが出やすいから、DISTINCTが唯一の解決策だと思ってる。

└

もしかしたら、OLAPじゃなくてOLTPかもね。

└

SQLって、基本的なことでも「二人に聞いたら三つの意見が返ってくる」感じだよね。「BTreeMap>があるとして、.keys()と.len()はどうやってやるの？」っていう。

「select *」がコードを壊すなら、コードに何か問題があるってことだよ。リッチ・ヒッキーがこれについて話してたと思う。必要以上のものを提供するのは、決して壊れる変更にはならないはず。特定の言語やフォーマット、ツールはこれをデフォルトで正しく処理してるけど、他のものは真実のソースが必要だね。

└

コードにselect *がある時点で、コードに何か問題があるんだよね、壊れるかどうかに関わらず。パフォーマンスや出力がテーブル定義に依存することになるから。リッチ・ヒッキーも、コード内で非ローカルな依存関係や影響を避ける重要性について話してたと思う。

└

理由は記事に書いてあって、真実だよ。 > スキーマの進化はビューを壊すことがあり、それが下流に影響を与えることもある。Select *は、スキーマの進化や名前の衝突の問題そのものなんだよね。

└

記事が言ってることに特に問題はないと思うよ。AとBを結合したビューがあって、そのビューが「select *」を使ってたら、AがBと同じ名前のカラムを追加したらどうなると思う？SQLiteでは、ビューの定義が自動的に拡張されて、出力のカラムの一つがエイリアスで区別されるようになるんだ。どのカラム名が変わるかは、結合するテーブルの順番によるから、これがコードを壊すこともあるよ。Postgresでは、ビューのカラムは定義時に修飾されるから、すぐには何も変わらない。でも、ビューの定義が更新されるとDDLでエラーが出るんだ。どんなシステムでも、大きなカラムが構成テーブルの一つに追加されるとパフォーマンスの問題が起きることがある。最善のアドバイスは、こういう問題を避けるために、プロダクションコードでは「select *」を絶対に使わないことだね。

└

select *は色々な理由で良くないけど、一番大きいのはリモートデータストアとの「契約」が不変じゃないことだね。データベースは、色々な理由でコードとは独立して変わることがある。信頼性の高いコードを書くなら、できるだけ少ない仮定をする必要がある。その仮定の一つがリモートスキーマがどうなっているかってこと。

重複を「修正」するためにDISTINCTを使いすぎることについて、私は複雑なクエリを設計する方法についての小さなチュートリアル（約9000語、2部構成）を書いたよ。 https://kb.databasedesignbook.com/posts/systematic-design-of...

└

いい記事がいっぱいあるね。ブックマークしたよ。追記：実は本でもあるんだね！

リストに載ってない大きな問題は、存在しないものを探すことだね。!=やNOT IN (...)を使うのは、ほぼいつも非効率的だよ（でも、他の条件で結果セットが絞られている場合はOKなこともある）。それに、DBがヌルをどう扱うかも理解しておくべきだね。ヌルと空文字列は同じ？ヌル == ヌルはどうなる？すべてのデータベースが同じように扱うわけじゃないからね。

└

それに、DBがヌルをどう扱うかも理解しておくべきだね。インデックスに関しても同様だよ。私が使ったDBではヌルをインデックスしていないから、「WHERE col IS NULL」は非効率的なんだ。たとえ「col」がインデックスされていてもね。もしそうなら、本当に必要なら「col」がヌルかどうかを示すchar(1)やbitの計算列を作って、それをインデックスするのがいいよ。

└

!=やNOT IN (...)を使うのは、ほぼいつも非効率的だよ。なんでそう言うの？私の理解では、NOT INの右辺が定数（行に依存しない意味で）であれば、条件は基本的にハッシュテーブルのルックアップになるから、ルックアップテーブルが大きくなければ通常は効率的だと思うんだけど。もっと効率的な代替手段は何？

最大のSQLアンチパターンは、SQLが実際にはプログラミング言語であることを認識しないことだね。だから、SQLのために一貫したインデントスタイルを作るべきだよ。私のスタイルについては、https://bentilly.blogspot.com/2011/02/sql-formatting-style.h...を見てね。次に、論理的なものをまとめるようにしよう。これが、サブクエリを共通テーブル式に移動させる理由だよ。そして最後に、賢くコメントすることを恐れないでね。

└

スタイルに関する意見は、適切なリンターがないとほとんど意味がないよね。

これらの「アンチパターン」は、SQLの悪い言語設計（実際には設計がない）への対処法に過ぎないよ。私はSQLデータベースで動作する言語を作っているから、これらのポイントすべてでより良くなることを期待しているんだ。もし誰か、ドキュメントが足りない半分完成した言語をチェックしたいなら、フィードバックをもらえると嬉しいな： https://lutra-lang.org

└

これ、めっちゃクールだね！頑張ってね、もっと準備が整ったらチェックするようにするよ。

過剰なCASE WHEN文の取り扱い ユーザー定義関数（UDF）は、ロジックを一箇所にまとめる別の選択肢だよ。 > インデックス付きカラムでの関数使用 つまり、クエリはサージャブルじゃない [0] > 重複を「修正」するためのDISTINCTの過剰使用 著者が結合からのファンアウトについて言ってることとは別に、正確な一致ではないレコードの「デデュープ」にこういうのを使うのが好きだよ：ROW_NUMBER() OVER (PARTITION BY ORDER BY ) = 1 一部のデータベースエンジンにはQUALIFY [1]があって、かなりクリーンなクエリになるよ。 [0] https://en.wikipedia.org/wiki/Sargable [1] https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_cla...

└

非サージャブルな問題は、式インデックスで簡単に解決できるよ。少なくともsqliteではね。

まだ解決してない問題を自分で作っちゃったんだ：マテリアライズドビューに依存するマテリアライズドビューがいくつかある。下の方のビューやテーブルが変更されると、すべてのビューを削除して再作成しなきゃいけない。稼働中の環境にウォームスタンバイを使ってる。読み取り専用の本番データベースがあるのは好きなんだけど、プライマリじゃないから、システムの負け組みたいな感じがする。最近Postgres 18にアップグレードしたんだけど、それがスタンバイをrm rfしてpg_basebackupで再構築しなきゃいけないってことを忘れてた…マジで楽しくなかった。

└

ビュー、トリガー、整合性制約をアンチパターンと呼びたいな。コードはデータモデルを扱うべきで、悪い状態がデータベースに入るのを絶対に許しちゃダメだよ。これらの「機能」からはパフォーマンスの損失や多くの足元をすくわれることがあるからね。

自分のクエリを早くしてサーバーのリソース使用量を減らすのに一番効果的だったのは、クエリをもっとサージャブルにすることに集中したことだね。 https://en.wikipedia.org/wiki/Sargable https://www.brentozar.com/blitzcache/non-sargable-predicates...