SQL group 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: 0sky@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_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: 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_idG*************************** 1. row ***************************id: 1select_type: SIMPLEtable: group_messagetype: refpossible_keys: idx_group_message_gid_uid,idx_gid_uid_gckey: idx_gid_uid_gckey_len: 4ref: constrows: 4Extra: Using where; Using index1 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_idG*************************** 1. row ***************************id: 1select_type: SIMPLEtable: group_messagetype: rangepossible_keys: idx_group_message_gid_uid,idx_gid_uid_gckey: idx_gid_uid_gckey_len: 4ref: NULLrows: 32Extra: 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。

SQL group by底层原理——本质是排序，可以利用索引事先排好序

标签：通过   调整   位置   rar   搜索   rom   plain   其他   分析   

小编还为您整理了以下内容，可能对您也有帮助：

SQL语句中，如果有group by 和order by两个语句，是先分组还是先排序？

先进行分组处理。

Group By 和 Having, Where ,Order by这些关键字是按照如下顺序进行执行的：Where, Group By, Having, Order by。

首先where将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量的将不符合条件的记录筛选掉，这样可以减少分组的次数)

然后通过Group By关键字后面指定的分组条件将筛选得到的视图进行分组

接着系统根据Having关键字后面指定的筛选条件，将分组视图后不满足条件的记录筛选掉

最后按照Order By语句对视图进行排序，这样最终的结果就产生了。

在这四个关键字中，只有在Order By语句中才可以使用最终视图的列名，如：

SELECT FruitName, ProctPlace, Price, ID AS IDE, Discount

FROM T_TEST_FRUITINFO

WHERE (ProctPlace = N'china')

ORDER BY IDE

这里只有在ORDER BY语句中才可以使用IDE，其他条件语句中如果需要引用列名则只能使用ID，而不能使用IDE。

SQL中的group by语句和order by语句怎么用？最好能有个例子

group by是分组的，比如你要查询一个班级的学生，各省分别有多少人。(表结构是学生表，有学号，姓名，所在省，备注等。)

就应该用到group by了，如：

SELECT 所在省,COUNT(*) AS 学生数

FROM 学生

GROUP BY 所在省

Order by是用来排序的。如，你想要把上例中查得的结果按人数从大到小，或从小到大排列，可以加这么一句，

ORDER BY 学生人数 (DESC或ASC);

好了，就这么个用法。

后面括号的关键字，指明了从大到小，还是从小到大，只能写一个，并且不要括号。

sql中order by和group by的区别

1、意思不一样。

order by 是指从英文里理解就是行的排序方式，默认的为升序。后面必须列出排序的字段名，可以是多个字段名。

group by 是指从英文里理解就是分组。必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标志字段。 

2、适用范围不同：order by 用于排序,一般与asc升序或desc降序一起使用。例如select * from 表A order by 列a。

group by 用于分类汇总,一般与聚合函数(比如avg平均、sum合计、max最大、min最小、count计算行)一起使用。例如select 月份,sum(工资)as 总工资 from 工资表 group by 月份。

3、定义不同：order by主要侧重许多数据的排序，例如按照大小顺序对数据进行排列，group by主要侧重许多数据的分组，例如按照性别、年龄、国家、学科等进行分组。

4、使用的函数不一样。

order by子句中的列必须包含在聚合函数或 GROUP BY 子句中。

group by 子句中可使用的函数AVG()  、MIN() 、 MAX()  、SUM() 、COUNT() 。

5、order by 从英文里理解就是行的排序方式，默认的为升序。 order by 后面必须列出排序的字段名，可以是多个字段名。

group by 从英文里理解就是分组。必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标志字段。

扩展资料

Microsoft Jet 数据库引擎 SQL 数据类型 由13个基本数据类型组成，它们是由 Microsoft Jet 数据库引擎和几个验证过的有效同义字定义的。常见的有：整形，单精度，双精度，可变长度字符，固定长度字符，长型，日期等等。

可以用ALTER TABLE 语句修正基数和增量。刚插进表中的行会有自动为基于新种值和增量值的列生成的值。如果新种和增长的值和以前的种值及增量匹配，就会造成复制。如果这个列是主键，复制值生成时可能导致错误。

定义成TEXT也称作MEMO）或CHAR（也称作有具体长度的 TEXT(n)）的字段中的字符被储存在 Unicode 表示格式。

Unicode字符一律需要两个字节存储每一字符。对于现有的主要包含字符数据的 Microsoft Jet数据库，这可能意味着数据库文件被转换成 Microsoft Jet 4.0格式时字长会增加将近一倍。

然而，从前由单字节字符群(SBCS)指示的众多字符群的Unicode 表示可以很容易地被压缩成一个单字节。有关的详细信息，参见CREATE TABLE。如果用COMPRESSION属性定义CHAR列，数据存储时会自动被压缩，提取时解压缩。

参考资料来源：百度百科-SQL数据类型

SQL语句中，有group by 和order by两个语句，是先分组还是先排序？

肯定是group by先

order by优先级最低，在最后

group by 语句执行后记录会按关键字自动升序?

不一定，group by只负责分组。