如何优化sql中order By语句

共计 4495 个字符，预计需要花费 12 分钟才能阅读完成。

这篇文章主要介绍“如何优化 sql 中 order By 语句”的相关知识，丸趣 TV 小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“如何优化 sql 中 order By 语句”文章能帮助大家解决问题。

在使用数据库进行数据查询时，难免会遇到基于某些字段对查询的结果集进行排序的需求。在 sql 中通常使用 orderby 语句来实现。将需要排序的字段放到 该关键词后，如果有多个字段的话，就用 , 分割。

select * from table t order by t.column1,t.column2;

上面的 sql 表示查询表 table 中数据，然后先按照 column1 排序，如果 column1 相同的话，在按照 column2 排序，排序的方式默认是降序。当然排序方式也是可以指定的。在被排序字段后添加 DESC,ASE, 分别表示降序和升序。

使用该 orderby 可以很方便的实现日常的排序操作。使用的多了，不知道你有没有遇到过这种场景：有时候使用 orderby 后，sql 执行效率非常慢，有时候却比较快，由于整天被 curd 缠身，也没有时间研究，反正就是觉得很神奇。趁这个周末比较闲，就来研究下，mysql 中 orderby 是怎么实现的。

为了方便描述，我们先建立一个数据表 t1，如下：

CREATE TABLE `t1` ( `id` int(11) NOT NULL not null auto_increment,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 `c` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`) ,
 KEY `a` (`a`) USING BTREE
) ENGINE=InnoDB;

并插入数据：

insert into t1 (a,b,c) values (1,1,3);
insert into t1 (a,b,c) values (1,4,5);
insert into t1 (a,b,c) values (1,3,3);
insert into t1 (a,b,c) values (1,3,4);
insert into t1 (a,b,c) values (1,2,5);
insert into t1 (a,b,c) values (1,3,6);

为了使索引生效，插入 10000 行 7,7,7，无关数据，数据量少的情况下，会直接全表扫描

insert into t1 (a,b,c) values (7,7,7);

我们现在需要查找 a= 1 的所有记录，然后按照 b 字段进行排序。

查询 sql 为

select a,b,c from t1 where a = 1 order by b limit 2;

为了防止在查询过程中全表扫描，我们在字段 a 上添加了索引。

首先我们先通过语句

explain select a,b,c from t1 where a = 1 order by b lmit 2;

查看 sql 的执行计划，如下所示：

在 extra 中我们可以看到出现了 Using filesort，这个表示 该 sql 执行过程中，执行了排序操作，排序操作在 sort_buffer 中完成，sort_buffer 是 mysql 分配给每个线程的一个内存缓冲区，该缓冲区专门用来完成排序，大小默认是 1M，其大小由变量 sort_buffer_size 进行控制。

mysql 在对 orderby 进行实现时，根据放入到 sort_buffer 中的字段内容不同，进行了两种不同实现方式：全字段排序和 rowid 排序。

全字段排序

首先我们先通过一张图整体看一下 sql 执行过程：

mysql 先根据查询条件确定需要排序的数据集，也就是表中 a= 1 的数据集，即主键 id 从 1 到 6 的这些记录。

整个 sql 的执行的过程如下：

1. 创建并初始化 sort_buffer, 并确定需要放到该缓冲区中的字段，也就是 a,b,c 这三个字段。

2. 从索引树 a 中找到第一个满足 a = 1 的主键 id，也就是 id=1。

3. 回表到 id 索引，取出整行数据，然后从整行数据中，取出 a,b,c 的值，放入到 sort_buffer 中。

4. 从索引 a 中按照顺序找到下一个 a = 1 的主键 id。

5. 重复步骤 3 和步骤 4，直到获取到最后一个 a = 1 的记录，也就是主键 id=5。

6. 此时满足条件 a = 1 的所有记录的 a,b,c 字段，全部读放到了 sort_buffer 中，然后，对这些数据按照 b 的值进行进行排序，排序的方式是快速排序。就是那个面试经常面到的快速排序，时间复杂度为 log2n 的快速排序。

7. 然后从排序后的结果集中取出前 2 行数据。

上面是就是 msql 中 orderby 的执行流程。因为放入到 sort_buffer 中的数据是需要输出的全部字段，所以这种排序被称为全排序。

看到这里不知道你是否会有疑问？如果需要排序的数据量很大的话，sort_buffer 装不下怎么办？

的确，如果 a = 1 的数据行特别多，且需要存放到 sort_buffer 中的字段比较多，可能不止 a,b,c 三个字段，有些业务可能需要输出更多字段。那么默认大小只有 1M 的 sort_buffer 很可能容纳不下。

当 sort_buffer 容纳不下的时候，mysql 会创建一批临时的磁盘文件来辅助排序。默认情况下会创建 12 个临时文件，将需要排序的数据分成 12 份，每一份单独排序，形成 12 个内部数据有序的文件，然后把这 12 个有序文件在合并成一个有序的大文件，最终完成数据的排序。

基于文件的排序，相比基于内存的排序，排序效率要低很多，为了提高排序的效率，应该尽量避免基于文件的排序，要想避免基于文件排序，就需要让 sort_buffer 可以容纳需要排序的数据量。

所以对于 sort_buffer 容纳不下的情况，mysql 进行了优化。就是在排序时候，降低存放到 sort_buffer 中的字段个数。

具体优化方式，就是下面的 rowId 排序

RowId 排序

在全字段排序实现中，排序的过程中，要把需要输出的字段全部放到 sort_buffer 中，当输出的字段比较多的时候，可以放到 sort_buffer 中的数据行就会变少。也就增大了 sort_buffer 无法容纳数据的风险，直至出现基于文件的排序。

rowId 排序对全字段排序的优化手段，主要是减少了放到 sort_buffer 中字段个数。

在 rowId 排序中，只会将需要排序的字段和主键 Id 放到 sort_buffer 中。

select a,b,c from t1 where a = 1 order by b limit 2;

在 rowId 的排序中的执行流程如下：

1. 初始化并创建 sort_buffer，并确认要放入的的字段，id 和 b。

2. 从索引树 a 中找到第一个满足 a = 1 的主键 id，也就是 id=1。

3. 回表主键索引 id，取出整行数据，从整行数据中取出 id 和 b，存入 sort_buffer 中。

4. 从索引 a 中取出下一条满足 a = 1 的 记录的主键 id。

5. 重复步骤 3 和 4，直到最后一个满足 a = 1 的主键 id，也就是 a =6。

6. 对 sort_buffer 中的数据，按照字段 b 排序。

7. 从 sort_buffer 中的有序数据集中，取出前 2 个，因为此时取出的数据只有 id 和 b，要想获取 a 和 c 字段，需要根据 id 字段，回表到主键索引中取出整行数据，从整行数据中获取需要的数据。

根据 rowId 排序的执行步骤，可以发现：相比全字段排序，rowId 排序的实现方式，减少了存放到 sort_buffer 中的数据量，降低了基于文件的外部排序的可能性。

那 rowid 排序有不足的地方吗？肯定有的，要不然全字段排序就没有存在的意义了。rowid 排序不足之处在于，在最后的步骤 7 中，增加了回表的次数，不过这个回表的次数，取决于 limit 后的值，如果返回的结果集比较小的话，回表的次数还是比较小的。

mysql 是如何在全字段排序和 rowId 排序的呢？其实是根据存放的 sort_buffer 中每行字段的长度决定的，如果 mysql 认为每次放到 sort_buffer 中的数据量很大的话，那么就用 rowId 排序实现，否则使用全字段排序。那么多大算大呢？这个大小的阈值有一个变量的值来决定，这个变量就是 max_length_for_sort_data。如果每次放到 sort_buffer 中的数据大小大于该字段值的话，就使用 rowId 排序，否则使用全字段排序。

orderby 的优化

上面讲述了 orderby 的两种排序的方式，以及一些优化策略，优化的目的主要就是避免基于磁盘文件的外部排序。因为基于磁盘文件的排序效率要远低于基于 sort_buffer 的内存排序。

但是当数据量比较大的时候，即使 sort_buffer 比较大，所有数据全部放在内存中排序，sql 的整体执行效率也不高，因为排序这个操作，本身就是比较消耗性能的。

试想，如果基于索引 a 获取到所有 a = 1 的数据，按照字段 b，天然就是有序的，那么就不用执行排序操作，直接取出来的数据，就是符合结果的数据集，那么 sql 的执行效率就会大幅度增长。

其实要实现整个 sql 执行过程中，避免排序操作也不难，只需要创建一个 a 和 b 的联合索引即可。

alter table t1 add index a_b (a,b);

添加 a 和 b 的联合索引后，sql 执行流程就变成了：

1. 从索引树 (a,b) 中找到第一个满足 a = 1 的主键 id，也就是 id=1。

2. 回表到主键索引树，取出整行数据，并从中取出 a，b，c，直接作为结果集的一部分返回。

3. 从索引树 (a,b) 上取出下一个满足 a = 1 的主键 id。

4. 重复步骤 2 和 3，直到找到第二个满足 a = 1 的主键 id, 并回表获取字段 a，b，c。

此时我们可以通过查看 sql 的执行计划，来判断 sql 的执行过程中是否执行了排序操作。

explain select a,b from t1 where a = 1 order by b lmit 2;

通过查看执行计划，我们发现 extra 中已经没有了 using filesort 了，也就是没有执行排序操作了。

其实还可以通过覆盖索引，对该 sql 进一步优化，通过在索引中覆盖字段 c，来避免回表的操作。

alter table t1 add index a_b_c (a,b,c);

添加索引 a_b_c 后，sql 的执行过程如下：

1. 从索引树 (a,b,c) 中找到第一个满足 a = 1 的索引，从中取出 a，b，c。直接作为结果集的一部分直接返回。

2. 从索引 (a,b,c) 中取出下一个，满足 a = 1 的记录作为结果集的一部分。

3. 重复执行步骤 2，直到查到第二个 a = 1 或者不满足 a = 1 的记录。

此时通过查看执行 sql 的的还行计划可以发现 extra 中只有 Using index。

explain select a,b from t1 where a = 1 order by b lmit 2;

关于“如何优化 sql 中 order By 语句”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注丸趣 TV 行业资讯频道，丸趣 TV 小编每天都会为大家更新不同的知识点。