遅いSQLを爆速へ！SQLでインデックスが効きづらい書き方＆対処法8選

今回は一般的にインデックスが効かないとされる書き方を8つ紹介します。
それぞれのデータベースの拡張機能やバージョンアップ等により、今回紹介する事例でもインデックスが効く可能性はありますが、
「インデックスが効かないパターンってなんだっけ、、」や「よく分からないけどSQLの速度が遅い！」と言う際に、参考にしてみてください。

1.LIKE文での中間一致・後方一致を使用する場合

LIKE文で中間一致(%keyword%)や後方一致(%keyword)を使用するとインデックスが効かない場合があります。
理由を調べたところ、データベースのB-treeインデックスが前方一致検索に最適化されている為のようです。

実際の例

-- インデックスが効かない例
SELECT * FROM users WHERE name LIKE '%田中%';
SELECT * FROM users WHERE name LIKE '%田中';

回避策

前方一致検索(keyword%)にできるのであれば、それが一番シンプルです。

-- インデックスが効く例
SELECT * FROM users WHERE name LIKE '田中%';

ただし、前方一致に変更すると検索結果が変わってしまいます。
中間一致・後方一致のまま高速に検索したい場合は、全文検索インデックス(FULLTEXT INDEX) の利用を検討してください。

-- MySQLの場合: FULLTEXT INDEXを作成
ALTER TABLE users ADD FULLTEXT INDEX ft_name (name);

-- MATCH ... AGAINST で検索
SELECT * FROM users WHERE MATCH(name) AGAINST('田中');

PostgreSQLの場合はpg_trgm拡張のトライグラムインデックスを使うことで、中間一致でもインデックスが効くようになります。

-- PostgreSQLの場合: pg_trgm拡張を有効化
CREATE EXTENSION pg_trgm;

-- GINインデックスを作成
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

-- 通常のLIKEがそのまま高速になる
SELECT * FROM users WHERE name LIKE '%田中%';

2.インデックスカラムを関数や演算子で加工する場合

インデックスカラムを関数や演算子で加工するとインデックスが効かない場合があります。

実際の例

以下の例では、YEAR()関数がorder_dateに適用されており、インデックスカラムを加工したことになります。

-- インデックスが効かない例
SELECT * FROM orders WHERE YEAR(order_date) = 2022;

回避策

関数の使用を避けて、範囲検索に書き換えることで、検索結果を変えずにインデックスが効くようになります。

-- インデックスが効く例（結果は同じ）
SELECT * FROM orders WHERE order_date >= '2022-01-01' AND order_date < '2023-01-01';

3.OR条件で列を検索する場合

OR条件で異なる列を検索すると、インデックスが効かない場合があります。

実際の例

-- インデックスが効かない例
SELECT * FROM products WHERE category = 'books' OR price > 1000;

回避策

OR条件の場合、AND条件に変更すると検索結果の意味が変わってしまう為、安易に置き換えることはできません。

検索結果を変えずにインデックスを活用するには、UNIONでそれぞれのクエリを結合する方法があります。

-- UNION で結合（重複は自動で排除される）
SELECT * FROM products WHERE category = 'books'
UNION
SELECT * FROM products WHERE price > 1000;

※UNION ALLは重複を排除しない為、元のOR条件と結果が変わる可能性があります。重複を許容できる場合や、条件が排他的であることが保証されている場合はUNION ALLの方が高速です。

また、それぞれの列に個別のインデックスが張られている場合、DBのオプティマイザがIndex Mergeを使ってOR条件でもインデックスを活用してくれることがあります。
EXPLAINで実行計画を確認してみてください。

4.NULL値のカラムを検索する場合

NULL値を含むカラムを比較する場合、インデックスが効かない場合があります。

実際の例

-- インデックスが効かない例
SELECT * FROM customers WHERE email IS NULL;

回避策

テーブル設計の段階で対応できるのであれば、非NULL制約をつけてデフォルト値を設定する方法があります。

-- テーブル設計で対応する例
COLUMN_NAME VARCHAR(50) NOT NULL DEFAULT ''

ただし、既存のテーブルに対して制約を変更するのは影響範囲が大きいです。

実際のところ、MySQL(InnoDB)やPostgreSQLではIS NULLでもインデックスが効きます。
「NULLだとインデックスが効かない」というのは古いバージョンや一部のDBに限った話であることが多いので、まずはEXPLAINで実行計画を確認するのがおすすめです。

5.条件に否定文を使用する場合

否定文やNOT演算子を使用すると、インデックスが効かない場合があります。

実際の例

-- インデックスが効かない例
SELECT * FROM customers WHERE name NOT IN('田中', '佐々木');
SELECT * FROM customers WHERE name IS NOT NULL;
SELECT * FROM customers WHERE name <> '田中';

回避策

否定条件を肯定条件(IN, =)に書き換えると検索結果が変わってしまうので、単純に置き換えることはできません。

否定条件はその性質上、テーブルの大部分を返すことが多い為、そもそもインデックスを使うよりフルスキャンの方が効率的だとDBが判断するケースが多いです。

対処としては以下のような方法があります。

• 対象データが少ない場合: 取得したい値をIN句で明示的に指定できないか検討する
• サブクエリと組み合わせている場合: NOT INをNOT EXISTSに書き換えることでパフォーマンスが改善することがある
• そもそもの設計を見直す: 否定条件で絞り込む必要がないように、フラグカラムなどを用意する

-- NOT IN を NOT EXISTS に書き換える例
-- 変更前
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- 変更後（結果は同じ）
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

6.データ型が異なる値と比較する場合

異なるデータ型を比較すると、DBによっては暗黙的に型変換がされる為エラーにはなりませんが、インデックスが効かない場合があります。

実際の例

/* price: INT */

-- インデックスが効かない例
SELECT * FROM products WHERE price = '100';

回避策

データ型を一致させることで、インデックスが効率よく効くようになります。
結果は同じですが、暗黙の型変換が発生しなくなります。

-- インデックスが効く例（結果は同じ）
SELECT * FROM products WHERE price = 100;

7.実テーブルではなくWITH句や副問い合わせで取得した仮想テーブルを参照する場合

WITH句や副問い合わせで取得したテーブルを参照する場合、元のテーブルではインデックスが効いていたとしても、取得した結果の仮想テーブルではインデックスが効かない場合があります。

実際の例

-- インデックスが効かない例
WITH recent_orders AS (
    SELECT * FROM orders WHERE order_date > '2022-01-01'
)
SELECT * FROM recent_orders WHERE product_id = 100;

回避策

条件を実テーブルに対してまとめることで、インデックスが効率よく効くようになります。
この場合、検索結果は変わりません。

-- インデックスが効く例（結果は同じ）
SELECT * FROM orders WHERE order_date > '2022-01-01' AND product_id = 100;

なお、PostgreSQL 12以降やMySQL 8.0では、オプティマイザがWITH句をインライン展開して最適化してくれる場合もあります。
こちらもEXPLAINで確認してみてください。

8.複合インデックスの先頭列を使用しない場合

複合インデックスを作成した場合、先頭の列を条件に含めないとインデックスが効かない場合があります。
これは「左端プレフィックスルール」と呼ばれます。

実際の例

-- 1:category, 2:item_nameの順番でインデックスを作成
CREATE INDEX category_item_name_index ON products(category, item_name);

-- インデックスが効かない例（先頭のcategoryを条件に含めていない）
SELECT * FROM products WHERE item_name = 'ABC book';

回避策

複合インデックスの先頭列を条件に含めることで、インデックスが効くようになります。

-- インデックスが効く例
SELECT * FROM products WHERE category = 'books' AND item_name = 'ABC book';

なお、WHERE句に両方の列が含まれていれば、記述順序が逆でも多くのDBオプティマイザが自動的に最適化してくれます。

-- 記述順序が逆でも、オプティマイザが最適化してくれる（インデックスは効く）
SELECT * FROM products WHERE item_name = 'ABC book' AND category = 'books';

もし先頭列なしでitem_name単体での検索が頻繁にある場合は、item_name単体のインデックスを別途作成することを検討してください。