このページは機械翻訳したものです。

8.2.1.17 GROUP BY の最適化

GROUP BY 句を満たすもっとも一般的な方法は、テーブル全体をスキャンし、各グループのすべての行が連続する新しい一時テーブルを作成することであり、それにより、この一時テーブルを使用してグループを見つけて、集約関数 (ある場合) を適用できます。 場合によっては、MySQL はそれよりはるかに優れた処理を実行でき、インデックスアクセスを使用した一時テーブルの作成を回避できます。

GROUP BY のインデックスを使用するための最も重要な前提条件は、すべての GROUP BY カラムが同じインデックスの属性を参照し、インデックスにそのキーが順番に格納されることです (たとえば、BTREE インデックスの場合は該当しますが、HASH インデックスの場合は該当しません)。 一時テーブルの使用をインデックスアクセスに置き換えられるかどうかは、クエリー内でインデックスのどの部分が使用されているか、その部分に指定された条件、および選択された集約関数にもよります。

次のセクションで詳しく説明するように、インデックスアクセスによって GROUP BY クエリーを実行する方法は 2 つあります。 最初の方法では、すべての範囲述語 (ある場合) とともにグループ化操作が適用されます。 2 つめの方法では、まず範囲スキャンを実行し、次に結果タプルをグループ化します。

• ルースインデックススキャン

• タイトインデックススキャン

一部の条件下では、GROUP BY が存在しない場合にも、緩やかなインデックススキャンを使用できます。 スキャン範囲アクセス方法のスキップを参照してください。

ルースインデックススキャン

GROUP BY を処理するもっとも効率的な方法は、インデックスを使用してグループ化するカラムを直接取得することです。 このアクセスメソッドでは、MySQL はキーが順序付けられている、インデックス型のプロパティーを使用します。(たとえば、BTREE)。 このプロパティーにより、インデックス内のすべての WHERE 条件を満たすキーを考慮する必要なく、インデックス内のルックアップグループを使用できます。 このアクセス方法では、インデックス内のキーの一部のみが考慮されるため、ルーズインデックススキャンと呼ばれます。 WHERE 句がない場合、ループインデックススキャンはグループ数と同じ数のキーを読み取ります。これは、すべてのキーの数よりはるかに小さい場合があります。 WHERE 句に範囲述語が含まれている場合 (セクション8.8.1「EXPLAIN によるクエリーの最適化」 の range 結合タイプの説明を参照)、ルーズインデックススキャンは範囲条件を満たす各グループの最初のキーを検索し、可能なかぎり少ない数のキーを再度読み取ります。 これは次の条件の下で可能です。

• クエリーが単一テーブルに対するものです。

• GROUP BY はインデックスの左端のプリフィクスを形成するカラムのみを指定し、ほかのカラムは指定しません。 (GROUP BY の代わりに、クエリーに DISTINCT 句がある場合、個々のすべての属性がインデックスの左端のプリフィクスを形成するカラムを参照します。) たとえば、テーブル t1 の (c1,c2,c3) にインデックスがある場合、クエリーに GROUP BY c1, c2 が含まれていれば、Loose Index Scan が適用されます。 クエリーに GROUP BY c2, c3 (カラムは左端のプリフィクスでない) または GROUP BY c1, c2, c4 (c4 はインデックス内にない) がある場合は適用できません。

• 選択リスト (ある場合) で使用されている集約関数が、MIN() と MAX() だけであり、それらはすべて同じカラムを参照します。 カラムはインデックス内にあり、GROUP BY のカラムの直後にある必要があります。

• クエリーで参照された GROUP BY からの部分以外のインデックスの部分は、定数である必要があります (つまり、定数と同等のもので参照されている必要があります) が、MIN() または MAX() 関数の引数を除きます。

• インデックス内のカラムの場合、プリフィクスだけでなく、完全なカラム値にインデックスが設定されている必要があります。 たとえば、c1 VARCHAR(20), INDEX (c1(10)) では、インデックスは c1 値の接頭辞のみを使用し、ループインデックススキャンには使用できません。

ループインデックススキャンがクエリーに適用可能な場合、EXPLAIN 出力の Extra カラムに Using index for group-by が表示されます。

テーブル t1(c1,c2,c3,c4) にインデックス idx(c1,c2,c3) があると仮定します。 Loose Index Scan アクセス方法は、次のクエリーに使用できます:

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

次に示す理由により、以下のクエリーはこのクイック選択メソッドで実行できません。

• MIN() または MAX() 以外の集約関数があります。

  SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

• GROUP BY 句内のカラムがインデックスの左端のプリフィクスを形成していません。

  SELECT c1, c2 FROM t1 GROUP BY c2, c3;

• クエリーは GROUP BY 部分のあとに続くキーの部分を参照し、そこに定数と同等のものがありません。

  SELECT c1, c3 FROM t1 GROUP BY c1, c2;

  クエリーに WHERE c3 = const が含まれていたため、ループインデックススキャンを使用できました。

Loose Index Scan アクセス方法は、すでにサポートされている MIN() および MAX() 参照に加えて、選択リストの他の形式の集計関数参照にも適用できます:

• AVG(DISTINCT)、SUM(DISTINCT)、および COUNT(DISTINCT) がサポートされています。 AVG(DISTINCT) と SUM(DISTINCT) は 1 つの引数をとります。 COUNT(DISTINCT) には複数のカラム引数を指定できます。

• クエリーに GROUP BY または DISTINCT 句があってはいけません。

• 前に説明した Loose Index Scan の制限が引き続き適用されます。

テーブル t1(c1,c2,c3,c4) にインデックス idx(c1,c2,c3) があると仮定します。 Loose Index Scan アクセス方法は、次のクエリーに使用できます:

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

タイトインデックススキャン

密インデックススキャンは、クエリー条件に応じて、全インデックススキャンまたはレンジインデックススキャンのいずれかになります。

ループインデックススキャンの条件が満たされていない場合でも、GROUP BY クエリーの一時テーブルの作成を回避できます。 WHERE 句に範囲条件がある場合、このメソッドはこれらの条件を満たすキーだけを読み取ります。 そうでない場合は、インデックススキャンを実行します。 このメソッドは、WHERE 句で定義された各範囲内のすべてのキーを読み取るか、範囲条件がない場合はインデックス全体をスキャンするため、タイトインデックススキャンと呼ばれます。 厳密なインデックススキャンでは、範囲条件を満たすすべてのキーが検出された後にのみグループ化操作が実行されます。

この方法が機能するには、GROUP BY キーの一部の前後に来るキーの一部を参照するクエリーのすべてのカラムに一定の等価条件がある必要があります。 同等条件からの定数は、インデックスの完全なプリフィクスを形成できるように、検索キーの「ギャップ」を埋めます。 これらのインデックスのプリフィクスは、インデックスルックアップに使用できます。 GROUP BY の結果にソートが必要で、インデックスの接頭辞である検索キーを形成できる場合、順序付きインデックスの接頭辞で検索するとすべてのキーがすでに順番に取得されるため、MySQL では余分なソート操作も回避されます。

テーブル t1(c1,c2,c3,c4) にインデックス idx(c1,c2,c3) があると仮定します。 次のクエリーは、前述の Loose Index Scan アクセス方法では機能しませんが、Tight Index Scan アクセス方法では機能します。

• GROUP BY にはギャップがありますが、条件 c2 = 'a' によってカバーされます。

  SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

• GROUP BY は、キーの最初の部分から開始されませんが、その部分に対して定数を与える条件があります。

  SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;