MySQLパフォーマンスチューニングのためのインデックスの基礎知識

仕事でMySQLのパフォーマンスチューニングをしていて、インデックスについて分かっていないことが多かったので調べたことをメモ。基本的なところから学習しなおした。

MySQLのインデックスは、カラムが特定の値をもつレコードの迅速な検索に使用される。インデックスを使用すれば、数百とか数億ものレコードが入っているテーブルから、一組のレコードを迅速に見つけて取り出すことが可能になる。
しかし、インデックスは速度を改善することもあるが、挿入の邪魔になって遅くなることもある。
インデックスを適切に使うために、まずはインデックスの基本概念をおさえる必要がある。

インデックスの概念

インデックスとは

インデックスの仕組みを理解するには、まずMySQLがどのようにクエリに応答するかを知る必要がある。
例えば、

SELECT * FROM phone_book WHERE last_name = 'Hoge'

というクエリを実行する。その時、MySQLは、以下のような動作をする。

phone_bookテーブル内のレコードをすべて読み出す。
last_nameフィールドを調べ、文字列'Hoge'に一致するかどうかを比較する。

上記の方法は、効率が悪い。レコードの数が増えるにつれ、特定のレコードを見つけ出すのに必要な処理も増える。因みにこれをO(n)問題という。

※O(n)とは、テーブル内のレコード数をnとすると、特定のレコードを見つけるための検索に要する処理量(オーダー)がレコードnに比例すること。

上記を効率化するにはどうすればいいのか？
本みたいに索引を作れば速く検索できる。
普通、本には巻末に索引が載っていて、用語や概念の場所をすぐに見つけ出すことができる。
これと同じようなことをすればよい。
インデックスを付けるというのは、本の索引のように、レコードの場所を示す値をつけること。
インデックスを付けるには以下のようにする。

ALTER TABLE phone_book ADD INDEX (last_name)

データベースサーバの観点からすると、インデックスを使用する理由は、クエリの実行結果から可能性のある行を迅速に選び出せるようにするため。
インデックスがなければ、MySQLはテーブル内のすべての行を調べなければならない。
一方でMySQLは、インデックスの値を別個のリストで保守し、データに変化があるたびにインデックスを更新し続ける必要がある。
テーブルのすべてのカラムにインデックスをつけると書き込みの速度が低下するし、データを保存するために必要な容量が増えてしまう。
インデックスを使用すると、検索などの性能は基本的に向上するが、容量が犠牲になる。

部分的インデックス

性能を向上させたいが、容量もあまり犠牲にしたくないときは部分的インデックスが使える。例えば、最初の４バイトだけにインデックスを付けることができる。

ALTER TABLE phone_book ADD INDEX (last_name(4))

これにより、インデックスのデータ部に必要な容量は減少する。ただし、複数のレコードに同じインデックスの値がついてしまう可能性がある。

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

複数のカラムにインデックスを付けることもできる。(複合インデックスとか、合成インデックスと呼ぶこともある。)

ALTER TABLE phone_book ADD INDEX (last_name, first_name)

複数に付ける場合、単に２つのインデックスを作成してはだめなのか？という疑問がある。
そうしてもいいが、MySQLでは、１つのクエリを実行するとき、１つのテーブルにつき１つのインデックスしか使用できない。
従って、first_nameとlast_nameに別々のインデックスが付いている場合、MySQLはどちらか一方を選択する。
MySQLは、経験に基づく推測により、どちらのインデックスの方が少ない行に一致するかを判断する。
マルチカラムインデックスを使用すれば、複数のカラムにインデックスが使われ、クエリの速度を向上させることができる(もちろん、向上できないときもある)。

Railsだと以下のように書ける。配列で複数指定する。

add_index :phone_book, [:last_name, :first_name]

ユニークインデックス

ユニークインデックスを使用すると、クエリで一致する行を見つけるために存在するだけでなく、特定のカラムで特定の値が一度しか出現しなくなる(NULLは除く)。
つまり、レコードを挿入または更新しようとするとき、すべての値を調べて、同じ値が既に存在しないことを確認する。
このようにユニークインデックスは制約条件として働く。

ALTER TABLE phone_book ADD UNIQUE(phone_number)

インデックスが適切に使われているかの確認

まずはindexが使われているか確認。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.8.2 EXPLAIN 構文
- EXPLAIN を利用すると、より速くレコードを検索する SELECT を得るために、どの時テーブルにインデックスを追加しなければならないかを確認できる。

どういうときにindexが使われるかは以下のサイトが参考になる。

一日目午後：MySQLの最適化 | Oliverの日記 | スラド
- indexが使われる時
  - フィールド値を定数と比較するとき (WHERE name = "hogehoge" )
  - フィールド値全体でJOINするとき ( WHERE a.name = b.name )
  - フィールド値の範囲を求める時 >,
  - LIKEで文字列の先頭が固定な時
  - MIN(), MAX() (複数要素indexの同一first fieldでsecond fieldのmin,max でも有効)
  - 文字列のプレフィックスをもとにしたORDER BY, GROUP BY
  - WHEREのすべてのフィールドがindexの一部の場合 (DBまったく参照されず)
- indexが使われない時
  - LIKEがワイルドカードで始まる時
  - DB全体を読んだ方が早いとMySQLが判断した時
  - 通常はindexはORDER BYには使われない
  - WHERE と ORDER BYのフィールドが違う時にはどちらかしか使われない

インデックスの構造

インデックスのデータの型の種類とそれぞれの特徴について。

Bツリー(balanced tree)インデックス
- 一番人気が高い。インデックスの型として最もよく使われる。
- 柔軟性、サイズ、全体的な性能のバランスをうまくとっているから。

ハッシュインデックス
- 二番目に人気が高い。
- ツリーよりもハッシュテーブルに似ていて、各キーにハッシュ関数を実行した結果に基づいて並べる。

Rツリーインデックス
- 空間データまたはN次元データを扱うときに用いられる。
- 地図や地球科学のアプリケーションで幅広く使われる。

インデックスとテーブル型

MySQLのストレージエンジンに組み込まれているインデックスについて。

MyISAMテーブル
- デフォルトではBツリーインデックスを使用
- Rツリーインデックスも使用できる(MySQL 4.1.0以降)

InnoDBテーブル
- Bツリーインデックスを使用
- データやデータディクショナリ(表の定義など)だけでなく、インデックスもテーブルスペースに格納
- クラスタ化インデックスを使用
- InnoDBの主キーによる検索は非常に高速

インデックスの保守

稼働中のインデックスの保守と管理については、特殊な作業は特に実行する必要はない。ただ、時々実行したほうが良い作業がある。

インデックス情報の取得

テーブルのクエリが遅くなったり、インデックスの問題が発生するなど、しばらくみなかったような問題が発生したとする。このようなとき、どのカラムにインデックスが付いているのか、いくつの値が存在するのか、インデックスのサイズはどれくらいか、といった情報を取得する必要がある。
こういうとき、show indexes from で詳細な情報を得ることができる。

show indexes from テーブル名;

以下のような情報が取得できる。

|Table   |Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|
+--------+----------+--------+------------+-----------+---------+-----------+--------+------+----+----------+-------+
|students|        0 |PRIMARY |          1 | id        | A       |     24964 |   NULL | NULL |    | BTREE    |       |

各データは以下のような内容。

Table	テーブル名
Non_unique	もしインデックスが複製を含む事ができなければ0、もしできるなら1。
Key_name	インデックス名
Seq_in_index	1から始まる、インデックス内のカラムシーケンス番号
Column_name	カラム名
Collation	カラムがインデックス内でどのようにソートされるか。 MySQL では、これは値 ‘A’ (昇順)か NULL (格納されない)を持つ事ができる。
Cardinality	インデックス内の固有値数の見積もり。ANALYZE TABLE か myisamchk -a を起動させる事で更新される。濃度が高ければ、その分 MySQL が接合を行う時にインデックスを利用する可能性は高くなる。 ※下記に別途説明。
Sub_part	もしカラムが部分的にだけインデックスされていた時のインデックスされる文字数。カラム全体がインデックスされていた時は NULL。
Packed	キーがどのようにパックされるのかを指示する。そうでなければ NULL。
Null	もしカラムが NULL を含んでいたら、YES。そうでなければ、NO。
Index_type	使用されるインデックス方法(BTREE、FULLTEXT、HASH、RTREE)
Comment	様々な意見

※カーディナリティとは(カーディナリティて何ですの - 極北データモデリング参照)

「カーディナリティが低い」とは、索引キーの値が行数に比べて少ない種類の値しか取らないことを意味。例えば「性別」は「男」「女」の2種類の値のいずれかになるが、これは最もカーディナリティの低いデータの例。
「行数に比較して少ない種類の値」というところがポイント。値が2種類しかなくても、行数が2行しかなければカーディナリティが低いとはいえない。また1万種類の値を取るデータでも、行数が数億行あれば十分カーディナリティは低いといえる。

統計情報の誤りや破壊

MySQL内部のインデックス統計情報が壊れるか、または誤っている場合(サーバがクラッシュするか、またはサーバが異常終了した結果として、このような状態になる可能性がある)、MySQLは非常に奇妙な動作を開始することがある。
統計情報が間違っている場合、MySQLはクエリに対してインデックスを使わないかもしれないし、一部のみインデックスを使うかもしれない。このような場合、クエリに一致する行数が多いという理由で、インデックスを使わずテーブルスキャンを実行した方が効率が良いとMySQLが判断することがある。
こういう時、インデックスの修復と分析のコマンドを試してみるとよい。

インデックス統計情報のリフレッシュ

時間がたつにつれて、テーブルに多くの変更が加わると、インデックスの効率が悪くなることがある。

最も良く発生しそうな問題は以下の２つ。
- ディスク上をデータブロックが移動することによるフラグメント化(断片化)
- インデックス統計情報が不正確になること

対応方法
- MyISAMテーブルの場合、インデックスデータを最適化するのは簡単
  - OPTIMIZE TABLEコマンドを使用して、テーブルにインデックスを付け直すことができる。
  - MySQLは、テーブル内のすべてのレコードを読み直し、すべてのインデックスをつくり直す。その結果、良好な統計情報を備え、緊密にパックされたインデックスができる。

- InnDBの場合、このような種類のチューニングが必要になることは少ない。
  - 手作業でインデックス全体を削除して作り直すか、テーブルの内容をダンプしてロードし直す。
  - ANALIZE TABLE不要(漢(オトコ)のコンピュータ道: 大人のためのInnoDBテーブルとの正しい付き合い方。)
  - InnoDBが自発的に統計情報を更新するから。
  - InnoDBのインデックス統計情報更新処理は比較的（というかかなり）軽い処理であるため、他の処理が止まる心配はない。