MySQL——distinct与group by去重 / 松散索引扫描&紧凑索引扫描

系统管理员 2023-09-25 19:30 87阅读 0赞

本篇介绍MySQL中的 distinct 和 group by的区别，包括用法、效率，涉及松散索引扫描和紧凑索引扫描的概念；

### distinct用法 ###

示例：

SELECT DISTINCT columns FROM table_name WHERE where_conditions;

DISTINCT关键词修饰查询的列（可以是多列），用于返回唯一的多个不同的列值；

DISTINCT多列的去重，则是根据指定的去重的列信息来进行，即只有所有指定的列信息都相同，才会被认为是重复的信息；

特殊情况：如果列具有NULL值，并且对该列使用DISTINCT子句，MySQL将保留一个NULL值，并删除其它的NULL值，因为DISTINCT子句将所有NULL值视为相同的值；

### group by去重用法 ###

在去重功能上，group by的使用和distinct类似；

除了列去重，group by的主要功能与其语义一样，根据列对数据分组，一般搭聚集配函数使用，用于数据的分组统计，如求和、最值、平均值、计数；

去重示例：

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

### distinct与 group by去重的区别 ###

DISTINCT 实际上和 GROUP BY 操作的实现非常相似，它们的实现都基于分组操作，只不过 DISTINCT 是在 GROUP BY 之后的每组中只取出一条记录而已；

DISTINCT和GROUP BY都是可以使用索引进行扫描搜索的，所以，在一般情况下，对于相同语义的DISTINCT和GROUP BY语句，我们可以对其使用相同的索引优化手段来进行优化，即同样可以通过松散索引扫描或紧凑索引扫描来实现；

对于DISTINCT来说，在无法仅仅使用索引即能完成 DISTINCT 的时候，MySQL 只能通过临时表Using temporary来完成；MySQL 会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行 filesort 操作；

对于GROUP BY来说，在MYSQL8.0之前，GROUP BY默认会依据字段进行**隐式排序**；

示例：

# 表结构 无索引
    CREATE TABLE `user_copy` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `username` VARCHAR(32) NOT NULL COMMENT '用户名',
        `sex` CHAR(1) NULL DEFAULT NULL COMMENT '性别' COLLATE 'utf8_general_ci',
        `address` VARCHAR(255) NULL DEFAULT NULL COMMENT '地址' COLLATE 'utf8_general_ci',
        PRIMARY KEY (`id`) USING BTREE
    )ENGINE=InnoDB;
    
    # SQL执行计划
    explain SELECT DISTINCT `username` FROM user_copy; 
    explain SELECT `username` FROM user_copy GROUP BY `username`;

结果：

![8e41c1f2302f4849840edb2c361a84e6.png][]

![194ece08cf76442ca095ab366b5d16ed.png][]

可以看到，上面这条使用group by的sql语句在使用了临时表的同时，还进行了filesort；

### group by的隐式排序 ###

对于隐式排序，我们可以参考Mysql官方的解释：

[https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html][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了；

且当结果集的大小超出系统设置临时表大小时，Mysql会将临时表数据copy到磁盘上面再进行操作，语句的执行效率会变得极低；这也是Mysql选择将此操作（隐式排序）弃用的原因；

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

[https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html][https_dev.mysql.com_doc_refman_8.0_en_order-by-optimization.html]

> Previously (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.

解释：

Mysql的低版本中，Group by会根据确定的条件进行隐式排序；在mysql 8.0中，已经移除了这个功能，所以不再需要通过添加order by null 来禁止隐式排序了，但是，查询结果可能与以前的 MySQL 版本不同；要生成给定顺序的结果，请按通过ORDER BY指定需要进行排序的字段；

### MySQL查询去重的小结 ###

（1）在语义相同，有索引的情况下，group by和distinct效率相同；

group by和distinct都能使用索引，而索引天然有序，可以避免排序，因此二者效率相同；此情况下，group by和distinct近乎等价，distinct可以被看做是特殊的group by；

（2）在语义相同，无索引的情况下，distinct效率高于group by；

原因是distinct 和 group by都会进行分组操作，但group by在Mysql8.0之前会进行隐式排序，导致触发filesort，执行效率更低；

但从Mysql8.0开始，Mysql就删除了隐式排序；所以Mysql8.0后，此时在语义相同，无索引的情况下，group by和distinct的执行效率也是近乎等价的；

（3）更推荐使用group by；

group by语义更为清晰，可对数据进行更为复杂的一些处理；group by的使用灵活性更高，group by能根据分组情况，对数据进行更为复杂的处理，例如通过having对数据进行过滤，或通过聚合函数对数据进行运算；

### 松散索引扫描&紧凑索引扫描 ###

上面提到一个概念——松散索引扫描&紧凑索引扫描，下面做一下简介，作为知识储备；

当一个查询有分组需求时，如group by或distinct等，可以利用与分组相兼容的索引来避免扫描全部数据行；为了提升扫描效率，MySQL引入了loose index scan，即松散索引扫描；

**分组操作如何利用索引？**

因为innodb使用基于B+ tree的索引组织表，因此索引上的列满足天然有序性，对于组合索引，对组合键值有序；这个特性可以被用于索引扫描不同的group，而不需要扫描全部的索引列；

当MySQL完全利用索引扫描来实现GROUP BY的时候，并不需要扫描所有满足条件的索引键即可完成分组操的方式，称为loose index scan，它可以最大限度的减少需要扫描的ROWs；

Loose Index Scan的示意如下图所示：

![945020c5046746bc938f1aa3c390c92f.jpeg][]

如上图所示，首先查询第一条索引记录，然后查询下一条prefix不同的记录，直到最后一条为止；可见，扫描的索引键的行数就是分组的组数，中间跳过了很多prefix相同的行；

当使用loose index scan时，执行计划会在Extra中显示“Using index for group-by”；

**为什么松散索引扫描的效率会很高？**

因为在没有WHERE 子句，也就是必须经过全索引扫描的时候， 松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多；而在WHERE 子句包含范围判断式或者等值表达式的时候， 松散索引扫描查找满足范围条件的每个组的第1 个关键字，并且再次读取尽可能最少数量的关键字；

**什么是紧凑索引？**

紧凑索引扫描和松散索引扫描的区别主要在于，在扫描索引的时候，它需要读取所有满足条件的索引键，然后再根据读取的全部数据来完成GROUP BY 操作得到相应结果，没有跳过一些索引键；

例如，group by语句中的where条件中，对索引列等值查询和范围查询，执行计划是不同的，分别是走松散索引和紧凑索引；

如下：

# 表结构 联合索引(`c1`, `c2`, `c3`)
    CREATE TABLE `t1` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `c1` VARCHAR(255) NULL DEFAULT '' COLLATE 'utf8_general_ci',
        `c2` VARCHAR(255) NULL DEFAULT '' COLLATE 'utf8_general_ci',
        `c3` VARCHAR(255) NULL DEFAULT '' COLLATE 'utf8_general_ci',
        PRIMARY KEY (`id`) USING BTREE,
        INDEX `c` (`c1`, `c2`, `c3`) USING BTREE
    )ENGINE=InnoDB;
    
    # （1）SQL执行计划 "Using where; Using index" 使用紧凑索引扫描
    explain SELECT DISTINCT `c1` FROM t1 WHERE `c2`>'B'; 
    
    # （2）SQL执行计划 "Using where; Using index for group-by" 使用松散索引扫描
    explain SELECT DISTINCT `c1` FROM t1 WHERE `c2`='B';

解释：因为对于组合索引，对组合键值有序；当做其中某个索引列的等值查询时，执行group by时还是能跳过一些不满足等值条件的行，这种情况走松散索引；但是做范围查询时，就需要找到每一行做范围匹配，因此走紧凑索引；

**执行计划示例**

下面也给出一些松散索引、紧凑索引和走临时表排序的示例；表结构同上表t1；

# （1）SQL执行计划 "Using where; Using index" 使用紧凑索引扫描，索引列范围查询
    explain SELECT DISTINCT `c1` FROM t1 WHERE `c2`>'B'; 
    
    # （2）SQL执行计划 "Using where; Using index for group-by" 使用松散索引扫描，索引列等值查询
    explain SELECT DISTINCT `c1` FROM t1 WHERE `c2`='B'; 
    
    # （3）SQL执行计划 "Using index for group-by" 使用松散索引扫描
    explain SELECT MIN(c2) from t1 group by c1;
    
    # （4）SQL执行计划 "Using index" 使用min/max之外的其它聚集函数，则不能使用松散索引扫描，使用紧凑索引扫描
    explain SELECT SUM(c2) from t1 group by c1;
    
    # （5）SQL执行计划 "Using index for group-by" 使用松散索引扫描，满足索引前缀
    explain SELECT `c1`,`c2` FROM t1 GROUP BY `c1`,`c2`; 
    
    # （6）SQL执行计划 "Using where; Using index for group-by" 使用松散索引扫描，满足索引前缀、索引列等值查询
    explain SELECT `c1`,`c2`,`c3` FROM t1  WHERE c3='C' GROUP BY `c1`,`c2`; 
    
    # （7）SQL执行计划 "Using where; Using index for group-by" 使用松散索引扫描，虽然不满足索引前缀，但前缀中的列为常量
    explain SELECT `c1`,`c2`,`c3` FROM t1  WHERE c1='C' GROUP BY `c1`,`c2`,`c3`; 
    
    # （8）SQL执行计划 "Using index; Using temporary; Using filesort"，使用临时表，不满足前缀索引，分组无法走索引，需要临时表并对分组内元素排序
    explain SELECT `c2`,`c3` FROM t1 GROUP BY `c2`,`c3`; 
    
    # （9）SQL执行计划 "Using index; Using temporary; Using filesort"，使用临时表，不满足前缀索引，分组无法走索引，需要临时表并对分组内元素排序
    explain SELECT `c1`,`c3` FROM t1 GROUP BY `c1`,`c3`; 
    
    # （10）SQL执行计划 "Using where; Using index"，使用紧凑索引扫描，却别于（9）使用临时表，尽管不满足前缀索引，但前缀中的列为常量
    explain SELECT `c1`,`c3` FROM t1 WHERE c2='B' GROUP BY `c1`,`c3`;

参考：

[京东一面：MySQL 中的 distinct 和 group by 哪个效率更高？][MySQL _ distinct _ group by]

[MySQL DISTINCT 的基本实现原理&紧凑/松散索引扫描示例][MySQL DISTINCT]

[MySQL松散索引扫描与紧凑索引扫描][MySQL]

[8e41c1f2302f4849840edb2c361a84e6.png]: https://img-blog.csdnimg.cn/img_convert/8e41c1f2302f4849840edb2c361a84e6.png
[194ece08cf76442ca095ab366b5d16ed.png]: https://img-blog.csdnimg.cn/img_convert/194ece08cf76442ca095ab366b5d16ed.png
[https_dev.mysql.com_doc_refman_5.7_en_order-by-optimization.html]: https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
[https_dev.mysql.com_doc_refman_8.0_en_order-by-optimization.html]: https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
[945020c5046746bc938f1aa3c390c92f.jpeg]: https://img-blog.csdnimg.cn/img_convert/945020c5046746bc938f1aa3c390c92f.jpeg
[MySQL _ distinct _ group by]: https://mp.weixin.qq.com/s/L3PfH6-Kithercw4Aviaeg
[MySQL DISTINCT]: https://www.jb51.net/article/165135.htm
[MySQL]: https://blog.51cto.com/u_12004792/3139800