制限を使用すると、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 をよろしくお願いいたします。