MySQLを利用したページングを実装しています。

大規模なデータを利用しているためフルスキャン対策として、Limit
Offsetを利用したページングは避け、Where句、Between句を利用したページングでパフォーマンスを担保しています。

<http://blog.yappo.jp/yappo/archives/000835.html>

しかし1点問題が出ました。
上記のサイトでも言及してある通り、次のページ、前のページのような施策には前ページレコードのLastIDを用いれば高パフォーマンスを維持しながらのページングが可能なのですが、Googleの検索結果のように、
「前へ 1 2 3 4 5 6 次へ」みたいなリッチなページングの実装を高パフォーマンスで行うアイディアが浮かばないのです。

現在の状況と実現したい施策は下記の通りです。

■Spotsテーブルの内容例

```

    spot_id | name | spot_category_id
          1 | hoge |                1
          2 | hugo |                2
          3 | huga |                1
    ・・・・
     100000 | hego |                1
    
```

まず、上記のテーブルがあったとして、 spot_category_idの値を10件取得します。

```

    select * from spots where spot_category_id = 1 order by spot_id asc limit 10;
    
```

この時、例えば下記の値が10件、返却されたとします。

```

    spot_id | name | spot_category_id
          1 | hoge |                1
          3 | huga |                1
    ・・・・
        100 | huge |                1
     100000 | hego |                1
    
```

次のページの10件を高パフォーマンスで取得しようと考えれば、アプリケーションからLastIDを受け取り、それを利用して下記のSQLを発行すればいいと思います。

```

    select * from spots where spot_category_id = 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
次へ」のような施策を高パフォーマンスを維持したまま実現するアイディアはありませんか？
CC BY-SA 3.0
2014-12-21T09:54:18.827
2319
2015-02-20T06:27:35.500
5422
question
mysql
大規模データを利用したMySQLページングの目次生成処理について
それほど大規模なデータを扱ったことはないのですが(数百万件*数百列くらい)、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`など)
CC BY-SA 3.0
2014-12-21T11:56:51.523
2325
2014-12-21T11:56:51.523
3313
2319
answer