MySQLの制限を使用して大規模なページングの問題を解決する方法

序文

日常の開発では、MySQL を使用してページングを実装する場合、常に MySQL 制限構文を使用します。ただし、その使用方法は非常に特殊なので、今日はそれをまとめてみましょう。

制限構文

limit 構文は、offset と limit の 2 つのパラメータをサポートします。前者はオフセットを示し、後者は最初の limit データを取得することを示します。

例えば：

## 条件を満たす最初の10個のステートメントを返す select * from user limit 10

## 条件を満たす11番目から20番目のデータを返す select * from user limit 10,20

上記から、限界 n は限界 0,n と同等であることもわかります。

パフォーマンス分析

実際の使用では、ページングの後ろにある一部のページの読み込みが遅くなることがわかります。つまり、次のようになります。

ユーザー制限 1000000,10 から * を選択

ステートメントの実行は遅いです。まずはテストしてみましょう。

まず、小さなオフセットで 100 個のデータを取得します (データの総量は約 200 個)。その後、オフセットを徐々に増やします。

select * from user limit 0,100 ---------時間消費 0.03秒
select * from user limit 10000,100 ---------時間消費 0.05秒
select * from user limit 100000,100 ---------時間消費 0.13秒
select * from user limit 500000,100 ---------時間消費 0.23秒
select * from user limit 1000000,100 ---------時間消費 0.50秒
select * from user limit 1800000,100 ---------時間消費 0.98秒

オフセットが増加するにつれて、パフォーマンスがどんどん悪化していくことがわかります。

なぜでしょうか? limit 10000,10 の構文は、実際には MySQL がデータの最初の 10010 行を見つけ、最初の 10000 行を破棄することを意味しているためです。このステップは実際には無駄です。

最適化

IDで最適化

まず、最後のページングの最大 ID を見つけ、次に、select * from user where id>1000000 limit 100 と同様に、ID のインデックスを使用してクエリを実行します。
これは主キーがインデックス化されるため非常に効率的ですが、ID が連続している必要があり、where ステートメントによってデータがフィルター処理されるためクエリに where ステートメントを含めることができないという欠点があります。

カバーインデックスによる最適化

MySQL クエリがインデックスに完全にヒットすると、カバーリング インデックスと呼ばれます。これは、クエリがインデックスを検索するだけで済み、データを取得するためにデータ テーブルに戻ることなく直接戻ることができるため、非常に高速です。したがって、最初にインデックス ID を見つけてから、その ID に基づいてデータを取得できます。

select * from (select id from job limit 1000000,100) a left join job b on a.id = b.id;

これには0.2秒かかりました。

要約する

確かに、MySQL を使用して大量のデータをページ分割するのは困難ですが、最適化する方法はいくつかあり、ビジネス シナリオと組み合わせてさらにテストを行う必要があります。
ユーザーが 10,000 ページに移動したときに、空を返さないのはなぜですか? それはそんなに退屈ですか?

さて、今回の記事は以上です。この記事の内容が皆さんの勉強や仕事に少しでも参考になれば幸いです。123WORDPRESS.COM をよろしくお願いいたします。