Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


8.2.1.16 GROUP BY の最適化

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

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

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

MySQL では、GROUP BY はソートに使用されるため、サーバーはグループ化に ORDER BY 最適化を適用することもあります。セクション8.2.1.15「ORDER BY の最適化」を参照してください。

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

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, がある場合に、ルースインデックススキャンを適用できます。クエリーに GROUP BY c2, c3 (カラムは左端のプリフィクスでない) または GROUP BY c1, c2, c4 (c4 はインデックス内にない) がある場合は適用できません。

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

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

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

ルースインデックススキャンをクエリーに適用できる場合、EXPLAIN 出力で、Extra カラムに Using index for group-by と示されます。

テーブル t1(c1,c2,c3,c4) にインデックス idx(c1,c2,c3) があると仮定します。ルースインデックススキャンアクセスメソッドは、次のクエリーに使用できます。

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 が含まれる場合、ルースインデックススキャンを使用できます。

ルースインデックススキャンアクセスメソッドは、選択リスト内で、すでにサポートされている MIN() および MAX() 参照に加えて、ほかの形式の集約関数参照にも適用できます。

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

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

  • ここでも先述したルーススキャンの制限が適用されます。

テーブル t1(c1,c2,c3,c4) にインデックス idx(c1,c2,c3) があると仮定します。ルースインデックススキャンアクセスメソッドは、次のクエリーに使用できます。

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

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

ルースインデックススキャンは次のクエリーに適用できません。

SELECT DISTINCT COUNT(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;
8.2.1.16.2 タイトインデックススキャン

タイトインデックススキャンは、クエリー条件によって、フルインデックススキャンまたは範囲インデックススキャンのいずれかになります。

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

このメソッドが機能するためには、クエリー内のすべてのカラムに、GROUP BY キーの前にくるか、または間の部分にあるキーの部分を参照する定数同等条件があれば十分です。同等条件からの定数は、インデックスの完全なプリフィクスを形成できるように、検索キーのギャップを埋めます。これらのインデックスのプリフィクスは、インデックスルックアップに使用できます。GROUP BY 結果のソートが必要で、インデックスのプリフィクスである検索キーを形成できる場合、順序付けされたインデックス内のプリフィクスによる検索で、すでにすべてのキーが順番に取得されているため、MySQL は余分なソート操作も避けられます。

テーブル t1(c1,c2,c3,c4) にインデックス idx(c1,c2,c3) があると仮定します。次のクエリーは、前述のルースインデックススキャンアクセスメソッドでは機能しませんが、タイトインデックススキャンアクセスメソッドでは機能します。

  • 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;
    

User Comments
Sign Up Login You must be logged in to post a comment.