mysqlのindexとorder by の速度を改善する。
mysqlを利用していて、indexをちゃんと張っているのにパフォーマンスが出ない。
explain でも type = ref / key = INDEX 等が表示されているのにすごくクエリーが遅かったりする。
思い切って index を消したら逆にパフォーマンスが改善した! why?
データ件数が数万件を越えたあたりからパフォーマンスが劇的に下がった。
と、悩んでいたりしませんか?
そんな悩みのひとつの解決策になってくれるかもしれません。
パフォーマンス測定の前提条件
- クエリーをキャッシュされないように、クエリキャッシュを 0 にします。
/etc/mysql/my.cnf query_cache_size = 0 #no cahce debug
- 負荷計測に使っているテーブル
create table task( task_key int auto_increment NOT NULL PRIMARY KEY -- 主キー , task_name text -- 名前 , task_info text -- 説明 , task_starttime datetime -- 開始日時 , task_endtime datetime -- 終了日時 , task_lat double -- 緯度 , task_lon double -- 経度 , task_rating int -- 評価 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
データはランダムに10万件追加します。
indexは説明に応じて張ります。
とりあえずはなしということにします。
恐怖 index を張れば張るほど遅くなるクエリー
index から実体へのシークは mysqlの速度を大幅に下げることで有名です。
事実、indexから実体に対して30%のアクセスがある場合はオプティマイザがindexを利用するのをやめるぐらいです。
シークってそんなに遅いものなのか?って思いますが、とりあえずは受け入れることにします。
MySQL では利用可能な場合でもインデックスが使用されない場合があることに注意してください。この一例として、インデックスの使用によって、MySQL がテーブルの 30% を超えるレコードにアクセスする必要が生じる場合が挙げられます（この場合は、必要なシークが大幅に減少するため、テーブルスキャンのほうが高速になる可能性が高くなります）。 ただしこのクエリに、レコードの一部のみを取り出す LIMITが使用されている場合、結果で返される少数のレコードを迅速に検索できるため、MySQL はインデックスを使用します。
http://dev.mysql.com/doc/refman/5.1/ja/mysql-indexes.html
と、書いてありますが、いろいろ試したんですが、limit があっても、where order by 等があればパフォーマンスは下がるみたいです。
A. indexなしの状態で検索してみます。
select SQL_CALC_FOUND_ROWS * from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' limit 0,1; 1 row in set (0.42 sec)
explain
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | task | ALL | NULL | NULL | NULL | NULL | 99983 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
0.42 sec ということで平凡な結果です。
indexをまったく利用していない状態(key = NULL) でデータベース全件スキャンが実行されています。
INDEXを張って更なる高速化を目指します。(*ﾟ∀ﾟ)=3ﾑﾊｰ
B.
indexを張ります。
create index index_task_time ON `task`(task_starttime,task_endtime);
C.
indexを張ったので早速使ってみましょう。
select SQL_CALC_FOUND_ROWS * from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' limit 0,1; 1 row in set (4.08 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 | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+-------------+
4.08 sec って、ずぎゃっああ 速度が劣化した!!
key=index_task_time なんでばっちりindexが使われているのに。
| indexなし | 0.42 sec |
| indexあり | 4.08 sec |
では、こういう状態では index はいらない子なんでしょうか?
index の恩恵を受けることはできないんでしょうか?
方法はあります。
D.
このクエリーだけを実行してみます。
select SQL_CALC_FOUND_ROWS task_key from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' limit 0,1; 1 row in set (0.04 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 | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
0.04 secで劇早です。
select * から select task.task_key に変更しました。
task.task_key は、 task テーブルのプライマリキーです。
このクエリーにはindexに一致する検索しか行われていないため、 explain の Extra で Using index の称号をゲットできました。
ただこれでは、プライマリーキーしか select していないのであんまり実際には利用価値はありません。
そこで別の方法を考えます。
order by を手なづける
order by が信じられないぐらい遅いときがあります。
次の例を見てください。
A.
まず、ソートなしでクエリーを実行します。
select SQL_CALC_FOUND_ROWS task_name from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' limit 0,1; 1 row in set (3.93 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 | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+-------------+
B.
order by をつけます。ソートするのは、 index にも登録されている task.task_starttime です。
select SQL_CALC_FOUND_ROWS task_name from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' order by task.task_starttime limit 0,1; 1 row in set (8.37 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 | +----+-------------+-------+-------+-----------------+-----------------+---------+------+--------+-------------+
| ソートあり | 1 row in set (3.93 sec) |
| ソートなし | 1 row in set (8.37 sec) |
2倍以上の違いです。
そもそも、たった10万のソートするのに4秒とは少々遅すぎます。
いったいなぜでしょうか?
C.
クエリを変更します。 select task.task_name から プライマリーキー task.task_key に変更します。
select SQL_CALC_FOUND_ROWS task.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,1; 1 row in set (0.01 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 | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
爆早です。どうやらソートの有無に時間がかかったのではないようです。
これによってわかるのは、 order by が実行されるときに、 select する表を作り上げているのではないかという疑問です。
where して order by したものを表示せよと命令しているわけですから、 where して order by して残った結果を select すればいいはずです。
この場合、 where も order by も index に格納されている値を利用しているのですから、where も order by も瞬間的に終わってもいいはずです。そして、残った行の中で必要なものだけを持ってきてもらえばいい。それは、 limit 1 だけから、1行にしかならないので、 index から 実体へのシークのペナルティもそんな高くはつかないだろうと思います。しかし、現実は違います。select にあげられたものをすべて結合して表を作成し、そこから、 where して order by しているように見えます。そして、その結果が 8.37 sec もかかってしまったと。
indexに入っていないものを select しながら、 order by してはいけないのです。
パフォーマンスを求めて
結局、わかったことは、 次の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 のようなものが導入されたら、 プライマリーキーの代わりに利用するともっと速度があがると思います。
以上。某所で発表した内容から、毒を抜いて抜いて抜きまくったバージョン。お子様にも安心です。
間違いがあったら教えてくらはい。
