【ITニュース解説】5 Steps to Optimize MySQL Deep Pagination

「Deep Pagination」は、Webサイトやアプリケーションで大量のデータを扱う際に発生しやすいパフォーマンスの問題の一つだ。多くの人がWebサイトで検索結果や記事リストを見るとき、「次へ」や「2ページ目、3ページ目」といったページ番号をクリックするだろう。これが「ページネーション」という機能だ。通常、データベースから特定の範囲のデータを取得するために「LIMIT offset, size」というSQL文を使う。たとえば、「LIMIT 0, 10」なら最初の10件、「LIMIT 10, 10」なら次の10件、といった具合だ。

しかし、データ量が非常に多くなり、数百万件の中からかなり後ろの方のページ、たとえば180万件目から10件のデータを取ろうとすると、「LIMIT 1800000, 10」のようなSQL文を使うことになる。このとき、データベースは180万件ものデータをまず読み込んで、最初の180万件を捨てて、そこから次の10件だけを取り出す、という非効率な処理をしてしまう。これは、まるで分厚い本の中から特定のページを探すときに、最初のページから1ページずつめくって、目的のページまで来たところでやっと読み始めるようなものだ。この無駄な読み込みと破棄が、データベースの処理速度を著しく低下させ、大量のコンピューター資源を消費する原因となる。このような、非常に深い位置にあるページを取得しようとすることから、「Deep Pagination」と呼ばれている。今回の記事は、このDeep Paginationがなぜ問題なのか、そしてその問題をどうやって効率的に解決できるのかを実験を通して検証したものだ。

この問題を具体的に検証するため、ある実験環境が用意された。CPUは4コア、メモリ8GB、ストレージは1TBのSSD、そしてMySQLのバージョンは5.7.24という、一般的な開発環境を想定した構成だ。 実験では、Webサイトのアクセスログを模倣したaccess_logsという名前のテーブルが作成された。このテーブルには、log_id（アクセスログのID）、user_id（ユーザーID）、request_url（アクセスされたURL）、created_at（アクセス日時）など、様々な情報が格納される。特にlog_idは各行を一意に識別する主キー（PRIMARY KEY）で、created_atにはデータの並べ替えに使うためのインデックス（idx_time）が張られている。インデックスは、本の索引のようなもので、データベースが目的のデータを素早く見つけるための目次やガイドブックの役割を果たす。このテーブルには、Deep Paginationの問題を顕著にするため、200万件ものテストデータがプログラムによって自動的に生成された。

次に、実際にパフォーマンスを比較する実験が行われた。 まず、「元のSQL」として、次のようなクエリが実行された。 SELECT log_id, user_id, request_url, ip_address, created_at FROM access_logs ORDER BY created_at LIMIT 1800000, 10; これは、created_atの昇順で並べたアクセスログの中から、180万件目から10件のデータを取得するというものだ。このSQLを実行したところ、実行時間は2.34秒かかった。さらに詳しく見ると、データベースは1,800,010行ものデータをスキャンし、そのうち1,800,010回も「Back-to-Table」操作を行っていた。Back-to-Table操作とは、インデックスを使ってデータの場所を見つけた後、実際にそのデータが格納されているメインのテーブル領域（データファイル）にアクセスして、インデックスには含まれていない残りのカラムの情報を読み出す処理のことだ。この処理はディスクへの追加アクセスを伴うため、回数が多くなると大きなオーバーヘッドとなる。

このパフォーマンス問題を解決するために、「最適化されたSQL」として「Deferred Join」という手法を使ったクエリが提案された。Deferred Joinは、クエリを二段階に分けて実行する考え方だ。まず、必要なデータの主キー（log_id）だけをインデックスを使って効率的に探し出す。そして、その主キーを使って、後から必要な全カラム（user_id, request_urlなど）をデータベースから取得する、という手順を踏む。 具体的には、次のようなSQLだ。 SELECT t1.log_id, t1.user_id, t1.request_url, t1.ip_address, t1.created_at FROM access_logs t1 INNER JOIN ( SELECT log_id FROM access_logs ORDER BY created_at LIMIT 1800000, 10 ) t2 ON t1.log_id = t2.log_id ORDER BY t1.created_at; このクエリでは、まず括弧の中のサブクエリが実行され、created_atでソートされたlog_idを180万件目から10件だけ取得する。このサブクエリはlog_idだけしか必要としないため、created_atインデックスとlog_id（主キーインデックスの一部）だけで完結でき、「カバーリングインデックス」として機能する。カバーリングインデックスとは、クエリで必要な全てのカラムの情報が、テーブルのデータ本体ではなく、インデックスの中に全て含まれている状態を指す。この状態では、データベースはインデックスだけを読み込めば良いため、データ本体にアクセスするBack-to-Table操作が不要になり、非常に高速にデータを取得できる。このサブクエリで取得したたった10件のlog_idを使って、外側のクエリでaccess_logsテーブル（t1）と結合し、最終的に必要なすべてのカラムを取得するという仕組みだ。

この最適化されたSQLを実行したところ、驚くべき結果が得られた。実行時間は元の2.34秒から0.46秒へと短縮され、80.3%もの高速化が実現されたのだ。さらに重要なのは、データベースがスキャンした行数が1,800,010行からわずか20行に、Back-to-Table操作も1,800,010回から10回に激減したことだ。これは、インデックスだけを使って効率的に主キーを特定し、その主キーを持つ実際のデータにピンポイントでアクセスできるようになったためだ。インデックスはデータ本体よりもコンパクトなので、物理的なディスクI/O（データの読み書き）も大幅に減らすことができる。

なぜこのような大幅な改善ができたのかは、SQLの「実行計画」を見ることでさらに詳しくわかる。実行計画とは、データベースがSQL文をどのように処理するかを示す計画表のようなものだ。 元のSQLの実行計画を見ると、「ALL」という表示があった。これは「フルテーブルスキャン」を意味し、テーブル全体を最初から最後まで読み込んでいることを示している。さらに「Using filesort」という表示もあり、これはデータベースがメモリ上でデータを並べ替えるというコストの高い処理を行っていることを示していた。つまり、200万件ものデータを全て読み込み、それをメモリ上で並べ替えてから180万件を捨てて残りの10件を取り出すという、非常に非効率な処理をしていたのだ。

一方、最適化されたSQLの実行計画を見ると、サブクエリの部分で「index」という表示があった。これは、created_atインデックスを使ってデータをスキャンしていることを意味する。さらに「Using index」と表示されていることから、このサブクエリはインデックスの情報だけで完結しており、データ本体にアクセスする必要がない「カバーリングインデックス」として機能していることがわかる。その後、外側のクエリでは、サブクエリで特定されたわずか10件のlog_idを使って、PRIMARYキーによる高速な結合が行われている。これにより、データベースは膨大な量のデータを読み捨てたり、メモリ上でソートしたりする無駄を完全に排除し、必要なデータに効率的にアクセスできるようになったのだ。

今回の比較分析を通じて、「Deferred Join」がMySQLのDeep Paginationクエリを最適化するための非常に効果的な解決策であることが確認できた。この方法は、クエリを二段階に分割し、まず「カバーリングインデックス」を利用して必要なデータの主キーIDを素早く特定し、次にその主キーを使って完全な行データを取得するというものだ。 このアプローチの主な利点は、膨大なBack-to-Table操作が劇的に減ること、インデックスデータがデータ本体よりも密に格納されているため物理的なディスクI/Oのオーバーヘッドが大幅に削減されること、そしてコストの高いフルテーブルスキャンやメモリでのソート処理が不要になることだ。また、この方法はインデックスが張られたソートフィールドを持つあらゆるDeep Paginationのシナリオに適用できる汎用性の高さも持ち合わせている。

Deferred Joinは、既存のシステムへの変更コストが低く、広い範囲で適用できるため、MySQLのDeep Paginationによるパフォーマンス問題に対する有力な解決策となる。実践的なアドバイスとしては、100ページを超えるような深いページネーションクエリに対して、この最適化を導入することを推奨する。 さらに、ビジネス要件に基づいてページネーションの最大深度に制限を設けることや、「カーソルベースのページネーション」（直前のページで取得した最後のデータのIDなどを手掛かりに次のデータを取得する方法）といった他の最適化戦略と組み合わせることで、より包括的で堅牢なページネーションソリューションを構築できるだろう。