MySQL ディープページング (数千万のデータを素早くページ分割する方法)

序文

バックエンド開発では、一度に大量のデータがロードされ、メモリやディスク IO のオーバーヘッドが過剰になることを防ぐために、ページング表示が必要になることがよくあります。このとき、MySQL の LIMIT キーワードが必要になります。しかし、LIMIT ページングですべてがうまくいくと思いますか? まだ若すぎて単純すぎます。データ量が多い場合、LIMIT が引き起こす可能性のある問題の 1 つは、ディープページングです。

場合

ここでは、電子商取引の注文詳細の表示を例にとります。新しいテーブルは次のようになります。

テーブル `cps_user_order_detail` を作成します (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主キー',
  `user_id` varchar(32) NOT NULL DEFAULT '' COMMENT 'ユーザーID',
  `order_id` bigint(20) デフォルト NULL コメント '注文ID',
  `sku_id` bigint(20) unsigned NOT NULL COMMENT '製品ID',
  `order_time` datetime DEFAULT NULL COMMENT '注文時間、形式 yyyy-MM-dd HH:mm:ss',
   主キー (`id`)、
   キー `idx_time_user` (`order_time`,`user_id`) BTREE の使用
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='ユーザー注文詳細';

次に、120 万件のレコードを手動でテーブルに挿入します。
ここで、ユーザーの注文詳細を注文時間の逆順にページに表示するという要件があります。
テーブル構造は合理化されており、要件はシンプルです。そこで、すぐにコードを書き終えて、テスト用にオンラインに公開しました。当初はすべて正常に動作していましたが、注文量が増え続けるにつれて、システムは次第に遅くなり、いくつかの慢查詢時々報告されました。
この時点で、これは LIMIT オフセットの問題であると考えるべきです。そうです、SQL が十分に美しくないのではなく、MySQL 自体のメカニズムの問題なのです。
ここでは、次の図に示すように、それぞれ 100 と 100 万の位置オフセットからページングする 2 つの SQL ステートメントを例として取り上げます。時間差が非常に大きいことがわかります。これには、他のデータの計算や処理にかかる時間は含まれません。1 回の SQL クエリには 1 秒以上かかるため、ユーザーに提供される機能では許容できません (電子商取引では、インターフェイスの RT が 200 ミリ秒を超えないことが求められることがよくあります)。

ここでは、以下に示すように実行プランを見てみましょう。

ここではまず、実行プランの Extra 列の可能な値と意味を紹介します。

where: を使用すると、オプティマイザーはインデックスを介してテーブルにデータをクエリする必要があることを示します。
インデックスの使用: カバーリングインデックスとは、インデックスを介してテーブルに戻らなくても、インデックスに直接アクセスするだけで必要なデータを取得できることを意味します。これは通常、クエリ対象のフィールドの結合インデックスを作成することで実現されます。
インデックス条件の使用: バージョン 5.6 以降に追加された新しい機能である有名なインデックスプッシュダウンは、減少回表次數ための MySQL の主要な最適化です。
filesort の使用: ファイルのソート。これは通常、ORDER BY 中に行われます。データの量が多すぎる場合、MySQL はソートのためにすべてのデータをメモリに呼び戻すため、より多くのリソースが消費されます。

上の図を見ると、オフセットが異なるだけで、同じステートメントでも実行プランが大きく異なっていることがわかります (少し誇張して表現します)。最初のステートメントLIMIT 100,6 type 列の値がrangeであり、これは範囲スキャンを示しています。 refもパフォーマンスが 1 レベル低くなりますが、インデックスを使用することも考慮されており、インデックスプッシュダウンも適用されます。つまり、WHERE 後の ORDER BY 時にインデックスが削除および選択され、後続の ORDER BY もインデックスプッシュダウンに基づいて最適化され、WHERE 条件がフィルタリングされるときに同期的に実行されます (テーブルに戻らずに)。
2 番目のステートメントLIMIT 1000000,6インデックスをまったく使用せず、 type 列の値はALLであり、明らかに完全なテーブルスキャンです。 Extra 列の「Using where」はテーブルの戻りが発生することを示し、「Using filesort」は ORDER BY 中にファイルの並べ替えが発生することを示します。ここで速度が遅くなる理由は 2 つあります。1 つ目は、ファイルのソートに時間がかかりすぎること、2 つ目は、条件に従って関連データをフィルタリングした後、オフセットに基づいてテーブルに戻り、すべての値を取得する必要があることです。上記のいずれの点においても、LIMIT オフセットが大きすぎることが原因であるため、実際の開発環境では、非統計テーブルレベルが 100 万を超えてはならないという要件に遭遇することがよくあります。

最適化

原因が分析されたので、実際の開発で LIMIT ディープページングを最適化するにはどうすればよいでしょうか。ここで 2 つの解決策を紹介します。
1 つは主キーインデックスの最適化です。それはどういう意味ですか？上記の文を次のように変更します。

SELECT * FROM cps_user_order_detail d WHERE d.id > #{maxId} AND d.order_time>'2020-8-5 00:00:00' ORDER BY d.order_time LIMIT 6;

上記のコードに示されているように、これもページ分割されていますが、maxId 制限があります。これは何を意味するのでしょうか。maxId は、前のページの最大の主キー ID です。したがって、この方法を使用する前提は次のとおりです。1) 主キーは自動増分である必要があり、UUID にすることはできません。また、基本的なページングパラメータ pageNo、pageSize を渡すだけでなく、フロントエンドは前の各ページの最大 ID も取得する必要があります。2) この方法はランダムなページジャンプをサポートしていません。つまり、ページを上下に移動することしかできません。次の図は、有名な電子商取引会社の実際のページを示しています。

2 つ目は、Elastic Search 検索エンジン最適化 (転置インデックスに基づく) です。実際、Taobao などの電子商取引企業は、基本的にすべての製品を ES 検索エンジンに入れています (このような膨大なデータを MySQL に入れるのは不可能であり、Redis に入れるのは現実的ではありません)。しかし、ES 検索エンジンを使用しても、ディープページングの問題が発生する可能性があります。その場合はどうすればよいでしょうか?答えはカーソルスクロールを通じてです。この点についてはここでは詳しく説明しません。興味のある方は調べてみてください。

まとめ

このブログを書いたのは、以前開発中に実際にそれを経験し、Byte のインタビューで面接官とそれについて話し合ったからです。 LIMIT の制限と最適化について知っていると、面接でそのことを伝えることができればプラスになります。MySQL の最適化はインデックスの構築と SQL の調整だけだと言わないでください (実際、実際の開発では、これら 2 つの最適化ソリューションの効果は最小限です)。結局のところ、MySQL の最適化がそれほど素晴らしいのであれば、ミドルウェアはそれほど多くないはずです。

これで、MySQL ディープページング (数千万のデータを素早くページ分割する方法) に関するこの記事は終了です。MySQL ディープページングの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: