数千万のデータを扱うMySQLのページングクエリのパフォーマンスを最適化する

MySQL のデータ量が多い場合、制限ページングが使用されます。ページ数が増えると、クエリの効率が低下します。

実験

1. limit start、count paging ステートメントを直接使用します。

select * from order 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 に変更して確認してみましょう。

select * from order limit 400000, 20 3.229秒

最後の記録ページを撮った時間を見てみましょう。

select * from order limit 800000, 20 37.44秒

明らかにこの時間は耐え難いものです。

このことから、次の 2 つのことが分かります。

1) 制限ステートメントのクエリ時間は開始レコードの位置に比例します。

2) MySQL の limit ステートメントは非常に便利ですが、レコード数が多いテーブルに直接使用するには適していません。

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

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

インデックス クエリを使用するステートメントにそのインデックス列 (カバー インデックス) のみが含まれている場合、クエリが非常に高速になることは誰もが知っています。

インデックス検索には最適化されたアルゴリズムがあり、データはクエリ インデックス上にあるため、関連するデータ アドレスを探す必要がなくなり、時間を大幅に節約できます。また、MySQL には関連するインデックス キャッシュもあり、同時実行性が高い場合にキャッシュを使用すると効果が高まります。

この例では、id フィールドが主キーであることがわかっているため、デフォルトの主キー インデックスが含まれます。次に、カバーリング インデックスを使用したクエリがどのように実行されるかを見てみましょう。

今回は、次のように、最後のページのデータをクエリします (id 列のみを含むカバー インデックスを使用)。

select id from order limit 800000, 20 0.2秒

すべての列をクエリするのにかかる 37.44 秒と比較すると、速度は 100 倍以上向上します。

すべての列をクエリする場合、2 つの方法があります。1 つは id>= 形式、もう 1 つは結合を使用する方法です。実際の状況を確認してください。

SELECT * FROM order WHERE ID > =(select id from order limit 800000, 1) limit 20

クエリ時間は0.2秒で、これは質的な飛躍です（笑）

別の書き方

SELECT * FROM order a JOIN (select id from order limit 800000, 20) b ON a.ID = b.id

クエリ時間も非常に短い