MySQL에서 구별 및 그룹화 기준을 사용하는 방법

먼저 일반적인 결론에 대해 이야기해 보겠습니다.

• 의미가 동일하고 인덱스가 있는 경우: group by 및 distinct를 둘 다 사용할 수 있습니다. 인덱스와 동일한 효율성을 갖습니다. 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

동일한 의미 체계 및 색인 없음: distinct가 group by보다 더 효율적입니다. 그 이유는 개별 및 group by는 모두 그룹화 작업을 수행하지만 group by는 정렬을 수행하고 파일 정렬을 트리거하여 SQL 실행이 비효율적일 수 있기 때문입니다.

이 결론을 바탕으로 다음과 같이 질문할 수 있습니다. 🎜🎜🎜🎜의미가 동일하고 있을 때 group by 및 distinct가 더 효율적인 이유는 무엇입니까? 지수는 똑같나요? 🎜🎜🎜어떤 상황에서 group by가 정렬 작업을 수행하나요? 🎜🎜🎜이 두 가지 질문으로 답을 찾아보세요. 다음으로 distinct 및 group by의 기본 사용법을 살펴보겠습니다. 🎜

distinct 사용

distinct 사용

rrreee🎜예: 🎜rrreee🎜DISTINCT 키워드는 고유하게 다른 값을 반환하는 데 사용됩니다. 쿼리 문의 첫 번째 필드 앞에 사용되며 주 절의 모든 열에 적용됩니다. 🎜🎜열에 NULL 값이 있고 해당 열에 DISTINCT 절을 사용하면 MySQL은 하나의 NULL 값을 유지하고 다른 NULL 값을 삭제합니다. > 절은 모든 NULL 값을 동일한 값으로 취급합니다. 🎜

고유 다중 열 중복 제거

🎜고유 다중 열 중복 제거는 지정된 중복 제거 열 정보를 기반으로 수행됩니다. 즉, 지정된 모든 열 정보만 동일합니다. 중복된 정보로 간주됩니다. 🎜rrreee

그룹별 사용

🎜기본 중복 제거의 경우 그룹별 사용은 고유와 유사합니다. 🎜

단일 열 중복 제거

🎜구문: 🎜rrreee🎜실행: 🎜rrreee

다중 열 중복 제거

🎜구문: 🎜rrreee🎜실행: 🎜rrreee

차이 예

🎜둘 사이의 구문 차이점은 group by가 단일 열을 중복 제거할 수 있는 반면, group by의 원칙은 먼저 결과를 그룹화하고 정렬한 다음 각 그룹의 첫 번째 열입니다. 그리고 group by 다음에 나오는 필드를 기반으로 중복 제거가 수행됩니다. 🎜🎜예: 🎜rrreee

원칙에 따라 고유하고 그룹화

🎜대부분의 예에서 DISTINCT는 특별한 GROUP BY로 간주될 수 있습니다. 그룹핑 연산을 기반으로 하며 루즈 인덱스 스캔과 컴팩트 인덱스 스캔을 통해 모두 구현할 수 있다. (인덱스 스캔은 다른 글에서 자세히 소개할 것이므로 여기서는 자세히 소개하지 않는다.) 🎜🎜DISTINCT 및 GROUP BY는 모두 색인을 사용하여 스캔하고 검색할 수 있습니다. 예를 들어, 다음 두 SQL(테이블 끝에 있는 추가 항목의 내용을 살펴보세요)에서 이 두 SQL을 분석하면 추가 항목에서 이 두 SQL이 컴팩트 인덱스 스캐닝을 사용한다는 것을 알 수 있습니다. 인덱스 사용 그룹별로. 🎜🎜따라서 일반적으로 동일한 의미를 갖는 DISTINCT 및 GROUP BY 문의 경우 동일한 인덱스 최적화 방법을 사용하여 최적화할 수 있습니다. 🎜rrreee🎜그러나 GROUP BY의 경우 MYSQL8.0 이전에는 GROUP Y가 기본적으로 필드별로 암시적으로 정렬되었습니다. 🎜🎜다음 sql 문은 임시 테이블을 사용하고 파일 정렬도 수행하는 것을 볼 수 있습니다. 🎜rrreee

암시적 정렬

🎜암시적 정렬에 대해서는 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 최적화 프로그램은 임시 테이블을 사용하도록 선택한 다음 이를 정렬해야 합니다. 코드>GROUP BY. 🎜🎜그리고 결과 세트의 크기가 시스템에서 설정한 임시 테이블 크기를 초과하는 경우 MySQL은 작동하기 전에 임시 테이블 데이터를 디스크에 복사하므로 명령문의 실행 효율성이 매우 낮아집니다. 이것이 MySQL이 이 작업(암시적 정렬)을 더 이상 사용하지 않기로 선택한 이유입니다. 🎜🎜위의 이유를 바탕으로 Mysql은 8.0에서 이를 최적화하고 업데이트했습니다.🎜

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

이전(MySQL 5.7 이하)에서는 GROUP BY가 특정 조건에서 암시적으로 정렬되었습니다. 더 이상 발생하지 않으므로 암시적 정렬을 억제하기 위해 끝에 ORDER BY NULL을 지정하는 것이 더 이상 필요하지 않습니다. 그러나 쿼리 결과는 이전 MySQL 버전과 다를 수 있습니다. 지정된 정렬 순서를 생성하려면 ORDER BY 절을 제공하세요. .

대략적인 설명:

과거(MySQL5.7 버전 이전)에는 Group by가 특정 조건에 따라 암시적 정렬을 수행했습니다. MySQL 8.0에서는 이 기능이 제거되었으므로 암시적 순서를 비활성화하기 위해 더 이상 order by null을 추가할 필요가 없습니다. 그러나 쿼리 결과는 이전 MySQL 버전과 다를 수 있습니다. 지정된 순서로 결과를 생성하려면 ORDER BY를 기준으로 정렬할 필드를 지정하세요. 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 및 independent는 동일한 효율성으로 인덱스를 사용할 수 있습니다. group by와 distinct는 거의 동일하므로, independent를 특별한 group by로 간주할 수 있습니다. 🎜
• 🎜 의미가 동일하고 색인이 없는 경우: distinct가 group by보다 더 효율적입니다. 그 이유는 distinct와 group by가 모두 그룹화 작업을 수행하지만 group by는 MySQL8.0 이전에 암시적 정렬을 수행하여 파일 정렬이 SQL 실행 효율성이 낮습니다. 그러나 MySQL8.0부터 MySQL은 암시적 정렬을 삭제했습니다. 따라서 현재 동일한 의미와 인덱스가 없는 경우 group by 및 distinct의 실행 효율성이 향상됩니다. 또한 거의 동일합니다. 🎜

🎜 distinct와 비교하여 group by는 의미가 명확합니다. 그리고 고유 키워드는 모든 필드에 적용되므로 복합 비즈니스 처리를 수행할 때 group by가 더 유연하여 그룹화 상황에 따라 데이터를 그룹화할 수 있습니다. having을 통한 데이터 필터링이나 집계 함수를 통한 데이터 작업과 같은 보다 복잡한 처리. 🎜

위 내용은 MySQL에서 구별 및 그룹화 기준을 사용하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!