MySQLのチューニングについて

MySQLのSELECT時にORDER　BYを使用した時のソートの話

テーブルにINDEXが貼られている事が前提ですが、
例えば３テーブルを結合し、ソートをかける時などに、
全てのテーブルの結合を行った後にマスターとなるテーブルで並び替えると、
Using filesortが発生し、SELECTの実行が遅くなる場合があります。

回避方法として、カラムの表示用のマスターテーブルと、ソート用に使うマスターテーブル(別名付け)を用意し
用途を分けたSELECT文にするなどがあります。

#sample.sql

SELECT
　　tbl1.col1,
　　tbl2.col2,
　　tbl3.col3
FROM
　　tbl1,tbl2,tbl3
INNER JOIN
　　tbl1 AS tbl4 ON tbl1.col1 = tbl4.col1
WHERE
　　tbl1.col1 = tbl2.col1
AND
　　tbl2.col1 = tbl3.col1
ORDER BY tbl4.col1;

LIMIT句とORDERBYについて

次のようなクエリにて

SELECT col1, col2 ... colx FROM tbl1 WHERE ...略... ORDER BY col1 LIMIT 10;

col1のINDEXが有効でない場合、tbl1の全データをソートした後に
Limitの10行だけを返すような挙動になります。

col1にINDEXが有効な場合は、tbl1のデータ件数に関わらずに
Limitの10行だけを返却する為、全データソートは行われません。

#WHERE句とソートについて
複数テーブル結合のソートを行う場合は、
ORDER BY句にてINDEXが貼られたカラムをWHERE句で使用しない事（または同名テーブルとして分けるなど）を意識するとクエリ速度が上がる場合があります。

上記の例sample.sqlなどではtbl1のcol1が主キーだった場合、
ORDER BYではtbl4(tbl1の別名テーブル)を指定し、WHERE句での絞り込みと
ソート時の使用カラムを分けるなどを行っています。

#IN句、NOT IN句について
サブクエリの問い合わせとして、INを使用している場合限定ですが、
IN句をEXISTSにて表現するとINDEXが有効のままサブクエリへ問い合わせが通ります。
（早くなる可能性がある。）
ただし、サブクエリでなくIN句の中を指定している場合（...IN ('A','B')等）は
EXISTSにすると逆に遅くなったります。

また、サブクエリのIN句の表現はEXISTSに置き換える事ができますが、
NOT IN句は必ずしもNOT EXISTSに置き換える事は出来ません。

#実行計画について
大体のクエリツールなどで実行計画を見る時、
SQLの頭にEXPLAIN を付けるとそのクエリの解析が行われます。

チューニング時に着目しやすい部分として、typeフィールドがあります。
typeフィールドは対象テーブルにどのようにアクセスしているかを
現す項目で、遅いクエリを解析するとALLや、index(カラムINDEXではない)などで
何十万レコードを持つテーブルにアクセスしていたりします。

フィールド内容の種類は

・const・・・PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。
・eq_ref・・・JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。
　constと似ているがJOINで用いられるところが違う。
・ref・・・ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索
　（WHERE key = value）を行った時に使われるアクセスタイプ。
・range・・・インデックスを用いた範囲検索。
・index・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
・ALL・・・フルテーブルスキャン。インデックスがまったく利用されていないことを示す。

などがあります。

実行計画は、表示項目の上から順に絞り込みを行なっていきます。

例：）
第１：実行計画 range・・・1000rows
第２：実行計画 const・・・300rows
第３：実行計画 range・・・20rows

第1→第2→第3の順に該当レコードの絞り込みを行うので、
同様の順序で上からレコードの絞り込みが徐々に減っているようなクエリが方が速いと言えます。

#まとめの様なもの
・SELECTでマスターに対して複数結合をして、ORDER　BYにてマスターカラムを指定する場合
同名をつけてWHERE句などで使用しているマスターのカラムと、ORDER　BY句で使用するカラムを分けると
早くなる時がある。

・LIMITとORDER　BYを使用する場合はORDER　BY句の指定カラムのINDEXが有効かを意識する
(ORDER　BYにて複数カラム指定されていた場合は、全部のINDEXが有効でないとダメだったかも・・・？)

・サブクエリ問い合わせでIN、NOT IN句を使用している場合はEXISTSに置き換えるとINDEXが有効になる事がある。
(サブクエリでない場合はIN句の方が速い)

・実行計画は上から順に解析され、const（最速）>eq_ref>・・・・ALL(最低)の順となっている

#参考URL
漢のコンピューター道様：http://nippondanji.blogspot.jp/2008/12/2008.html