【MySQL】マルチインデックスの制約の話とか

#はじめに
　
大前提として、INDEXは極力少なくしてクエリを捌けさせる構造がベスト。
無駄なINDEXが存在するだけで、容量を食い(場合によっては、部分INDEXという手もありますが...)、
パフォーマンスも落ちるので二重苦となります。
　
　
INDEXは、本当に必要なものだけ！

　
　
これを踏まえた上で、マルチインデックスの話に移りたいと思います。
かなり説明を省いています。ご了承下さい。

#マルチインデックスとは
　
簡単に言うと、複数のカラムに対してのINDEX。単一のINDEXより高速な検索が可能です。

詳しくはこちら → http://dev.mysql.com/doc/refman/4.1/ja/multiple-column-indexes.html
　

ex.1

create table test (
  col1 int(5),
  col2 int(5),
  col3 int(5),
  primary key (col1),
  key cols_index (col1, col2, col3)　#マルチインデックス
) ENGINE=InnoDB;

　
例えばex.1のような、マルチインデックスを含めたテーブル構造だった場合。

##INDEX使用不可
　

ex.2

select * from test where col2 = 3;
select * from test where col2 = 1 and col3 = 3;
select * from test where col2 = 3 and col1 = 3;

　
マルチインデックスは、__クエリ文がINDEX作成時のカラム順に基づいていないと、
INDEXが使えない__という制約が存在します。
ex.2のようなクエリだと、INDEXの順番に基づいていないため、使用不可ということ
になります。
　
　
##INDEX使用可
　

ex.3

select * from test where col1 = 3 and col2 = 3;

　
これだったらカラム順に沿っているので、INDEXが使えるということになります。
　
　
#マルチインデックス作成時のポイント
　
##１つのクエリを実行する時、１つのテーブルにつき１つのINDEXしか使用できない。
　
つまり、もしINDEXがex.1の__cols_index__のようなマルチインデックスの他に、例えば__index (col1)__といった、マルチインデックス内のカラムを利用した単一INDEXが存在していた場合、場合によっては単一INDEXのほうは無駄になるということです。※1
この辺りは、EXPLAINとか使うとよりわかり易いです。今回は説明を省略します。
　
※1　どうしてもユーザーIDのみ持ってきたい時とかは必要な時もあるかも？
　　ただ、INDEXの性質がIDに基づいて対象テーブルを決めている場合だったらマルチインデックスだけで十分です。
　
　
#Performance Tuning のためのヒント
　
・COVERING INDEX
→簡単に言うと、INDEXだけで完結するINDEX。これによってランダムアクセスが無くなりま
　すので、I/Oの回数を減らすことができます。よって、これが高速化につながります。

・可能なところはNOT NULLを宣言

・JOINに使う列は同じデータ型に

・varchar(255)ではなく、varchar(64)に