【SQL】インデックスが利用されない検索条件についてEXPLAINで検証してみた

検索条件によってはSQLでインデックスが利用されないケースがあります。

達人に学ぶDB設計徹底指南書などでインデックスが利用されないと一般的に言われているケースは以下の通りです。

インデックスが利用できないと一般的にいわれているケース

インデックス列に対して演算をする
インデックス列に対してSQL関数を適用している
否定条件を利用している
後方一致、もしくは中間一致でLIKEを利用している
暗黙の型変換を利用している
IS NULを利用している
ORを利用している

しかし手元で検証してみたところ上記のうちインデックスが利用されるケースもありました。
今回はインデックスが利用されないといわれている検索条件のEXPLAIN結果について紹介します。

なお、EXPLAINの読み方の詳細解説はMySQLのEXPLAINの読み方とチューニング時のチェックポイントで紹介していますのであわせてご覧になってください。

1 検証に利用したダミーデータについて
2 インデックスが利用されなかったケース
3 インデックスが利用されたケース
4 まとめ
5 参考

検証に利用したダミーデータについて

usersテーブルにダミーデータを作成し、インデックスの利用可否を検証しました。
usersテーブルの詳細は以下の通りです。

usesテーブルはid, first_name, last_name, age, created_at, update_adのカラムを持つ
usersテーブルには100万件のレコードが存在する
ageとlast_nameにインデックスを作成する
ageのカーディナリティは79、last_nameのカーディナリティは473

インデックスの詳細は以下の通りです。

> SHOW INDEX FROM users \G;

*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 951399
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: index_users_on_age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 79
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: users
   Non_unique: 1
     Key_name: index_users_on_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 473
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
3 rows in set (0.00 sec)

MySQLは5.7.34を利用しています。

インデックスが利用されなかったケース

以下のケースは一般的にいわれている通りインデックスが利用されませんでした。

インデックス列に対して演算をする
インデックス列に対してSQL関数を適用している
否定条件を利用している
後方一致、もしくは中間一致でLIKEを利用している

インデックス列に対して演算をする

たとえばageに対してage/2のようにインデックス列に対して演算をするとインデックスが利用されませんでした。

-- インデックスが使われない

> EXPLAIN SELECT * FROM users WHERE age/2 = 15 \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 996364
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

age/2 = 15とする代わりにage = 30やage = 60/2とすればインデックスが利用されます。

-- インデックスが使われる

> EXPLAIN SELECT * FROM users WHERE age = 30 \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 22196
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

-- インデックスが使われる

> EXPLAIN SELECT * FROM users WHERE age = 60/2 \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 22196
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

インデックス列に対してSQL関数を適用している

たとえばlast_nameに対してMySQLで用意されている文字列関数のSUBSTR()を利用してSUBSTR(last_name, 1, 1)のようにするとインデックスが利用されませんでした。SUBSTR()は指定された部分文字列を返す関数です。

-- インデックスが使われない

> EXPLAIN SELECT * FROM users WHERE SUBSTR(last_name, 1, 1) = 'A' \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 996364
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.05 sec)

SUBSTR(last_name, 1, 1) = 'A'の代わりにlast_name LIKE 'A%'とすればインデックスが利用されます。

-- インデックスが使われる

> EXPLAIN SELECT * FROM users WHERE last_name LIKE 'A%' \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: range
possible_keys: index_users_on_last_name
          key: index_users_on_last_name
      key_len: 258
          ref: NULL
         rows: 42228
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

否定条件を利用している

否定条件の場合はインデックスが利用されませんでした。

-- インデックスが使われない

> EXPLAIN SELECT * FROM users WHERE age <> 30 \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: index_users_on_age
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 996364
     filtered: 80.58
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

否定条件の場合は選択範囲が広くなる傾向があります。
たとえばage <> 30の場合はageが30以外のレコードがすべて選択されます。ですので、否定条件の場合はそもそもインデックスを使う必要のないケースがほとんどです。

後方一致、もしくは中間一致でLIKEを利用している

インデックスが作成されたカラムはソート状態で管理されます。ですので、後方一致や中間一致ではインデックスが利用できません。

-- インデックスが使われない

> EXPLAIN SELECT * FROM users WHERE last_name LIKE '%A%' \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 996364
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

-- インデックスが使われない

> EXPLAIN SELECT * FROM users WHERE last_name LIKE '%A' \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 996364
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

先頭一致であればインデックスが利用できます。

-- インデックスが使われる

> EXPLAIN SELECT * FROM users WHERE last_name LIKE 'A%' \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: range
possible_keys: index_users_on_last_name
          key: index_users_on_last_name
      key_len: 258
          ref: NULL
         rows: 42228
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

インデックスが利用されたケース

以下のケースは一般的にインデックスが利用できないといわれていますが、手元の検証環境ではインデックスが利用されました。

暗黙の型変換を利用している
IS NULを利用している
ORを利用している

暗黙の型変換を利用している

検索条件で「数値型と文字列」「型文字列と数値」のように異なる型どうしを比較した場合、暗黙の型変換が行われます。たとえばage < '21'は内部でage < 21と変換されます。
暗黙で型変換が行われるケースでもインデックスが利用されました。

-- インデックスが使われる

> EXPLAIN SELECT * FROM users WHERE age  < '21' \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: range
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: NULL
         rows: 75004
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

IS NULを利用している

B-treeインデックスにおいてNULLはデータの値として扱われないためIS NULL検索ではインデックスが利用されないという記述をよく目にします。¹ ²

しかしMySQLのドキュメントや、IS NULLはインデックスを使うのかの記事にもあるようにIS NULLでもインデックスが利用されました。

-- インデックスが使われる

> EXPLAIN SELECT * FROM users WHERE age IS NULL \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

ORを利用している

『ORではインデックスが利用されない』『ORをINやUNION ALLに書き換えることでインデックスが利用される』と一般的にいわれています。
しかし、自分の手元の検証環境ではORでもインデックスが利用されました。

-- インデックスが使われる

> EXPLAIN SELECT * FROM users WHERE age = 30 OR age = 45 \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: range
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: NULL
         rows: 44256
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

もちろん、ORをINやUNION ALLに書き換えたSQLでもインデックスは利用されます。

-- インデックスが使われる

> EXPLAIN SELECT * FROM users WHERE age IN(30, 45) \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: range
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: NULL
         rows: 44256
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

-- インデックスが使われる

> EXPLAIN SELECT * FROM users WHERE age = 30 UNION ALL SELECT * FROM users WHERE age = 45 \G;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: users
   partitions: NULL
         type: ref
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 22196
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: users
   partitions: NULL
         type: ref
possible_keys: index_users_on_age
          key: index_users_on_age
      key_len: 5
          ref: const
         rows: 22060
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

まとめ

インデックスが利用できないと一般的に言われているケース

インデックス列に対して演算をする
インデックス列に対してSQL関数を適用している
否定条件を利用している
後方一致、もしくは中間一致でLIKEを利用している
暗黙の型変換を利用している
IS NULを利用している
ORを利用している

検証環境においてインデックスが利用されなかったケース

インデックス列に対して演算をする
インデックス列に対してSQL関数を適用している
否定条件を利用している
後方一致、もしくは中間一致でLIKEを利用している

検証環境においてインデックスが利用されたケース

暗黙の型変換を利用している
IS NULを利用している
ORを利用している

Twitter(@nishina555)やってます。フォローしてもらえるとうれしいです！

メニュー

【SQL】インデックスが利用されない検索条件についてEXPLAINで検証してみた

検証に利用したダミーデータについて

インデックスが利用されなかったケース

インデックス列に対して演算をする

インデックス列に対してSQL関数を適用している

否定条件を利用している

後方一致、もしくは中間一致でLIKEを利用している

インデックスが利用されたケース

暗黙の型変換を利用している

IS NULを利用している

ORを利用している

まとめ

参考

プロフィール

オススメ記事

カテゴリー

アーカイブ

検証に利用したダミーデータについて

インデックスが利用されなかったケース

インデックス列に対して演算をする

インデックス列に対してSQL関数を適用している

否定条件を利用している

後方一致、もしくは中間一致でLIKEを利用している

インデックスが利用されたケース

暗黙の型変換を利用している

IS NULを利用している

ORを利用している

まとめ

参考

プロフィール

オススメ記事

人気の記事

カテゴリー

アーカイブ

タグ