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と実行時間
ご覧のとおり、制限オフセットが大きいほど、実行時間が長くなります。 原因分析 まず、上記の表の最初の行を例に、この SQL ステートメントの実行プロセスを分析してみましょう。 sex 列はインデックス列なので、MySQL は sex インデックス ツリーをたどり、sex=1 のデータを見つけます。 次に、非クラスター化インデックスに主キー ID の値が格納され、クエリ ステートメントですべての列のクエリが必要なため、ここでテーブル リターンが発生します。性別インデックス ツリーで値が 1 のデータにヒットした後、そのリーフ ノードの値、つまり主キー ID の値を使用して、主キー インデックス ツリーのこの行の他の列 (名前、性別) の値をクエリし、最終的に結果セットに返して、最初の行のデータが正常にクエリされるようにします。 最後の SQL ステートメントでは、limit 100, 10 が必要です。これは、101 から 110 までのデータを照会することを意味します。ただし、MySQL は最初の 110 行を照会し、最初の 100 行を破棄します。最終的に、結果セットには 101 から 110 行のみが残り、実行は終了します。 まとめると、上記の実行プロセスにおいて、オフセットが大きい制限の実行時間が長くなる理由は次のとおりです。
上記の 2 つの理由を組み合わせると、MySQL はテーブルの戻りに多くの時間を費やし、テーブルの戻りの結果が結果セットに表示されず、クエリ時間がどんどん長くなります。 最適化計画 カバーインデックス 無効なテーブル戻りがクエリの遅延の主な原因であるため、最適化計画はテーブル戻りの数を減らすことです。制限 a、b では、まずデータ a+1 から a+b の ID を取得し、次にテーブルに戻って他の列のデータを取得するとします。このようにして、テーブル戻りの数が減り、速度が確実に大幅に速くなります。 これにはカバーリング インデックスが含まれます。いわゆるカバーリング インデックスを使用すると、テーブル リターンを通じて主キー インデックスから他の列をクエリしなくても、非主クラスター化インデックスから必要なデータを取得できるため、パフォーマンスが大幅に向上します。 この考え方に基づく最適化ソリューションは、まず主キー ID をクエリし、次に主キー ID に基づいて他の列データをクエリすることです。最適化された SQL と実行時間を次の表に示します。
案の定、実行効率が大幅に向上しました。 条件付きフィルタリング もちろん、並べ替えに基づいて条件付きフィルタリングを行うという、欠陥のあるアプローチもあります。 たとえば、上記のユーザー テーブルでは、制限ページングを使用して 1000001 から 1000010 までのデータを取得したいと考えています。次のような SQL を記述できます。 性別が 1 で ID が 1000000 の場合、user から * を選択し、> (性別が 1 の場合、user から ID を選択し、limit 1000000, 1) を 10 に制限します。 ただし、この最適化方法は条件付きであり、主キー ID が正しい順序になっている必要があります。順序付けられた条件下では、主キー ID の代わりに作成時間などの他のフィールドを使用することもできますが、前提条件としてこのフィールドにインデックスが付けられていることが必要です。 つまり、条件付きフィルタリングを使用して制限を最適化するには多くの制限があります。一般的に、最適化にはカバーリング インデックスを使用することをお勧めします。 まとめ この論文では、主に大きなオフセットによる制限ページングが遅い理由を分析し、対応する最適化ソリューションも提案しています。大きなオフセットによる制限ページングの実行時間が長い問題を最適化するために、カバーリング インデックスの使用を推奨しています。 これが皆様のお役に立てば幸いです。 上記は、MySQL の制限ページングが大きなオフセットで遅くなる理由と最適化ソリューションの詳細な内容です。MySQL の制限ページングの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。 以下もご興味があるかもしれません:
|
>>: VMware 仮想マシンのインストール Apple Mac OS の超詳細なチュートリアル
目次背景実装のアイデア成果を達成する背景最近取り組んでいるプロジェクトは、Vue2 で構築されたプロ...
職業的な観点からも、人生の観点からも、良い再建をすることは本当に簡単ではありません。楽観的で熱心で前...
フロントエンド開発者としては、IEの落とし穴は避けて通れません。他のブラウザはいいのにIEは壊れてい...
問題の起源docker を使用する場合、残念ながら docker コンテナ内のホストのポート 80 ...
MySQL 運用上の問題点を記録します。ビジネスシナリオと問題の説明外部インターフェースをリクエスト...
目次Vuex とは何ですか? Vuex 使用サイクル図私のストアディレクトリvuexの例の実装要約す...
1. 矢印関数1. 矢印関数自体はこれをバインドしないという事実を利用します。 2. render(...
序文MySQL スロー クエリ ログは、MySQL が提供するログ レコードの一種です。これは、応答...
目次1. インストール2. はじめに3. 使用4. 必要に応じてEChartsチャートとコンポーネン...
MySQL インストーラーは、MySQL ソフトウェアのあらゆるニーズに対応する、使いやすいウィザー...
現象: divを一定の振幅で円、楕円などに変更する方法: CSSのborder-radiusプロパテ...
目次序文考えるライブラリディレクトリの解析とダウンロード使い方ファイルの場所実際の通話質問要約する序...
では、GIF、PNG、JPG のどの形式を候補形式として選択すればよいのでしょうか。また、どの画像形...
技術的背景Latex は文書作成、特に記事作成には欠かせないツールであり、必須のテキスト組版ツールで...
問題の説明ご存知のとおり、CSS を記述する場合、HTML のクラスの定義または ID の定義に従っ...