“order by”是怎么工作的？

2019-08-18  本文已影响0人  面壁人4号

今天为大家转述一篇极客时间的MySQL实战45讲付费专栏的内容，由于本人之前对于这块内容理解的比较模糊，所以今天就特地拿出来一起学习一下，如果有什么不对的地方还请大家多多斧正。下面就进入正题吧！

首先请看一个例子

在我们开发过程中，一定会遇到这种情况根据指定字段进行排序，例如你要针对以下市民表进行查询，你的查询字段是city，并且需要是按照姓名排序，最终返回前100个人的姓名和年龄。表结构定义如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

这时，我们一般人的sql会这么写：

select city,name,age from t where city='杭州' order by name limit 1000  ;

这个语句看上起逻辑清晰，但是你了解它的执行流程吗？今天，我们就来看看这个语句是怎么执行的，以及有什么参数会影响执行的行为。

全字段排序

我们应该知道，如果要想排序查询的时候进行全字段扫描那么就需要给查询的字段加上索引，在这里为了避免进行全表所描，我们给表的city字段加上普通索引。
在city字段加上索引之后，我们使用explain命令来看看这个语句的执行情况。

图 1 使用 explain 命令查看语句的执行情况
首先我们看到的是，使用了city索引，然后Extra这个字段中的“Using filesort”表示就是要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer.
为了说明这个SQL查询语句的执行过程，我们先来看看-下city的这个索引示意图。
图 2 city 字段的索引示意图
从图中可以看到，满足city='杭州'条件的航，是从ID_X到ID_(X+N)的这些记录。
通常情况下，这个语句的执行流程如下所示：
  1. 初始化sort_buffer，确定放入name、city、age这三个字段；
  2. 从索引city找到第一个满足city='杭州'条件的主键id，也就是图中的ID_X;
  3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
  4. 从索引city取下一条记录的主键id;
  5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中ID_Y;
  6. 对sort_buffer中的数据按照字段name做快速排序；
  7. 对照排序结果取前1000行返回给客户端。
    我们可以暂时把这个排序称为全字段排序，执行的流程示意图如下：
    图 3 全字段排序
    ”按name排序“这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于所需的排序内存和参数sort_buffer_size。
    sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果排序的数据量小于这个值，那么排序就在内存中完成。但是如果排序数据量超过这个值，则不得不利用磁盘临时文件辅助排序。

ps：这里为什么要使用不得不这个词呢？是因为MySQL的设计原则是尽可能的使用内存。
至于如何确认一个排序语句是否使用了临时文件，可以参考：原文，
通过执行计划看到”Using filesort“，这个是不是就代表这个语句使用了外部文件排序呢？这个问题留给你

rowid排序

在上面这个算法过程中，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段太多，这样内存里能够同时放下的行数就会减少，要分成很多个临时文件，排序的性能会很差。
所以如果但行很大，这个方法的效率不够好。
那么，MySQL中如果单行长度太大它会怎么做呢？
下面就来介绍rowid排序，按照上面的语句，执行流程会变成这个样子：

  1. 初始化 sort_buffer，确定放入两个字段，即 name和id;
  2. 从索引city找到第一个满足city='杭州'条件的主键id，也就是图中的ID_X;
  3. 到主键id索引取出整行，取name、city这两个字段，存入sort_buffer中；
  4. 从索引city取下一条记录的主键id;
  5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中ID_Y;
  6. 对sort_buffer中的数据按照字段name进行排序；

  7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name、age三个字段返回给客户端；
    这个执行流程的示意图如下：


    图 5 rowid 排序

ps : 需要说明的是最后的“结果集”是一个逻辑概念，实际上MySQL服务端从排序后的sort_buffer中依次取出id,然后到原表查到city、name、age这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

总结

看到这里，你就了解了，MySQL做排序是一个成本比较高的操作。那么你会问，是不是所有的order by 都需要排序呢？如果不排序就能得到正确的结果，对系统的消耗会小很多，语句的执行时间也会变得更短。
其实，并不是所有的order by语句，都需要排序操作的。从上面的分析中，我们可以看到，MySQL之所要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。
如果我们能够保证从city这个索引取出来的行都是name排序好的，是不是就可以不用再排序了呢？
确实是这样的。
所以我们可以给市民表t建一个city和name的联合索引，对应的sql语句是：

alter table t add index city_user(city, name);
图 7 city 和 name 联合索引示意图

这样整个查询过程的流程就变成了：

  1. 从索引（city,name）找到第一个满足city='杭州'条件的主键id;
  2. 到主键id索引中取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
  3. 从索引（city,name）取下一条记录的主键id;

  4. 重复步骤2，3直到查询到第1000条记录，或者不满足city='杭州'条件时循环结束；


    图 8 引入 (city,name) 联合索引后，查询语句的执行过程

    我们可以再看下执行计划


    图 9 引入 (city,name) 联合索引后，查询语句的执行计划

强烈推荐去看原文，上面还有更详细的内容原文

上一篇下一篇

猜你喜欢

热点阅读