Mysqlページング&関連クエリーの最適化

以下は『高性能Mysql』を参考に
関連クエリーの最適化
この話題は基本的に本全体で議論されていますが、ここで特に言及しなければならないのは:

ONまたはUSING句の列にインデックスがあることを確認します.インデックスを作成するときは、関連付けの順序を考慮します.テーブルAとテーブルBがカラムcで関連付けられている場合、オプティマイザの関連順序がB、Aである場合、Bテーブルの対応するカラムにインデックスを付ける必要はありません.使用されていないインデックスは、追加の負担をもたらすだけです.一般に、他の理由がない限り、関連付けられた順序の2番目のテーブルの対応するカラムにインデックスを作成する必要があります.

は、任意のGROUP BYとORDER BYの式が1つのテーブルのカラムにのみ関連していることを保証し、MySQLがインデックスを使用してこのプロセスを最適化することができます.

MySQLをアップグレードするときは、関連構文、演算子の優先度など、他の変更が発生する可能性がある点に注意してください.以前は通常の関連である場所がデカルト積になる可能性があるため、異なるタイプの関連では異なる結果が生成される可能性がある

最適化LIMITページング
システムでページング操作を行う必要がある場合、通常はLIMITにオフセット量を加える方法で実現し、適切なORDER BY句を追加します.対応するインデックスがあれば、通常は効率的です.そうしないと、MySQLは大量のファイルソート操作を行う必要があります.
1つの非常に一般的で頭が痛い問題は、オフセット量が非常に大きい場合に注意することです.例えば、LIMIT 1000,20のようなクエリーである可能性があります.この場合、MySQLは1 0,020件のレコードをクエリーして最後の20件だけを返す必要があります.前の10,000件のレコードは捨てられます.このような世代は非常に高いです.すべてのページが同じ頻度でアクセスされる場合、このようなクエリは平均してテーブルの半分のデータにアクセスする必要があります.このクエリを最適化するには、ページ内でページングの数を制限するか、大きなオフセット量のパフォーマンスを最適化します.
このようなページング・クエリーを最適化する最も簡単な方法は、すべてのカラムをクエリーするのではなく、インデックスを使用してスキャンを上書きすることです.次に、必要に応じて関連付け操作を行い、必要なカラムを返します.オフセット量が大きい場合、このような効率が非常に向上します.次のクエリを考慮します.

mysql> SELECT film_id,description FROM sakila.film ORDER BY title LIHIT 50,5;

このテーブルが非常に大きい場合は、このクエリを次のように書き換えたほうがいいです.

mysql> SELECT film.film_id,Film.description
    ->  FROM  sakila.film
    ->INNER JOIN(
    ->  SELECT film.film_id FROM sakila.film
    ->  ORDER BY title LIMIT 50,5
    ->) AS lim USING(film_id);

ここでの「遅延関連付け」は、MySQLができるだけ少ないページをスキャンし、アクセスが必要なレコードを取得した後、関連付けられた列に基づいて元のテーブルに戻って必要なすべての列をクエリーする効率を大幅に向上させます.この技術は、関連クエリのLIMIT句を最適化するためにも使用できます.
LIMITクエリーを既知の場所のクエリーに変換して、MySQLが範囲スキャンで対応する結果を得ることもできます.たとえば、1つの場所列にインデックスがあり、境界値があらかじめ計算されている場合、上のクエリは次のように書き換えられます.

mysql> SELECT film_id, description FROM sakila.Film
       -> WHERE position BETWEEN so AND 54 0RDER BY position;

データをランキングする問題もこれと似ていますが、GROUP BYと同時に使用されることが多いです.この場合、順位情報は、通常、予め計算され記憶される必要がある.
LIMITとOFFSTの問題は、実はOFFSETの問題です.MySQLは不要なローを大量にスキャンして捨てます.ブックマークを使用して、前回取得したデータの位置を記録することができれば、次回はそのブックマークの位置から直接スキャンを開始することができ、OFFSETの使用を回避することができる.たとえば、レンタルレコードに従ってページをめくる必要がある場合は、最新のレンタルレコードに基づいて後に遡ることができます.これは、レンタルレコードのプライマリ・キーが単調に増加しているためです.まず、次のクエリを使用して、最初の結果のセットを取得します.

mysql> SELECT * FROM sakila.rental
      -> ORDER BY rental id DESC LIMIT 20;

上記のクエリが、プライマリ・キーが1 6 049～1 6 030のレンタル・レコードを返すと仮定すると、次のクエリは1 6 030という点から開始できます.

mysql> SELECT * FROM sakila*rental
    -> WHERE rental id < 16030,
    -> ORDER BY rental id DESC LIMIT 20;

このテクノロジーの利点は、ページをめくってもパフォーマンスが良いことです.他の最適化方法には、事前に計算された要約テーブルを使用するか、プライマリ・キー列とソートする必要があるデータ列のみを含む冗長テーブルに関連付けることも含まれます.Sphinxを使用していくつかの検索操作を最適化することもでき、付録Fを参照してより多くの関連情報を得ることができる.