MySQLのINDEXとか最適なクエリとか
MySQLのチューニングに関して、特にINDEXを貼ったときのSELECT文で躓いたのでメモ。
INDEXとは
まず、INDEXを使わないシンプルなSQL文で、クエリに対してMySQLがどのように応答するのだろうか。以下、サンプル。
SELECT * FROM hoge WHERE foo = 'bar';
上記のようなクエリを実行すると、MySQLはhogeテーブルに格納されている全てのレコードを読み、fooフィールドに文字列barが存在するか確認する。レコードの数が数百数千であれば問題ないのだろうが、数十万件数百万件のレコードが記録されている場合、これでは効率が悪い。
この問題を解決するために、INDEXが存在する。まさしく書籍における牽引のように、レコードの格納場所を予め指定しておく便利な機能だ。投げられたクエリに応じて、可能性のある行を素早く選出できるようになる。INDEXを付与するためには、以下のように書く。
ALTER TABLE hoge ADD INDEX (foo);
注意点
しかし、欠点もある。検索の性能は劇的に改善することが多いが、往々にして容量の圧迫や定期的な更新問題が生じることを頭に入れておきたい。
もう１つ重要なのは、MySQLは1つのクエリで、1つのテーブルに対し、1つのINDEXしか機能しないという原則だ。この原則を忘却した状態でいくらクエリを投げてみても、高速化は見込めない。ちなみに、１つのクエリで１つのテーブルに対し複数のINDEXを貼った場合、どのINDEXが適用されるかはクエリによって変化する。
では、複数のINDEXをどうしても利用したいという場面ではどうすればよいのだろうか。
複合INDEX
複合INDEXを使えば問題は解決できる。INDEXにしたい列を繋ぎ合わせることで１つのINDEXとして扱える。しかし、BTREEとFULLTEXTなど型が違うと作成できないようだ。また、複合される順番によっても左右されるので、実行するクエリのWHERE句などを考慮に入れて作成する必要がある。このあたりはEXPLAIN構文を用いて要確認。以下で作成。
ALTER TABLE hoge ADD INDEX (foo,bar);
あとはサブクエリで別テーブルから引っ張ってくることでINDEXを用いたり、UNION文を使用することが候補に上がる。未だ軽負荷・高速なクエリを模索中。
メモ MySQLの最適化
【MySQLがindexを活用する時】
フィールド値を定数と比較 (WHERE name = "hogehoge")
フィールド値全体でJOIN (WHERE a.name = b.name)
フィールド値の範囲を求める
LIKEで文字列の先頭が固定
MIN(),MAX()(複数要素indexの同一firstfieldでsecondfieldのmin,maxでも有効)
文字列のプレフィックスをもとにしたORDER BY,GROUP BY
WHEREのすべてのフィールドがindexの一部の場合(DBまったく参照されず)
【indexが使われない時】
LIKEがワイルドカードで始まる
DB全体を読んだ方が早いとMySQLが判断
通常はindexはORDER BYには使われない
WHEREとORDER BYのフィールドが違う時にはどちらかしか使われない
【EXPLAIN構文の際Extrasで見たくないもの】
using filesort(余分なソート)
using temporary(一時ファイルの作成)
【Extrasで見たいもの】
using index(DB本体を読む必要なし)
Where used(type:ALLとの組み合わせだとindex作成推奨)
【SELECTの最適化】
LEFT JOIN, STRAIGHT JOINとUSINGの組み合わせはWHEREより早い
WHERE field INはかなり早い
WHEREが使えるならHAVINGは使うな(HAVINGはindex使わず)
