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

本篇介绍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`; 

结果:

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

group by的隐式排序

对于隐式排序,我们可以参考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了;

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

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

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的示意如下图所示:

如上图所示,首先查询第一条索引记录,然后查询下一条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 的基本实现原理&紧凑/松散索引扫描示例

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

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.mfbz.cn/a/5119.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

CVE-2023-28708 原理剖析

CVE-2023-28708 原理剖析这应该不是一个严重的漏洞,可能评分只能为低,因为并没有什么卵用。 话不多说,直接进入正题 我的复现环境: tomcat-8.5.50 首先我们得简单写一个servlet,当然不写也没事,因为我们的…

【C语言学习】结构体

结构体(Struct)从本质上讲是一种自定义的数据类型,只不过这种数据类型比较复杂,是由 int、char、float 等基本类型组成的。你可以认为结构体是一种聚合类型。 在实际开发中,我们可以将一组类型不同的、但是用来描述同…

[技术经理]02 什么是技术经理?

目录01什么是技术经理02总结01什么是技术经理 什么是技术经理? 我用一句话概括为:专业技术团队的管理者。 技术经理,是一种管理职位,通常是在软件开发、互联网等科技公司或技术团队中担任。 技术经理的职责,**是管理…

Docker入门

文章目录Docker为什么出现Docker能干嘛学习途径Docker安装Docker的基本组成环境说明安装步骤阿里云镜像加速底层原理Docker为什么出现 一款产品从开发到上线,从操作系统,到运行环境,再到应用配置。作为开发运维之间的协作我们需要 关心很多东…

文献阅读(247)AIpa

题目:Alpa: Automating Inter- and Intra-Operator Parallelism for Distributed Deep Learning时间:2022会议:OSDI研究机构:UCB 传统的DNN并行策略: 现有的分布式训练系统要么需要用户手动创建并行化计划&#xff0c…

测试笔记:接口测试

目录1.接口(1)接口概念(2)接口类型2、接口风格(1)传统风格(2)RESTful风格接口3、接口测试(1)接口测试是什么(2)接口测试原理&#xff…

Node.js学习笔记——fs模块

fs全称为file system,称之为文件系统,是Node.js中的内置模块,可以对计算机中的磁盘进行操作。 本章节会介绍如下操作: 文件写入文件读取文件移动与重命名文件删除文件夹操作查看资源状态 一、文件写入 文件写入就是将数据保存…

利用nginx实现动静分离的负载均衡集群实战

前言 大家好,我是沐风晓月,今天我们利用nginx来作为负载,实现两台apache服务器的动静分离集群实战; 本文收录于沐风晓月的专栏《linux基本功-系统服务实战》,更多内容可以关注我的博客: https://blog.csd…

Visual Studio 2015 + cmake编译QT5程序

概述 由于QT的集成开发环境QTCreate,在代码调试功能上远不及Visual Studio方便,因此,在Windows平台,可以使用Visual Studio来开发调试QT程序,本文章就主要介绍下,如何使用CMAKE编译QT5程序,并使…

【JAVA真的没出路了吗?】

2023年了,转行IT学习Java是不是已经听过看过很多次了。随之而来的类似学Java没出路、Java不行了、对Java感到绝望等等一系列的制造焦虑的话题也在网上层出不穷,席卷了一大片的对行业不了解的吃瓜群众或是正在学习中的人。如果是行外人真的会被这种言论轻…

【教程】使用ChatGPT制作基于Tkinter的桌面时钟

目录 描述 代码 效果 说明 下载 描述 给ChatGPT的描述内容: python在桌面上显示动态的文字,不要显示窗口边框。窗口背景和标签背景都是透明的,但标签内的文字是有颜色。使用tkinter库实现,并以class的形式书写,方…

GPS时间序列分析---剔除跳跃点,拟合时间序列

通常利用GPS时间序列进行数据分析时,会遇到大地震的发生,这个时候会导致GPS的观测结果出现很大的跳跃值,这对后续的数据处理和分析带来了困难。这里分享一个最近了解的,可以用于处理这一问题的工具包---TSAnalyzer。下面主要介绍该…

Adobe:当创意工作遇上生成式AI

放眼全球IT行业,当前最炙手可热的领域是什么?答案显然只有一个:因为ChatGPT而火爆全球的生成式AI(Artificial Intelligence Generated Content,简称AIGC),又称人工智能生成内容。那么当创意设计…

再学一下Feign的原理

简介 Feign是Spring Cloud Netflix组件中的一个轻量级Restful的HTTP服务客户端,它简化了服务间调用的方式。 Feign是一个声明式的web service客户端.它的出现使开发web service客户端变得更简单.使用Feign只需要创建一个接口加上对应的注解, 比如FeignClient注解。…

Go分布式爬虫学习笔记(十四)

文章目录14_context为什么需要Context?级联退出Context 的使用方法。context.ValuecontextContext 最佳实践Context 底层原理14_context Never start a goroutine without knowing how it will stop。 如果你不知道协程如何退出,就不要使用它。 为什么需要Context?…

家政服务系统APP小程序需具备哪些功能?

由于工作忙碌或者是懒人经济作祟,现代人对于家政服务的需求直线上升。而且互联网技术深入生活的方方面面,让上门家政服务系统开发成为很多线下家政公司转型互联网的方式,那么开发一款家政服务系统APP小程序需具备哪些功能呢? …

逻辑回归 算法推导与基于Python的实现详解

文章目录1 逻辑回归概述2 逻辑回归公式推导与求解2.1 公式推导2.2公式求解3 基于Python的实现3.1可接收参数3.2 完整代码示例1 逻辑回归概述 逻辑回归(Logistic Regression)是一种用于分类问题的统计学习方法。它基于线性回归的原理,通过将线…

【软考——系统架构师】架构、系分、软设的区别和联系

🔎这里是【软考——系统架构师】,关注我考试轻松过线 👍如果对你有帮助,给博主一个免费的点赞以示鼓励 欢迎各位🔎点赞👍评论收藏⭐️ 文章目录👀三科相同点👀三科不同点--上午题&am…

CISAW-CISDR灾难备份与恢复专业级认证

证书样板: 认证机构 中国网络安全审查技术与认证中心(英文缩写为:CCRC,原为中国信息安全认证中心)于 2006 年由中央机构编制委员会办公室批准成立,为国家市场监督管理总局直属事业单位。依据《网络安全法》 《网络安…

Java每日一练(20230401)

目录 1. 合并K个升序链表 🌟🌟🌟 2. 最长有效括号 🌟🌟🌟 3. 分割回文串 🌟🌟 🌟 每日一练刷题专栏 🌟 Golang每日一练 专栏 Python每日一练 专栏 …
最新文章