SQL limitを使ったページング、ランダム抽出、速度改善をおこなう
  • facebookページ
  • twitterページ
  • 2019.09.05

    SQL limitを使ったページング、ランダム抽出、速度改善をおこなう

    SQLのlimit句の使い方についてまとめました。

    limit句は大量レコードのページングや、最新・最古の一件を取得するようなケースでよく使われます。

    limit句の使い方

    以下のサンプルコードは、MySQL前提となります。データベースとして、MySQLのサンプルデータベースEmployeesを使っています。

    limit句の構文

    取得データの上限設定の指定のために使います。

    カンマ指定時は、limit <開始位置>,<最大取得件数>と指定可能です。「limit 取得したい行数 OFFSET 開始位置」と意味は同じです。

    件数が多いテーブルに対してlimitを設定してselectすると、レスポンス時間を短くできます。ページングをおこなうときに便利です。

    limitは、標準SQLではないため、主要DBMSでは使えないものもあります。MySQLとPostgreSQLでは使用可能ですが、OracleやSQL ServerではLimit句が使えません。

    そのため、Oracleでは、ROWNUMを使って擬似的にlimitと似た動作を実現可能です。

    SQL Serverの場合は、OFFSET x ROWSやFETCH NEXT x ROWS ONLYといった命令を組み合わせて、limitと同様の動作を実現しています。

    limitを使って、抽出結果の最新データを取得

    order by 、descと limit 1の組み合わせで、最新の一件を取得できます。

    最新の十件を昇順にソートしたいような場合は、サブクエリ(副問い合わせ)を組み合わせます。

    emp_no(10001~499999)の大きいものから10件取得し、昇順(数字の小さい順)にソートできました。

    SQL実行結果

    limitを使ってランダムに指定件数のデータを抽出する

    乱数をorder byに指定すると、テーブル全体から指定件数をランダムに抽出することが可能です。

    たとえば、salariesテーブルからランダムに10件抽出するには、以下のSQLを実行すれば可能。

    実行すると、1.2秒かかりました。

    • 10 total, Query took 1.2157 seconds.

    ただし、レコード全件にレコードを割り振るため、かなり遅くなります。

    サブクエリでlimit使用時のエラー

    MySQLでは、サブクエリ内でlimitを使うとエラーになります。

    上記のSQLを実行すると、下記のエラーが出力されます。

    「MySQLのこのバージョンでは、IN、ALL、ANY、SOMEサブクエリとLIMITを同時に使えない」というエラー内容です。

    上記のSQLのように「サブクエリのサブクエリ」と言う形にし、in句内に直接limitが出現しないように工夫することでエラー回避が可能です。

    limit使用で速度が低下するケース

    ページングのためにlimitとoffsetを組み合わせると、offsetの値が大きくなるにつれ速度は遅くなります。

    例えば、「limit 100,10」という指定では、内部で110件取得して先頭から100個捨てる処理をおこなうためです。クエリを実行するたびに、毎回OFFSET分の検索をおこなっているんですね。

    実際、どのくらい処理時間が変わるのか、比較してみましょう。

    283万件のデータが保存されているsalariesテーブルで、以下のSQLを実行してみます。

    • 100 total, Query took 0.0003 seconds.

    offsetが0の状態で、0.003秒です。

    • 100 total, Query took 0.3642 seconds.

    offsetに2,000,000を設定すると、処理時間が1214倍になりました。

    1ページあたり100レコード表示するとして、1万ページ目にはレスポンス時間が1,214倍になります。

    ポテパンダの一言メモ

    単純計算で、100ページまでページングをおこなうと、レスポンス時間が6倍程度になります。

    limitのパフォーマンスを改善するには、where句でページ条件を指定

    offsetの代わりに、where句の条件を変化させることでページング時の速度低下を改善できる可能性があります。

    ただし、抽出条件に沿った連番・抜け無しのIDカラムがあることが前提になるため、使える局面は限定的と言えるでしょう。

    offset以外の要因で、ページングのパフォーマンスが低下する原因はインデックスだというケースがあります。

    以下のSQLを実行したときの実行時間を計測してみました。

    • 1 total, Query took 0.7003 seconds.

    以下のSQLを実行し、salariesテーブルにto_date、emp_noの2カラムのインデックスを設定します。

    上記のselect文を実行すると、以下の実行時間となりました。

    • 1 total, Query took 0.0003 seconds.
    ポテパンダの一言メモ

    インデックスを設定すると、実行時間が1/2334に短縮できました。

    limitのまとめ

    ポテパンダの一言メモ
    • limitはページング用に使うのが便利
    • offsetの値が大きくなると、実行速度が遅くなる
    • offsetの代わりにwhere句でページング条件を指定すると速度改善の可能性あり
    • where句とlimit句の組み合わせはインデックス設定で速度改善の可能性あり

    ポテパンが提供するサービスについて

    本メディア「ポテパンスタイル」を運営する株式会社ポテパンは、エンジニアキャリア領域で複数サービスを提供しています。

    ポテパンフリーランス

    ポテパンフリーランス

    フリーランスエンジニアの方に高単価案件をご紹介しております。弊社ではフリーランス案件を常時300件ほど保有しており、その中からあなたに適した案件をご案内いたします。また、これから独立してフリーランスになる方の無料個別相談も承っております。フリーランスになった後の案件獲得方法やお金面(税金や保険など)についてお答えいたします!フリーエンジニアになりたい方向けのコンテンツも盛りだくさんです。

    ポテパンキャリア

    ポテパンキャリア

    エンジニア職専門の転職エージェントです。ポテパンキャリアでは、技術のわかるエージェントがあなたの転職をサポートします。エージェント自身がエンジニアなので、あなたと同じ目線で仕事内容や今後のキャリアについて一緒に考えることができます。年収800万円以上のハイスペック転職をご希望の方は「ポテパンプロフェッショナル」もご用意しておりますのでご利用下さいませ。

    ポテパンキャンプ

    ポテパンキャンプ

    ポテパンキャンプでは、RubyにてゼロからオリジナルのECサイトを作り上げてる3ヶ月間の実践型カリキュラムを提供しております。すでに本スクールの卒業生は、エンジニア職として様々な企業様に就職しております。なお、本スクールは受講料25万円と他社スクールに比べ格安となっており、またポテパンからご紹介させていただいた企業へ就職が決まった場合は、全額キャッシュバックいたします。



    株式会社ポテパンは、企業とエンジニアの最適なマッチングを追求しています。気になるサービスがあれば、ぜひ覗いてみてください!

    ポテクラバナー ポテプロバナー

    この記事をシェア

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









    ABOUT US

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

    READ MORE

    ポテパンおすすめ案件