orderby命令功能（对order by的理解）

本文目录

• 对order by的理解
• order by是什么意思
• sql中order by和group by的区别

对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_files》0，则表示使用了磁盘文件来进行排序。

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

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 是排序的意思。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台油泵。

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

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列，数据存储时会自动被压缩，提取时解压缩。