クエリを考える際の範囲条件と複数等価条件の違い
こんにちは，coreです
indexを使って，テーブルの最適化を行なっているのですが
どうもEXPLAIN結果を見て，「using index」と出ていると
それだけで満足してしまいます_(┐「ε:)_
それではよくない！
ということで，曖昧になりがちな複合インデックスについて．
エントリーのタイトル，自分でもどうつけようか迷ったんですが
とりあえず詰まったので勉強しながら書いていきます…
今回の場面設定
- テーブルの最適化
- SELECTで範囲検索を行いたい
- explainの結果から分からない状況
- テーブル定義は次の通り
createdb user_data(
user_id int unsigned
age int unsigned #20-80歳
sex varchar(2) #M,F
updatetime int unsigned #unixtime
)type=InnoDB;
で，じゃあ今回は上のテーブルから
20代の女の子，テーブル登録が2013年8月に当てはまる行をSELECTします．
その前に
indexはどうやってはりましょうね
とりあえず，user_idは恐らくユーザ固有のもので
他のテーブルにもuser_idがあって，結びつくはずなので
auto_incrementにしているわけではありませんが
user_idはprimary keyにします
mysql> alter table user_data add primary key(user_id);
それから，セカンダリインデックスについては
今回はSELECT文の，WHERE句以降に
user_id以外の全てのカラムが入ってくるので
複合インデックスになることは大体察しがつきます．
ではどういう順番にインデックスを貼ればいいのでしょうか
コメントアウトにあるように，
実際の想定されるデータ値から考えてみます．
---
age -> 20歳から80歳（つまり60通り）
sex -> 女性か男性（２通り）
updatetime -> unixtime
---
あれ．
えーっと，ageとsexについては値が何通りなのか想像出来ますが
updatetimeについては，決め打ちの値が出せません．
このテーブルに登録されている日時は
「いつが最古で，そしていつまでの値が入っているのか」を
明確化していないからですね．
まぁ，なのでとりあえず
「たくさんある」認識で進めていきます．
複合インデックスはindex i1(A,B,C)としたときに
Aの値をソートし，それにあわせてB，Cをおいた状態でindexが作成されます．
だからそれがごちゃごちゃにならないよう
パターンが少ないものからindexをはっていくと 良いような気がします
mysql> alter table user_data add index i1(sex, age, updatetime);
で SELECT文です．
とりあえず素直に書いてみました．
mysql> SELECT user_id from user_data -> WHERE sex = 'F' -> AND age BETWEEN 20 AND 29 -> AND updatetime BETWEEN 1375282800 AND 1377961199;
ところで
MySQLは，範囲条件が２つある場合
その片方でしか綺麗に取ってくることができません．
なので，少し格好悪いかたちになりますが
ageは全てIN句で指定します．
mysql> SELECT user_id from user_data -> WHERE sex = 'F' -> AND age IN(20,21,22,23,24,25,26,27,28,29) -> AND updatetime BETWEEN 1375282800 AND 1377961199;
先ほどあげたクエリと，このクエリ
EXPLAINでみたときに，type=rangeと表示され
ともに範囲クエリであるように思えます．
ただ前者は，カラムageにおいて「値の範囲」を条件とし
後者は「値の等価」を条件としてます．
範囲条件の場合，MySQLはindexの他の列を考慮しませんが
等価条件の場合，そういった制限がありません．
ついつい忘れがちです．
IN句で書ききれないときは，
テーブル設計を見なおしたり
検索条件そのものの方針が誤っていたり
…っていう可能性が
高いのではないでしょうか．
