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

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 のデータ ポイントを含むテーブルでテストされており、その効果は非常に明白です。

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

以下もご興味があるかもしれません:
  • MySQL ページングの原理と効率的な MySQL ページング クエリ ステートメント
  • MySQL 百万レベルのページング最適化 (MySQL 千万レベルの高速ページング)
  • 3 つのデータベース (Oracle、MySQL、SqlServer) のページング クエリの例
  • MySQL 制限ページング最適化方法の共有
  • php+mysql ページングコードの詳細な説明
  • mysql+php ページングクラス (テスト済み)
  • MySQL ページング最適化分析
  • MySQL でページングに LIMIT を使用する方法

<<:  JavaScript データ構造 双方向リンクリスト

>>:  CentOS 7 で NFS ファイル共有ストレージ サービスを構築するための完全な手順

推薦する

Docker でコンテナのポート マッピングを動的に変更する方法

前書き: Docker のポート マッピングは、多くの場合、Docker Run コマンド中に -p...

DockerはRedisをインストールし、操作用のビジュアルクライアントを導入します

1 はじめにRedis 、 ANSI C言語で開発されたKey-Valueベースの高性能NoSQLデ...

MySQL サーバー 5.7.20 のインストールと設定方法のグラフィック チュートリアル

この記事ではMySQL 5.7.20のインストールと設定方法を記録し、皆さんと共有します1. MyS...

http:// の代わりに // を使用する利点は何ですか (アダプティブ https)

//デフォルトプロトコル/ デフォルト プロトコルの使用は、リソース アクセス プロトコルが現在の...

Linux における mv コマンドの高度な使用例

序文mv コマンドは、move の略語で、ファイルを移動したり、ファイル名を変更したり (ファイルの...

Vueリストレンダリングキーの原理と機能の詳細な説明

目次リストレンダリングキーの原理と機能主要原則の分析キーの役割要約するリストレンダリングキーの原理と...

MySQLのエンコードの不一致によって発生する可能性のある問題

ストアドプロシージャとコーディングMySQL ストアド プロシージャでは、テーブルとデータのエンコー...

MySQL における int の最大値の詳細な説明

導入2日前に見た問題について詳細に書きます。バイトコンピューターがバイナリに基づいていることは誰もが...

Vue のルータールーティングの 2 つのモード (ハッシュと履歴) の詳細な説明

ハッシュモード(デフォルト)動作原理:ウェブページのハッシュ値の変化を監視する—> onhas...

フォームにファイルをアップロードした後にアクションを保存するよう促す理由と解決策

jsonデータはhtml形式で返される必要がありますつまり、 response.setContent...

Windows Server 2008R2、2012、2016、2019 の違い

目次共通バージョンの紹介共通バージョンのダウンロードアドレスとインストール以下に簡単な違いを示します...

Linux trコマンドの使い方

01. コマンドの概要tr コマンドは、標準入力からの文字を置換、圧縮、削除できます。ある文字セット...

Angularが予期しない例外エラーを処理する方法の詳細な説明

前面に書かれたコードがどれだけ適切に記述されていても、すべての可能性のある例外を完全に処理することは...

Docker Toolboxを完全にアンインストールする方法

Docker Toolbox は、Windows 10 Professional より前のバージョン...

MySQL データベースの 1045 エラーの解決方法

ローカル データベースがサーバー データベースに接続されているときに発生する 1045 の問題を解決...