ページング関連のクエリとUI設計【MySQL】
Limit Offsetは遅い
MySQLを用いてページネーションを作成する際、はじめに遭遇するのが「Limit Offset」構文かもしれません。 MySQLに用意されている、ページングを実現するには便利な機能です。
SELECT * FROM tableA LIMIT 10 OFFSET 250
OFFSETに指定された数字の次の行からLIMITに指定された数だけ抽出します。
しかし大量件数となると、この方法では問題があります。 ソートされていない場合はフルスキャンになります。ソートされている場合でも、OFFSETに指定された行まで捜査して廃棄するため、その件数が多くなるほどコストがかかります。 パフォーマンス以外にも、抽出データがその時々によって異なる可能性があります。再度クエリを実行するまでの間に、データが追加されたり削除された場合です。 したがって、次のようなクエリが推奨されます。
SELECT * FROM tableA WHERE id < 10 ORDER BY id DESC LIMIT 10
（ORDER BY id ASCの場合はid > 10）
この場合、WHERE句で指定された目的の行まで直行するため速くなります。また、連番のidで指定する場合、最後尾以外ならば抽出データが異なることはありません。
ただし、WHERE句に指定されるカラムにはPRIMARY_KEYが設定されているかindexが貼られていることが条件になります。また「抜け」のない連番でなくてはなりません。
ページネーションをどうするか
次のようなページネーションは、最大ページ数を算出する必要があるため、別テーブルでレコード数を保存していない場合で大量件数の場合には、大きなコストがかかるでしょう。
https://promonista.com/wp-content/uploads/2017/06/pagination3.png
最大ページ数を表示せず、現在のページを中心に前後のページ番号の表示数を抑えることで、クエリのパフォーマンス悪化を抑えることができるでしょう。
前後のページ番号を表示するためには、そのページが存在すること、つまりデータ件数が現在のページで表示されるデータから前後に、少なくとも1件以上あることが必要です。先ほどのWHERE句を使ったデータ抽出を応用することで、ページが存在する確証を得ることができます。
1ページ10件とした場合に、LIMITで11件を指定し、データが11件取得できたら、次のページが存在するとわかります。
SELECT * FROM tableA WHERE id < 10 ORDER BY id DESC LIMIT 11
前にもページがあるかを確かめる場合には、次のようにUNIONを使って、前にもあるかを確かめるクエリを合成します。
(SELECT * FROM tableA WHERE id <= 10 ORDER BY id DESC LIMIT 1) UNION (SELECT * FROM tableA WHERE id > 10 ORDER BY id DESC LIMIT 11)
１つ目のクエリが、あるページの最初のレコードの直前にレコードがあるかを確認する（つまり前にページが存在するか）クエリです。２つ目のクエリが、あるページのレコード件数 + 1件までを抽出対象とすることで、対象を全件取得できた場合には次のページが存在することを確認できるクエリになります。
+-1の部分を、1ページあたりの件数に合わせて拡大していくことで、存在を確認できるページの数を増やすことができます。
このようにしてページネーションのクエリを工夫することができますが、そもそもGoogleやECサイトのようなページネーションが必要かどうかを設計段階でよく検討してみるのも良いでしょう。
YappoLogs: なぜ SQL_CALC_FOUND_ROWS や LIMIT OFFSET のページングが良く無いのか
MySQLのlimitで大量データのパフォーマンスが遅い重い！
