[解決済み】MYSQLのLIMITオフセットが高いと、なぜクエリが遅くなるのですか？

質問

シナリオ：1600万以上のレコードを持つテーブル[サイズ2GB]。SELECTでLIMITオフセットが高いほど、ORDER BY *primary_key*を使用した場合、クエリが遅くなる。

そこで

SELECT * FROM large ORDER BY `id`  LIMIT 0, 30 

よりもはるかに少ないコストで

SELECT * FROM large ORDER BY `id` LIMIT 10000, 30 

これでは30レコードしか注文できないし、どっちも同じだ。つまり、ORDER BYによるオーバーヘッドではありません。

現在、最新の30行をフェッチする場合、約180秒かかります。この単純なクエリを最適化するにはどうしたらよいでしょうか？

どのように解決するのですか？

オフセットが大きくなると、クエリの速度が遅くなるのは当然です。 OFFSET + LIMIT レコードを取得します（そして LIMIT となります。） この値が高いほど、クエリの実行時間は長くなります。

クエリは OFFSET なぜなら、第一に、レコードの長さが異なる場合があり、第二に、削除されたレコードによるギャップがある場合があるからです。そのため、各レコードをチェックし、カウントする必要がある。

と仮定すると id が MyISAM テーブルの主キー、あるいは InnoDB テーブルのユニークな非主キーフィールドである場合、このトリックを使うことで高速化することができます。

SELECT  t.* 
FROM    (
        SELECT  id
        FROM    mytable
        ORDER BY
                id
        LIMIT 10000, 30
        ) q
JOIN    mytable t
ON      t.id = q.id

こちらの記事をご覧ください。

• MySQL ORDER BY / LIMIT パフォーマンス：遅い行のルックアップ