技術の分かち合い|MySQLはGROUP BYの4種類の方式を実行します
7747 ワード
テキストリンク:https://www.percona.com/blog/2018/02/05/four-ways-to-execute-mysql-group-by/
作者:Peter Zaitsev
原文:管長龍
日常的なクエリーでは、インデックスまたはその他のデータ検索の方法は、クエリーの実行中に最も高い部分ではない可能性があります.たとえば、MySQL GROUP BYは、クエリーの実行時間の90%以上を担当する可能性があります.
MySQLがGROUP BYを実行する際の主な複雑さは、GROUP BY文の集約関数の計算です.UDF集約関数は、個々のグループを構成するすべての値を次から次へと取得する.これにより、別のグループに移動する前に、単一のグループの集約関数の数値を計算できます.
もちろん、問題は、ほとんどの場合、ソースデータ値がグループ化されないことです.様々なグループからの値は、処理中に互いに追従する.そのため、特別なステップが必要です.
MySQL GROUP BYの処理
前に見た同じtableを見てみましょう.
同じGROUP BY文を異なる方法で実行します.
1、MySQLの中のIndex Ordered GROUP BY
この場合、GROUP BYの列にインデックスがあります.これにより、グループごとにデータをスキャンし、GROUP BYを動的に実行することができる(低コスト).
LIMITを使用して取得したグループの数を制限したり、「インデックスの上書き」を使用したりすると、シーケンスインデックススキャンが非常に迅速な操作であるため、特に有効です.
少量のグループがあり、インデックスが上書きされていない場合は、インデックス順序スキャンによって大量のIOが発生する可能性があります.だからこれは最適化された計画ではないかもしれません.
2、MySQLの外部ソートGROUP BY
グループ順にデータのインデックスをスキャンできない場合は、外部ソート(MySQLではfilesortとも呼ばれます)でデータを取得できます.
私がここでSQLを使っていることに気づくかもしれません.BIG_RESULTはこの計画を得るためにヒントを与えた.それがなければ、MySQLはこのような状況でこの計画を選択しません.
一般的に、MySQLは、テンポラリ・テーブルを持つよりもソートが有効であるため、多くのグループを持っている場合にのみこの計画を使用するのが好きです(以下で説明します).
3、MySQLの仮表GROUP BY
この場合、MySQLもフルテーブルスキャンを行います.ただし、追加のソート転送を実行するのではなく、一時テーブルを作成します.このテンポラリ・テーブルには、各グループに1つのローが含まれており、各入力ローについて、対応するグループの値が更新されます.たくさんの更新!これはメモリ内では適切かもしれませんが、結果テーブルが大きすぎて更新が大量のディスクIOになると、非常に高価になります.この場合、外部選別計画は通常より良い.
MySQLのデフォルトでは、この計画はこの例で使用されますが、ヒントを提供しない場合は、SQL_を使用するよりもほとんど使用されます.BIG_RESULTが提示した計画は10倍遅い.
このクエリに「ORDER BY NULL」を追加したことに気づくかもしれません.これは、テンポラリ・テーブルのクリーンアップを示す唯一の計画です.それがなければ、私たちはこの計画を得ました.
その中でtemporaryとfilesortの「2つの最悪の」ヒントを得た.
MySQL 5.7は、クエリが不要であっても、グループ順にソートされたGROUP BY結果を常に返します(これは、追加のソート転送が高価になる可能性があります).ORDER BY NULLは、アプリケーションがこれを必要としないことを示します.
場合によっては、集約関数を使用して異なるテーブルの列にアクセスするJOINクエリーなど、GROUP BYを使用するテンポラリ・テーブルが唯一の選択である可能性があります.
MySQLがGROUP BYのテンポラリ・テーブルを実行する計画を強制する場合は、SQL_を使用します.SMALL_RESULTヒント.
4、MySQLのインデックスはスキャンをスキップしたGROUP BYに基づく
最初の3つのGROUP BYの実行方法は、すべての集約関数に適用されます.しかし、その中のいくつかの人は4つ目の方法を持っています.
この方法は、非常に特殊な集約関数であるMIN()とMAX()にのみ適用されます.これらは、グループ内のすべてのローを巡回して値を計算する必要はありません.
このようなインデックスがある場合、グループ内の最小または最大のグループ値に直接ジャンプできます.
インデックスが(K)カラムにのみ確立されている場合、各グループのMAX(ID)値を見つけるにはどうすればいいですか?これはInnoDBテーブルです.InnoDBテーブルはすべてのインデックスにPRIMARY KEYを効果的に添付することを覚えておいてください.(K)は(K,ID)となり,このクエリに対してSkip-SCan最適化を用いることができる.
この最適化は、各グループに多数のローがある場合にのみ有効になります.それ以外の場合、MySQLは、より伝統的な方法を使用してこのクエリを実行する傾向があります(方法#1で詳細に説明されているインデックス順序GROUP BYなど).
MIN()／MAX()重合関数を用いたが,他の最適化もそれらに適用した.たとえば、GROUP BYのない集約関数(実際にはすべてのテーブルにグループがある)がある場合、MySQLは統計分析フェーズでインデックスからこれらの値を取得し、実行フェーズでテーブルを完全に読み取ることを回避します.
フィルタとグループ化
MySQLがGROUP BYを実行する4つの方法を検討した.簡単にするために、表全体にGROUP BYを使用しましたが、フィルタは適用されませんでした.WHERE句がある場合、同じ概念が適用されます.
この場合,K列の範囲を用いてデータフィルタリング/ルックアップを行い,テンポラリテーブルがある場合にGROUP BYを実行する.
場合によっては、メソッドが競合しないことがあります.ただし、他の場合は、GROUP BYのインデックスまたは他のインデックスを使用してフィルタする必要があります.
このクエリで使用される特定の定数に基づいて、GROUP BYに対してインデックス順スキャン(WHERE句を解析するためにインデックスから「破棄」する)を使用するか、WHERE句を解析するためにインデックスを使用する(ただし、一時テーブルを使用してGROUP BYを解析する)かを見ることができます.
私の経験では、MySQL GROUP BYが常に正しい選択をしていないところです.FORCE INDEXを使用してクエリーを実行する必要がある場合があります.
作者:Peter Zaitsev
原文:管長龍
日常的なクエリーでは、インデックスまたはその他のデータ検索の方法は、クエリーの実行中に最も高い部分ではない可能性があります.たとえば、MySQL GROUP BYは、クエリーの実行時間の90%以上を担当する可能性があります.
MySQLがGROUP BYを実行する際の主な複雑さは、GROUP BY文の集約関数の計算です.UDF集約関数は、個々のグループを構成するすべての値を次から次へと取得する.これにより、別のグループに移動する前に、単一のグループの集約関数の数値を計算できます.
もちろん、問題は、ほとんどの場合、ソースデータ値がグループ化されないことです.様々なグループからの値は、処理中に互いに追従する.そのため、特別なステップが必要です.
MySQL GROUP BYの処理
前に見た同じtableを見てみましょう.
mysql> show create table tbl \G
*************************** 1. row ***************************
Table: tbl
Create Table: CREATE TABLE `tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`g` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2340933 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)同じGROUP BY文を異なる方法で実行します.
1、MySQLの中のIndex Ordered GROUP BY
mysql> select k, count(*) c from tbl group by k order by k limit 5;
+---+---+
| k | c |
+---+---+
| 2 | 3 |
| 4 | 1 |
| 5 | 2 |
| 8 | 1 |
| 9 | 1 |
+---+---+
5 rows in set (0.00 sec)
mysql> explain select k, count(*) c from tbl group by k order by k limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: index
possible_keys: k
key: k
key_len: 4
ref: NULL
rows: 5
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)この場合、GROUP BYの列にインデックスがあります.これにより、グループごとにデータをスキャンし、GROUP BYを動的に実行することができる(低コスト).
LIMITを使用して取得したグループの数を制限したり、「インデックスの上書き」を使用したりすると、シーケンスインデックススキャンが非常に迅速な操作であるため、特に有効です.
少量のグループがあり、インデックスが上書きされていない場合は、インデックス順序スキャンによって大量のIOが発生する可能性があります.だからこれは最適化された計画ではないかもしれません.
2、MySQLの外部ソートGROUP BY
mysql> explain select SQL_BIG_RESULT g, count(*) c from tbl group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 998490
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
mysql> select SQL_BIG_RESULT g, count(*) c from tbl group by g limit 5;
+---+---+
| g | c |
+---+---+
| 0 | 1 |
| 1 | 2 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
+---+---+
5 rows in set (0.88 sec)グループ順にデータのインデックスをスキャンできない場合は、外部ソート(MySQLではfilesortとも呼ばれます)でデータを取得できます.
私がここでSQLを使っていることに気づくかもしれません.BIG_RESULTはこの計画を得るためにヒントを与えた.それがなければ、MySQLはこのような状況でこの計画を選択しません.
一般的に、MySQLは、テンポラリ・テーブルを持つよりもソートが有効であるため、多くのグループを持っている場合にのみこの計画を使用するのが好きです(以下で説明します).
3、MySQLの仮表GROUP BY
mysql> explain select g, sum(g) s from tbl group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 998490
filtered: 100.00
Extra: Using temporary
1 row in set, 1 warning (0.00 sec)
mysql> select g, sum(g) s from tbl group by g order by null limit 5;
+---+------+
| g | s |
+---+------+
| 0 | 0 |
| 1 | 2 |
| 4 | 4 |
| 5 | 5 |
| 6 | 12 |
+---+------+
5 rows in set (7.75 sec)この場合、MySQLもフルテーブルスキャンを行います.ただし、追加のソート転送を実行するのではなく、一時テーブルを作成します.このテンポラリ・テーブルには、各グループに1つのローが含まれており、各入力ローについて、対応するグループの値が更新されます.たくさんの更新!これはメモリ内では適切かもしれませんが、結果テーブルが大きすぎて更新が大量のディスクIOになると、非常に高価になります.この場合、外部選別計画は通常より良い.
MySQLのデフォルトでは、この計画はこの例で使用されますが、ヒントを提供しない場合は、SQL_を使用するよりもほとんど使用されます.BIG_RESULTが提示した計画は10倍遅い.
このクエリに「ORDER BY NULL」を追加したことに気づくかもしれません.これは、テンポラリ・テーブルのクリーンアップを示す唯一の計画です.それがなければ、私たちはこの計画を得ました.
mysql> explain select g, sum(g) s from tbl group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 998490
filtered: 100.00
Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)その中でtemporaryとfilesortの「2つの最悪の」ヒントを得た.
MySQL 5.7は、クエリが不要であっても、グループ順にソートされたGROUP BY結果を常に返します(これは、追加のソート転送が高価になる可能性があります).ORDER BY NULLは、アプリケーションがこれを必要としないことを示します.
場合によっては、集約関数を使用して異なるテーブルの列にアクセスするJOINクエリーなど、GROUP BYを使用するテンポラリ・テーブルが唯一の選択である可能性があります.
MySQLがGROUP BYのテンポラリ・テーブルを実行する計画を強制する場合は、SQL_を使用します.SMALL_RESULTヒント.
4、MySQLのインデックスはスキャンをスキップしたGROUP BYに基づく
最初の3つのGROUP BYの実行方法は、すべての集約関数に適用されます.しかし、その中のいくつかの人は4つ目の方法を持っています.
mysql> explain select k,max(id) from tbl group by k \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 4
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index for group-by
1 row in set, 1 warning (0.00 sec)
mysql> select k,max(id) from tbl group by k;
+---+---------+
| k | max(id) |
+---+---------+
| 0 | 2340920 |
| 1 | 2340916 |
| 2 | 2340932 |
| 3 | 2340928 |
| 4 | 2340924 |
+---+---------+
5 rows in set (0.00 sec)この方法は、非常に特殊な集約関数であるMIN()とMAX()にのみ適用されます.これらは、グループ内のすべてのローを巡回して値を計算する必要はありません.
このようなインデックスがある場合、グループ内の最小または最大のグループ値に直接ジャンプできます.
インデックスが(K)カラムにのみ確立されている場合、各グループのMAX(ID)値を見つけるにはどうすればいいですか?これはInnoDBテーブルです.InnoDBテーブルはすべてのインデックスにPRIMARY KEYを効果的に添付することを覚えておいてください.(K)は(K,ID)となり,このクエリに対してSkip-SCan最適化を用いることができる.
この最適化は、各グループに多数のローがある場合にのみ有効になります.それ以外の場合、MySQLは、より伝統的な方法を使用してこのクエリを実行する傾向があります(方法#1で詳細に説明されているインデックス順序GROUP BYなど).
MIN()／MAX()重合関数を用いたが,他の最適化もそれらに適用した.たとえば、GROUP BYのない集約関数(実際にはすべてのテーブルにグループがある)がある場合、MySQLは統計分析フェーズでインデックスからこれらの値を取得し、実行フェーズでテーブルを完全に読み取ることを回避します.
mysql> explain select max(k) from tbl \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)フィルタとグループ化
MySQLがGROUP BYを実行する4つの方法を検討した.簡単にするために、表全体にGROUP BYを使用しましたが、フィルタは適用されませんでした.WHERE句がある場合、同じ概念が適用されます.
mysql> explain select g, sum(g) s from tbl where k>4 group by g order by NULL limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition; Using temporary
1 row in set, 1 warning (0.00 sec)この場合,K列の範囲を用いてデータフィルタリング/ルックアップを行い,テンポラリテーブルがある場合にGROUP BYを実行する.
場合によっては、メソッドが競合しないことがあります.ただし、他の場合は、GROUP BYのインデックスまたは他のインデックスを使用してフィルタする必要があります.
mysql> alter table tbl add key(g);
Query OK, 0 rows affected (4.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select g, sum(g) s from tbl where k>1 group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: index
possible_keys: k,g
key: g
key_len: 4
ref: NULL
rows: 16
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select g, sum(g) s from tbl where k>4 group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: range
possible_keys: k,g
key: k
key_len: 4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)このクエリで使用される特定の定数に基づいて、GROUP BYに対してインデックス順スキャン(WHERE句を解析するためにインデックスから「破棄」する)を使用するか、WHERE句を解析するためにインデックスを使用する(ただし、一時テーブルを使用してGROUP BYを解析する)かを見ることができます.
私の経験では、MySQL GROUP BYが常に正しい選択をしていないところです.FORCE INDEXを使用してクエリーを実行する必要がある場合があります.