SQLのoffsetはレコード取得開始位置を指定できる 大量データでは速度劣化に注意
  • facebookページ
  • twitterページ
  • 2020.05.30

    SQLのoffsetはレコード取得開始位置を指定できる 大量データでは速度劣化に注意

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

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

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

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

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

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

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

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

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

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

    参考)PostgreSQL Sample Database

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

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


    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE