Comment utiliser distinct et group by dans MySQL

Parlons d'abord de la conclusion générale :

• Lorsque la sémantique est la même et qu'il y a un index : group by et distinct peuvent utiliser le indice, et l'efficacité est la même. 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

Avec la même sémantique et sans index : distinct est plus efficace que group by. La raison en est que distinct et group by effectueront des opérations de regroupement, mais group by peut effectuer un tri et déclencher le tri de fichiers, ce qui entraînera une exécution SQL inefficace.

Sur la base de cette conclusion, vous pouvez vous demander : 🎜🎜🎜🎜Pourquoi group by et distinct sont-ils plus efficaces lorsque la sémantique est la même et qu'il y a est-ce qu'un indice est pareil ? 🎜🎜🎜Dans quelles circonstances group by effectuera-t-il des opérations de tri ? 🎜🎜🎜Cherchez les réponses à ces deux questions. Examinons ensuite les utilisations de base de distinct et group by. 🎜

Utilisation de distinct

Utilisation de distinct

rrreee🎜Par exemple : 🎜rrreee🎜DISTINCT Le mot-clé est utilisé pour renvoyer des valeurs différentes de manière unique. Il est utilisé avant le premier champ de l'instruction de requête et s'applique à toutes les colonnes de la clause principale. 🎜🎜Si une colonne a des valeurs NULL et que vous utilisez la clause DISTINCT sur la colonne, MySQL conservera une valeur NULL et supprimera les autres valeurs NULL car le DISTINCT clause will Toutes les valeurs NULL sont traitées comme la même valeur. 🎜

Déduplication multi-colonnes distincte

🎜distinct La déduplication multi-colonnes est effectuée sur la base des informations de colonne de déduplication spécifiées, c'est-à-dire que seules toutes les informations de colonne spécifiées sont les mêmes. considérées comme des informations en double. 🎜rrreee

Utilisation de group by

🎜Pour la déduplication de base, l'utilisation de group by est similaire à distinct. 🎜

Déduplication d'une seule colonne

🎜Syntaxe : 🎜rrreee🎜Exécution : 🎜rrreee

Déduplication de plusieurs colonnes

🎜Syntaxe : 🎜rrreee🎜Exécution : 🎜rrreee

Exemple de différence

🎜La différence de syntaxe entre les deux est que group by peut dédupliquer une seule colonne, tandis que le principe de group by est de d'abord regrouper et trier les résultats, puis de renvoyer le première colonne de chaque groupe. Et la déduplication est effectuée en fonction des champs suivant group by. 🎜🎜Par exemple : 🎜rrreee

distinct et groupe par principe

🎜Dans la plupart des exemples, DISTINCT peut être considéré comme un GROUP BY spécial, leur implémentation est basé sur des opérations de regroupement, et elles peuvent toutes être implémentées via des analyses d'index libres et des analyses d'index compactes (les analyses d'index seront présentées en détail dans d'autres articles, elles ne seront donc pas présentées en détail ici). 🎜🎜DISTINCT et GROUP BY peuvent tous deux être numérisés et recherchés à l'aide d'index. Par exemple, les deux SQL suivants (il suffit de regarder le contenu de l'extra en fin de tableau), on analyse ces deux SQL, on voit que dans l'extra, ces deux SQL utilisent le scanning d'index compact Utilisation d'index pour groupe -by. 🎜🎜Donc, en général, pour les instructions DISTINCT et GROUP BY avec la même sémantique, nous pouvons utiliser la même méthode d'optimisation d'index pour les optimiser. 🎜rrreee🎜Mais pour GROUP BY, avant MYSQL8.0, GROUP Y serait implicitement trié par champs par défaut. 🎜🎜Vous pouvez voir que l'instruction SQL suivante utilise une table temporaire et effectue également le tri de fichiers. 🎜rrreee

Tri implicite

🎜Pour le tri implicite, on peut se référer à l'explication officielle de MySQL : 🎜🎜https://dev.mysql.com/doc/refman/5.7/en/order-by-optimisation. html🎜

🎜GROUP BY trie implicitement par défaut (c'est-à-dire en l'absence de désignateurs ASC ou DESC pour les colonnes GROUP BY. Cependant, en s'appuyant sur le tri implicite GROUP BY (c'est-à-dire en l'absence de désignateurs ASC ou DESC). désignateurs) ou le tri explicite pour GROUP BY (c'est-à-dire en utilisant des désignateurs ASC ou DESC explicites pour les colonnes GROUP BY) est obsolète. Pour produire un ordre de tri donné, fournissez une clause ORDER BY.🎜

🎜explication approximative Voici une. regardez : 🎜🎜GROUP BY est par défaut un tri implicite (ce qui signifie qu'il sera également trié même si la colonne GROUP BY n'a pas d'indicateur ASC ou DESC). Cependant, GROUP BY pour le tri explicite ou implicite est obsolète. Pour générer un ordre de tri donné, fournissez une clause ORDER BY. 🎜🎜Ainsi, avant MySQL8.0, GROUP BY triait les résultats en fonction du champ d'effet (le champ qui suit GROUP BY) par défaut. Lorsque l'index peut être utilisé, GROUP BY ne nécessite pas d'opérations de tri supplémentaires ; mais lorsque l'index ne peut pas être utilisé pour le tri, l'optimiseur MySQL doit choisir d'utiliser une table temporaire puis la trier <. code>GROUPE PAR. 🎜🎜Et lorsque la taille de l'ensemble de résultats dépasse la taille de la table temporaire définie par le système, MySQL copiera les données de la table temporaire sur le disque avant de fonctionner, et l'efficacité d'exécution de l'instruction deviendra extrêmement faible. C'est pourquoi MySQL a choisi de déprécier cette opération (tri implicite). 🎜🎜Sur la base des raisons ci-dessus, Mysql a optimisé et mis à jour cela dans la version 8.0 :🎜

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

Précédemment (MySQL 5.7 et versions antérieures), GROUPE BY est trié implicitement sous certaines conditions. Dans MySQL 8.0, cela ne se produit plus, donc spécifier ORDER BY NULL à la fin pour supprimer le tri implicite (comme cela était fait précédemment) n'est plus nécessaire. Cependant, les résultats des requêtes peuvent différer des versions précédentes de MySQL. Pour produire un ordre de tri donné, fournissez une clause ORDER BY Tri implicite basé sur certaines conditions. Dans MySQL 8.0, cette fonctionnalité a été supprimée, il n'est donc plus nécessaire d'ajouter order by null pour désactiver l'ordre implicite, cependant, les résultats des requêtes peuvent différer des versions précédentes de MySQL. Pour produire des résultats dans un ordre donné, précisez les champs à trier par ORDER BY.

Par conséquent, notre conclusion ressort également :

• Avec la même sémantique et le même index : les deux group by et distinct peuvent utiliser des index avec la même efficacité. Parce que group by et distinct sont presque équivalents, distinct peut être considéré comme un group by spécial.

• 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能根据分组情况，对数据进行更为复杂的处理，例如通过havingAvec la même sémantique et sans index : distinct est plus efficace que group by. La raison est que distinct et group by effectueront des opérations de regroupement, mais group by effectuera un tri implicite avant MySQL8.0, ce qui entraînera le tri des fichiers. être déclenché. L’efficacité de l’exécution SQL est faible. Mais à partir de MySQL8.0, MySQL a supprimé le tri implicite. Par conséquent, à ce stade, sous la même sémantique et sans index, l'efficacité d'exécution de group by et distinct. C'est aussi presque équivalent.

#🎜🎜#Comparé à distinct, group by a une sémantique claire. Et comme le mot-clé distinct prendra effet sur tous les champs, group by est plus flexible lors de l'exécution d'un traitement métier composite. group by peut regrouper les données en fonction de la situation de regroupement. des traitements plus complexes, tels que le filtrage des données via avoir ou l'exploitation des données via des fonctions d'agrégation. #🎜🎜#

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!