大きなオフセットによる MySQL 制限ページングが遅い理由と最適化ソリューション

大きなオフセットによる MySQL 制限ページングが遅い理由と最適化ソリューション

MySQL では通常、limit を使用してページ上のページング機能を完了しますが、データ量が大きな値に達すると、ページをめくるほど、インターフェースの応答速度が遅くなります。

この記事では、主に、オフセットが大きい場合に制限ページングが遅くなる理由と、その最適化ソリューションについて説明します。この状況をシミュレートするために、まずテーブル構造と実行される SQL を紹介します。

シナリオシミュレーション

テーブルステートメントの作成

ユーザー テーブルの構造は、ID、性別、名前など比較的シンプルです。SQL 実行時間の変化をより明確にするために、名前の列が 9 つあります。

テーブル `user` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主キー',
 `sex` tinyint(4) NULL デフォルト NULL コメント '性別 0-男性 1-女性',
 `name1` varchar(255) 文字セット utf8 照合 utf8_general_ci NULL デフォルト NULL コメント 'Name',
 `name2` varchar(255) 文字セット utf8 照合 utf8_general_ci NULL デフォルト NULL コメント 'Name',
 `name3` varchar(255) 文字セット utf8 照合 utf8_general_ci NULL デフォルト NULL コメント 'Name',
 `name4` varchar(255) 文字セット utf8 照合 utf8_general_ci NULL デフォルト NULL コメント 'Name',
 `name5` varchar(255) 文字セット utf8 照合 utf8_general_ci NULL デフォルト NULL コメント 'Name',
 `name6` varchar(255) 文字セット utf8 照合 utf8_general_ci NULL デフォルト NULL コメント 'Name',
 `name7` varchar(255) 文字セット utf8 照合 utf8_general_ci NULL デフォルト NULL コメント 'Name',
 `name8` varchar(255) 文字セット utf8 照合 utf8_general_ci NULL デフォルト NULL コメント 'Name',
 `name9` varchar(255) 文字セット utf8 照合 utf8_general_ci NULL デフォルト NULL コメント 'Name',
 BTREEを使用した主キー(`id`)
 インデックス `sex`(`sex`) BTREE 使用
) ENGINE = InnoDB AUTO_INCREMENT = 9000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

データ入力

ここで、合計 9,000,000 件のレコードを含むデータを入力するためのストアド プロシージャが作成されます。関数が実行された後、別の SQL ステートメントが実行され、性別フィールドが変更されます。

ps: この関数の実行には長い時間がかかります。617.284 秒間実行しました。

CREATE DEFINER=`root`@`localhost` PROCEDURE `data`()
始める 
 iをintとして宣言します。 
 i=1 に設定します。 
 i<=9000000の間 
  ユーザー値に挿入(i,0,i,i,i,i,i,i,i,i,i);
  i=i+1 と設定します。 
 終了しながら;
終わり

-- 偶数 ID のユーザーの性別を 1-女性に設定します。update user set sex=1 where id%2=0;

SQLと実行時間

構文実行時間
性別 = 1 のユーザーから * を選択し、制限 100, 10 を指定します。 OK、時間: 0.005000秒
性別 = 1 のユーザーから * を選択し、制限 1000, 10 を指定します。 OK、時間: 0.007000秒
性別 = 1 のユーザーから * を選択し、制限 10000, 10 を指定します。 OK、時間: 0.016000秒
性別 = 1 のユーザーから * を選択し、制限 100000, 10 を指定します。 OK、時間: 0.169000秒
性別 = 1 のユーザーから * を選択し、制限 1000000, 10 を指定します。 OK、時間: 5.892000秒
性別 = 1 のユーザーから * を選択し、制限 10000000, 10 を指定します。 OK、時間: 33.465000秒

ご覧のとおり、制限オフセットが大きいほど、実行時間が長くなります。

原因分析

まず、上記の表の最初の行を例に、この SQL ステートメントの実行プロセスを分析してみましょう。

sex 列はインデックス列なので、MySQL は sex インデックス ツリーをたどり、sex=1 のデータを見つけます。

次に、非クラスター化インデックスに主キー ID の値が格納され、クエリ ステートメントですべての列のクエリが必要なため、ここでテーブル リターンが発生します。性別インデックス ツリーで値が 1 のデータにヒットした後、そのリーフ ノードの値、つまり主キー ID の値を使用して、主キー インデックス ツリーのこの行の他の列 (名前、性別) の値をクエリし、最終的に結果セットに返して、最初の行のデータが正常にクエリされるようにします。

最後の SQL ステートメントでは、limit 100, 10 が必要です。これは、101 から 110 までのデータを照会することを意味します。ただし、MySQL は最初の 110 行を照会し、最初の 100 行を破棄します。最終的に、結果セットには 101 から 110 行のみが残り、実行は終了します。

まとめると、上記の実行プロセスにおいて、オフセットが大きい制限の実行時間が長くなる理由は次のとおりです。

  • すべての列をクエリすると、テーブルが返されます
  • limit a, b は最初の a+b 個のデータに対してクエリを実行し、最初の a 個のデータを破棄します。

上記の 2 つの理由を組み合わせると、MySQL はテーブルの戻りに多くの時間を費やし、テーブルの戻りの結果が結果セットに表示されず、クエリ時間がどんどん長くなります。

最適化計画

カバーインデックス

無効なテーブル戻りがクエリの遅延の主な原因であるため、最適化計画はテーブル戻りの数を減らすことです。制限 a、b では、まずデータ a+1 から a+b の ID を取得し、次にテーブルに戻って他の列のデータを取得するとします。このようにして、テーブル戻りの数が減り、速度が確実に大幅に速くなります。

これにはカバーリング インデックスが含まれます。いわゆるカバーリング インデックスを使用すると、テーブル リターンを通じて主キー インデックスから他の列をクエリしなくても、非主クラスター化インデックスから必要なデータを取得できるため、パフォーマンスが大幅に向上します。

この考え方に基づく最適化ソリューションは、まず主キー ID をクエリし、次に主キー ID に基づいて他の列データをクエリすることです。最適化された SQL と実行時間を次の表に示します。

最適化されたSQL実行時間
ユーザー a から * を選択して、 (ユーザーから id を選択して、性別 = 1、制限 100、10)、a.id=b.id で b を結合します。 OK、時間: 0.000000秒
ユーザー a から * を選択して、 (ユーザーから id を選択して、性別 = 1、制限 1000、10)、a.id=b.id で b を選択します。 OK、時間: 0.00000秒
ユーザー a から * を選択して、 (ユーザーから id を選択して、性別 = 1、制限 10000、10) b を a.id=b.id で結合します。 OK、時間: 0.002000秒
ユーザー a から * を選択して、 (ユーザーから id を選択して、性別 = 1、制限 100000、10) b を a.id=b.id で結合します。 OK、時間: 0.015000秒
ユーザー a から * を選択し、 (ユーザーから id を選択し、性別 = 1、制限 1000000、10) b を a.id=b.id で結合します。 OK、時間: 0.151000秒
ユーザー a から * を選択して、 (ユーザーから id を選択して、性別 = 1、制限 10000000, 10) b を a.id=b.id で結合します。 OK、時間: 1.161000秒

案の定、実行効率が大幅に向上しました。

条件付きフィルタリング

もちろん、並べ替えに基づいて条件付きフィルタリングを行うという、欠陥のあるアプローチもあります。

たとえば、上記のユーザー テーブルでは、制限ページングを使用して 1000001 から 1000010 までのデータを取得したいと考えています。次のような SQL を記述できます。

性別が 1 で ID が 1000000 の場合、user から * を選択し、> (性別が 1 の場合、user から ID を選択し、limit 1000000, 1) を 10 に制限します。

ただし、この最適化方法は条件付きであり、主キー ID が正しい順序になっている必要があります。順序付けられた条件下では、主キー ID の代わりに作成時間などの他のフィールドを使用することもできますが、前提条件としてこのフィールドにインデックスが付けられていることが必要です。

つまり、条件付きフィルタリングを使用して制限を最適化するには多くの制限があります。一般的に、最適化にはカバーリング インデックスを使用することをお勧めします。

まとめ

この論文では、主に大きなオフセットによる制限ページングが遅い理由を分析し、対応する最適化ソリューションも提案しています。大きなオフセットによる制限ページングの実行時間が長い問題を最適化するために、カバーリング インデックスの使用を推奨しています。

これが皆様のお役に立てば幸いです。

上記は、MySQL の制限ページングが大きなオフセットで遅くなる理由と最適化ソリューションの詳細な内容です。MySQL の制限ページングの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL最適化ソリューション: スロークエリログを有効にする
  • MySQL 選択最適化ソリューションに関する簡単な説明
  • MySQLクエリ最適化: 100万件のデータに対するテーブル最適化ソリューション
  • MySQLの大規模テーブル最適化ソリューションについての簡単な説明
  • MySQL 関数インデックス最適化ソリューション
  • MySQL 最適化ソリューション リファレンス
  • MySQLの一般的な最適化ソリューション

<<:  Vue が配列の変更を監視できない問題の解決方法

>>:  VMware 仮想マシンのインストール Apple Mac OS の超詳細なチュートリアル

推薦する

Vueグローバルカスタム命令の実践 モーダルドラッグ

目次背景実装のアイデア成果を達成する背景最近取り組んでいるプロジェクトは、Vue2 で構築されたプロ...

良いリファクタリングを行うには、コードをリファクタリングするだけでなく、人生をリファクタリングすることも重要です。

職業的な観点からも、人生の観点からも、良い再建をすることは本当に簡単ではありません。楽観的で熱心で前...

JavaScriptはブラウザがIEかどうかを判定します

フロントエンド開発者としては、IEの落とし穴は避けて通れません。他のブラウザはいいのにIEは壊れてい...

dockerコンテナがIP経由でホストマシンにアクセスできない問題を解決する方法の詳細な説明

問題の起源docker を使用する場合、残念ながら docker コンテナ内のホストのポート 80 ...

MySQL のデッドロックとデータベースおよびテーブル シャーディングの問題の詳細な説明

MySQL 運用上の問題点を記録します。ビジネスシナリオと問題の説明外部インターフェースをリクエスト...

Vueプロジェクトでvuexを使用する方法

目次Vuex とは何ですか? Vuex 使用サイクル図私のストアディレクトリvuexの例の実装要約す...

Reactでのイベントバインディングの実装は3つの方法を指しています

1. 矢印関数1. 矢印関数自体はこれをバインドしないという事実を利用します。 2. render(...

MySQL スロークエリログの役割と公開

序文MySQL スロー クエリ ログは、MySQL が提供するログ レコードの一種です。これは、応答...

Vue プロジェクトに ECharts を導入する

目次1. インストール2. はじめに3. 使用4. 必要に応じてEChartsチャートとコンポーネン...

Windows システムに mysql5.7.21 をインストールするための詳細なチュートリアル

MySQL インストーラーは、MySQL ソフトウェアのあらゆるニーズに対応する、使いやすいウィザー...

CSSのborder-radiusプロパティを使用して円弧を設定します

現象: divを一定の振幅で円、楕円などに変更する方法: CSSのborder-radiusプロパテ...

Vue で pdfjs を使用して PDF ファイルをプレビューする方法

目次序文考えるライブラリディレクトリの解析とダウンロード使い方ファイルの場所実際の通話質問要約する序...

ウェブ画像形式としてPNG、JPG、GIFを選択して使用する方法

では、GIF、PNG、JPG のどの形式を候補形式として選択すればよいのでしょうか。また、どの画像形...

dockerでpdflatex環境を設定する方法

技術的背景Latex は文書作成、特に記事作成には欠かせないツールであり、必須のテキスト組版ツールで...

CSSのマッチング問題を解決する

問題の説明ご存知のとおり、CSS を記述する場合、HTML のクラスの定義または ID の定義に従っ...