パフォーマンスを求めて - お前の血は何色だ!! 4

結局、わかったことは、次の4つ。

index から実体へのシークは遅い。
すべてがindex内で完結するクエリーは早い。
limit をつけても where や order by すると意味がない。
indexを張るなら Using indexe をゲットできないと負けかな。

では、select で取得する値すべてに index を張りますか? 場合によっては可能ですが、テーブルに文字列なんかがふんだんに含まれていると難しいものがあり、現実的ではありません。

そこでこんな方法を提案します。2段階にわけてクエリーを打ちます。

A.
task テーブルの 2008/6/5 〜 2008/6/18 のデータを開始日順にならべて、先頭5件だけ表示せよ。

select SQL_CALC_FOUND_ROWS * from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' order by task.task_starttime limit 0,5;
5 rows in set (8.73 sec)

explain

 +----+-------------+-------+-------+-----------------+-----------------+---------+------+--------+-------------+
 | id | select_type | table | type  | possible_keys   | key             | key_len | ref  | rows   | Extra       |
 +----+-------------+-------+-------+-----------------+-----------------+---------+------+--------+-------------+
 |  1 | SIMPLE      | task  | index | index_task_time | index_task_time | 18      | NULL | 100192 | Using where |
 +----+-------------+-------+-------+-----------------+-----------------+---------+------+--------+-------------+

当然ながら、8.73 secとクソのように遅いです。
このクエリーを高速化します。

B.
select * をプライマリーキーの select task_key に変更します。

select SQL_CALC_FOUND_ROWS task_key from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' order by task.task_starttime limit 0,5;
5 rows in set (0.03 sec)

explain

 +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
 | id | select_type | table | type  | possible_keys   | key             | key_len | ref  | rows  | Extra                    |
 +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
 |  1 | SIMPLE      | task  | range | index_task_time | index_task_time | 9       | NULL | 50096 | Using where; Using index |
 +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+--------------------------+

このクエリーの結果のようになりました。
 +----------+
 | task_key |
 +----------+
 |    26783 |
 |    61433 |
 |    88226 |
 |    92431 |
 |    95981 |
 +----------+

次にこの順番にデータを取得します。
この順番は order by された内容ですから、絶対に変更してはいけません。
この順番にデータを取得するにはどうすればいいんでしょうか?

3つの案があります。

1.
ひとつづつ取り出して php等でマージ。

select * from task where task_key = 26783

DBとの交信回数がもったいないので没

2.
UNION ALL でクエリーを結合する。

select * from task where task_key = 26783 UNION ALL select * from task where task_key = 61433 UNION ALL select * from task where task_key = 88226 ... 以下略

1より正解に近い。だけど、行数が長くなるのと、結局は毎回クエリーを発行しているのでmysqlにやさしくないので没。もうひとがんばり。

3.
http://q.hatena.ne.jp/1189526421 により、 order by で並び準が指定できるらしい。これを応用する

 select * from task where task_key in (26783,61433,88226,92431,95981) order by case when 26783 then 1 when 61433 then 2 when 88226 then 3 when 92431 then 4 when 95981 then 5 end;

ベストチョイス!(エスプレッソのダブルぐらいベストチョイス)

C.
3の方法で問い合わせをして見ます。

select * from task where task_key in (26783,61433,88226,92431,95981) order by case when 26783 then 1 when 61433 then 2 when 88226 then 3 when 92431 then 4 when 95981 then 5 end;
5 rows in set (0.00 sec)

explain

 +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
 | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
 +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
 |  1 | SIMPLE      | task  | range | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using where |
 +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

できました!!

select SQL_CALC_FOUND_ROWS task_key from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' order by task.task_starttime limit 0,5;
5 rows in set (0.03 sec)

select * from task where task_key in (26783,61433,88226,92431,95981) order by case when 26783 then 1 when 61433 then 2 when 88226 then 3 when 92431 then 4 when 95981 then 5 end;
5 rows in set (0.00 sec)

処理にかかった時間は、 0.03 sec + 0.00 sec = 0.03 sec と爆早です。

indexなし	1.02 sec
indexだけ	8.73 sec
2重クエリ	0.03 sec

差は歴然です。
mysqlを利用していてクエリーが遅くて悩んでいる人は試してみてはどうでしょうか?

余談ですが、mysqlに postgresql の oid のようなものが導入されたら、プライマリーキーの代わりに利用するともっと速度があがると思います。

以上。某所で発表した内容から、毒を抜いて抜いて抜きまくったバージョン。お子様にも安心です。
間違いがあったら教えてくらはい。