MySQL Group by最適化の詳細な説明

標準の Group by ステートメントには、select a,count(*) from t group by a; などの並べ替え、グループ化、集計関数が含まれています。このステートメントでは、並べ替えにデフォルトで a が使用されます。列 a にインデックスがない場合、 a と count(*) をカウントするための一時テーブルが作成され、次に sort_buffer を通じて a でソートされます。

標準的な実行プロセス

構造：

テーブル t1(id int 主キー、a int、b int、インデックス(a)) を作成します。
デリミタ;;
プロシージャ idata() を作成する
始める
 iをintとして宣言します。

 i=1 に設定します。
 i<=1000の間、
 t1 に値 (i, i, i) を挿入します。
 i=i+1 と設定します。
 終了しながら;
終わり;;
区切り文字 ;
idata() を呼び出す。

この関数は、(1,1,1) から (1000,1000,1000) までの 1000 個のステートメントを t1 に挿入します。

select id%10 as m, count(*) as c from t1 group by m; を実行します。

分析：

インデックスを使用するということは、このステートメントがカバーリング インデックスを使用してインデックス a を選択するため、テーブルに戻る必要がないことを意味します。
一時を使用するということは、一時テーブルを使用することを意味します。
filesort を使用すると、ソートが必要であることを示します。

プロセス：

1. 2 つのフィールド m と c を持つ一時メモリ テーブルを作成します。主キーは m です。
2. テーブルt1のインデックスaをスキャンし、リーフノードのid値を1つずつ取り出し、id%10の結果を計算し、それをxとして記録します。
1) 一時テーブルに主キー x を持つ行がない場合、レコード (x,1) を挿入します。
2) テーブル内に主キー x を持つ行がある場合は、行 x の c 値に 1 を加算します。

ステップ 2 メモリ内の一時テーブルに格納されているフィールドの合計長がパラメータ tmp_table_size で設定されたサイズに達したことが検出された場合、メモリ内の一時テーブルはディスク上の一時テーブルにアップグレードされ、トラバーサル計算が再開されます。
3. トラバーサルが完了したら、フィールド m に従ってソートし、結果セットをクライアントに返します。

最終的なソートは、下の図の破線ボックス内の操作です。sort_buffer のサイズが十分に大きくない場合は、ソートを補助するために一時テーブルが使用されます。

最適化

最適化されていない (つまり、グループ化列にインデックスがない) group by の全体的なプロセスは、次のように要約できます。データは順序付けられていないため、一時テーブルを作成する必要があり、次に各データがどのグループに属するかを 1 つずつ判断し、最後にグループ化列に従って並べ替えます。したがって、最適化には 2 つのアプローチがあります。

並べ替えを削除

返されたデータをソートする必要がない場合は、ソートを禁止できます。つまり、上記のステートメントを select a,count(*) from t group by a order by null に変更します。

並べ替え

レコードがソートフィールドによってソートされている場合、データは次のような構造になります。

このように、実際に返されるフィールドを取得したり、集計関数を計算したりするときには、順番にアクセスしていくだけで済みます。列の値が次のものになったときに、現在のグループアクセスが終了し、以前にカウントされたデータが直接返されることがわかります。これにより、一時テーブルの作成が回避され、ソートでは sort_buffer を使用した追加のソートが必要なくなります。これにより実行効率が大幅に向上します。

成し遂げる

1. グループ化フィールドがインデックスの作成に適している場合は、グループ化フィールドのインデックスを直接作成します。

MySQL バージョン 5.7 は、列データの関連する更新を実装するために使用される生成列メカニズムをサポートしています。次のように列zを作成し、z列にインデックスを作成します（MySQL 5.6以前の場合は、通常の列とインデックスを作成してこの問題を解決することもできます）

テーブル t1 を変更し、列 z int を追加し、常に as(id % 100) として生成し、インデックス (z) を追加します。

次に解析します:

一時テーブルや追加のソートは使用されないため、パフォーマンスが向上します。

2. グループ化フィールドが適切でない（使用率が非常に低い）場合は、SQL_BIG_RESULT を使用して最適化を試みることができます。

SQL_BIG_RESULT ヒントを group by ステートメントに追加することで、このステートメントには大量のデータが含まれており、一時ディスク テーブルを直接使用する必要があることをオプティマイザーに伝えることができます。 MySQL オプティマイザーは、ディスク一時テーブルが B+ ツリーに格納されており、そのストレージ効率は配列ほど高くないことを認識します。したがって、SQL_BIG_RESULT は大量のデータを示すために使用されるため、ディスク容量を考慮すると、配列に直接格納する方が適切です。したがって、SQL_BIG_RESULT を使用した後、オプティマイザーは配列構造のディスク一時テーブルを使用します。

ただし、ディスク一時テーブルを使用する条件が満たされていない場合、ディスク一時テーブルは使用されません。つまり、sort_buffer スペースが、返されてソートされるフィールドの合計長を格納できる場合、配列構造 sort_buffer が使用されます。合計フィールドが sort_buffer サイズを超える場合、ソートを支援するために配列構造のディスク一時テーブルが追加されます。

sort_buffer に十分なスペースがある場合、データは sort_buffer 内でソートされ、インデックスとして機能します。

上記の例を引き続き使用して、SQL_BIG_RESULTを使用します。

テーブル t1 を変更し、列 z int を追加し、常に as(id % 100) として生成し、インデックス (z) を追加します。

具体的なプロセスは以下のとおりです。

1. sort_buffer を初期化し、そこに整数フィールド (m で示される) を配置します。
2. テーブルt1のインデックスaをスキャンし、その中のid値を1つずつ取り出し、id%10の値をsort_bufferに格納します。
3. スキャンが完了したら、sort_buffer のフィールド m をソートします (sort_buffer のメモリが不足している場合は、ディスク上の一時ファイルを使用してソートが行われます)。
4. ソートが完了すると、順序付けられた配列が得られます。

分析：

この時点では一時テーブルは使用されていないことがわかりますが、ソートには sort_buffer が直接使用され、一時テーブルの使用によって発生するパフォーマンスの消費が抑えられます。

要約する

1. group by ステートメントの結果をソートする必要がない場合は、ステートメントの後に order by null を追加します。通常、一時テーブルを使用する必要はありません (上記の 2 つの最適化は、どちらもソートが必要であるという前提で提案されています)。
2. group by プロセスで上記のテーブルのインデックスを使用するようにします。確認方法は、explain 結果に Using temporary と Using filesort がないかどうかを確認します。
3. group by でカウントする必要があるデータの量が多くない場合は、メモリ内の一時テーブルのみを使用するようにします。また、tmp_table_size パラメータを適切に増やすことで、ディスク上の一時テーブルの使用を避けることもできます。
4. データの量が多すぎる場合は、SQL_BIG_RESULT ヒントを使用して、ソート アルゴリズムを直接使用してグループ化の結果を取得するようにオプティマイザーに指示します。

以上がMySQL Group by最適化の詳細な説明です。MySQL Group by最適化の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。