効率的なMySQLページングの詳細な説明

序文

通常、大量のデータを扱う MySQL クエリには「ページング」戦略が採用されます。ただし、ページを後の位置に移動すると、MySQL は破棄する必要のあるデータをスキャンするのに多くの時間を費やすため、クエリは非常に遅くなります。

基本的なページングテクニック

通常、効率的なページングを実現するには、クエリ内の WHERE 条件列とソート列に結合インデックスを適用する必要があります。
たとえば、インデックス (a, b, c) を作成すると、次のクエリでインデックスを使用できるようになり、クエリの効率が向上します。

1. フィールドの並べ替え

注文する 
a,b で順序付け
a、b、c で​​順序付け 
ORDER BY a DESC、b DESC、c DESC 

2. フィルタリングと並べ替え

a = const ORDER BY b, c の場合 
a = const かつ b = const の場合、c で ORDER BY する 
a = const ORDER BY b, c の場合 
a = const かつ b > const の場合、b, c で ORDER BY する 

3. 次のクエリは上記のインデックスを使用できません

ORDER BY a ASC, b DESC, c DESC // ソート方向が一貫していません WHERE g = const ORDER BY b, c // フィールド g はインデックスの一部ではありません WHERE a = const ORDER BY c // フィールド b は使用されていません 
WHERE a = const ORDER BY a, d // フィールド d はインデックスの一部ではありません

大量のデータのページめくりの問題を解決

1. LIMIT M,NのクエリをLIMIT Nに変更します。
たとえば、LIMIT 10000,20 の場合、MySQL は最初の 10,000 行を読み取ってから次の 20 行を取得する必要があり、これは非常に非効率的です。LIMIT N を使用すると、各ページの最初または最後のレコードの ID でフィルタリングし、降順と昇順を使用して前/次のページの結果セットを取得できます。
2. ユーザーが閲覧できるページ数を制限します。実際の製品使用では、ユーザーが 10,000 番目の検索結果を気にすることはほとんどありません。
3.遅延連想法を使う
カバーリング インデックスを使用してクエリを実行し、必要な主キーを返し、返された主キーを元のテーブルに関連付けて必要な行を取得することで、Mysql がスキャンして破棄する必要がある行の数を減らすことができます。

例:
インデックス (性別、評価) を使用したクエリ:

mysql> SELECT <cols> FROM profiles INNER JOIN (
-> SELECT <主キー列> FROM プロファイル
-> WHERE x.sex='M' ORDER BY 評価 LIMIT 100000, 10
-> ) AS x USING(<主キーの列>);

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。