大きなオフセットによる 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と実行時間

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

原因分析

まず、上記の表の最初の行を例に、この 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 と実行時間を次の表に示します。

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

条件付きフィルタリング

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

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

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

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

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

まとめ

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

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

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