Heim >Datenbank >MySQL-Tutorial >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
的基础使用。
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
多列的去重,则是根据指定的去重的列信息来进行,即只有所有指定的列信息都相同,才会被认为是重复的信息。
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
的使用和 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
都是可以使用索引进行扫描搜索的。例如以下两条 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.
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. #🎜🎜#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. #🎜🎜#rrreeegroup by
“ der von „distinct
“. #🎜🎜#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: #🎜🎜#rrreeeDISTINCT
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#🎜🎜#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
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. 🎜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. 🎜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!