数千万の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 列の可能な値と意味を紹介します。

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

上の図を見ると、同じステートメントでも、オフセットが異なるだけで、実行プランに大きな違いが生じます (少し誇張して表現してもかまいません)。最初のステートメントでは、LIMIT 100,6type 列の値が range であり、これは範囲スキャンを示しています。ref よりもパフォーマンスが 1 レベル低くなりますが、インデックスを使用することも考慮されており、インデックス プッシュダウンも適用されます。つまり、WHERE 後の順序時にインデックスが削除および選択され、後続の 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 の最適化がそれほど素晴らしいのであれば、ミドルウェアはそれほど多くないはずです。
私は Shaoxia Lu Fei です。テクノロジーと共有が大好きです。

上記は、MySQL で数千万のデータを迅速にページ分割する方法の詳細です。MySQL の高速ページ分割の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。