西宁网站建设有限公司,无锡网站App微信,衡水seo外包,wordpress 功能插件先说大致的结论#xff08;完整结论在文末#xff09;#xff1a;在语义相同#xff0c;有索引的情况下#xff1a;group by和distinct都能使用索引#xff0c;效率相同。在语义相同#xff0c;无索引的情况下#xff1a;distinct效率高于group by。原因是distinct 和 …先说大致的结论完整结论在文末在语义相同有索引的情况下group by和distinct都能使用索引效率相同。在语义相同无索引的情况下distinct效率高于group by。原因是distinct 和 group by都会进行分组操作但group by可能会进行排序触发filesort导致sql执行效率低下。基于这个结论你可能会问为什么在语义相同有索引的情况下group by和distinct效率相同在什么情况下group by会进行排序操作带着这两个问题找答案。接下来我们先来看一下distinct和group by的基础使用。另外如果你近期准备面试跳槽建议在Java面试库小程序在线刷题涵盖 2000 道 Java、MySQL 面试题几乎覆盖了所有主流技术面试题。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它们的实现都基于分组操作且都可以通过松散索引扫描、紧凑索引扫描(关于索引扫描的内容会在其他文章中详细介绍就不在此细致介绍了)来实现。另外如果你近期准备面试跳槽建议在Java面试库小程序在线刷题涵盖 2000 道 Java、MySQL 面试题几乎覆盖了所有主流技术面试题。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.htmlGROUP 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了。且当结果集的大小超出系统设置临时表大小时Mysql会将临时表数据copy到磁盘上面再进行操作语句的执行效率会变得极低。这也是Mysql选择将此操作隐式排序弃用的原因。另外如果你近期准备面试跳槽建议在Java面试库小程序在线刷题涵盖 2000 道 Java、MySQL 面试题几乎覆盖了所有主流技术面试题。基于上述原因Mysql在8.0时对此进行了优化更新https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.htmlPreviously (MySQL 5.7 and lower), GROUP BY sorted implicitly under certain conditions. In MySQL 8.0, that no longer occurs, so specifying ORDER BY NULL at the end to suppress implicit sorting (as was done previously) is no longer necessary. However, query results may differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.大致解释一下从前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都会进行分组操作但group by在Mysql8.0之前会进行隐式排序导致触发filesortsql执行效率低下。但从Mysql8.0开始Mysql就删除了隐式排序所以此时在语义相同无索引的情况下group by和distinct的执行效率也是近乎等价的。推荐group by的原因group by语义更为清晰group by可对数据进行更为复杂的一些处理相比于distinct来说group by的语义明确。且由于distinct关键字会对所有字段生效在进行复合业务处理时group by的使用灵活性更高group by能根据分组情况对数据进行更为复杂的处理例如通过having对数据进行过滤或通过聚合函数对数据进行运算。
