mysql DISTINCT 的实现与优化

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: DISTINCT实际上和GROUP BY的操作非常相似,只不过是在GROUP BY之后的每组中只取出一条记录而已。所以,DISTINCT的实现和GROUP BY的实现也基本差不多,没有太大的区别。

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_message\G

***************************1row  ***************************

id1SELECT_type:SIMPLE

table:group_message

type:range

possible_keys:NULL

key:  idx_gid_uid_gc

key_len:4ref:  NULL

rows:10Extra: Using index for  group-by

1  row  in  set  (0.00sec)

 

我们可以很清晰的看到,执行计划中的Extra信息为Usingindex for group-by,这代表什么意思?为什么我没有进行GROUP BY操作的时候,执行计划中会告诉我这里通过索引进行了GROUP BY呢?其实这就是于DISTINCT的实现原理相关的,在实现DISTINCT的过程中,同样也是需要分组的,然后再从每组数据中取出一条返回给客户端。而这里的Extra信息就告诉我们,MySQL利用松散索引扫描就完成了整个操作。当然,如果MySQLQuery Optimizer要是能够做的再人性化一点将这里的信息换成Using index for distinct那就更好更容易让人理解了,呵呵。

 

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

sky@localhost: example 11:03:53> EXPLAIN SELECT DISTINCT user_id

->FROM group_message

->WHERE group_id = 2\G

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

id:1SELECT_type: SIMPLE

table:group_message

type:ref

possible_keys:idx_gid_uid_gc

key:idx_gid_uid_gc

key_len:4ref: const

rows:4Extra: Using WHERE; Using index

1row 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 < 10\G

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

id:1SELECT_type: SIMPLE

table:group_message

type:range

possible_keys:idx_gid_uid_gc

key:idx_gid_uid_gc

key_len:4ref: NULL

rows:32Extra: Using WHERE; Using index; Using temporary

1row 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_id\G

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

id:1SELECT_type: SIMPLE

table:group_message

type:range

possible_keys:idx_gid_uid_gc

key:idx_gid_uid_gc

key_len:4ref: NULL

rows:32Extra: Using WHERE; Using index; Using temporary; Usingfilesort

1row in set (0.00 sec)

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

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

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
缓存 算法 关系型数据库
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
|
6天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
MySQL底层概述—7.优化原则及慢查询
|
7天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
8天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
|
9天前
|
关系型数据库 MySQL 数据库
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
56 23
|
10天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
13天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
25天前
|
监控 关系型数据库 MySQL
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
51 22
|
30天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
261 0
|
1月前
|
SQL 关系型数据库 MySQL
MySQL派生表合并优化的原理和实现
通过本文的详细介绍,希望能帮助您理解和实现MySQL中派生表合并优化,提高数据库查询性能。
82 16