MySQLでdistinctとgroup byを使用する方法-mysql チュートリアル-php.cn

まず一般的な結論について話しましょう:

セマンティクスが同じでインデックスがある場合: group by と distinct はどちらも同じ効率でインデックスを使用できます。
同じセマンティクスでインデックスなしの場合: distinct は group by より効率的です。その理由は、distinct と group by の両方がグループ化操作を実行しますが、group by がソートを実行してファイルソートをトリガーする可能性があり、結果として SQL の実行効率が低下するためです。

この結論に基づいて、次のように疑問に思うかもしれません:

セマンティクスが同じで、 ## と distinct の効率は同じですか?
group by
はどのような状況で並べ替え操作を実行しますか?

distinct

と group by の基本的な使用法を見てみましょう。 distinct の使用法

distinct の使用法

SELECT DISTINCT columns FROM table_name WHERE where_conditions;

ログイン後にコピー

例:

mysql> select distinct age from student;
+------+
| age  |
+------+
|   10 |
|   12 |
|   11 |
| NULL |
+------+
4 rows in set (0.01 sec)

ログイン後にコピー

DISTINCT

このキーワードは、一意に異なる値を返すために使用されます。価値観。これはクエリステートメントの最初のフィールドの前に使用され、メイン句のすべての列に適用されます。カラムに NULL 値があり、そのカラムで

DISTINCT

句を使用すると、MySQL は 1 つの NULL 値を保持し、他の NULL 値を削除します。 ## 句ステートメントは、すべての NULL 値を同じ値として扱います。 distinct 複数列重複排除

distinct

複数列重複排除は、指定された重複排除列情報に基づいて実行されます。つまり、指定されたすべての列情報のみが実行されます。同様の場合は重複情報とみなされます。

SELECT DISTINCT column1,column2 FROM table_name WHERE where_conditions;
mysql> select distinct sex,age from student;
+--------+------+
| sex    | age  |
+--------+------+
| male   |   10 |
| female |   12 |
| male   |   11 |
| male   | NULL |
| female |   11 |
+--------+------+
5 rows in set (0.02 sec)

ログイン後にコピー

group by の使用法基本的な重複排除の場合、

group by

の使用法は

distinct

と似ています。単一列重複排除構文:

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

ログイン後にコピー

実行:

mysql> select age from student group by age;
+------+
| age  |
+------+
|   10 |
|   12 |
|   11 |
| NULL |
+------+
4 rows in set (0.02 sec)

ログイン後にコピー

複数列重複排除

構文:

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

ログイン後にコピー

実行:

mysql> select sex,age from student group by sex,age;
+--------+------+
| sex    | age  |
+--------+------+
| male   |   10 |
| female |   12 |
| male   |   11 |
| male   | NULL |
| female |   11 |
+--------+------+
5 rows in set (0.03 sec)

ログイン後にコピー

違いの例

2 つの構文の違いは、

group by

は単一列の重複排除を実行できることと、

group by の原則であることです。

結果は最初にグループ化および並べ替えられ、次に各グループの最初のデータが返されます。そして、group by に続くフィールドに基づいて重複排除が実行されます。例:

mysql> select sex,age from student group by sex;
+--------+-----+
| sex    | age |
+--------+-----+
| male   |  10 |
| female |  12 |
+--------+-----+
2 rows in set (0.03 sec)

ログイン後にコピー

区別および原則によるグループ化

ほとんどの例では、

DISTINCT

は特別な

GROUP BY# と見なされます。 ##、それらの実装はグループ化操作に基づいており、いずれもルーズインデックススキャンとコンパクトインデックススキャンを通じて実装できます (インデックススキャンの内容については他の記事で詳しく紹介するため、ここでは詳しく紹介しません) 。

DISTINCT と GROUP BY

は両方ともインデックスを使用してスキャンおよび検索できます。たとえば、次の 2 つの SQL (表の最後にあるエクストラの内容を見てください)。これら 2 つの SQL を分析すると、エクストラでは、これら 2 つの SQL がコンパクトなインデックススキャンを使用していることがわかります。

Using Indexグループ -by の場合。 したがって、一般に、同じセマンティクスを持つ DISTINCT ステートメントと GROUP BY

ステートメントについては、同じインデックス最適化メソッドを使用して最適化できます。

mysql> explain select int1_index from test_distinct_groupby group by int1_index;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_distinct_groupby | NULL       | range | index_1       | index_1 | 5       | NULL |  955 |   100.00 | Using index for group-by |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set (0.05 sec)
mysql> explain select distinct int1_index from test_distinct_groupby;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_distinct_groupby | NULL       | range | index_1       | index_1 | 5       | NULL |  955 |   100.00 | Using index for group-by |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set (0.05 sec)

ログイン後にコピー

ただし、MYSQL8.0 より前の GROUP BY では、GROUP Y

はデフォルトで暗黙的にフィールドによってソートされます。

ご覧のとおり、次の SQL ステートメントは一時テーブルを使用し、ファイルソートも実行します。

mysql> explain select int6_bigger_random from test_distinct_groupby GROUP BY int6_bigger_random;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                           |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|  1 | SIMPLE      | test_distinct_groupby | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97402 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1 row in set (0.04 sec)

ログイン後にコピー

暗黙的なソート暗黙的なソートについては、MySQL の公式説明を参照してください:

https://dev.mysql.com/doc/refman/5.7 /en /order-by-optimization.html

GROUP BY は、デフォルトで暗黙的に並べ替えます (つまり、GROUP BY 列に ASC または DESC 指定子がない場合)。ただし、暗黙的な GROUP BY 並べ替えに依存します (つまり、ASC または DESC 指定子がない場合の並べ替え)、または GROUP BY の明示的な並べ替え (つまり、GROUP BY 列に明示的な ASC または DESC 指定子を使用する) は非推奨になりました。特定の並べ替え順序を生成するには、ORDER BY 句を指定します。 .

大まかな説明:

GROUP BY は、デフォルトで暗黙的な並べ替えになります (つまり、GROUP BY 列に ASC または DESC インジケーターがない場合でも並べ替えが実行されます)。ただし、明示的または暗黙的な並べ替えのための GROUP BY は非推奨となり、特定の並べ替え順序を生成するには、ORDER BY 句を指定します。

したがって、MySQL8.0 より前では、

GROUP BY

はデフォルトで効果フィールド (

GROUP BY

の後続フィールド) に従って結果を並べ替えます。インデックスを使用できる場合、

GROUP BY は追加の並べ替え操作を必要としませんが、インデックスを並べ替えに使用できない場合、MySQL オプティマイザーは一時テーブルの使用を選択してから並べ替える必要があります でグループ化します。 そして、結果セットのサイズがシステムで設定された一時テーブルのサイズを超えると、MySQL は動作前に一時テーブルのデータをディスクにコピーするため、ステートメントの実行効率が非常に低くなります。これが、MySQL がこの操作 (暗黙的なソート) を非推奨にすることを選択した理由です。 上記の理由に基づいて、Mysql は 8.0 でこれを最適化および更新しました:

https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

以前 (MySQL 5.7 以前)、GROUP BY は暗黙的に以下でソートされていました。 MySQL 8.0 では、そのようなことは発生しないため、(以前のように) 暗黙的なソートを抑制するために最後に ORDER BY NULL を指定する必要はなくなりました。ただし、クエリの結果は以前の MySQL バージョンと異なる場合があります。ソート順序を指定するには、ORDER BY 句を指定します。

大まかな説明:

以前 (MySQL5.7 バージョンより前)、Group by は特定の条件に基づいて暗黙的なソートを実行していました。 MySQL 8.0 では、この機能が削除されたため、order by null を追加して暗黙的な順序付けを無効にする必要はなくなりましたが、クエリ結果は以前の MySQL バージョンと異なる場合があります。指定された順序で結果を生成するには、ORDER BY で並べ替えるフィールドを指定します。

したがって、次のような結論も得られます:

同じセマンティクスとインデックスの場合: group by と distinct どちらもインデックスを使用でき、効率は同じです。 group by と distinct はほぼ同等であるため、distinct は特別な group by とみなすことができます。
同じセマンティクスでインデックスがない場合: distinct は group by より効率的です。その理由は、distinct と group by の両方がグループ化操作を実行しますが、group by は MySQL8.0 より前に暗黙的なソートを実行し、ファイルソートがトリガーされ、 SQLの実行効率が低い。ただし、MySQL8.0 から MySQL では暗黙的なソートが削除されたため、現時点では同じセマンティクスでインデックスなしの group by と distinct の実行効率はほぼ同じ、同等。

distinct と比較すると、group by のセマンティクスは明確です。また、distinct キーワードはすべてのフィールドに有効になるため、複合的な業務処理を実行する場合は、group by の方が柔軟であり、グループ化の状況に応じてデータを更新できます。 having を使用してデータをフィルター処理したり、集計関数を使用してデータを操作したりするなど。

以上がMySQLでdistinctとgroup byを使用する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。