orderby,orderby与groupby的顺序

http://www.itjxue.com  2023-01-05 00:14  来源:未知  点击次数: 

order by 和 group by 的区别

order by 和 group by的区别为:指代不同、侧重点不同、引证用法不同

一、指代不同

1、order by:排序依据。

2、group by:进行分组。

二、侧重点不同

1、order by:用来对数据库的一组数据进行排序。

2、group by:指定的规则对数据进行分组,所谓的分组就是将一个“数据集”划分成若干个“小区域”,然后针对若干个“小区域”进行数据处理。

三、引证用法不同

1、order by:order作“次序,顺序”“治安,秩序”“整齐,有条理”解时,是不可数名词。作“订购,订货”“命令,嘱咐”“汇票,汇单”解时,是可数名词。

2、group by:group的基本意思是“群,团体,组类”,指由很多的人或物偶然或有意组成的一个有秩序、有组织的整体,有时也可指较小的“人群”,还可以指一个大型的商业机构,即“集团”或某种“类别”。

对order by的理解

前言

日常开发中,我们经常会使用到order by,亲爱的小伙伴,你是否知道order by 的工作原理呢?order by的优化思路是怎样的呢?使用order by有哪些注意的问题呢?本文将跟大家一起来学习,攻克order by~

一个使用order by 的简单例子

假设用一张员工表,表结构如下:

表数据如下:

我们现在有这么一个需求:查询前10个,来自深圳员工的姓名、年龄、城市,并且按照年龄小到大排序。对应的 SQL 语句就可以这么写:

这条语句的逻辑很清楚,但是它的底层执行流程是怎样的呢?

order by 工作原理

explain 执行计划

我们先用Explain关键字查看一下执行计划

我们可以发现,这条SQL使用到了索引,并且也用到排序。那么它是怎么排序的呢?

全字段排序

MySQL 会给每个查询线程分配一块小内存,用于排序的,称为 sort_buffer。什么时候把字段放进去排序呢,其实是通过idx_city索引找到对应的数据,才把数据放进去啦。

我们回顾下索引是怎么找到匹配的数据的,现在先把索引树画出来吧,idx_city索引树如下:

idx_city索引树,叶子节点存储的是主键id。还有一棵id主键聚族索引树,我们再画出聚族索引树图吧:

我们的查询语句是怎么找到匹配数据的呢?先通过idx_city索引树,找到对应的主键id,然后再通过拿到的主键id,搜索id主键索引树,找到对应的行数据。

加上order by之后,整体的执行流程就是:

执行示意图如下:

将查询所需的字段全部读取到sort_buffer中,就是全字段排序。这里面,有些小伙伴可能会有个疑问,把查询的所有字段都放到sort_buffer,而sort_buffer是一块内存来的,如果数据量太大,sort_buffer放不下怎么办呢?

磁盘临时文件辅助排序

实际上,sort_buffer的大小是由一个参数控制的:sort_buffer_size。如果要排序的数据小于sort_buffer_size,排序在sort_buffer 内存中完成,如果要排序的数据大于sort_buffer_size,则借助磁盘文件来进行排序

如何确定是否使用了磁盘文件来进行排序呢?可以使用以下这几个 命令

可以从 number_of_tmp_files 中看出,是否使用了临时文件。

number_of_tmp_files 表示使用来排序的磁盘临时文件数。如果number_of_tmp_files0,则表示使用了磁盘文件来进行排序。

使用了磁盘临时文件,整个排序过程又是怎样的呢?

TPS: 借助磁盘临时小文件排序,实际上使用的是归并排序算法。

小伙伴们可能会有个疑问,既然sort_buffer放不下,就需要用到临时磁盘文件,这会影响排序效率。那为什么还要把排序不相关的字段(name,city)放到sort_buffer中呢?只放排序相关的age字段,它不香吗?可以了解下rowid 排序。

rowid 排序

rowid 排序就是,只把查询SQL需要用于排序的字段和主键id,放到sort_buffer中。那怎么确定走的是全字段排序还是rowid 排序排序呢?

实际上有个参数控制的。这个参数就是max_length_for_sort_data,它表示MySQL用于排序行数据的长度的一个参数,如果单行的长度超过这个值,MySQL 就认为单行太大,就换rowid 排序。我们可以通过 命令 看下这个参数取值。

max_length_for_sort_data 默认值是1024。因为本文示例中name,age,city长度=64+4+64 =132 1024, 所以走的是全字段排序。我们来改下这个参数,改小一点.

使用rowid 排序的话,整个SQL执行流程又是怎样的呢?

执行示意图如下:

对比一下全字段排序的流程,rowid 排序多了一次回表。

什么是回表?拿到主键再回到主键索引查询的过程,就叫做回表”

我们通过optimizer_trace,可以看到是否使用了rowid排序的:

全字段排序与rowid排序对比

一般情况下,对于InnoDB存储引擎,会优先使用全字段排序。可以发现 max_length_for_sort_data 参数设置为1024,这个数比较大的。一般情况下,排序字段不会超过这个值,也就是都会走全字段排序。

order by的一些优化思路

我们如何优化order by语句呢?

联合索引优化

再回顾下示例SQL的查询计划

我们给查询条件city和排序字段age,加个联合索引idx_city_age。再去查看执行计划:

可以发现,加上idx_city_age联合索引,就不需要Using filesort排序了。为什么呢?因为索引本身是有序的,我们可以看下idx_city_age联合索引示意图,如下:

整个SQL执行流程变成酱紫:

流程示意图如下:

从示意图看来,还是有一次回表操作。针对本次示例,有没有更高效的方案呢?有的,可以使用覆盖索引:

覆盖索引:在查询的数据列里面,不需要回表去查,直接从索引列就能取到想要的结果。换句话说,你SQL用到的索引列数据,覆盖了查询结果的列,就算上覆盖索引了。”

我们给city,name,age 组成一个联合索引,即可用到了覆盖索引,这时候SQL执行时,连回表操作都可以省去啦。

调整参数优化

我们还可以通过调整参数,去优化order by的执行。比如可以调整sort_buffer_size的值。因为sort_buffer值太小,数据量大的话,会借助磁盘临时文件排序。如果MySQL服务器配置高的话,可以使用稍微调整大点。

我们还可以调整max_length_for_sort_data的值,这个值太小的话,order by会走rowid排序,会回表,降低查询性能。所以max_length_for_sort_data可以适当大一点。

当然,很多时候,这些MySQL参数值,我们直接采用默认值就可以了。

使用order by 的一些注意点:没有where条件,order by字段需要加索引吗

日常开发过程中,我们可能会遇到没有where条件的order by,那么,这时候order by后面的字段是否需要加索引呢。如有这么一个SQL,create_time是否需要加索引:

无条件查询的话,即使create_time上有索引,也不会使用到。因为MySQL优化器认为走普通二级索引,再去回表成本比全表扫描排序更高。所以选择走全表扫描,然后根据全字段排序或者rowid排序来进行。

如果查询SQL修改一下:

分页limit过大时,会导致大量排序怎么办?

假设SQL如下:

索引存储顺序与order by不一致,如何优化?

假设有联合索引 idx_age_name, 我们需求修改为这样:查询前10个员工的姓名、年龄,并且按照年龄小到大排序,如果年龄相同,则按姓名降序排。对应的 SQL 语句就可以这么写:

我们看下执行计划,发现使用到Using filesort

这是因为,idx_age_name索引树中,age从小到大排序,如果age相同,再按name从小到大排序。而order by 中,是按age从小到大排序,如果age相同,再按name从大到小排序。也就是说,索引存储顺序与order by不一致。

我们怎么优化呢?如果MySQL是8.0版本,支持Descending Indexes,可以这样修改索引:

使用了in条件多个属性时,SQL执行是否有排序过程

如果我们有联合索引idx_city_name,执行这个SQL的话,是不会走排序过程的,如下:

但是,如果使用in条件,并且有多个条件时,就会有排序过程。

这是因为:in有两个条件,在满足深圳时,age是排好序的,但是把满足上海的age也加进来,就不能保证满足所有的age都是排好序的。因此需要Using filesort。

order by什么意思

order by

排序依据; 顺序; 字段名; 降序排列; 记录排序;

[例句]All the other ingredients, including water, have to be listed in descendingorder by weight.

所有其他原料,包括水在内,都需要根据重量依次降序排列。

order by是什么意思

order by 是排序的意思。order by 列名1,列名2,..... 你按照什么排序就写什么列。 如果列名后什么都不写,也可以写ASC,表示升序排列,如果要想降序排列,在列名后面写desc 。

order by是什么意思

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

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

在使用group by的语句中,只能select用于分类的列(表达式),或聚合函数。

where条件用于group by之前,having用于group by 之后对结果进行筛选。

order用法

一、名词order:

1、作“顺序”、“次序”。常用短语:in order of 按顺序。如:

A list of some of the common metals in the order of their activity is given below. 下面列出了某些金属按其活泼顺序排列的一个表格.

2、作“秩序”。常用短语:keep ... in order 使......有序。如:

The machine is in good working order. 机器运转良好.

Our laboratory is kept in good order. 我们的实验室保持整齐.(句中in good order为主语补语.)

3、作“指示、命令”。常用短语:give sb. anorderto do sth.命令某人做某事。如:

He gave orders for the work to be started. (=…orders that the work should be started.)他下令开始工作.

4、点菜;所点的饮食菜肴。

二、动词order:

1、作”命令”。常用短语:ordersb.to do sth. 令某人做某事。如:

The doctor ordered him to stay there.医生嘱咐他留在那里。(不定式短语to stay there作宾语补语)

2、作“点餐、“订购”。常用短语:ordersth.for sb./ordersb.for sth.为某人订购/点某物。如:

They have ordered 1,000 oil pumps from our factory.他们已向我厂订购1,000台油泵。

来源:网络。本文版权归原创作者所以。

order by 和 group by 的区别?

order by 和 group by 的区别:

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

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

3,在使用group by的语句中,只能select用于分类的列(表达式),或聚合函数。

where条件用于group by之前,having用于group by 之后对结果进行筛选。

扩展资料:

一、order by用法: 排序查询、asc升序、desc降序

示例:

1.select * from 学生表?

2.order by 年龄 ?

3.查询学生表信息、按年龄的升序(默认、可缺省、从低到高)排列显示也可以多条件排序、 比如 order by 年龄,成绩 desc ?

4.按年龄升序排列后、再按成绩降序排列。

二、group by用法:? 分组查询、having 只能用于group by子句、作用于组内,having条件子句可以直接跟函数表达式。使用group by 子句的查询语句需要使用聚合函数。

示例:

1.select 学号,SUM(成绩) from 选课表 group by 学号 ?按学号分组、查询每个学号的总成绩

2.select 学号,AVG(成绩) from 选课表

3.group by 学号

4.having AVG(成绩)(select AVG(成绩) from 选课表 where 课程号='001')

5.order by AVG(成绩) desc

6.查询平均成绩大于001课程平均成绩的学号、并按平均成绩的降序排列。

参考资料:

百度百科- 结构化查询语言

(责任编辑:IT教学网)

更多

推荐linux服务器文章