MySQL ページング分析の原理と効率改善

MySQL ページング分析の原理と効率改善

PERCONA PERFORMANCE CONFERENCE 2009 では、Yahoo のエンジニア数名が「MySQL を使用した効率的なページネーション」と題するレポートを発表し、多くの注目すべき点が取り上げられました。この記事は、元のレポートをさらに拡張したものです。

まず、ページングの基本原則を見てみましょう。

MySQL> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20\G
**************** 1. 行 ***************
id: 1
選択タイプ: シンプル
表: メッセージ
タイプ: インデックス
可能なキー: NULL
キー: PRIMARY
キーの長さ: 4
参照: NULL
行数: 10020
余分な：
セット内の 1 行 (0.00 秒)

limit 10000,20 は、条件を満たす 10020 行をスキャンし、最初の 10000 行を破棄し、最後の 20 行を返すことを意味します。問題はここにあります。limit 100000,100 を使用すると、100100 行をスキャンする必要があります。同時実行性の高いアプリケーションでは、各クエリで 100,000 行以上をスキャンする必要があり、パフォーマンスは間違いなく大幅に低下します。この記事では、n 行のみがスキャンされるため、制限 n のパフォーマンスは問題にならないとも述べられています。

この記事には、ページめくりの「手がかり」を提供する「手がかり」アプローチについて書かれています。たとえば、SELECT * FROM message ORDER BY id DESC、ページ区切りは id の降順、ページあたり 20 項目、現在のページは 10 ページ目、現在のページ エントリの最大 id は 9527、最小 id は 9500 です。「前のページ」や「次のページ」などのジャンプのみを提供する場合 (ページ N へのジャンプなし)、「前のページ」を処理するときの SQL ステートメントは次のようになります。

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;

「次のページ」を処理する場合、SQL ステートメントは次のようになります。

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;

ページがいくつめくられても、クエリごとに 20 行だけがスキャンされます。

欠点は、「前のページ」と「次のページ」の形式でしかリンクを提供できないことですが、当社の製品マネージャーは「<前のページ 1 2 3 4 5 6 7 8 9 次のページ>」のようなリンクを非常に気に入っています。どうすればよいでしょうか?

LIMIT m,n が避けられない場合、効率を最適化する唯一の方法は、m をできるだけ小さくすることです。以前の「手がかり」アプローチを拡張し、SELECT * FROM message ORDER BY id DESC を使用して、ID の降順でページ番号を付け、1 ページあたり 20 項目にします。現在のページは 10 ページ目で、現在のページ エントリの最大 ID は 9527、最小 ID は 9500 です。たとえば、ページ 8 にジャンプする場合、私が見た SQL ステートメントは次のように記述できます。

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;

13ページへジャンプ:

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 40,20;

原理は依然として同じです。現在のページ ID の最大値と最小値を記録し、ジャンプ ページと現在のページ間の相対オフセットを計算します。ページが近いためオフセットは大きくならず、m 値は比較的小さくなり、スキャンされる行数が大幅に削減されます。実際、従来の制限 m,n では、相対オフセットは常に最初のページです。この場合、後ろをめくると効率が低下します。上記の方法では、このような問題はありません。

SQL ステートメントの ASC と DESC に注意してください。結果が ASC で取得された場合は、表示時に反転することを忘れないでください。

合計 600,000 のデータ ポイントを含むテーブルでテストされており、その効果は非常に明白です。

読んでいただきありがとうございます。お役に立てれば幸いです。このサイトをサポートしていただきありがとうございます。