だるろぐ

2011-04-09

limit/offsetについて考える

| 02:23 | limit/offsetについて考えるを含むブックマーク

LIMIT 20 OFFSET (:page - 1) * 20 みたいなクエリは :page に大きい値が入れれるように設計されてるとクエリに殺されるので、 WHERE key = :offset_for_next_page LIMIT 20 なクエリになるよう設計してほしい。

http://twitter.com/kamipo/status/56304601049210880

俺もボスに教わるまで知らなかったのだが、

mysql> select id from mentions order by id asc limit 100, 10;

がすることは、

  • データを10個だけfetchする

ではなく、

  • 110個データをfetchして、先頭から100個捨てる

だ。何を今更って感じですよねー知ったのは10ヶ月ほど前でした。俺の未熟さを思い知れ。


で。このようにlimitを付けてデータを取得するときというのは、大抵データが多すぎるから小分けにして取得したいとき。

webアプリだと、その小分けにして取得した結果を1ページ目、2ページ目…として表示する。検索エンジンの検索結果ページのように。

んで当然そういう場合って、見ているページから 次のページ or 前のページ に移動したい。どうするのというと、えーと例えばデータを降順で10件ずつ取得するとすると、

  • 検索条件を作成する。例えば where status_id > 100000000000 のように
  • 検索条件に合うデータのidを limit 1 を使って1件だけ取得する(record_aとでもする)
    • record_a のidは 1871 とでもしておこう
mysql> select id from mentions where status_id > 100000000000 limit 1;
  • 検索条件に、「record_aのidよりidが大きい」を加え、limit句の値は10にする
mysql> select * from mentions where status_id > 100000000000 and id >= 1871 limit 10;

ここまでで欲しいデータが10件取得出来た。で、「次へ or 前へ」をするには、そもそもこの次のデータ、前のデータが存在するか分かればいい。ので、

  • この検索条件に該当するデータが、取得したデータの前後にそれぞれ存在か調べる。存在の有無が分かればいいので limit 1 で
    • さっき取得したデータのidの範囲は 1871 から 1880 だった、とする
mysql> select id from mentions where status_id > 100000000000 and id < 1871 limit 1;
mysql> select id from mentions where status_id > 100000000000 and id > 1880 limit 1;
  • 前者の結果が存在すれば「前へ」が表示できて、後者の結果が存在すれば「次へ」が表示できる
    • 「前へ」「次へ」のページで取得するデータについては、それぞれの存在を確認したクエリで select * と limit 10 とすればいい

とか。まぁ極力無駄は抑えたんじゃなかろうか。

尚実際にこういうことをしようとするともうちょっと工夫が要る。「次へ」を見ているときに、更にそのページにおける「次へ」にあたるデータの存在の確認と、「前へ」を見ているときに更にそのページにおける「前へ」にあたるデータの存在の確認の方法は違ってくる。簡単だから頑張れ。


あとこういうことをして「あー無駄なfetchを抑えて前後へのページングも付けたぜ」とか思ってると、

googleみたいに 1 2 3... ってして各ページへのリンクも付けて

とか言われると通用しない。しませんでした。俺の頭じゃ

  1. 検索条件に合うデータをcountして、その結果から計算する
  2. 検索条件に合うデータのidをfetchして、その結果から計算する

しか思い付かなかった上に面倒だったので冒頭で挙げたやつに戻った。

ちゃんと考えたら案外あっさり出てきそうな気もするけど考える気がない。


尚こういう考え方もある。状況に応じて好きにしろい。

http://twitter.com/kamipo/status/56390988075237376

http://twitter.com/kamipo/status/56391325649608704

俺の場合は正確性が求められる上に、利用される個所が負荷にならない程度に少なかったので冒頭のでもよかった。

飽きて疲れて眠いので終わる。

通りすがり通りすがり 2012/02/07 16:55 >110個データをfetchして、先頭から100個捨てる

何を言っているんですか?

shinoutshinout 2012/02/09 08:35 >110個データをfetchして、先頭から100個捨てる

複雑なクエリだとその都度orderを計算しますから基本的にはそうなると思います.

> select id from mentions order by id asc limit 100, 10;

この例ではidはおそらくプライマリキーで適切にインデックスが貼られていると思うので、ちょうど10件だけfetchできるとおもいます。
以下は私のイメージ.

書類を渡されて「101ページから110ページまでを抜き出して」と言われたら101ページを頭だしして(indexを利用)瞬時にできる. これは「1ページから10ページまでを抜き出して」という労力と等価です。

しかし「「経理部」と書いてあるページの11枚目から20枚目を抜き出して」と言われたら無理です。
ぱっとページを抜き出して「経理部」が存在してもそれが何番目かわからないから結局1番目からみていかないといけません。だから1枚目から10枚目を抜き出すよりも時間のかかる処理です.
つまりwhere句で条件を指定したとたんにorder byは効かなくなるからボスの言っているとおりになるわけです。

しかしその書類の末尾に索引がついていたら
「経理部 ... 2,5,14,17,...」
などと書いているので、そこの11~20をもってくることは瞬時にfetch可能.

よって適切にインデックスを貼ることによってorder byのコストは抑えられます.

トラックバック - http://d.hatena.ne.jp/hirafoo/20110409/1302283428
リンク元

事務用品 名刺 デザイン