質問: 1 and spot_id > 100000 order by spot_id asc limit 10; ``` Googleの検索結果のように、「前へ 1 2 3 4 5 6 次へ」のような機能を実現するのであれば、問題は、spot_idが連番になっていない(まばら)ため、各ページのURLのパラメタにLastIDを持たすことは難しいのでDBをフルスキャンするLimit Offsetを利用するしかないのでは?と考えてしまっています。 こちら、どうにか Googleの検索結果のように、「前へ 1 2 3 4 5 6 次へ」のような施策を高パフォーマンスを維持したまま実現するアイディアはありませんか?
回答: それほど大規模なデータを扱ったことはないのですが(数百万件*数百列くらい)、3点ほど思いつきました。 ### 1. Limit Offsetの部分を別クエリにする 実際のテーブル構造にもよるとは思いますが ``` select * from spots inner join ( select spot_id from spots where spot_category_id = 1 order by spot_id asc limit 100000, 10 ) s on spots.spot_id = s.spot_id ``` として先に重たいLimit Offsetをインデックス列で行っておくと若干の改善は可能かもしれません。この場合は`spot_category_id`列にもnon_uniqueなインデックスがあると良いです。 ### 2. データの更新処理時に順序も更新する もしLimit Offsetを絶対に避けるのでしたら、`spot_id`列を更新毎に振り直すか、新しい列を追加して順番を管理するといった荒業も考えられます。(歯抜けがそれほど起こらない場合には有効) ### 3. KVSなどを併用して、ページに対応した開始位置をあらかじめ保持しておく `where`句の条件で考えられる全パターンを網羅してもそれほど条件の組み合わせ数が多くない場合は、別の方法もあります。(多くても管理が煩雑になるだけで、不可能ではありません。) 別のテーブルあるいはキーバリューストア型DB(以下、KVS)で、ページング位置に該当する`spot_id`をあらかじめ計算して格納しておくという方法です。 この利点は、`spot_id`に歯抜けが起こった場合も、歯抜けした値より後ろの値を保持しているデータに対してだけ1を引けばいいので、更新処理時間がそれほどかからないということです。(KVSによっては数値のインクリメント/デクリメントに関して原子性を保証してくれる製品もあります。たとえば`Redis`など)