SQLのoffsetについてまとめています。
SQLのoffsetは、レコード取得の開始位置を指定する
offsetは、検索結果数が大きい場合などレコード取得開始位置を指定するSQLコードです。
以下は、PostgreSQLでサンプルデータベース（PostgreSQL Sample Database）を使った例です。１つ目のSQLでtitle（題名）テーブルから２０件取得、２つ目のSQLでtitleテーブルからoffsetで１０件読み飛ばして２０件取得しています。
- dvdrental=# select title from film limit 20;
- title
- -------------------
- Chamber Italian
- Grosse Wonderful
- Airport Pollock
- Bright Encounters
- Academy Dinosaur
- Ace Goldfinger
- Adaptation Holes
- Affair Prejudice
- African Egg
- Agent Truman
- Airplane Sierra
- Alabama Devil
- Aladdin Calendar
- Alamo Videotape
- Alaska Phantom
- Date Speed
- Ali Forever
- Alice Fantasia
- Alien Center
- Alley Evolution
- (20 rows)
- dvdrental=# select title from film limit 20 offset 10;
- title
- ----------------------
- Airplane Sierra
- Alabama Devil
- Aladdin Calendar
- Alamo Videotape
- Alaska Phantom
- Date Speed
- Ali Forever
- Alice Fantasia
- Alien Center
- Alley Evolution
- Alone Trip
- Alter Victory
- Amadeus Holy
- Amelie Hellfighters
- American Circus
- Amistad Midsummer
- Anaconda Confessions
- Analyze Hoosiers
- Angels Life
- Annie Identity
- (20 rows)
MySQLのoffset指定は、LIMITの引数を2つ指定することで可能
MySQLではoffset句が実装されていません。機能としてはlimit句の一部として実装されています。例えば、limit 10,5と指定するとoffsetとして10件分読み飛ばし、5件を取得する指定になります。
- mysql> SELECT * FROM employees
- LIMIT 10,5;
- +--------+------------+------------+-----------+--------+------------+
- | emp_no | birth_date | first_name | last_name | gender | hire_date |
- +--------+------------+------------+-----------+--------+------------+
- | 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
- | 10012 | 1960-10-04 | Patricio | Bridgland | M | 1992-12-18 |
- | 10013 | 1963-06-07 | Eberhardt | Terkki | M | 1985-10-20 |
- | 10014 | 1956-02-12 | Berni | Genin | M | 1987-03-11 |
- | 10015 | 1959-08-19 | Guoxiang | Nooteboom | M | 1987-07-02 |
- +--------+------------+------------+-----------+--------+------------+
- 5 rows in set (0.02 sec)
【関連記事】
▶MySQLのlimitのサンプルコード集 offset指定や効率的な件数取得方法は？
offsetを使うとどのくらい遅くなるかexplain analyzeで確認する
大量データが格納されているテーブルに対して、offsetを使ったselectを実行すると、値が大きいほどクエリからレスポンスが返ってくるのに時間がかかります。
どのくらい差があるのでしょうか？16,044件のデータが入っているrentalテーブルで試してみました。order byによるソートにindexが利用できるとは言え、それなりの差が出そうです。rentalテーブルは、postgresql用のサンプルデータベースです。
実行時間の比較は、explain analyzeを使います。
- dvdrental=# explain select * from rental order by rental_date limit 10;
- QUERY PLAN
- -------------------------------------------------------------------------------------------------------------------------------
- Limit (cost=0.29..0.81 rows=10 width=36)
- -> Index Scan using idx_unq_rental_rental_date_inventory_id_customer_id on rental (cost=0.29..846.62 rows=16044 width=36)
- (2 rows)
- dvdrental=# explain analyze select * from rental order by rental_date limit 10;
- QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------
- Limit (cost=0.29..0.81 rows=10 width=36) (actual time=0.017..0.023 rows=10 loops=1)
- -> Index Scan using idx_unq_rental_rental_date_inventory_id_customer_id on rental (cost=0.29..846.62 rows=16044 width=36) (actual t
- ime=0.016..0.021 rows=10 loops=1)
- Planning time: 0.100 ms
- Execution time: 0.071 ms
- (4 rows)
- dvdrental=# explain analyze select * from rental order by rental_date limit 10 offset 16000;
- QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------------------------------
- -------------------------------------
- Limit (cost=844.30..844.83 rows=10 width=36) (actual time=3.532..3.535 rows=10 loops=1)
- -> Index Scan using idx_unq_rental_rental_date_inventory_id_customer_id on rental (cost=0.29..846.62 rows=16044 width=36) (actual t
- ime=0.014..2.887 rows=16010 loops=1)
- Planning time: 0.179 ms
- Execution time: 3.556 ms
- (4 rows)
offsetなしのSQLの実行時間（Execution time）が0.071msなのに比べて、offset 16000を指定したSQLが3.556msとなってます。約50倍の差が出ています。
もし、offsetなしのSQLが0.1秒かかるとしたら、offset 16000のSQLは5秒かかる計算です。
ページングなどで、1回のクエリ実行時間を小さくする必要がある場合は、ページング用のidなどをカラムとして設定するなど高速化の工夫をする必要があります。
参考）OFFSETを使わない高速なページネーションの実現 – Qiita
まとめ
- offsetは、指定した数だけ検索結果を読み飛ばすためのSQLコード
- MySQLなど、offsetはlimitの一部機能として実装されているケースもある
- offsetは指定した数が大きくなるほどパフォーマンスが劣化するため、explain analyzeなどでクエリ実行にかかるコストを確認する必要あり
