MySQL カバリングインデックス

カバリングインデックス

データが欲しい時に、より小さなコストでやろうと思ったら、
行全体を取得するよりも、インデックスのみで処理するようが良いに決まっている。

そこで、クエリを処理するのに必要なデータを全て含んでいるようなインデックスをカバリングインデックスと呼ぶ。
これは非常に有効な手段で、特にInnnoDBにおいては、セカンダリインデックスは、主キーの値をそのままリーフノードに格納するため、クエリをカバーするセカンダリインデックスによって、主キーでもう１つのインデックスルックアップを回避することができる。

カバリングインデックスが実行された時、EXPLAIN句のEXTRA列にUsing Indexが表示される。

それでは、これから、複合インデックスを用いてカバリングインデックスを活用することを考える。
Userのageとnameがの一覧が必要であれば、(age, name)の複合インデックスをUserテーブルにはる・
そして、

SELECT
  age,
  name
FROM
  users

これは、Using Indexとなる。

注意したいのが、MySQLにおけるクエリオプティマイザは、クエリを実行する前にインデックスを選択するため、
インデックスがWHERE条件をカバーしてもSELECTをカバーしないとカバリングインデックスと判断されないことだ。
そのため、下記のようなクエリは、チューニングを台無しにする。

SELECT
  *
FROM
  users
WHERE
  age >= 20
  AND name LIKE "riita%"

これはUsing Whereとなる。

SELECT * により、全ての列をカバーしているインデックスのみがカバリングインデックスであり、そのようなインデックスは存在しないため。

この場合は、カバリングインデックスとして、(id, age, name)を採用して、
(※ InnnoDBでは、セカンダリインデックスがリーフノードに主キー値を格納するため、事実上では、インデックス(id, name)は上記のクエリをカバーし、実際上記のクエリに対して、インデックスを使用する)

SELECT
  *
FROM
  users AS list
INNER JOIN (
  SELECT
    id
  FROM
    users
  WHERE
    age >= 20
    AND name LIKE "riita%") AS ind
ON
  ind.id = list.id

とすることで、カバリングインデックスとして、探索をすることができる。
ただし、WHERE句のヒット率が高すぎる場合、行を取得するコストを削減するよりも、
相関サブクエリを起動するコストがかかってしまいあまりパフォーマンスが改善されないような場合もある。

ソートのためのインデックススキャン

ORDER BY句をインデックスがカバーしていれば、そのインデックスを用いて並び替えを行うため、
クイックソートアルゴリズム$O(nlogn)$を使用する必要がなくなる。
クイックソートアルゴリズムを使用するとオプティマイザーが判断した時、Explain句のExtra列にUsing filesortと記述されることを確認すると良い。

ORDER BYがインデックスで順序付けをすることができるのは下記の場合である。

• まず、WHERE句とORDER BY句がどちらもインデックスにカバーされている必要がある。
• そして、インデックスの順序がORDER BY句と同じかつ、全ての列が同じ方向でソートされている場合
• 左端プレフィックスの原則を満たしている
  • WHERE句によって定数を確保していれば問題ない

以下のような時には、順序付けにインデックスが使用されないので注意

• ORDER BY id DESC, age ASCとしているが、インデックスがASCで並んでいる
• WHEREまたはORDER BYをインデックスがカバーできていない
• カバーしていても左端プレフィックスの原則に準拠していない
• 最右端以外のインデックスが範囲指定をしてしまう(範囲指定すると残りのインデックスを使用できない)

冗長インデックス、重複インデックス

やめましょう

参考

実践ハイパフォーマンスMySQL 第3版
Baron Schwartz、Peter Zaitsev、Vadim Tkachenko　著、菊池 研自　監訳、株式会社クイープ　訳
2013年11月 発行
https://www.oreilly.co.jp/books/9784873116389/