MySQL ページングクエリ最適化テクニック

ページング クエリを使用するアプリケーションでは、LIMIT と OFFSET を含むクエリが非常に一般的であり、そのほとんどすべてに ORDER BY 句が含まれます。インデックス ソートを使用すると、パフォーマンスの最適化に非常に役立ちます。そうでない場合、サーバーは大量のファイル ソートを実行する必要があります。

よくある問題は、オフセット値が大きすぎることです。クエリが LIMIT 10000、20、10020 行が生成され、前の 10000 行は破棄されるため、コストが非常に高くなります。すべてのページが同じ頻度でアクセスされると仮定すると、このようなクエリは平均してテーブルの半分をスキャンします。最適化するには、ページ分割されたビューでアクセスできるページの最大数を制限したり、大規模でコストの低いクエリをより効率的にしたりできます。

パフォーマンスを向上させる簡単な方法は、行全体ではなくカバーインデックスに対してクエリを実行することです。結果を完全な行と結合し、必要な追加の列を取得できます。これは、たとえば次のクエリのように、より効率的になります。

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

データ テーブルが大きい場合は、次のように最適化できます。

film.film_id、film.descriptionを選択します。
sakila.filmより
	内部結合（
    sakila.film から film_id を選択
    タイトルで注文 制限 50, 5)
  ) を lim USING(film_id); として使います。

この「推論された結合クエリ」は、インデックスを使用してサーバーがデータを確認するためにアクセスする行数を減らすため、効果的に機能します。レビューに必要な行が見つかったら、それらの行は対応するデータ テーブルの行と結合され、対応する行の他の列が取得されます。

場合によっては、制限を固定位置クエリに変換することもできます。これは、インデックスの範囲スキャンによって完了できます。たとえば、position という固定位置の列を事前計算する場合、クエリを次のように書き換えることができます。

sakila.film から film_id、description を選択
WHERE 位置が 50 から 54 の間である場合、ORDER BY 位置;

ソートされたデータも同様の方法で処理できますが、通常は GROUP BY 操作の影響を受けます。ほとんどの場合、ソート値は事前に計算して保存する必要があります。

LIMIT と OFFSET の本当の問題は OFFSET であり、これはサーバーが多くの行を破棄することを意味します。順序付けされたブックマークを使用して、取得する次の行の位置を記録すると、最後の位置から次のデータにアクセスできます。たとえば、最新のレンタル レコードから開始して逆方向に作業しながらレンタル レコードをページ分割する必要がある場合、レコードの主キーは常に増加するという事実に頼ることができるため、次のようにデータの最初のページをクエリできます。

sakila.rentalから*を選択
rental_id で並べ替え、DESC LIMIT 20 を指定します。

このクエリは 16049 から 16030 までのデータを返します。次のクエリは、前のクエリが終了したところから開始できます。

sakila.rentalから*を選択
レンタルID < 16030 
rental_id で並べ替え、DESC LIMIT 20 を指定します。

このトリックは、クエリを開始するオフセットがどれだけ離れていても機能します。

その他の手法としては、事前に計算された統計情報の使用や、冗長な主キーとソート列を持つテーブルを結合してクエリを実行することなどが挙げられます。どちらも、スペースを時間と交換することでクエリの効率を向上させます。

上記は、MySQL ページングクエリの最適化手法の詳細です。MySQL ページングクエリの最適化の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。