主キーを含む複合インデックスのパフォーマンスについて

Question

MySQLを使用してます。
とあるテーブルにおいて

PRIMARY KEY (id)

と設定されているテーブルに、

INDEX(id, delete_flag)

のようなインデックスを貼ることは、パフォーマンス上意味があるのでしょうか？

id カラムがユニークである以上、主キーの方のインデックスのみで
行を特定できるので、上記のインデックスは追加するのは不要だと思いました。

Answer 1

その手のインデックスはカバリングインデックスとして使えます。

例えば次のようなテーブルがあったとして、

create table t (
    id int not null primary key,
    delete_flag boolean not null,
    str char(10)
);

id と delete_flag のみを select するSQLを explain すると次のようになります。

mysql> explain select id, delete_flag from t \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL

これはつまりテーブル全体が読まれています（不要な str フィールドも含めて）。

次の通りにインデックスを作成すると、

create index idx__id__delete_flag  on t (id, delete_flag);

explain は次のように変わります。

mysql> explain select id, delete_flag from t \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: idx__id__delete_flag
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index

Extra に Using index とありますが、これはインデックスのみで SQL が完結できたためテーブルそのもの（＝主キー）は読まなかったという意味です。

テーブルそのもの（＝主キー）よりもセカンダリインデックス（主キーじゃないインデックス）の方がサイズは小さいので、後者の方が良い性能がでます。

もちろん、セカンダリインデックスだけで SQL が完結できなければならないので、次のように SQL で str フィールドも使っているとカバリングインデックスにはなりません。

mysql> explain select id, delete_flag from t where str = 'ore' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where

Comments

[sairoutine]

遅ればせながらありがとうございました。
納得が行きました。