じぶん対策

日々学んだことをアウトプットして備忘録にしています。

MySQLのインデックスの役割と活用について

はじめに

WEBエンジニアとなってクエリパターンの実装をする際に、SQLのパフォーマンスについて意識する場面が出てきました。
その際にインデックスが貼られていないカラムのソートのパフォーマンスについて指摘をいただく機会があったのでこの機会に調査してまとめたいと思います。
WEB開発の基本としてのSQLがまだまだ足りていないと感じているのでできるだけキャッチアップしたいと考えて記事を書きました。
この記事は特にMySQLについてのまとめとなります。

参考

https://dev.mysql.com/doc/refman/5.6/ja/optimization-indexes.html

https://dev.mysql.com/doc/refman/5.6/ja/mysql-indexes.html

https://use-the-index-luke.com/ja/sql/where-clause

インデックスとは

まずは公式ドキュメント等を参照しながらインデックスがどういうものか理解したいと思います。

  • インデックスは特定のカラムのある行を素早く見つけるために使用される。
  • インデックスがない場合、関連する行を見つけるために戦闘業から始めてテーブル全体を読み取る必要がある。
  • この時のコストはテーブルが大きいほど大きくなります。

インデックスを使用するパターン

MySQLは次の操作にインデックスを使用する。

  • WHERE句に一致する行を素早く見つけるため
  • 行を考慮に入れないようにするため(複数のインデックスから選択する場合に通常最小行を見つけるインデックスを使用します)
  • テーブルにマルチカラムインデックスがある場合、オプティマイザはインデックスの左端のプリフィクスを使用して行をルックアップできる。
  • 結合の実行時に、他のテーブルから行を取得するため。カラムが同じ型とサイズで宣言されていると、MySQLはカラムのインデックスをより効率的に使用できる。（VARCHARとCHARは同じサイズで宣言されていれば同じとみなされます。）
  • 特定のインデックス設定されたカラムkey_colに対して、MIN()あるいはMAX()値を見つけるため。
  • 使用可能なインデックスの左端のプリフィクスに対してソートまたはグループ化が行われている場合(例えば、ORDER BY key_part1, key_part2)に、テーブルをソートまたはグループ化するため。全てのキーパートのあとにDESCが付けられている場合、キーは逆の順序で読み取られます。
  • 場合によって、データ行を参照しないで値を取得するように、クエリを最適化できます。(クエリーの必要な全ての結果を提供するインデックスは、カバーするインデックスと呼ばれます。)クエリーがテーブルから特定のインデックスに含まれるカラムのみを使用している場合、極めて高速に、選択した値をインデックスツリーから取得できます。

小さなテーブルまたはレポートクエリーが行の大半または全てを処理する大きなテーブルに対するクエリーではインデックスはあまり重要ではありません。クエリーで行の大半にアクセスする必要がある場合は、順次読み取る方が、インデックスを処理するより高速です。
クエリーで全ての行が必要でない場合でも、順次読み取りは、ディスクシークを最小にします。

上記を参考にすると、以下のことがわかります。 - インデックスを設定しない場合、レコードが多くなってくると単純な検索にも時間がかかるようになる。フルテーブルスキャンが行われるため。 - インデックスを設定することでWHERE句での比較等が高速化する。

https://roy29fuku.com/infra/sql/mysql-summary-of-index/

主キー(プライマリキー)の使用

また、通常MySQLではテーブルにデフォルトでidカラムが設定されています。idにはPRIMARYキーが設定されており、インデックスが設定されています。すなわち、WHERE句を使用する場合でもidを使用した場合は高速に結果を取得できます。
また、プライマリキーにはNULL値を含めることができないため、NOT NULL最適化からメリットが得られます。(NULLが多いデータベースの場合により有効になる)

外部キーの使用

https://dev.mysql.com/doc/refman/5.6/ja/optimizing-foreign-keys.html

テーブルに多くのカラムがあり、さまざまなカラムを組み合わせてクエリを作成する場合、あまり頻繁に使用されないデータをそれぞれ少数のカラムを持つ個別のテーブルに分割し、それらをメインテーブルのIDカラムを複製してメインテーブルに紐づけると効率的な場合があります。

カラムインデックス

https://dev.mysql.com/doc/refman/5.6/ja/column-indexes.html

もっとも一般的なインデックスの種類には単一カラムというものがある。
データ構造にそのカラムのコピーを格納し、対応するカラム値のある行を高速にルックアップすることができる。
Bツリーデータ構造により、以下の操作が素早くなる。

  • WHERE句内の =, >, <=, BETWEEN, INなどの演算子に対応する特定の値、値のセット、または値の範囲を見つける

プリフィックスインデックス

インデックス指定でcol_name(N)構文を使用することで文字列カラムの先頭のN文字のみを使用するインデックスを作成できる。
メリットはインデックスファイルをかなり小さくできること。

マルチカラムインデックス

単一のインデックスで絞りきれないような場合にはマルチカラムインデックスという機能を使用できます。

https://dev.mysql.com/doc/refman/5.6/ja/multiple-column-indexes.html

実際にインデックスを使用する場面について

ここまで公式ドキュメントを元にインデックスの仕組みについて確認してきました。
実際に使用する際に脳死でインデックスを貼れば良い、というわけでもなさそうです。

インデックスを使用すべきパターンと使用すべきでないパターンについて調査してみます。

インデックスを貼るべきカラムとは

参考:
https://qiita.com/katsukii/items/3409e3c3c96580d37c2b

https://qiita.com/C058/items/1c9c57f634ebf54d99bb#%E3%83%81%E3%83%A5%E3%83%BC%E3%83%8B%E3%83%B3%E3%82%B0%E3%81%99%E3%82%8B%E9%9A%9B%E3%81%AE%E3%83%A1%E3%83%A2

上記Qiitaを参考にまとめてみます。

  • デーブル内のデータ量が多く、少量のレコードを検索する場合
  • WHERE句の条件、結合の条件、ORDER BY句の条件に利用する。
  • NULL値が多いデータから、NULL値以外の検索をする。

インデックスを作成すべきではないカラム

  • 表の規模が小さいか、表から大部分のレコードを検索する場合
  • WHERE句等の条件としてあまり使用されないもの
  • 列の値が頻繁に挿入、更新、削除される
  • WHERE句の条件として使用されるが、列が式の一部として参照される

インデックスを使用するとき

  • フィールド値を定数と比較する時
  • フィールド値全体でJOINするとき
  • フィールド値の範囲を求めるとき
  • LIKEで文字列の先頭が固定の時
  • MIN(),MAX()
  • 文字列のプレフィックスを元にしたORDER BY,GROUP BY
  • WHEREのすべてのフィールドがindexの一部の場合

使用しない時

  • LIKEがワイルドカードで始まる時
  • DB全体を読んだ方が早いとMySQLが判断した時
  • 通常はindexはORDER BYには使われない
  • WHEREとORDER BYのフィールドが違う時にはどちらかしか使われない

カーディナリティーとは

一般的にインデックスはカーディナリティが高いものを設定するとより効果的らしいです。
カーディナリティーという単語自体初耳だったので調査してみます。

一言で言うと

カラムの値の種類の絶対値

レコードの数に対してカラムの値の種類が少ない場合はカーディナリティが低いと表現します。
例えば、以下の2つカラムがあるとします。

  1. 性別の値が入力されているパターン。
  2. 1年間の日付が入力されている365日が入力されているパターン。

1の場合は2パターン。2の場合は365パターンあります。
定義からするとレコードの数に対してのパターンの数なので前者がカーディナリティが低くて後者がカーディナリティが高いということになります。

インデックスの貼る順序は、カーディナリティの高い順に貼っていくのが定石らしいです。
貼る順序で一回の処理で絞り込む量が変わってくるのでカーディナリティの高い順に貼ることで効果が高くなります。
複合インデックスを貼る際にもこの理論は適用されます。

参考:

https://blog.shibayu36.org/entry/2012/06/02/210848

https://kiyotakakubo.hatenablog.com/entry/20101117/1289952549

インデックスが貼られているカラムを確認する方法

ちなみにどのカラムにインデックスが貼られているかは以下のコマンドで確認できます。

show index from テーブル名;

また、実行するコマンドにexplainをつけることで実行プランを確認できます。

https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html

まとめ

  • インデックスを作成することで検索などの処理が高速化できる。
  • インデックスを貼るべきカラムを選ぶ基準はカーディナリティの高いカラム。
  • 実際の速度の改善についてはexplainを使用して調べる。

所感

インデックスを適切に利用することで処理の高速化ができる、というか基本的に貼るべき。
インデックスを貼るべきカラムの選定については考える基準やベストプラクティスのようなものがあるので仕様と合わせて考慮する必要があると思いました。
カーディナリティという考え方自体を初めて知りましたが、パフォーマンスを考える上でとても大事な考え方でした。
インデックスの仕組みを一通り抑えることがSQLの勉強の第一歩かなと思いました。
個人的にSQLについてまだまだ知るべきことが多いのでまたまとめたいと思います。