続・罠シリーズです。
今回はoffsetについての紹介です。
selectした結果の1000件目から（offset）100件取得（limit）という感じで、1000〜1100件を取得する、一見すると非常に効率が良さそうに見えますね。
しかしここに罠が潜んでいるのです・・・！
普通にoffset limitで検索してみる
試しに10件のレコードから、以下のように2件レコードを取得してみます。ありきたりな奴です。
mysql> select * from t_test2 limit 2 offset 5; +------+------+ | id | name | +------+------+ | 6 | fff | | 7 | ggg | +------+------+ 2 rows in set (0.00 sec)
普通ですね。
実行計画
ではexplainして実行計画を見てみましょう。
mysql> explain select * from t_test2 limit 2 offset 5; +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_test2 | ALL | NULL | NULL | NULL | NULL | 10 | | +----+-------------+---------+------+---------------+------+---------+------+------+-------+
！？ あれ？rowsが10？？？
なんと、テーブルを全件捜査したという事じゃないですか！
5件目から2件を取得しているのではなく、全件取得した後、いらない部分を切り捨てているという事じゃないですか！
レコード数を増やしてoffset limitする
もしかしたらデータ数が少なすぎて、インデックスを使うより全件スキャンした方が速いと実行計画は判断した可能性があります。
という事で、もうちょっとレコード数増やしてそうならないようにしてみましょう。
mysql> select count(*) from t_test; +----------+ | count(*) | +----------+ | 317222 | +----------+ 1 row in set (0.00 sec) mysql> explain select * from t_test limit 2 offset 5; +----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | t_test | index | NULL | PRIMARY | 4 | NULL | 317222 | Using index | +----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+ 1 row in set (0.00 sec)
ふーむ。やっぱり全件スキャンですね。
PKによってインデックスを使っているとはいえ、フルスキャンしている事は事実のようです。
アプリケーションのページング処理が異様に遅いと感じた場合、恐らくこれが原因である場合がほとんどなので、注意しましょう。
解消法
私が実際やってしまった例として、solrのインデックス生成を効率化するため、1000件selectして1000件インデックス生成、次の1000件をselectして・・・以下略、という処理をlimit offsetで繰り返して小刻みにインデックス生成していました。
しかし、ふとslow-query-logを見ると、大量にログが出ていました。実は全件スキャンが都度発生していて、小刻みにSQLを発行していても効率は最悪なのでした。
では解決策です。
綺麗な解決策ではないですが、アプリ側で1000件づつ取得するように書いてあげればいいのです。
MySQLのbetweenを使えばindexが使われ全件スキャンも起きないので、それを利用するようにアプリケーションのコードを修正してあげればよいのです！
between版の実行計画
ちなみにbetweenの場合のexplainはこうなりました。
mysql> explain select * from t_test where id between 2 and 5; +----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | t_test | range | PRIMARY | PRIMARY | 4 | NULL | 5 | Using where; Using index | +----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
おおお！インデックスが有効、且つ、全件スキャンが起きていません！！
これでSQL上は問題無い事がわかったので、次はbetwennを使ってこの問題を回避するアプリケーションコードを考えてみます。
アプリケーション側でbetweenするようコードを修正
まず、t_test（大量データの入ったテーブル）のPKだけをもつtemporaryテーブルを生成します。
-- 一応あらかじめtemporaryテーブルを削除 mysql> drop temporary table if exists t_test_tmp; Query OK, 0 rows affected (0.00 sec) -- auto_incrementのカラムとPKのみを集めた t_test_tmp という名前のtemporary tableを生成。ここではMyISAMにします mysql> create temporary table t_test_tmp (seq bigint primary key auto_increment, id int not null) engine=myisam; Query OK, 0 rows affected (0.00 sec) -- PKのみを全てinsertする。insert対象がtemporaryなので非常に速いです mysql> insert into t_test_tmp select null, id from t_test; Query OK, 317222 rows affected (0.81 sec) Records: 317222 Duplicates: 0 Warnings: 0 -- データの中身を確認しておきます mysql> select * from t_test_tmp; +-----+----+ | seq | id | +-----+----+ | 1 | 0 | | 2 | 1 | ・・・ 省略 ・・・ | 317221 | 317220 | | 317222 | 317221 | +--------+--------+ 317222 rows in set (0.09 sec)
ここでのポイントは、auto_incrementのseqカラムを追加したことです。
seqは、drop table => create table => insert 、という手順を踏んでいるので、確実に1始まりの綺麗な連番になる事が保証されます。
つまり、betwenn seq 0 and 1000 とすれば、確実に1000件だけを取得できるという事になりますね。
seqを絞り込みの検索条件のためだけに使い、idとnameをselectする、これでどうでしょう。
-- ここでbetweenを使ってseqに対して0〜1000件を取得 mysql> select org.* from t_test_tmp tmp inner join t_test org on tmp.id = org.id where tmp.seq between 0 and 1000; +-----+-----------+ | id | name | +-----+-----------+ | 0 | 名前0 | | 1 | 名前1 | ・・・ 省略 ・・・ | 998 | 名前998 | | 999 | 名前999 | +-----+-----------+ 1000 rows in set (0.01 sec) -- explainで実行計画をチェック mysql> explain select org.* from t_test_tmp tmp inner join t_test org on tmp.id = org.id where tmp.seq between 0 and 1000; +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+ | 1 | SIMPLE | tmp | range | PRIMARY | PRIMARY | 8 | NULL | 1058 | Using where | | 1 | SIMPLE | org | eq_ref | PRIMARY | PRIMARY | 4 | test.tmp.id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+ 2 rows in set (0.00 sec)
キター！全件スキャンを発生させずにインデックスが有効な検索ができました！
後はアプリケーション側でseqのオフセット値を増やすコードを書けば大丈夫ですね。
s2jdbcの例
以下はjava・s2jdbcの例です。
final int LIMIT = 1000; final int COUNT_INTERVAL = 10000; int count = 0; int offset = 0; while(true) { List<TableName> entityList = jdbcManager.selectBySql(TableName.class, "select org.* from t_test_tmp tmp inner join t_test org on tmp.id = org.id where tmp.seq between ? and ?", offset, (offset + LIMIT)).getResultList(); if (entityList == null) { break; } for (TableName tableName : entityList) { // ここでsolrのインデックス生成とか count++; if (0 < count && count % INTERVAL == 0) { System.out.println("インデックス" + count + "件生成") } } offset += limit; } System.out.println("インデックス" + count + "件生成") System.out.println("インデックス生成完了")
雑感
非常に面倒な方法ですが、超絶遅いoffset limit地獄はこれで回避できます。
最初に生成したt_test_tmpはtemporary tableを使っているので、このトランザクションが終了すると同時に自動的に消えるので、変なゴミが残る心配もありません。
私は実際この手法で実装し、レプリケーション環境下でも特に問題無く運用できています。
本当はもっと簡単な方法があるような気がしてモヤモヤしているので、もしもっと簡単な方法があれば、是非教えて下さい！
