MySQLのページング制限のパフォーマンス問題についての簡単な説明

MySQL ページング クエリは通常、制限を通じて実装されます。 limit は 1 つまたは 2 つの整数パラメータを受け入れます。パラメータが 2 つの場合、最初のパラメータは返される最初のレコード行のオフセットを指定し、2 番目のパラメータは返されるレコード行の最大数を指定します。初期レコード行オフセットは 0 です。 PostgreSQL との互換性のため、limit は limit # offset # もサポートします。

質問：

オフセットが小さい場合は、limit を直接使用してクエリを実行しても問題ありません。ただし、データ量が増えると、limit ステートメントのオフセットが大きくなり、ページングが進むにつれて速度が大幅に低下します。

最適化のアイデア: データ量が多い場合は、レコードをスキャンしすぎないようにする

解決策: サブクエリ ページング メソッドまたは JOIN ページング メソッド。 JOIN ページングとサブクエリ ページングの効率は基本的に同じレベルであり、消費される時間も基本的に同じです。

ここに例があります。一般的に、MySQL の主キーは自動増分数値型です。この場合、最適化には次の方法が使用できます。

実際の運用環境で 60,000 件のレコードを含むテーブルを例に、最適化前後のクエリ時間を比較します。

-- 従来の制限、ファイルスキャン [SQL]
SELECT * FROM tableName ORDER BY id LIMIT 50000,2;
影響を受ける行: 0
時間: 0.171秒

-- サブクエリ方式、インデックススキャン [SQL]
テーブル名から * を選択
WHERE id >= (SELECT id FROM tableName ORDER BY id LIMIT 50000 , 1)
制限 2;
影響を受ける行: 0
時間: 0.035秒

-- JOIN ページングメソッド [SQL]
テーブル名からt1を選択する
JOIN (SELECT id FROM tableName ORDER BY id LIMIT 50000, 1) AS t2
t1.id <= t2.id の場合、t1.id によって LIMIT 2 が設定されます。
影響を受ける行: 0
時間: 0.036秒

最適化後、パフォーマンスが何倍も向上していることがわかります。

最適化の原則:

サブクエリはインデックスに対して実行され、通常のクエリはデータ ファイルに対して実行されます。一般的に、インデックス ファイルはデータ ファイルよりもはるかに小さいため、より効率的に操作できます。すべてのフィールドの内容を取得する必要があるため、最初の方法では多数のデータ ブロックにまたがって取得する必要がありますが、2 番目の方法では基本的にインデックス フィールドに従って検索した後、対応する内容を直接取得するため、当然ながら効率が大幅に向上します。したがって、制限を最適化するには、制限を直接使用するのではなく、まずオフセット ID を取得し、次に制限サイズを直接使用してデータを取得します。

実際のプロジェクトでは、同様の戦略パターンを使用してページングを処理できます。たとえば、1 ページあたり 100 件のレコードがある場合、ページ数が 100 未満の場合は最も基本的なページング方法が使用されますが、ページ数が 100 を超える場合はサブクエリ ページング方法が使用されます。

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