MySQLページング完全ガイド：1000万件実測で判明したOFFSET劣化とキーセット方式

はじめに

MySQL 8.0で大規模データをページングする際、LIMIT ... OFFSET ...構文は本当に高速なのでしょうか？

本記事では、MySQL 8.0（Docker環境）に1000万件のデータを投入して実測した結果をもとに、以下を明らかにします。

• LIMIT OFFSET方式は10万件を超えると速度劣化する（500万件で5.33秒）
• ROW_NUMBER方式はMySQLでは最適化が効かず常に遅い（全範囲で12〜19秒）
• キーセットページネーション(Seek Method)なら一貫して高速（500万件でも0.003秒、99.9%高速化）
• MySQL 8.0ではQuery Cacheが廃止されたため、代替手段の検討が必要

すべての検証コードはGitHubで公開しており、誰でも再現可能です。

検証環境

• データベース: MySQL 8.0（Dockerコンテナ）
• データ件数: 10,000,010件
• テーブル: ソフトウェア商材を扱う商社向けデータ（trading_software）
• インデックス: price DESC, software_id DESCの複合インデックス
• 計測ツール: MySQL Profiling（SET profiling = 1 + SHOW PROFILES）

テーブル定義例

CREATE TABLE trading_software (
    software_id    BIGINT         NOT NULL AUTO_INCREMENT,
    software_name  VARCHAR(100)   NOT NULL,
    vendor_name    VARCHAR(100)   NOT NULL,
    price          DECIMAL(10,2)  NOT NULL,
    stock_quantity INT            NOT NULL,
    registered_at  DATETIME       NOT NULL,
    PRIMARY KEY (software_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• software_idは主キー（AUTO_INCREMENT）
• ソートに利用するカラム例としてpriceやregistered_atを想定

サンプルデータ投入

INSERT INTO trading_software (software_name, vendor_name, price, stock_quantity, registered_at) VALUES
('ERP Package', 'VendorA', 500000, 10, NOW()),
('CRM Suite', 'VendorB', 300000, 5, NOW()),
('Security App', 'VendorC', 800000, 2, NOW()),
('Accounting Tool', 'VendorA', 200000, 8, NOW()),
('Analytics Platform', 'VendorB', 900000, 1, NOW()),
('Mobile Dev Kit', 'VendorC', 250000, 15, NOW()),
('Test Automation', 'VendorA', 150000, 20, NOW()),
('DevOps Suite', 'VendorB', 400000, 12, NOW()),
('Legacy Migration', 'VendorA', 650000, 3, NOW()),
('E-commerce Platform', 'VendorC', 1000000, 4, NOW());

LIMIT OFFSET 方式

• 基本構文

SELECT
    t.*
FROM trading_software t
ORDER BY t.price DESC, t.software_id DESC
LIMIT 20 OFFSET 100;

• 注意点と性能特性
  • 小規模OFFSET（1万件以下）: 高速に動作し実用的（0.002秒〜0.12秒）
  • 中規模OFFSET（10万件）: 速度劣化が発生（4.52秒）
  • 大規模OFFSET（100万件以上）: 深刻な速度劣化（4.68秒〜5.33秒）
  • MySQLは小さなOFFSETではインデックススキャンを使用するが、大きなOFFSETではフルテーブルスキャン+ソートになる
  • 必ずEXPLAIN ANALYZEで実行計画を確認し、大規模ページングではキーセット方式を検討すること

ROW_NUMBER 方式

• 基本構文

SELECT * FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER (ORDER BY t.price DESC, t.software_id DESC) AS rn
    FROM trading_software t
) tmp
WHERE tmp.rn BETWEEN 101 AND 120;

• 注意点と性能特性
  • MySQLではROW_NUMBER()を使ったページングは常にフルテーブルスキャンになる
  • ウィンドウ関数の結果を一時テーブル（Materialize）に格納してからフィルタリングするため
  • OFFSETの位置に関係なく、常に全件をソート・処理する（12〜19秒）
  • MySQLではROW_NUMBER方式はページングに不向き
  • 標準SQLだが、MySQLではLIMIT OFFSETのほうが高速です

重要: MySQLではROW_NUMBER方式はページングに使用すべきではありません。実測で全ての範囲で12〜19秒かかり、LIMIT OFFSETよりも遅い結果となりました。

インデックス設計と最適化

ページングクエリのパフォーマンスは、適切なインデックスの有無に大きく依存します。

• ソートキーに対する複合インデックス

-- price でソートする場合
CREATE INDEX idx_trading_software_price
  ON trading_software (price DESC, software_id DESC);

-- registered_at でソートする場合
CREATE INDEX idx_trading_software_date_id
  ON trading_software (registered_at DESC, software_id DESC);

• インデックス設計のポイント
  • ソートキーを第1カラムに配置する
  • 一意性を保証するため主キーを第2カラムに追加する
  • MySQL 8.0以降は降順インデックス（DESC）をネイティブサポートしています
  • インデックスによりIndex scanが働けば、大量データからでも高速に必要件数だけ取得可能です

実行計画の確認

各方式の実行計画を確認し、最適化が効いているかをチェックします。

LIMIT OFFSET方式の実行計画

EXPLAIN ANALYZE
SELECT t.*
FROM trading_software t
ORDER BY t.price DESC, t.software_id DESC
LIMIT 20 OFFSET 100;

小規模OFFSET（100件）の実行計画:

-> Limit/Offset: 20/100 row(s)  (cost=0.364 rows=20)
    -> Index scan on t using idx_trading_software_price

大規模OFFSET（100万件）の実行計画:

-> Limit/Offset: 20/1000000 row(s)  (cost=1.03e+6 rows=20)
    -> Sort: t.price DESC, t.software_id DESC, limit input to 1000020 row(s)
        -> Table scan on t  (rows=9.97e+6)

小規模OFFSETではインデックススキャンが使用されますが、大規模OFFSETではテーブルフルスキャン+ソートになります。

ROW_NUMBER方式の実行計画

EXPLAIN ANALYZE
SELECT * FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER (ORDER BY t.price DESC, t.software_id DESC) AS rn
    FROM trading_software t
) tmp
WHERE tmp.rn BETWEEN 101 AND 120;

実行計画:

-> Filter: (tmp.rn between 101 and 120)
    -> Table scan on tmp
        -> Materialize
            -> Window aggregate: row_number()
                -> Sort: t.price DESC, t.software_id DESC
                    -> Table scan on t  (rows=9.97e+6)

常にフルテーブルスキャン→ソート→Materialize→フィルタの流れになり、OFFSETの位置に関係なく遅い。

キーセットページネーション方式の実行計画

EXPLAIN ANALYZE
SELECT t.*
FROM trading_software t
WHERE (t.price < 901159.27) 
   OR (t.price = 901159.27 AND t.software_id < 5005185)
ORDER BY t.price DESC, t.software_id DESC
LIMIT 20;

実行計画:

-> Limit: 20 row(s)
    -> Index range scan on t using idx_trading_software_price
       over (price = 901159.27 AND 5005185 < software_id) OR (901159.27 < price)

WHERE句による範囲絞り込みが効き、インデックスレンジスキャンで必要な20件のみを取得。

キーセットページネーション (Seek Method)

大規模ページング（10万件以上）で速度劣化を避けるための手法です。

• 基本構文

-- 初回ページ取得
SELECT
    t.*
FROM trading_software t
ORDER BY t.price DESC, t.software_id DESC
LIMIT 20;

-- 2ページ目以降 (前ページの最後の値を使用)
SELECT
    t.*
FROM trading_software t
WHERE (t.price < :last_price) 
   OR (t.price = :last_price AND t.software_id < :last_id)
ORDER BY t.price DESC, t.software_id DESC
LIMIT 20;

• 注意点と性能特性

  • WHERE句で範囲を絞り込むため、インデックスを効率的に利用します
  • 大規模OFFSETでも一貫して高速（0.002秒〜0.005秒）
  • 不要な読み飛ばしが発生せず、データ量に依存しないパフォーマンス
  • リアルタイムデータ更新に強い（ページング中の挿入/削除の影響が少ない）

• 制約事項

  • 任意ページへのジャンプができない（順次ページングのみ）
  • 前ページの最後の値（カーソル）を保持する必要がある
  • ソートキーが一意でない場合は主キーを組み合わせる必要がある
  • アプリケーション側でカーソル管理のロジックが必要です

• 実装のポイント

  • 複合ソートキーの場合は、OR条件で正しく連結する
  • インデックスはソートキーと主キーの複合インデックスを作成すること
  • クライアントに最後の値(price, id)を返し、次回リクエストで受け取る

方式別の性能比較（実測結果）

MySQL 8.0（Docker）で1000万件のデータを使用して計測した結果です。

位置	LIMIT OFFSET	ROW_NUMBER	Keyset	改善率
0〜20件目	0.002秒	19.06秒	0.002秒	-
100件目〜	0.002秒	13.21秒	0.004秒	-
1万件目〜	0.12秒	13.20秒	0.002秒	98%高速化
10万件目〜	4.52秒	12.54秒	0.002秒	99.9%高速化
100万件目〜	4.68秒	14.00秒	0.005秒	99.9%高速化
500万件目〜	5.33秒	12.23秒	0.003秒	99.9%高速化

結論

• 小規模ページング（1万件以下）: LIMIT OFFSETで実用的（0.002秒〜0.12秒）
• 中規模ページング（10万件）: LIMIT OFFSETで速度劣化が発生（4.52秒）、キーセット方式を推奨
• 大規模ページング（100万件以上）: キーセット方式のみ実用的（LIMIT OFFSET方式は4〜5秒）
• ROW_NUMBER方式: MySQLでは使用すべきではない（常に12〜19秒）

MySQLの特徴: ROW_NUMBER方式がOracleより大幅に遅い理由は、MySQLのウィンドウ関数最適化が限定的で、常に全件をMaterializeするためです。OracleのWINDOW NOSORT STOPKEYのような早期打ち切り最適化がありません。

MySQL 8.0 Query Cache廃止への対応

MySQL 8.0ではQuery Cache機能が完全に削除されました。これはOracle DatabaseのRESULT_CACHEヒントに相当する機能がないことを意味します。

代替手段

1. InnoDB Buffer Pool

   • データページレベルでの自動キャッシュ
   • 同じデータへのアクセスは高速化される
   • 設定: innodb_buffer_pool_sizeを適切に調整

2. ProxySQL Query Cache

   • 外部プロキシによるクエリ結果キャッシュ
   • MySQLの前段に配置して透過的にキャッシュ
   • TTLベースでキャッシュを管理

3. アプリケーションキャッシュ

   • Redis、Memcachedなどの外部キャッシュ
   • ページング結果を明示的にキャッシュ
   • キャッシュ無効化ロジックの実装が必要

4. MySQL Router with caching_sha2_password

   • 接続プーリングとキャッシュ
   • 高可用性構成での利用

キャッシュ戦略の推奨

頻繁にアクセスされる初期ページ
    → アプリケーションキャッシュ（Redis等）でキャッシュ
    
深いページ（100万件以上）
    → キーセットページネーションを使用（キャッシュ不要）
    
更新頻度の低いマスターデータ
    → ProxySQLまたはアプリケーションキャッシュ

ベストプラクティスまとめ

1. ページング方式の選択フローチャート

データ件数とアクセスパターンを確認
    ↓
1万件以下、または初期ページのみアクセス
    → LIMIT OFFSET（シンプルで高速）
    
10万件以上、深いページまでアクセス
    → キーセットページネーション（必須）
    
任意ページジャンプが必須
    → ページ範囲を制限 + LIMIT OFFSET
    
ROW_NUMBER方式
    → MySQLでは使用しない

2. セキュリティ（動的ソート）

• ORDER BYを動的に組み立てる際は必ずホワイトリスト検証すること
• プリペアドステートメントではORDER BY句のカラム名をバインドできない
• SQLインジェクション対策として、許可されたカラム名のみを受け入れる

-- NG: SQLインジェクションリスク
ORDER BY ${user_input}

-- OK: ホワイトリスト検証（アプリケーション側）
CASE sort_column
    WHEN 'price' THEN 'price'
    WHEN 'registered_at' THEN 'registered_at'
    WHEN 'software_id' THEN 'software_id'
    ELSE 'software_id'
END

3. 本番環境での検証

• 実際のデータ量で必ずEXPLAIN ANALYZEを実行し、インデックスが使用されているか確認すること
• 統計情報を最新化（ANALYZE TABLE trading_software）すること
• 大規模データならパーティショニング、読み取りレプリカも検討すること

4. MySQLバージョン対応

バージョン	推奨方式
MySQL 5.7以前	LIMIT OFFSET（ROW_NUMBER未対応）
MySQL 8.0以降	LIMIT OFFSET、Keyset 利用可能（ROW_NUMBERは非推奨）

Oracle版との比較

本検証結果はOracle版と対をなすMySQL版です。

項目	Oracle 19c	MySQL 8.0
ページング構文	OFFSET ... FETCH	LIMIT ... OFFSET
ROW_NUMBER最適化	WINDOW NOSORT STOPKEY（早期打ち切り）	なし（常に全件処理）
クエリキャッシュ	RESULT_CACHE（組み込み）	廃止（外部ツール必要）
降順インデックス	論理的（オプティマイザ最適化）	物理的（8.0以降ネイティブ対応）
大規模OFFSET性能	劣化あり（500万件で7.17秒）	劣化あり（500万件で5.33秒）
キーセット性能	高速（500万件で0.38秒）	高速（500万件で0.003秒）

検証の再現性

本記事のすべての性能データは、実際の環境で計測した結果です。再現性を担保するため、検証に使用したすべてのコードを公開しています。

GitHubリポジトリ

MySQLPaging

以下が含まれます

• Docker環境: compose.ymlでMySQL 8.0を即座に起動
• DDL: テーブル・インデックス作成スクリプト
• データ投入: 1000万件のランダムデータ生成ストアドプロシージャ
• パフォーマンステスト: 3方式（LIMIT OFFSET、ROW_NUMBER、Keyset）の実測スクリプト
• 実行計画確認: EXPLAIN ANALYZEによる最適化確認スクリプト
• 結果サマリー: パフォーマンス比較表とレポート

実行方法

git clone https://github.com/okamyuji/MySQLPaging.git
cd MySQLPaging
cp env.example .env
docker-compose up -d
# コンテナ起動後
./run_all_tests.sh

すべてのテストが自動実行され、結果が表示されます。

参考情報

• Using Window Functions for Efficient Pagination
• Using SQL's OFFSET and FETCH with Window Functions for Efficient Pagination and Total Count
• Keyset Pagination (Seek Method)
• MySQL 8.0 Reference Manual - Window Functions