読者です 読者をやめる 読者になる 読者になる

文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

offsetの罠:その1:少しだけ対策

mysql

続・罠シリーズ。

今回はoffsetについて紹介。
selectした結果の1000件目から(offset)100件取得(limit)という感じで、1000〜1100件を取得する
一見すると非常に効率が良さそうに見える。しかしここに罠が潜む・・

試しに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件を取得しているのではなく、全件取得した後、いらない部分を切り捨てているのだ。

もうちょっとレコード数増やしてもう一回試してみる。

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によってインデックスを使っているとはいえ、フルスキャンしてる。
offset が異常に遅いなと思ったらこれを疑いましょう。

私が実際やってしまった例として、solrのインデックス生成を効率化するため、
1000件selectして1000件インデックス生成、次の1000件をselectして・・・以下略、
という処理をlimit offsetで繰り返して小刻みにインデックス生成していた。
ふとslow-query-logを見ると、大量にログが出ていた。

では解決策。
綺麗な解決策ではないが、アプリ側で1000件づつ取得するように書いてあげればいい。
betweenを使えばindexが使われるので、それを利用する。
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)

よし、問題無い事がわかったので、betwennを使ってこの問題を回避してみよう。
まず、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のオフセット値を増やしていけばいい。以下はjavas2jdbcの例。

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("インデックス生成完了")

非常に面倒な方法だが、とりあえずこれで回避できる。
最初に生成したt_test_tmpはtemporary tableを使っているので、このトランザクションが終了すると同時に自動的に消える。
実際にレプリケーション環境下でも特に問題無く運用できています。

もっと簡単な方法があれば教えて下さい。。

環境:MySQL5.1.45、MacPRO、MacOSX10.6、CPU:Xeon2.8G X 4、MEM:4G

実践ハイパフォーマンスMySQL 第2版

実践ハイパフォーマンスMySQL 第2版