当前位置:首页 > mysql > 正文内容

mysql中distinct的实现与优化

phpmianshi4年前 (2017-04-23)mysql431

概念

DISTINCT 实际上和 GROUP BY的操作非常相似,只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以,DISTINCT 的实现和 GROUP BY 的实现也基本差不多,没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现,当然,在无法仅仅使用索引即能完成 DISTINCT 的时候,MySQL 只能通过临时表来完成。但是,和 GROUP BY 有一点差别的是,DISTINCT 并不需要进行排序。也就是说,在仅仅只是 DISTINCT 操作的 Query 如果无法仅仅利用索引完成操作的时候,MySQL 会利用临时表 来做一次数据的“缓存”,但是不会对临时表中的数据进行 filesort 操作。当然,如果我们在进行 DISTINCT 的时候还使用了 GROUP BY 并进行了分组,并使用了类似于 MAX 之类的聚合函数操作,就无法避免 filesort 了。

示例

1. 首先看看通过松散索引扫描完成 DISTINCT 的操作:

EXPLAIN SELECT DISTINCT group_id FROM group_message\G

只列重点:

*************************** 1. row ***************************

key: idx_gid_uid_gc

Extra: Using index for group-by


我们可以很清晰的看到,执行计划中的 Extra 信息为“Using index for group-by”,这代表什么意思?为什么我没有进行 GROUP BY 操作的时候,执行计划中会告诉我这里通过索引进行了 GROUP BY 呢?其实这就是于 DISTINCT 的实现原理相关的,在实现 DISTINCT 的过程中,同样也是需要分组的,然后再从每组数据中取出一条返回给客户端。而这里的 Extra 信息就告诉我们,MySQL 利用松散 索引扫描就完成了整个操作。


2. 我们再来看看通过紧凑索引扫描的示例:

EXPLAIN SELECT DISTINCT user_id FROM group_message WHERE group_id = 2\G

*************************** 1. row ***************************

key: idx_gid_uid_gc

Extra: Using WHERE; Using index


这里的显示和通过紧凑索引扫描实现 GROUP BY 也完全一样。实际上,这个 Query 的实现过程中, MySQL 会让存储引擎扫描 group_id = 2 的所有索引键,得出所有的 user_id,然后利用索引的已排序 特性,每更换一个 user_id 的索引键值的时候保留一条信息,即可在扫描完所有 gruop_id = 2 的索引 键的时候完成整个 DISTINCT 操作。


3. 下面我们再看看无法单独使用索引即可完成 DISTINCT 的时候会是怎样:

EXPLAIN SELECT DISTINCT user_id FROM group_message WHERE group_id > 1 AND group_id < 10\G

*************************** 1. row ***************************

key: idx_gid_uid_gc

Extra: Using WHERE; Using index; Using temporary


当 MySQL 无法仅仅依赖索引即可完成 DISTINCT 操作的时候,就不得不使用临时表来进行相应的操 作了。但是我们可以看到,在 MySQL 利用临时表来完成 DISTINCT 的时候,和处理 GROUP BY 有一点区 别,就是少了 filesort。实际上,在 MySQL 的分组算法中,并不一定非要排序才能完成分组操作的, 这一点在上面的 GROUP BY 优化小技巧中我已经提到过了。实际上这里 MySQL 正是在没有排序的情况下 实现分组最后完成 DISTINCT 操作的,所以少了 filesort 这个排序操作。


4. 最后再和 GROUP BY 结合试试看:

EXPLAIN SELECT DISTINCT max(user_id) FROM group_message WHERE group_id > 1 AND group_id < 10 GROUP BY group_id\G

*************************** 1. row ***************************

key: idx_gid_uid_gc

Extra: Using WHERE; Using index; Using temporary; Using filesort


最后我们再看一下这个和 GROUP BY 一起使用带有聚合函数的示例,和上面第三个示例相比,可以 看到已经多了 filesort 排序操作了,因为我们使用了 MAX 函数的缘故。

总结

对于 DISTINCT 的优化,和 GROUP BY 基本上一致的思路,关键在于利用好索引,在无法利用索引 的时候,确保尽量不要在大结果集上面进行 DISTINCT 操作,磁盘上面的IO 操作和内存中的IO 操作性能 完全不是一个数量级的差距。

版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

innodb中内存管理机制BufferPool

innodb中内存管理机制BufferPool

内存结构InnoDB主索引是聚簇索引,索引与数据共用表空间,对于InnoDB而言,数据就是索引,索引就是数据。InnoDB缓存机制和MyISAM缓存机制的最大区别就是在于,InnoDB不仅仅是缓存索引...

mysql中锁详解读这一篇就够了

InnoDB的七种锁1. 自增锁(Auto-inc Locks)2. 共享/排他锁(Shared and Exclusive Locks)3. 意向锁(Intention Locks)4. 插入意向锁...

mysql中count(x)到底慢不慢

总结MyISAM 表不支持事务,但是count(*) 很快,因为他直接记录了一个总数MyISAM在统计表的总行数的时候会很快,但是有个大前提,不能加有任何WHERE条件。这是因为:MyISAM对于表的...

mysql中index_merge索引合并优化

前言深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术,我们才知道应该如何在表上建立索引。MySQL在分析执行计划时发现走单个索引的过滤效果都不是...

MyISAM与InnoDB的区别

1、 存储结构MyISAM:每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYD...

mysql中performance_schema(一)配置篇

背景    performance_schema最早在MYSQL 5.5中出现,而现在5.6,5.7中performance_schema又添加了更多的监控项,统计信息也...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。