MySQLのinnodbのインデックスについて調べてみた

database

innodbの主キー*1はクラスターインデックス

クラスターインデックスでは、主キー(B-tree)のリーフページにデータが直接格納されています。
以下の図のようなイメージです。

株式会社スタイルズ

図は引用するとややこしいのでしてませんが、図を見たほうが分かりやすいです。

InnoDBの主キーは次の図のように「データが主キーのリーフノードに含まれる」という構造になっている。このような構造をクラスタインデックスという。

漢(オトコ)のコンピュータ道: 知って得するInnoDBセカンダリインデックス活用術！

理解しにくいとこなので、別のエントリーも引用。このエントリーにも分かりやすい図があります。

セカンダリーインデックスのリーフには主キーの値が入ってる

クラスタインデックスを用いる場合、データはすべて主キーに格納されているので、セカンダリインデックスは特殊な構造にならざるを得ない。セカンダリインデックスの値からデータを取得するにはどうすればいいのだろうか？ご存知の方も多いだろうが、セカンダリインデックスのリーフノードには主キーの値が格納されている。

漢(オトコ)のコンピュータ道: 知って得するInnoDBセカンダリインデックス活用術！

前述のエントリーだけど、セカンダリーインデックスの図も分かりやすい。

主キーを定義しない場合でも、結局主キーはある

もしテーブルに PRIMARY KEY を定義しなければ、MySQL は主キーとして NOT NULL カラムだけを持つ最初の UNIQUE インデックスを選択し、InnoDB がそれを集合インデックスとして利用します。もしテーブル内にそのようなインデックスがなければ、InnoDB は、行が InnoDB がそのようなテーブル内の行に割り当てた行 ID によってオーダされる集合インデックスを内部的に生成します。行 ID は、新しい行が挿入されると単調に増加する6バイトのフィールドです。従って、行 ID によってオーダされた行は物理的に挿入順になっています。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13 InnoDB テーブルおよびインデックスの構造
主キーを定義
定義したものが主キーになる
NOT NULLかつUNIQUEインデックスが付いたカラムがある
それが主キー扱いになる
何も無い場合
内部的に主キーを生成する

自分のクラスターインデックスへの理解が間違ってなければ、innodbはそもそもデータをB+treeの中に置くので、主キーが無いとB+treeが構成できず、主キーが無いということはありえないはず。
あと、結局内部的に主キーが作られるので、主キーが要らない様なテーブルで、INSERTの負荷軽減の為に主キーを作らないのは、あまり意味がなさそう。

インデックスカーディナリティ

ユニークなレコードが多い程、カーディナリティが高くなる。カーディナリティが高いと、インデックスの効果が高くなる。要するに、違う値がなるべく多い、出来ればユニークなカラムほど、インデックスの効果が高い。

3つのカーディナリティ

データベースまわりでカーディナリティとか濃度という言葉がバラバラの意味で使われているので整理する。
以下の３種類の使われ方がある。

1. relationのカーディナリティ
リレーション中のタプルの数（不正確に言えばテーブル中のレコード数）をカーディナリティという場合。
これが原義。
Date「データベースシステム概論」P-90

一つの組(tuple)は表の一つの行に対応し、一つの属性(attribute)は一つの列に対応している。
組の数は濃度(cardinality)とよばれ、属性の数は次数(degree)とよばれる。

2. relationshipのカーディナリティ
エンティティ間の関係が1対1・1対多・多対多のどれか、ということをカーディナリティという場合。

3. キーのカーディナリティ
キーの値の数と、全レコード件数との比を、カーディナリティという場合。
日本語の「濃度」のイメージに近いのでわかりやすいような、そうでもないような。

http://www.oracle.co.jp/interactive/Techniques/VLDB/Dss/step0302.html
ビットマップ索引の効果を左右するのは「カーディナリティ」です。
「カーディナリティが低い」とは、索引キーの値が行数に比べて少ない種類の値しか取らないこと
を意味します。例えば「性別」は「男」「女」の2種類の値のいずれかになりますが、これは最も
カーディナリティの低いデータの例だといえます。
「行数に比較して少ない種類の値」というところがポイントです。値が2種類しかなくても、行数が
2行しかなければカーディナリティが低いとはいえません。また1万種類の値を取るデータでも、
行数が数億行あれば十分カーディナリティは低いといえます。

カーディナリティて何ですの - 極北データモデリング

RDBでテーブルの作り方が良くわからなくなってきたので、いったんまとめてみた - kanonjiの日記で、テーブルのhasOneやhasManyの関係の事をカーディナリティと呼ぶと思ってたので、インデックスカーディナリティは最初混乱しました。でも、どうやら合計で3種類のカーディナリティがあるらしい。カーディナリティって言葉に意味持たせすぎじゃ・・・

インデックスの情報を見る

SHOW INDEX 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    |       |
MySQLパフォーマンスチューニングのためのインデックスの基礎知識 - 久保清隆のブログ

こんな結果が得られる。各項目の説明は、引用元に表があります。

Covering Index

innodbで全行数をCOUNT(*)やCOUNT(id)で取得しようとすると、意外に重たい処理になるらしいです。主キーで数えれば、たいていの場合ただのintだしユニークだしインデックスもあるし、早そうな印象があります。どうやら、クラスターインデックスである事が原因で、主キーを構成するB-treeのリーフに、実データが入っている事から、全主キーを調べるという事はテーブルの全データを読み込むという事になるみたい*2です。

このような構造になっていることには利点と欠点があるが、大きな利点は主キーの値で検索をすると非常に高速だということだ。主キーのリーフノードにたどり着いたときには、既にデータのフェッチも完了している。データとインデックスが別々に格納されているタイプのストレージエンジンでは、インデックスからデータの位置を読み取って、その後データファイルからデータをフェッチする。このように二段階の操作が必要であると、キャッシュが効いていない場合には余分なディスクI/Oが生じてしまうだろう。だが、クラスタインデックスになっていると、インデックスを検索するだけでデータもフェッチできるのである。

漢(オトコ)のコンピュータ道: 知って得するInnoDBセカンダリインデックス活用術！

同じエントリーばかり引用してるけど、ここによると「主キーを検索するだけでデータもフェッチできる」とあります。だから「大きな利点は主キーの値で検索をすると非常に高速」ともあるんだと思う。このエントリーで明言してないけど、件数を調べるときに主キーを使っても、データもフェッチしてしまって、主キーを調べるだけというのが出来ないという事なのかな？

要はいかに I/O を減らすかが重要になってくるというわけです。例えばこのような SQL を考えてみます。

SELECT hoge, fuga FROM table_1 WHERE foo = 1;

普通に考えると [foo] にインデックスを張って、リーフで得られた値から他の列値 (hoge, fuga) を取得する、とやりたくなるところです。ただ、ここで [foo, hoge, fuga] という複合インデックスを張ることで、リーフだけで必要なデータが全て得られ、その後のランダムアクセスが無くなるため高速になります。このようなインデックスだけで完結するインデックスを Covering Index と言うそうです。

MySQLでインデックスを使って高速化するならCovering Indexが使えそう - (ﾟ∀ﾟ)o彡 sasata299's blog

ともあれ、Covering Indexを使うと高速になる可能性があるらしい。Covering Indexというのはインデックスだけで完結するインデックスだそう。

クラスタインデックスにおけるセカンダリインデックスは、悪いことばかりではない。そもそも検索がセカンダリインデックスだけで済むようにCovering Indexにすれば非常に高速だ。その辺の事情については、過去記事「InnoDBでCOUNT()を扱う際の注意事項あれこれ。」にも書いてあるので参照して頂きたい。

漢(オトコ)のコンピュータ道: 知って得するInnoDBセカンダリインデックス活用術！

Covering Indexについては、ちょっと複雑で引用するのにちょうど良い文量のエントリーが見当たらなかったけど、上記2つの引用元が分かりやすいです。

Covering Indexを使うにはセカンダリインデックスを使うので、COUNTが早くなっても更新処理が逆に遅くなるかも知れない。

SHOW ENGINE INNODB STATUS と InnoDBモニタ、innodbテーブルモニタ

InnoDB は InnoDB 内部の状態についての情報をプリントする InnoDB モニタを含んでいます。ご自分の SQL クライアントにスタンダード InnoDB モニタのアウトプットをフェッチする為に、いつでも SHOW ENGINE INNODB STATUS SQL ステートメントを利用する事ができます。 この情報は性能調整をするうえで役立ちます。

http://dev.mysql.com/doc/refman/5.1/ja/innodb-monitor.html 
SHOW ENGINE INNODB STATUS\G

innodbの内部情報を知るためのクエリーと、innodbモニタ、innodbテーブルモニタという機能もあります。

書いた日

2011-12-19
例によって下書きのまま放置してた、筈なんだけど、これを書いた記憶が無くって普通に読んでしまった。
他の下書きは、一応書いたって事くらいは覚えてるんだけどなぁ。
自分で読んでて「全主キーを調べるという事はテーブルの全データを読み込むという事」の根拠が無くて気になったので、ちょっと加筆しました。

*1:プライマリーキー

*2:どこでその情報知ったか分からなくなったので、ちょっと自信ないですが