MySQL DISTINCT 的基本实现原理详解
作者:Sky.Jian 发布时间:2024-01-15 17:21:29
前言
DISTINCT 实际上和 GROUP BY 操作的实现非常相似,只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以,DISTINCT 的实现和 GROUP BY 的实现也基本差不多,没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现,当然,在无法仅仅使用索引即能完成 DISTINCT 的时候,MySQL 只能通过临时表来完成。
但是,和 GROUP BY 有一点差别的是,DISTINCT 并不需要进行排序。也就是说,在仅仅只是 DISTINCT 操作的 Query 如果无法仅仅利用索引完成操作的时候,MySQL 会利用临时表来做一次数据的“缓存”,但是不会对临时表中的数据进行 filesort 操作。
当然,如果我们在进行 DISTINCT 的时候还使用了 GROUP BY 并进行了分组,并使用了类似于 MAX 之类的聚合函数操作,就无法避免 filesort 了。
下面我们就通过几个简单的 Query 示例来展示一下 DISTINCT 的实现。
1.首先看看通过松散索引扫描完成 DISTINCT 的操作:
sky@localhost : example 11:03:41> EXPLAIN SELECT DISTINCT group_id
-> FROM group_messageG
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: NULL
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 10
Extra: Using index for group-by
1 row in set (0.00 sec)
我们可以很清晰的看到,执行计划中的 Extra 信息为“Using index for group-by”,这代表什么意思?为什么我没有进行 GROUP BY 操作的时候,执行计划中会告诉我这里通过索引进行了 GROUP BY 呢?
其实这就是于 DISTINCT 的实现原理相关的,在实现 DISTINCT的过程中,同样也是需要分组的,然后再从每组数据中取出一条返回给客户端。而这里的 Extra 信息就告诉我们,MySQL 利用松散索引扫描就完成了整个操作。
当然,如果 MySQL Query Optimizer 要是能够做的再人性化一点将这里的信息换成“Using index for distinct”那就更好更容易让人理解了,呵呵。
2.我们再来看看通过紧凑索引扫描的示例:
sky@localhost : example 11:03:53> EXPLAIN SELECT DISTINCT user_id
-> FROM group_message
-> WHERE group_id = 2G
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: ref
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: const
rows: 4
Extra: Using WHERE; Using index
1 row in set (0.00 sec)
这里的显示和通过紧凑索引扫描实现 GROUP BY 也完全一样。实际上,这个 Query 的实现过程中,MySQL 会让存储引擎扫描 group_id = 2 的所有索引键,得出所有的 user_id,然后利用索引的已排序特性,每更换一个 user_id 的索引键值的时候保留一条信息,即可在扫描完所有 gruop_id = 2 的索引键的时候完成整个 DISTINCT 操作。
3.下面我们在看看无法单独使用索引即可完成 DISTINCT 的时候会是怎样:
sky@localhost : example 11:04:40> EXPLAIN SELECT DISTINCT user_id
-> FROM group_message
-> WHERE group_id > 1 AND group_id < 10G
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using WHERE; Using index; Using temporary
1 row in set (0.00 sec)
当 MySQL 无法仅仅依赖索引即可完成 DISTINCT 操作的时候,就不得不使用临时表来进行相应的操作了。但是我们可以看到,在 MySQL 利用临时表来完成 DISTINCT 的时候,和处理 GROUP BY 有一点区别,就是少了 filesort。
实际上,在 MySQL 的分组算法中,并不一定非要排序才能完成分组操作的,这一点在上面的 GROUP BY 优化小技巧中我已经提到过了。实际上这里 MySQL 正是在没有排序的情况下实现分组最后完成 DISTINCT 操作的,所以少了 filesort 这个排序操作。
4.最后再和 GROUP BY 结合试试看:
sky@localhost : example 11:05:06> EXPLAIN SELECT DISTINCT max(user_id)
-> FROM group_message
-> WHERE group_id > 1 AND group_id < 10
-> GROUP BY group_idG
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using WHERE; Using index; Using temporary; Using filesort
1 row in set (0.00 sec)
最后我们再看一下这个和 GROUP BY 一起使用带有聚合函数的示例,和上面第三个示例相比,可以看到已经多了 filesort 排序操作了,正是因为我们使用了 MAX 函数的缘故。要取得分组后的 MAX 值,又无法使用索引完成操作,只能通过排序才行了。
由于 DISTINCT的实现基本上和 GROUP BY 的实现差不多,所以这篇文章就不再画图展示实现过程了
来源:http://isky000.com/database/mysql_distinct_implement
猜你喜欢
- Python标准库的OS模块对操作系统的API进行了封装,并且使用统一的API访问不同操作系统的相同功能。OS模块包含与操作系统的系统环境、
- 最近要做数据库同步,如果网上找了例子,成功,记录下来,下回再看。这个是网上找的一编文章。以下配置在本机上已经成功:实现功能:A为主服务器,B
- 这里直接给出第一个版本的直接实现:import osimport numpy as npfrom sklearn.cluster impor
- 前言:如何悄悄的打开朋友的摄像头,看看她最近过的怎么样,嘿嘿!这次让我带你们来实现这个功能。注: 这个程序仅限在朋友之间开玩笑,别去搞什么违
- 前言今天我要教大家的是 如何实现nonebot插件之ChatGpt注意,本文涉及异步爬虫,json文件读写等知识点准备1.获取开发者key获
- 1.彻底弄懂CSS盒子模式一(DIV布局快速入门)3.彻底弄懂CSS盒子模式三(浮动的表演和清除的自述) 4.彻底弄懂CSS盒子模式四(绝对
- 一提到数字图像处理,可能大多数人就会想到matlab,但matlab也有自身的缺点:1、不开源,价格贵2、软件容量大。一般3G以上,高版本甚
- FastText是一个三层的神经网络,输入层、隐含层和输出层。FastText的优点:使用浅层的神经网络实现了word2vec以及文本分类功
- 前言最近写了oracle过程,有个ORACLE中dbms_output.put_line输出不了的问题。 花了近一个小时。下面描述一个这解决
- 代码很简洁,也很简单,就不多废话了。/** * 去除多余的0 */ function del0($s)&
- 问题作为一个负责几个服务器的数据库管理员,我接到许多电话是关于磁盘空间的。我所做的第一件事是找到可以缩小的数据文件来释放一些磁盘上的空间。我
- 将dataframe添加到texttable里面,实现格式化输出。data=[{"name":"Amay&qu
- 最近花些时间学习了一下Python,并写了一个多线程的爬虫程序来获取电影天堂上资源的迅雷下载地址,代码已经上传到GitHub上了,需要的同学
- 主要有以下步骤:1、人脸检测2、人脸预处理3、从收集的人脸训练机器学习算法4、人脸识别5、收尾工作人脸检测算法:基于Haar的脸部检测器的基
- 在使用opencv显示图像时,有时候需要显示多张图像,就会出现多个窗口,显得冗余,用户不好操作。这时候就想着能不能将这些图像在一个窗口中显示
- 准备工作:首先,我们需要 import 几个工具包,一个是 python 标准库中的 wave 模块,用于音频处理操作,另外两个是 nump
- python查找多层嵌套字典的值def find_dic(item, key): if isinstance(it
- 本文用python在TCP的基础上实现一个HTTP客户端, 该客户端能够复用TCP连接, 使用HTTP1.1协议. 一. 创建HT
- 一.WITH AS的含义 WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片
- 在ASP编程中,身份认证可以说是常要用到的。但怎么样才能做到认证的安全呢?表单提交页面:sub.htm &