受講料が最大70%OFF 受講料が最大70%OFF

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

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

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

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

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用のサンプルデータベースです。

参考)PostgreSQL Sample Database

実行時間の比較は、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などでクエリ実行にかかるコストを確認する必要あり

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

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

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

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

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

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

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

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

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

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

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