So verwenden Sie „distinct“ und „group by“ in MySQL

Lassen Sie uns zunächst über die allgemeine Schlussfolgerung sprechen:

• Im gleichen Fall Semantik und Indizierung Unten: Sowohl group by als auch distinct können Indizes mit der gleichen Effizienz verwenden. group by和 distinct 都能使用索引，效率相同。

• 在语义相同，无索引的情况下：distinct 效率高于group by。原因是 distinct 和 group by都会进行分组操作，但group by可能会进行排序，触发 filesort，导致 sql 执行效率低下。

基于这个结论，你可能会问：

• 为什么在语义相同，有索引的情况下，group by和 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 将保留一个 NULL 值，并删除其它的 NULL 值，因为DISTINCT子句将所有 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)

区别示例

两者的语法区别在于，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，它们的实现都基于分组操作，且都可以通过松散索引扫描、紧凑索引扫描(关于索引扫描的内容会在其他文章中详细介绍，就不在此细致介绍了)来实现。

DISTINCT和GROUP BY都是可以使用索引进行扫描搜索的。例如以下两条 sql（只单单看表格最后 extra 的内容），我们对这两条 sql 进行分析，可以看到，在 extra 中，这两条 sql 都使用了紧凑索引扫描Using index for group-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)

但对于GROUP BY来说，在 MYSQL8.0 之前，GROUP Y默认会依据字段进行隐式排序。

可以看到，下面这条 sql 语句在使用了临时表的同时，还进行了 filesort。

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 implicitly sorts by default (that is, in the absence of ASC or DESC designators for GROUP BY columns). However, relying on implicit GROUP BY sorting (that is, sorting in the absence of ASC or DESC designators) or explicit sorting for GROUP BY (that is, by using explicit ASC or DESC designators for GROUP BY columns) is deprecated. To produce a given sort order, provide an ORDER BY clause.

大致解释一下：

GROUP BY 默认隐式排序（指在 GROUP BY 列没有 ASC 或 DESC 指示符的情况下也会进行排序）。然而，GROUP BY 进行显式或隐式排序已经过时（deprecated）了，要生成给定的排序顺序，请提供 ORDER BY 子句。

所以，在 MySQL8.0 之前,GROUP BY会默认根据作用字段（GROUP BY的后接字段）对结果进行排序。在能利用索引的情况下，GROUP BY不需要额外进行排序操作；但当无法利用索引排序时，MySQL 优化器就不得不选择通过使用临时表然后再排序的方式来实现GROUP BY

Mit der gleichen Semantik und ohne Index: distinct ist effizienter als group by. Der Grund dafür ist, dass sowohl „distinct“ als auch „group by“ Gruppierungsvorgänge durchführen, group by jedoch möglicherweise eine Sortierung durchführt und eine Dateisortierung auslöst, was zu einer ineffizienten SQL-Ausführung führt.

Basierend auf dieser Schlussfolgerung fragen Sie sich vielleicht: #🎜🎜##🎜🎜##🎜🎜##🎜🎜#Warum gibt es Indizes, wenn die Semantik die ist Sind group by und distinct gleich? #🎜🎜##🎜🎜##🎜🎜#Unter welchen Umständen führt group by Sortiervorgänge durch? #🎜🎜##🎜🎜##🎜🎜# Finden Sie die Antwort mit diesen beiden Fragen. Schauen wir uns als Nächstes die grundlegenden Verwendungen von distinct und group by an. #🎜🎜#

Verwendung von „distinct“

Verwendung von „distinct“

rrreee#🎜🎜#Zum Beispiel: #🎜🎜#rrreee#🎜🎜#DISTINCT Schlüsselwort verwenden, um einen eindeutigen Wert zurückzugeben. Es wird vor dem ersten Feld in der Abfrageanweisung verwendet und gilt für alle Spalten im Hauptsatz. #🎜🎜##🎜🎜#Wenn eine Spalte einen NULL-Wert hat und die DISTINCT-Klausel für die Spalte verwendet wird, behält MySQL einen NULL-Wert bei und löscht die anderen NULL-Werte, weil DISTINCT-Klausel behandelt alle NULL-Werte als denselben Wert. #🎜🎜#<h4>eindeutige mehrspaltige Deduplizierung</h4>#🎜🎜#<code>eindeutige Die mehrspaltige Deduplizierung wird basierend auf den angegebenen Deduplizierungsspalteninformationen durchgeführt, d. h. nur auf allen Wenn angegeben Wenn die Spalteninformationen identisch sind, werden sie als doppelte Informationen betrachtet. #🎜🎜#rrreee

Verwendung von „group by“

#🎜🎜#Für die grundlegende Deduplizierung ähnelt die Verwendung von „group by“ der von „distinct“. #🎜🎜#

Einzelspaltige Deduplizierung

#🎜🎜#Syntax: #🎜🎜#rrreee#🎜🎜#Ausführung: #🎜🎜#rrreee

Mehrspaltige Deduplizierung

#🎜🎜 # Grammatik: #🎜🎜#rrreee#🎜🎜#Ausführung: #🎜🎜#rrreee

Unterschiedsbeispiel

#🎜🎜#Der Syntaxunterschied zwischen den beiden besteht darin, dass group by lauten kann Bei der Durchführung einer Einzelspalten-Deduplizierung besteht das Prinzip von group by darin, zuerst die Ergebnisse zu gruppieren und zu sortieren und dann das erste Datenelement in jeder Gruppe zurückzugeben. Und die Deduplizierung wird basierend auf den Feldern durchgeführt, die auf group by folgen. #🎜🎜##🎜🎜#Zum Beispiel: #🎜🎜#rrreee

unterscheiden und nach Prinzip gruppieren

#🎜🎜#In den meisten Beispielen kann DISTINCT als Spezial GROUP BY, ihre Implementierung basiert auf Gruppierungsvorgängen und sie können alle durch loses Index-Scannen und kompaktes Index-Scannen gescannt werden (der Inhalt des Index-Scannens wird in anderen Artikeln ausführlich vorgestellt, daher werde ich nicht darauf eingehen Stellen Sie es hier ausführlich vor. #🎜🎜##🎜🎜# Sowohl DISTINCT als auch GROUP BY können mithilfe von Indizes gescannt und durchsucht werden. Zum Beispiel die folgenden zwei SQLs (schauen Sie sich einfach den Inhalt des Extras am Ende der Tabelle an). Wenn wir diese beiden SQLs analysieren, können wir sehen, dass diese beiden SQLs im Extra das kompakte Index-Scanning verwenden Verwenden Sie den Index für group -by. #🎜🎜##🎜🎜#Also können wir im Allgemeinen für DISTINCT- und GROUP BY-Anweisungen mit derselben Semantik dieselben Indexoptimierungsmethoden zum Optimieren verwenden. #🎜🎜#rrreee#🎜🎜#Aber für GROUP BY wurde GROUP Y vor MYSQL8.0 standardmäßig implizit nach Feldern sortiert. #🎜🎜##🎜🎜#Wie Sie sehen können, verwendet die folgende SQL-Anweisung eine temporäre Tabelle und führt auch eine Dateisortierung durch. #🎜🎜#rrreee

Implizite Sortierung

#🎜🎜#Für die implizite Sortierung können wir uns auf die offizielle Erklärung von MySQL beziehen: #🎜🎜##🎜🎜#https://dev.mysql.com/ doc/ refman/5.7/en/order-by-optimization.html#🎜🎜#

#🎜🎜#GROUP BY sortiert standardmäßig implizit (d. h. in Abwesenheit von ASC- oder DESC-Bezeichnern für GROUP BY-Spalten). Sich auf implizite GROUP BY-Sortierung (d. h. Sortierung ohne ASC- oder DESC-Bezeichner) oder explizite Sortierung für GROUP BY (d. h. durch Verwendung expliziter ASC- oder DESC-Bezeichner für GROUP BY-Spalten) zu verlassen, ist veraltet, um eine bestimmte Sortierung zu erzeugen Geben Sie für die Bestellung eine ORDER BY-Klausel an.#🎜🎜#

#🎜🎜#Allgemeine Erklärung: #🎜🎜##🎜🎜#GROUP BY standardmäßige implizite Sortierung (bezogen auf die GROUP BY-Spalte ohne ASC oder DESC). Indikatoren sind ebenfalls vorhanden sortiert). Allerdings ist GROUP BY für die explizite oder implizite Sortierung veraltet. Um eine bestimmte Sortierreihenfolge zu generieren, stellen Sie eine ORDER BY-Klausel bereit. #🎜🎜##🎜🎜#Vor MySQL8.0 sortierte GROUP BY die Ergebnisse standardmäßig nach dem Effektfeld (dem nachfolgenden Feld von GROUP BY). . Wenn der Index verwendet werden kann, sind für GROUP BY keine zusätzlichen Sortiervorgänge erforderlich. Wenn der Index jedoch nicht zum Sortieren verwendet werden kann, muss sich der MySQL-Optimierer für die Verwendung einer temporären Tabelle entscheiden und diese dann sortieren code>GROUP BY. #🎜🎜##🎜🎜#Und wenn die Größe der Ergebnismenge die vom System festgelegte Größe der temporären Tabelle überschreitet, kopiert MySQL die temporären Tabellendaten vor dem Betrieb auf die Festplatte und die Ausführungseffizienz der Anweisung wird extrem gering . Aus diesem Grund hat MySQL beschlossen, diesen Vorgang (implizite Sortierung) nicht mehr zu verwenden. #🎜🎜##🎜🎜#Aus den oben genannten Gründen hat MySQL dies in 8.0 optimiert und aktualisiert: #🎜🎜#

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

Früher (MySQL 5.7 und niedriger) wurde unter bestimmten Bedingungen implizit durch GROUP BY sortiert Tritt nicht mehr auf, daher ist die Angabe von ORDER BY NULL am Ende zur Unterdrückung der impliziten Sortierung (wie zuvor) nicht mehr erforderlich. Die Abfrageergebnisse können jedoch von früheren MySQL-Versionen abweichen. Geben Sie eine ORDER BY-Klausel an .

Eine grobe Erklärung:

In der Vergangenheit (vor der MySQL5.7-Version) führte Group by eine implizite Sortierung basierend auf bestimmten Bedingungen durch. In MySQL 8.0 wurde diese Funktionalität entfernt, sodass es nicht mehr erforderlich ist, order by null hinzuzufügen, um die implizite Reihenfolge zu deaktivieren. Die Abfrageergebnisse können jedoch von denen früherer MySQL-Versionen abweichen. Um Ergebnisse in einer bestimmten Reihenfolge zu erhalten, geben Sie die zu sortierenden Felder nach ORDER BY an. 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 之前会进行隐式排序，导致触发 filesort，sql 执行效率低下。但从 MySQL8.0 开始，MySQL 就删除了隐式排序，所以，此时在语义相同，无索引的情况下，group by和distinct的执行效率也是近乎等价的。

相比于distinct来说，group by的语义明确。且由于 distinct 关键字会对所有字段生效，在进行复合业务处理时，group by的使用灵活性更高，group by能根据分组情况，对数据进行更为复杂的处理，例如通过having

Daher kommt auch unser Fazit: 🎜

• 🎜Bei gleicher Semantik und Indizierung: group by und unique kann Indizes mit der gleichen Effizienz verwenden. Da group by und distinct nahezu gleichwertig sind, kann „distinct“ als spezielles group by betrachtet werden. 🎜
• 🎜Mit der gleichen Semantik und ohne Index: distinct ist effizienter als group by. Der Grund dafür ist, dass sowohl distinct als auch group by Gruppierungsvorgänge durchführen, group by jedoch vor MySQL8.0 eine implizite Sortierung durchführt, was zu einer Dateisortierung führt Die Effizienz der SQL-Ausführung ist gering. Ab MySQL 8.0 hat MySQL jedoch die implizite Sortierung gelöscht. Daher gilt zu diesem Zeitpunkt die Ausführungseffizienz von group by und distinct unter derselben Semantik und ohne Index Es ist auch fast gleichwertig. 🎜

🎜Im Vergleich zu distinct hat group by eine klare Semantik. Und da das Schlüsselwort „distinct“ auf alle Felder wirkt, ist group by flexibler, wenn eine zusammengesetzte Geschäftsverarbeitung durchgeführt wird. group by kann die Daten entsprechend der Gruppierungssituation durchführen komplexere Verarbeitung, wie etwa das Filtern von Daten durch have oder das Bearbeiten von Daten durch Aggregatfunktionen. 🎜

Das obige ist der detaillierte Inhalt vonSo verwenden Sie „distinct“ und „group by“ in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!