制限を使用すると、MySQL のページングがどんどん遅くなるのはなぜですか?

制限を使用すると、MySQL のページングがどんどん遅くなるのはなぜですか?

Aniu は新しい会社に入社したばかりです。彼の最初の仕事は、条件に基づいて注文テーブルのデータをファイルにエクスポートすることでした。Aniu は「これは簡単すぎる」と考え、すぐに次のステートメントを書いて、自分のコードは免除対象製品であることをテスターに​​伝えました。

声明は次のとおりです。

name='lilei' かつ create_time>'2020-01-01 00:00:00' の注文から * を選択 limit start,end

予想外に、しばらくオンラインになった後、本番環境では早期警告が発行され始め、この SQL は実行時間が 50 秒を超える低速 SQL であり、ビジネスに重大な影響を与えていることがわかりました。
アニウはすぐに兄のユアンユアンに原因究明を手伝ってくれるよう頼みました。ユアンユアンはすぐに問題を解決し、アニウのために次の実験を行いました。

1. テスト実験

MySQL ページングでは、limit start、count ページング ステートメントを直接使用します。

製品制限開始、カウントから*を選択

開始ページが小さい場合、クエリのパフォーマンスの問題はありません。次のように、10、100、1000、10000 (ページあたり 20 レコード) からのページングの実行時間を見てみましょう。

積限界から * を選択 10, 20 0.016 秒 積限界から * を選択 100, 20 0.016 秒 積限界から * を選択 1000, 20 0.047 秒 積限界から * を選択 10000, 20 0.094 秒

開始レコードが増加すると時間も増加することがわかりました。これは、ページング ステートメントの制限が開始ページ番号と密接に関係していることを示しています。
次に、開始レコードを40w(レコードの約半分)に変更します。

製品制限400000、20から*を選択 3.229秒

最後のページの記録が出てくる時間を見てみましょう

製品制限 866613 から * を選択、20 37.44 秒

このようにページ番号が最も大きいページの場合、この時間は明らかに耐えられないほど長いです。
このことから、次の 2 つのことが分かります。
制限ステートメントのクエリ時間は、開始レコードの位置に比例します。
MySQL の limit ステートメントは非常に便利ですが、レコード数が多いテーブルに直接使用するには適していません。

2. 制限ページング問題に対するパフォーマンス最適化手法

2.1 テーブルをカバーするインデックスを使用してページングクエリを高速化する

インデックス クエリを使用するステートメントにそのインデックス列 (カバー インデックス) のみが含まれている場合、クエリが非常に高速になることは誰もが知っています。
インデックス検索には最適化されたアルゴリズムがあり、データはクエリ インデックス上にあるため、関連するデータ アドレスを探す必要がなくなり、時間を大幅に節約できます。
また、MySQL には関連するインデックス キャッシュもあり、同時実行性が高い場合にキャッシュを使用すると効果が高まります。
この例では、id フィールドが主キーであることがわかっているため、デフォルトの主キー インデックスが含まれます。次に、カバーリング インデックスを使用したクエリがどのように実行されるかを見てみましょう。
今回は、次のように、最後のページのデータをクエリします (id 列のみを含むカバー インデックスを使用)。

製品制限 866613, 20 から ID を選択

クエリ時間は 0.2 秒で、すべての列をクエリするのに必要な 37.44 秒よりも約 100 倍高速です。
すべての列をクエリする場合も、2 つの方法があります。

2.2 id>= 形式の使用:

製品から*を選択 
ID > =(製品制限 866613 から ID を選択、1) 制限 20

クエリ時間は 0.2 秒であり、これは質的な飛躍です。

2.3 結合の使用

製品aから*を選択 
JOIN (製品制限 866613, 20 から ID を選択) b ON a.ID = b.id

要約:

理由を述べていないと思いますか?その理由は、select * を使用する場合、limit 600000 が直接使用され、10 回のスキャンは約 600,000 のデータであり、テーブルに 600,000 回戻る必要があるためです。つまり、パフォーマンスの大部分はランダム アクセスで消費され、最終的に 10 のデータのみが使用されることになります。最初に ID を見つけてから、関連付けによってレコードをクエリすると、インデックスが条件を満たす ID をすばやく見つけて、テーブルに 10 回戻ることができるため、はるかに高速になります。必要なデータを取得できます。

これで、MySQL ページングが制限によってどんどん遅くなる理由についての記事は終わりです。MySQL ページング制限の遅さの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL クエリの最適化: LIMIT 1 はテーブル全体のスキャンを回避し、クエリの効率を向上させます
  • MySQL 最適化 query_cache_limit パラメータの説明
  • MySQLのorder byとlimitを混在させる際の落とし穴の詳細な説明
  • MySQL ページングの制限パラメータの簡単な例
  • 大きなオフセットによる MySQL 制限ページングが遅い理由と最適化ソリューション
  • MySQL のソートとページング (order by と limit) と既存の落とし穴
  • MySQLの制限を使用して大規模なページングの問題を解決する方法
  • MySQL における制限関数と合計関数の混在使用の問題の詳細な説明
  • MySQL Limitクエリのパフォーマンスを向上させる方法
  • MySQL Limitパフォーマンス最適化とページングデータパフォーマンス最適化の詳細な説明
  • MySQL の制限ページング最適化ソリューションの実装に関する簡単な説明
  • MySQL のクエリパフォーマンスに対する制限の影響

<<:  最新の高品質な英語無料フォント36個を公開

>>:  Docker イメージの最適化 (1.16GB から 22.4MB)

推薦する

JavaScript カスタム カレンダー効果

この記事では、JavaScriptカスタムカレンダーエフェクトの具体的なコードを参考までに紹介します...

Docker イメージ管理の一般的な操作コード例

ミラーリングも Docker のコアコンポーネントの 1 つです。ミラーリングはコンテナ操作の基盤で...

MySQL GTID マスターとスレーブの不一致を修復するソリューション

目次解決策1: レプリカを再構築する前提条件アドバンテージ欠点手順マスター奴隷解決策2: データ修復...

MySQL SQL 最適化チュートリアル: IN クエリと RANGE クエリ

まず、in() クエリについて説明します。 「High Performance MySQL」では、イ...

MySQLデータの重複チェックと重複排除の実装ステートメント

テーブル user があり、フィールドは id、nick_name、password、email、p...

MySql のインデックス、ロック、トランザクションの知識ポイントのまとめ

この記事では、MySql のインデックス、ロック、トランザクションに関する知識のポイントをまとめてい...

CentOS 7.x のマスターおよびスレーブ DNS サーバーの展開

1. 準備例: 2 台のマシン: 192.168.219.146 (マスター)、192.168.21...

MySQL でテーブルデータをクリアする 2 つの方法とその違い

MySQL でデータを削除するには 2 つの方法があります。切り詰めは大まかな伐採の一種である削除は...

Docker ベースの MySQL マスタースレーブレプリケーション環境を構築するための実装手順

1. はじめに以前のプログラム アーキテクチャは次の形式になります。プログラムのサイズが大きくなると...

JavaScriptの強力な演算子をいくつか見てみましょう

目次序文1. ヌル合体演算子2. ??= 空代入演算子3. ?. オプション連鎖演算子4. ?: 三...

div が iframe に覆われるいくつかの状況とその解決策

類似の構造:コードをコピーコードは次のとおりです。 <div></div>&...

XHTML チュートリアル: Transitional と Strict の違い

実際、XHTML 1.0 は、Transitional DOCTYPE と Strict DOCTY...

div の高さをブラウザの高さに合わせて調整する方法

この古くからある疑問は、数え切れないほどのフロントエンド開発者やバックエンドプログラマーを悩ませてき...

MySQL 8.0 WITH クエリの詳細

目次MySQL 8 の WITH クエリについて学ぶ1. 例3. 練習するMySQL 8 の WIT...

mysql8.0.12 でルートパスワードをリセットする方法

データベースをインストールした後、誤ってインストール ウィンドウを閉じたり、長期間 root ユーザ...