深入掌握MySQL：实用学习笔记

更新:11-14 神话故事我要投稿纠错投诉

大家好，深入掌握MySQL：实用学习笔记相信很多的网友都不是很明白，包括也是一样，不过没有关系，接下来就来为大家分享关于深入掌握MySQL：实用学习笔记和的一些知识点，大家可以关注收藏，免得下次来找不到哦，下面我们开始吧！

在语义相同且没有索引的情况下：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;例如：

mysqlselect 与学生不同的年龄；

+------+

|年龄|

+------+

| 10 | 10

| 12 | 12

| 11 | 11

|空|

+------+

集合中4 行（0.01 秒） DISTINCT 关键字用于返回唯一不同的值。它用在查询语句中的第一个字段之前，并适用于主子句中的所有列。

如果一列有NULL 值，并且您对该列使用DISTINCT 子句，MySQL 将保留一个NULL 值并删除其他NULL 值，因为DISTINCT 子句将所有NULL 值视为相同的值。

distinct多列去重根据指定的去重列信息进行独特的多列去重。即只有当指定的所有列信息都相同时才会被视为重复信息。

SELECT DISTINCT column1,column2 FROM table_name WHERE where_conditions;mysqlselect 不同的性别，年龄来自学生；

+--------+------+

|性别|年龄|

+--------+------+

|男| 10 | 10

|女| 12 | 12

|男| 11 | 11

|男|空|

|女| 11 | 11

+--------+------+

5 rows in set (0.02 sec)

group by的使用

对于基本的去重，group by的使用与distinct:类似

单列去重语法：

SELECT 列FROM table_name WHERE where_conditions GROUP BY 列；执行：

mysql按年龄从学生组中选择年龄；多列去重语法：

SELECT 列FROM table_name WHERE where_conditions GROUP BY 列；执行：

mysql按年龄从学生组中选择年龄；

+------+

|年龄|

+------+

| 10 | 10

| 12 | 12

| 11 | 11

|空|

+------+

4 rows in set (0.02 sec)区别示例两者语法差异在于group by可以进行单列去重。 group by的原理是先对结果进行分组排序，然后返回每组中的第一条数据。并且根据group by后面的字段进行重复数据删除。

例如：

mysql按性别从学生组中选择性别、年龄；

+--------+-----+

|性别|年龄|

+--------+-----+

|男| 10 | 10

|女| 12 | 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——经过。

因此，一般来说，对于语义相同的DISTINCT和GROUP BY语句，我们可以使用相同的索引优化方法来对其进行优化。

mysqlexplain select int1_index from test_distinct_groupby group by int1_index;

+----+-------------+----------------------+--------- - ----+--------+----------------+---------+--------- + ------+------+----------+--------------------- --+

|编号|选择类型|表|隔断|类型|可能的键|关键| key_len |参考|行|过滤|额外|

+----+-------------+----------------------+--------- - ----+--------+----------------+---------+--------- + ------+------+----------+--------------------- --+

| 1 |简单|测试_distinct_groupby |空|范围|索引_1 |索引_1 | 5 |空| 955 | 955 100.00 |使用索引进行分组|

+----+-------------+----------------------+--------- - ----+--------+----------------+---------+--------- + ------+------+----------+--------------------- --+

一组1 行（0.05 秒）

mysqlexplain从test_distinct_groupby中选择distinct int1_index;

+----+-------------+----------------------+--------- - ----+--------+----------------+---------+--------- + ------+------+----------+--------------------- --+

|编号|选择类型|表|隔断|类型|可能的键|关键| key_len |参考|行|过滤|额外|

+----+-------------+----------------------+--------- - ----+--------+----------------+---------+--------- + ------+------+----------+--------------------- --+

| 1 |简单|测试_distinct_groupby |空|范围|索引_1 |索引_1 | 5 |空| 955 | 955 100.00 |使用索引进行分组|

+----+-------------+----------------------+--------- - ----+--------+----------------+---------+--------- + ------+------+----------+--------------------- --+

1 row in set (0.05 sec) 但是对于GROUP BY，在MYSQL8.0 之前，GROUP Y 默认会按字段隐式排序。

正如您所看到的，以下SQL 语句使用临时表并执行文件排序。

mysqlexplain select int6_bigger_random from test_distinct_groupby GROUP BY int6_bigger_random;

+----+-------------+----------------------+--------- - ----+--------+----------------+--------+---------+--- - --+--------+----------+---------------------------- - -----+

|编号|选择类型|表|隔断|类型|可能的键|关键| key_len |参考|行|过滤|额外|

+----+-------------+----------------------+--------- - ----+--------+----------------+--------+---------+--- - --+--------+----------+---------------------------- - -----+

| 1 |简单|测试_distinct_groupby |空|全部|空|空|空|空| 97402 | 97402 100.00 |使用临时的；使用文件排序|

+----+-------------+----------------------+--------- - ----+--------+----------------+--------+---------+--- - --+--------+----------+---------------------------- - -----+

1 row in set (0.04 sec)隐式排序对于隐式排序，我们可以参考Mysql官方的解释：

MySQL : MySQL 5.7 参考手册： 8.2.1.14 ORDER BY 优化

默认情况下，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优化器就不得不选择使用临时表来实现GROUP BY，然后再排序。

并且当结果集的大小超过系统设置的临时表大小时，Mysql会在操作前将临时表数据复制到磁盘上，语句的执行效率会变得极低。这也是Mysql选择弃用这个操作（隐式排序）的原因。

基于以上原因，Mysql在8.0对此进行了优化更新：

MySQL : MySQL 8.0 参考手册： 8.2.1.16 ORDER BY 优化

以前（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都会进行分组操作，但在Mysql8.0之前group by会进行隐式排序，导致filesort被触发，SQL执行效率低下。但从Mysql8.0开始，Mysql删除了隐式排序。因此，在相同语义且无索引的情况下，group by和distinct的执行效率几乎相当。