フレームワーク開発時代のSQLチューニング基礎（２）SQLのやっちゃだめ前編 について確認してみた の続き

フレームワーク開発時代のSQLチューニング基礎（２）SQLのやっちゃだめ前編　について確認してみた

の続きです。

GROUP BYやORDER BYにインデックスを使う。

に対して突っ込もうと思ったら、ほんとにINDEXを使っててあれれ？
となっていたのですが、ふと思いついてもう一度検証して見ました。

まずサンプルの表構造

mysql> desc tab1;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| col1  | int(11) | NO   | PRI | NULL    | auto_increment |
| col2  | int(11) | YES  |     | NULL    |                |
| col3  | int(11) | YES  |     | NULL    |                |
| col4  | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

mysql> show indexes from tab1;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tab1  |          0 | PRIMARY       |            1 | col1        | A         |     1000245 |     NULL | NULL   |      | BTREE      |         |
| tab1  |          1 | tab1_123_idx  |            1 | col1        | A         |     1000245 |     NULL | NULL   |      | BTREE      |         |
| tab1  |          1 | tab1_123_idx  |            2 | col2        | A         |     1000245 |     NULL | NULL   | YES  | BTREE      |         |
| tab1  |          1 | tab1_123_idx  |            3 | col3        | A         |     1000245 |     NULL | NULL   | YES  | BTREE      |         |
| tab1  |          1 | tab1_col4_idx |            1 | col4        | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.07 sec)

col1 のINDEXはPRIMARYになっています。
それと、col1, col2, col3のコンポジットINDEX、およびcol4のINDEXがある状態です。
表の件数は100万程で、col1をカウントアップしながらINSERTしたので、内部の行データはcol1の順で物理的にソートされているはずです。



この状態でORDER BY col1をやってみます。

mysql> explain select col4, max(col1) from tab1 group by col1;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | tab1  | index | NULL          | PRIMARY | 4       | NULL | 1000245 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------+
1 row in set (0.00 sec)

うん。やっぱりINDEXフルスキャンが発生しています。

では次。

mysql> explain select * from tab1 order by col4;
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL | 1000386 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.01 sec)

こんどは表のフルスキャンになり、INDEXは使ってません。
はーはー。なるほど。
ちょっとわかった気がしますが、確認のためにもう１つ。

mysql> explain select * from tab1 order by col1,col2,col3;
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | tab1  | ALL  | NULL          | NULL | NULL    | NULL | 1000386 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.01 sec)

うん。やっぱりINDEXはつかいません。


原因はクラスタINDEXのせいです。多分。
InnoDBのPRIMARY INDEXは、クラスタINDEXで作成されます。
クラスタINDEXは、簡単にいえば、そのキーの順で表の行も格納しようとするINDEXです。
つまり、col1のINDEXがPRIMARYなら、表の行データも物理的にcol1の順でソート済みの状態になっているわけです。
なおかつ、このINDEXをたどるとリーフページには、行データそのもが格納されているため、INDEXを見てから表へアクセスという2つのステップを踏まなくてよいことになります。

参考：知って得するInnoDBセカンダリインデックス活用術！

つまり、col1でソートした結果が欲しいなら、col1のクラスタINDEXの最小値まで一旦辿って、そこから次のリーフ、次のリーフとINDEXフルスキャンをすると、必要な行データは全て得られ、かつ既にソート済みの状態で読み込まれるわけです。
なので、MySQLはcol1のINDEXを使ってくれたわけですね。


ところが、ORDER BY col1, col2, col3 とすると、INDEXを使わずに表のフルスキャンになってしまいます。
これは、col1だけの索引では、第二ソートキーのcol2、第三ソートキーのcol3までの並び順には対応できないですよね。
なので、次に候補にあがるINDEXはcol1+col2+col3のINDEXになるはずです。
しかに、こいつはクラスタINDEXではないため、INDEXを辿って行アドレスを取得したあと、改めて表へアクセスする必要があります。
しかもこれは、1行単位でINDEX→表→INDEX→表→・・・と繰り返すことになります。
当然、I/O回数が膨大になるので、それをやるくらいなら最初から表をフルスキャンで読み込み、あとでメモリ内でソートしたほうが速いとオプティマイザが判断した結果だと思われます。
もちろん、ORDER BY col4も同じ理屈です。


と、いうことで、MySQLであっても、ORDER BYのためだけにINDEXを使うとは限らない（というか使わない）ということになります。
WHEREの条件との組み合わによってソート処理がスキップできる場合がある、という程度に覚えておくとよいかも。


ソートとINDEXの関係については、マニュアルにちゃんと書いてあったので、こちらを参照してください。
http://dev.mysql.com/doc/refman/5.1/ja/order-by-optimization.html


そんなわけで、昨日のもやもやはこれで解決できました。
めでたし。