Pagination(ページネーション)がずれる、抜ける問題を解決する #SQL

Pagination実装のつらみ

大体の場合、Limit,Offsetをパラメーターに渡す事で実現してるケースが多いと思います。
ただPaging実装は突き詰めるといろいろ課題や困ったポイントがあるので、結構シンプルだけど悩みますよね。

今回はそんなつらみポイントと解決するオシャレなページネーションについて書いていきます。

e.g

以下のようなテーブルがあったとして、

mysql > desc sample;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| id              | bigint(20) unsigned | NO   | PRI | NULL    |       |
| name            | varchar(20)         | NO   |     | NULL    |       |
| created_at      | datetime            | NO   |     | NULL    |       |
| updated_at      | datetime            | NO   |     | NULL    |       |

データは1~100のidが途中欠損なく入っているとする。

sampleテーブルに以下のシーケンスで処理を行う

1page目(limit 10 offset 0)で取得する SELECT id from sample ORDER BY ID limit 10 offset 0

1,2,3,4,5,6,7,8,9,10

idが1~5のデータを削除する DELETE FROM sample WHERE id <= 5
2page目(limit 10 offset 10)で取得する

SELECT id FROM sample ORDER BY ID limit 10 offset 10

15,16,17,18,19,20,21,22,23,24

このケースの問題点

paging途中のレコード削除の件数分、本来閲覧されるべきデータ(id11~14)が歯抜けで飛ばされる。
paging途中でレコード追加の場合は、追加件数分が前のページと重複する。

いろいろ悲しいですよね。
ユーザー体感としても、うーん...って感じで辛い。

解決策 (巨人の肩に乗る)

facebookのAPIインターフェースを参考に考えてみましょう。

timeベースのページネーション (facebook)

時間ベースでデータを検索する形式です。

facebookのpaginationの構成パラメーター

until:時間ベースのデータのレンジの終端
since:時間ベースのデータのレンジの先端
limit:返却可能性のある最大数

ポイント

offsetでスタートの絶対位置を指定していないので、削除や追加によるズレや重複が発生しない。
最新から遡るケースであれば、最初の検索では、untilに現在時刻を入れる。
以降は終端レコードの値をuntilに入れて遡っていきます。

1回目の検索レコードのcreated_atの終端が"2017-02-17 01:41:28"
select id,created_at from sample where created_at > "2017-02-17 01:41:28" order by created_at limit 5

ただし、sinceで使う値がunique保証できない場合、終端レコードと同じcreated_atを持つと歯抜けが出る。


mysql> select id,created_at from sample order by created_at desc ,id asc limit 10;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  9 | 2017-04-05 02:46:50 |
|  8 | 2017-02-17 03:07:54 |
|  3 | 2017-02-15 06:26:53 |
|  4 | 2017-02-15 06:26:53 |
|  5 | 2017-02-15 06:26:53 |
|  6 | 2017-02-15 06:26:53 |
|  7 | 2017-02-15 06:26:53 |
|  2 | 2017-02-07 09:16:45 |
|  1 | 2017-02-07 09:12:20 |
+----+---------------------+

mysql> select id,created_at from sample where created_at < "2017-04-05 06:26:53" order by created_at desc,id asc limit 3;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  9 | 2017-04-05 02:46:50 |
|  8 | 2017-02-17 03:07:54 |
|  3 | 2017-02-15 06:26:53 |
+----+---------------------+
3 rows in set (0.00 sec)

mysql> select id,created_at from sample where created_at < "2017-02-15 06:26:53" order by created_at desc,id asc limit 3;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  2 | 2017-02-07 09:16:45 |
|  1 | 2017-02-07 09:12:20 |
+----+---------------------+
2 rows in set (0.00 sec)

id4~7が歯抜けになってます。

untilに用いる値が十分なunique性を担保できる場合は以下は不要です。
確実な正確性が必須でなければ、sinceをnano,microレベルとかにしておけば現実レベルではって気もしますが。

1回目の終端レコード
created_at:"2017-02-1 06:26:53"
id:3

select 
    id,
    created_at
from 
    sample
where 
    created_at <= "2017-02-1 06:26:53"
and
    id != 3
order by 
    created_at desc,id asc
limit 10

uniquekeyである idの大小で順序を保証できるなら
id > 3でも十分表現できたりはします。
このクエリだと
否定演算子を使っているので、速度的にはむむむ... ですが。

ちなみに
twitter API

まとめ

ページネーションは用途にも依ると思いますが、割と１度は考えさせられる問題だなぁ..と。
有名なサービスはしっかりこうした細かな所を対応しているので、参考になります。
構造のアプローチを少し変えるだけでシンプルなまま解決できるのは大事ですね :)

性質上、戻るを実装するタイプのUIのページングは大変だなぁ、って気もしますが、
一方向の場合は、こちらの方が優位かな、と。

参考

facebook api
twitter api