MySQL分组查询Group By实现原理详解
作者:mdxy-dxy 发布时间:2024-01-14 12:00:15
由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作。当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算。所以,在GROUP BY 的实现过程中,与 ORDER BY 一样也可以利用到索引。
在MySQL 中,GROUP BY 的实现同样有多种(三种)方式,其中有两种方式会利用现有的索引信息来完成 GROUP BY,另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。
1、使用松散(Loose)索引扫描实现 GROUP BY
何谓松散索引扫描实现 GROUP BY 呢?实际上就是当 MySQL 完全利用索引扫描来实现 GROUP BY 的时候,并不需要扫描所有满足条件的索引键即可完成操作得出结果。
下面我们通过一个示例来描述松散索引扫描实现 GROUP BY,在示例之前我们需要首先调整一下 group_message 表的索引,将 gmt_create 字段添加到 group_id 和 user_id 字段的索引中:
sky@localhost: example 08:49:45> create index idx_gid_uid_gc
-> on group_message(group_id,user_id,gmt_create);
Query OK, rows affected (0.03 sec)
Records: 96 Duplicates: 0 Warnings: 0
sky@localhost: example 09:07:30> drop index idx_group_message_gid_uid
-> on group_message;
Query OK, 96 rows affected (0.02 sec)
Records: 96 Duplicates: 0 Warnings: 0
然后再看如下 Query 的执行计划:
sky@localhost: example 09:26:15> EXPLAIN
-> SELECT user_id,max(gmt_create)
-> FROM group_message
-> WHERE group_id < 10
-> GROUP BY group_id,user_id\G
*************************** 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: 8
ref: NULL
rows: 4
Extra: Using where; Using index for group-by
我们看到在执行计划的 Extra 信息中有信息显示“Using index for group-by”,实际上这就是告诉我们,MySQL Query Optimizer 通过使用松散索引扫描来实现了我们所需要的 GROUP BY 操作。
下面这张图片描绘了扫描过程的大概实现:
要利用到松散索引扫描实现 GROUP BY,需要至少满足以下几个条件:
◆GROUP BY 条件字段必须在同一个索引中最前面的连续位置;
◆在使用GROUP BY 的同时,只能使用 MAX 和 MIN 这两个聚合函数;
◆如果引用到了该索引中 GROUP BY 条件之外的字段条件的时候,必须以常量形式存在;
为什么松散索引扫描的效率会很高?
因为在没有WHERE子句,也就是必须经过全索引扫描的时候, 松散索引扫描需要读取的键值数量与分组的组数量一样多,也就是说比实际存在的键值数目要少很多。而在WHERE子句包含范围判断式或者等值表达式的时候, 松散索引扫描查找满足范围条件的每个组的第1个关键字,并且再次读取尽可能最少数量的关键字。
2.使用紧凑(Tight)索引扫描实现 GROUP BY
紧凑索引扫描实现 GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候,读取所有满足条件的索引键,然后再根据读取恶的数据来完成 GROUP BY 操作得到相应结果。
sky@localhost : example 08:55:14> EXPLAIN
-> SELECT max(gmt_create)
-> FROM group_message
-> WHERE group_id = 2
-> GROUP BY user_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: ref
possible_keys: idx_group_message_gid_uid,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.01 sec)
这时候的执行计划的 Extra 信息中已经没有“Using index for group-by”了,但并不是说 MySQL 的 GROUP BY 操作并不是通过索引完成的,只不过是需要访问 WHERE 条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现 GROUP BY 的执行计划输出信息。
下面这张图片展示了大概的整个执行过程:
在 MySQL 中,MySQL Query Optimizer 首先会选择尝试通过松散索引扫描来实现 GROUP BY 操作,当发现某些情况无法满足松散索引扫描实现 GROUP BY 的要求之后,才会尝试通过紧凑索引扫描来实现。
当 GROUP BY 条件字段并不连续或者不是索引前缀部分的时候,MySQL Query Optimizer 无法使用松散索引扫描,设置无法直接通过索引完成 GROUP BY 操作,因为缺失的索引键信息无法得到。但是,如果 Query 语句中存在一个常量值来引用缺失的索引键,则可以使用紧凑索引扫描完成 GROUP BY 操作,因为常量填充了搜索关键字中的“差距”,可以形成完整的索引前缀。这些索引前缀可以用于索引查找。而如果需要排序GROUP BY结果,并且能够形成索引前缀的搜索关键字,MySQL还可以避免额外的排序操作,因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。
3.使用临时表实现 GROUP BY
MySQL 在进行 GROUP BY 操作的时候要想利用所有,必须满足 GROUP BY 的字段必须同时存放于同一个索引中,且该索引是一个有序索引(如 Hash 索引就不能满足要求)。而且,并不只是如此,是否能够利用索引来实现 GROUP BY 还与使用的聚合函数也有关系。
前面两种 GROUP BY 的实现方式都是在有可以利用的索引的时候使用的,当 MySQL Query Optimizer 无法找到合适的索引可以利用的时候,就不得不先读取需要的数据,然后通过临时表来完成 GROUP BY 操作。
sky@localhost : example 09:02:40> EXPLAIN
-> SELECT max(gmt_create)
-> FROM group_message
-> WHERE group_id > 1 and group_id < 10
-> GROUP BY user_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: range
possible_keys: idx_group_message_gid_uid,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
这次的执行计划非常明显的告诉我们 MySQL 通过索引找到了我们需要的数据,然后创建了临时表,又进行了排序操作,才得到我们需要的 GROUP BY 结果。整个执行过程大概如下图所展示:
当 MySQL Query Optimizer 发现仅仅通过索引扫描并不能直接得到 GROUP BY 的结果之后,他就不得不选择通过使用临时表然后再排序的方式来实现 GROUP BY了。
在这样示例中即是这样的情况。 group_id 并不是一个常量条件,而是一个范围,而且 GROUP BY 字段为 user_id。所以 MySQL 无法根据索引的顺序来帮助 GROUP BY 的实现,只能先通过索引范围扫描得到需要的数据,然后将数据存入临时表,然后再进行排序和分组操作来完成 GROUP BY。
猜你喜欢
- #-*- coding:utf-8 -*- from win32com.client import Dispatch import time
- 1 前言正则表达式是对字符串(包括普通字符(例如,a 到 z 之间的字母)和特殊字符(称为“元字符”))操作的一种逻辑公式,就是用事先定义好
- 一、所需库安装pip install PyAudiopip install SpeechRecognitionpip install bai
- 我们需要评估模型预测值来评估训练的好坏。 模型评估是非常重要的,随后的每个模型都有模型评估方式。使用TensorFlow时,需要把模型评估加
- 如下所示:class Login(QMainWindow): """登录窗口""
- 强大的group by 代码如下:select stdname, isnull(sum( case stdsubject whe
- Python之POST调用Restful接口示例# -*- coding: utf-8 -*-import jsonimport reimp
- 电脑面前的你,是否也希望能让电脑听命于你? 当你累的时候,只需说一声“我累了”,电脑就会放着优雅的轻音乐来让你放松。
- 本文实例讲述了Python数据结构之图的应用。分享给大家供大家参考,具体如下:一、图的结构二、代码# -*- coding:utf-8 -*
- 前言:话说,我一直没能理解美工究竟是什么(这是一篇投稿)。因为要求确实很多。目前,我只能理解成,是前端开发+页面设计+用户体验设计的全能手。
- 系统模块import os系统模块用于对系统进行操作。常用方法os模块的常用方法有数十种之多,本文中只选出最常用的几种,其余的还有权限操作、
- 假设我们要添加一个我们自己的Middleware,用来记录每次请求的日志下面就是一个符合规范的Middleware, 构造函数中接受一个WS
- 前言文章抄袭在互联网中普遍存在,很多博主都收受其烦。近几年随着互联网的发展,抄袭等不道德行为在互联网上愈演愈烈,甚至复制、黏贴后发布标原创屡
- 1.启动H2数据库不打开浏览器窗口(默认是打开的) 2.数据库创建SQL增加了支持BigDecimal类型,h2数据库默认是不支持bigde
- 复制目录: 包含多层子目录方法: 递归, 深度遍历,广度遍历深度遍历&广度遍历:思路:1.获得源目录子级目录,并设置目标目录的子级路
- 听说有个面试题是: 如何快速向mysql中插入1000w条数据?我私下试了一下, 发现插入10000条数据用了0.9s, 插入10w条数据用
- 用Splash做页面抓取时,如果爬取的量非常大,任务非常多,用一个Splash服务来处理的话,未免压力太大了,此时可以考虑搭建一个负载均衡器
- 本文通过一个实际的散点图案例,展示了如何使用pyqt5嵌套一个pyecharts图层的方法,通过这个技巧,可以在pyqt5的框架中也实现精美
- 正文之前上午给爸爸打了个电话庆祝他50岁生日,在此之前搞了个大扫除,看了会知乎,到实验室已经十一点多了。约喜欢的妹子吃饭失败,以至于工作积极
- 应用场景在嵌入式开发中,常常需要将一个binary文件分割成多个文件,或者将一个binary的某块区域抓成一个单独文件。本篇blog以pyt