計算値でフィルタリングする
列に関数を適用するとインデックスが使われなくなる理由と、面接での確認ポイントを学びます。
「計算値でフィルタリングする」はCoddyKit上の無料Coding Interview Prepレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはCoding Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Coding Interview Prepコースには全4レッスンが含まれています。
この質問でレベルの差がわかる理由
この問いは一見無害です: このクエリは正しいのに、なぜ遅いのでしょうか。多くの場合、答えはWHERE句でインデックス付きの列を関数で包んでいることです。その結果、述語が非サージャブルになります。オプティマイザはインデックスを使えなくなり、すべての行をスキャンしなければなりません。
このレッスンでは、サージャビリティについて説明し、面接官が求める書き換え方法を示し、計算を伴うフィルターをどこに置くべきかを扱います。
サージャブルを一言で定義すると
サージャブル（Search ARGument ABLE）とは、述語がインデックスを使って一致する行へ直接シークできることです。経験則として、比較の片側にはインデックス付きの列をそのまま記述する必要があり、関数や式の中に埋め込んではいけません。
- サージャブル:
col = 5、col > 100、col LIKE 'abc%' - 非サージャブル:
FUNC(col) = 5、col + 1 > 100
列に関数を適用するアンチパターン
ここでの目的は、2024年に行われた注文を取得することです。列をYEAR()で包むと、比較する前にすべての行について年を計算するようエンジンに強制するため、order_dateのインデックスが役に立たなくなります。
正しい結果は返しますが、テーブル全体をスキャンします。大きなテーブルでは、ミリ秒で終わるか数分かかるかの違いになります。
-- non-sargable: function on the indexed column
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024;範囲に書き換える
修正方法は、order_dateをそのままにして、条件を半開区間として表現することです。これでorder_dateのインデックスは2024年の開始位置へ直接シークし、2025年で停止できます。
結果は同じですが、全体スキャンではなくインデックスの範囲スキャンになります。この範囲への書き換えは、面接で最も頻繁に問われるサージャビリティの修正方法です。
-- sargable: column stays bare
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01';列に対する算術演算
同じ問題は算術演算にも潜んでいます。WHERE salary + bonus > 100000やWHERE price * 0.9 < 50はいずれも列に対して計算するため、インデックスの利用を妨げます。
可能な場合は計算を定数側へ移します。たとえばprice * 0.9 < 50をprice < 50 / 0.9に書き換えます。リテラルは一度だけ計算され、priceはそのままインデックスを利用できる状態になります。
-- before: math on the column (non-sargable)
WHERE price * 0.9 < 50
-- after: math on the constant (sargable)
WHERE price < 50 / 0.9大文字と小文字を区別しない検索の場合
WHERE LOWER(email) = 'a@b.com'は、emailに対する通常のインデックスでは非サージャブルです。すべての行のメールアドレスを先に小文字化する必要があるためです。
本番環境での修正方法は2つあります。正規化して小文字化したコピーを保存し、それにインデックスを作成する方法と、LOWER(email)という式自体にインデックスを付ける関数インデックスを作成する方法です。関数インデックスという選択肢に言及すると、実務経験を示せます。
-- functional index makes the expression sargable
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';本当に計算が必要な場合
たとえば比率で絞り込む場合のように、範囲への書き換えができず、フィルターが本当に計算値に依存することもあります。それでもWHEREでSELECTの別名を参照することはできません。WHEREはSELECTリストより先に評価されるためです。
そのため、式をWHEREで繰り返すか、クエリをサブクエリまたはCTEで包み、外側のクエリで計算列をフィルターします。
SELECT *
FROM (
SELECT *, revenue / NULLIF(visits, 0) AS rev_per_visit
FROM stats
) t
WHERE t.rev_per_visit > 2.5;集約関数はWHEREではなくHAVINGに記述する
集約である計算は、そもそもWHEREには記述できません。WHEREはグループ化の前に個々の行をフィルターするためです。WHERE SUM(amount) > 1000はエラーになります。
集約に対するフィルターはGROUP BYの後に実行されるHAVINGに記述します。どの句がその計算を参照できるかを理解しているかどうかは、実行順序に関する頻出質問です。
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;面接官が確認するポイント
面接官は、列に関数を適用した遅いクエリを示し、結果を変えずに高速化するよう求めます。取るべき対応は次のとおりです。
- 列に関数を適用している箇所が非サージャブルだと特定する
- 列をそのままに保つよう書き換える（範囲または定数側への計算移動）
- 書き換えられない場合は、関数インデックスまたは保存された計算列を提案する
EXPLAINで実行計画がシーケンシャルスキャンからインデックススキャンに変わったことを確認すると答えが締まります。
トレードオフへの理解
バランスの取れた説明をしましょう。インデックスや関数インデックスは読み取りを高速化しますが、書き込みを遅くし、ストレージも消費します。小さなテーブルなら全体スキャンで問題なく、インデックスの追加は無駄な作業です。
シニアらしい答えは条件付きです。この列が大きなテーブルにあり、この方法で頻繁に絞り込むのであれば、述語をサージャブルにするか関数インデックスを追加します。そうでなければ、そのままにします。面接では、独断的な原則よりも状況に応じた判断が重要です。
関数インデックスで計算をSARGableにする
大文字と小文字を区別しない検索など、変換後の値で本当にフィルタリングしなければならない場合があります。インデックスをあきらめるのではなく、フィルタリングに使う正確な式に対して式（関数）インデックスを作成してください。
- 列を関数でラップしていても、オプティマイザーはインデックスを使えるようになります。
- インデックスの式は、述語の式と完全に一致していなければなりません。
-- index the expression you filter on
CREATE INDEX idx_users_lower_email ON users (lower(email));
-- now this predicate stays sargable
SELECT * FROM users WHERE lower(email) = 'amy@example.com';理解度チェック
オプティマイザがインデックスを利用できる述語を特定してください。
まとめ
重要なポイント:
- インデックス付きの列が関数や算術演算の中ではなく、そのまま現れている述語はサージャブルです
YEAR(col) = 2024は半開区間に書き換え、計算は定数側に移します- 避けられない式には関数インデックスまたは保存された計算列を使用します
WHEREではSELECTの別名を使用できません。集約はHAVINGに記述します
典型的な問いは遅いクエリであり、典型的な解決策は列をそのままに保つことです。
よくある質問
「計算値でフィルタリングする」レッスンは無料ですか？
はい。「計算値でフィルタリングする」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。
「計算値でフィルタリングする」で何を学びますか？
列に関数を適用するとインデックスが使われなくなる理由と、面接での確認ポイントを学びます。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
Coding Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのCoding Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。
「計算値でフィルタリングする」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このCoding Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのCoding Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。