Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

SQLのoffsetについてまとめています。

SQLのoffsetは、レコード取得の開始位置を指定する

offsetは、検索結果数が大きい場合などレコード取得開始位置を指定するSQLコードです。

以下は、PostgreSQLでサンプルデータベース(PostgreSQL Sample Database)を使った例です。1つ目のSQLでtitle(題名)テーブルから20件取得、2つ目のSQLでtitleテーブルからoffsetで10件読み飛ばして20件取得しています。

  1. dvdrental=# select title from film limit 20;
  2. title
  3. -------------------
  4. Chamber Italian
  5. Grosse Wonderful
  6. Airport Pollock
  7. Bright Encounters
  8. Academy Dinosaur
  9. Ace Goldfinger
  10. Adaptation Holes
  11. Affair Prejudice
  12. African Egg
  13. Agent Truman
  14. Airplane Sierra
  15. Alabama Devil
  16. Aladdin Calendar
  17. Alamo Videotape
  18. Alaska Phantom
  19. Date Speed
  20. Ali Forever
  21. Alice Fantasia
  22. Alien Center
  23. Alley Evolution
  24. (20 rows)
  25.  
  26. dvdrental=# select title from film limit 20 offset 10;
  27. title
  28. ----------------------
  29. Airplane Sierra
  30. Alabama Devil
  31. Aladdin Calendar
  32. Alamo Videotape
  33. Alaska Phantom
  34. Date Speed
  35. Ali Forever
  36. Alice Fantasia
  37. Alien Center
  38. Alley Evolution
  39. Alone Trip
  40. Alter Victory
  41. Amadeus Holy
  42. Amelie Hellfighters
  43. American Circus
  44. Amistad Midsummer
  45. Anaconda Confessions
  46. Analyze Hoosiers
  47. Angels Life
  48. Annie Identity
  49. (20 rows)

MySQLのoffset指定は、LIMITの引数を2つ指定することで可能

MySQLではoffset句が実装されていません。機能としてはlimit句の一部として実装されています。例えば、limit 10,5と指定するとoffsetとして10件分読み飛ばし、5件を取得する指定になります。

  1. mysql> SELECT * FROM employees
  2. LIMIT 10,5;
  3.  
  4. +--------+------------+------------+-----------+--------+------------+
  5. | emp_no | birth_date | first_name | last_name | gender | hire_date |
  6. +--------+------------+------------+-----------+--------+------------+
  7. | 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
  8. | 10012 | 1960-10-04 | Patricio | Bridgland | M | 1992-12-18 |
  9. | 10013 | 1963-06-07 | Eberhardt | Terkki | M | 1985-10-20 |
  10. | 10014 | 1956-02-12 | Berni | Genin | M | 1987-03-11 |
  11. | 10015 | 1959-08-19 | Guoxiang | Nooteboom | M | 1987-07-02 |
  12. +--------+------------+------------+-----------+--------+------------+
  13. 5 rows in set (0.02 sec)

【関連記事】
MySQLのlimitのサンプルコード集 offset指定や効率的な件数取得方法は?

offsetを使うとどのくらい遅くなるかexplain analyzeで確認する

大量データが格納されているテーブルに対して、offsetを使ったselectを実行すると、値が大きいほどクエリからレスポンスが返ってくるのに時間がかかります。

どのくらい差があるのでしょうか?16,044件のデータが入っているrentalテーブルで試してみました。order byによるソートにindexが利用できるとは言え、それなりの差が出そうです。rentalテーブルは、postgresql用のサンプルデータベースです。

参考)PostgreSQL Sample Database

実行時間の比較は、explain analyzeを使います。

  1. dvdrental=# explain select * from rental order by rental_date limit 10;
  2. QUERY PLAN
  3. -------------------------------------------------------------------------------------------------------------------------------
  4. Limit (cost=0.29..0.81 rows=10 width=36)
  5. -> Index Scan using idx_unq_rental_rental_date_inventory_id_customer_id on rental (cost=0.29..846.62 rows=16044 width=36)
  6. (2 rows)
  7.  
  8. dvdrental=# explain analyze select * from rental order by rental_date limit 10;
  9. QUERY PLAN
  10. ----------------------------------------------------------------------------------------------------------------------------------------
  11. ----------------------------------
  12. Limit (cost=0.29..0.81 rows=10 width=36) (actual time=0.017..0.023 rows=10 loops=1)
  13. -> 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
  14. ime=0.016..0.021 rows=10 loops=1)
  15. Planning time: 0.100 ms
  16. Execution time: 0.071 ms
  17. (4 rows)
  18.  
  19. dvdrental=# explain analyze select * from rental order by rental_date limit 10 offset 16000;
  20. QUERY PLAN
  21. ----------------------------------------------------------------------------------------------------------------------------------------
  22. -------------------------------------
  23. Limit (cost=844.30..844.83 rows=10 width=36) (actual time=3.532..3.535 rows=10 loops=1)
  24. -> 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
  25. ime=0.014..2.887 rows=16010 loops=1)
  26. Planning time: 0.179 ms
  27. Execution time: 3.556 ms
  28. (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などでクエリ実行にかかるコストを確認する必要あり

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。