mysql 之order by工作流程

用下面这个表为列 来看看MySQL order by 的工作方式

代码解读

复制代码

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;

     现在有个需求 查询城市为杭州的所有人的姓名 并且按照姓名的排序返回前1000位的name age ？？？

SQL 如下：

代码解读

复制代码

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

贴出explain SQL的执行结果

Extra 中 Using index condition; 这个是之前文章中提到的索引下推 ICP Using filesort 这个表示需要排序 mysql会给每个线程分配一块内存  叫做sort_buffer

需要注意的是 order by name 这个动作 有可能在内存中完成 也可能使用外部排序 这取决于排序需要的内存和sort_buffer_size

sort_buffer_size 也就是MySQL为排序开辟的内存空间 如果要排序的数据量小于sort_buffer_size 排序就在内存中完成 如果大的话就需要利用磁盘临时辅助排序

我们可以利用 optimizer_trace   来确定一个排序语句是否使用了临时文件 具体的分析 看optimizer_trace 这篇文章 我直接贴图了

1. number_of_tmp_files 表示排序过程中使用的临时文件 如果number_of_tmp_files = 0 表示没有用到临时文件 上图为4 表示使用了外部排序 sort_buffer_size越小 需要分成的份数越多 number_of_tmp_files就越大
2. 注意下 explain rows的扫描行数是1824 而我们在上面排序扫描的行数是1825 是因为建立外部排序临时表存储在磁盘上 internal_tmp_disk_storage_engine 的默认值是innoDB 查询optimizer_tarce表时 把数据从临时表取出来会把扫描行数加+1

接下来看order by的另一种排序  =====>rowid排序

上面排序算法中 存在一个问题 如果查询的数据太多 sort_buffer 中放的数据太多 这样临时文件就越多 性能会很差

mysql 配置中 max_length_for_sort_data 用于控制排序的行数 如果排序的单行超过这个值 mysql就认为单行过大 采用rowid算法

来看下rowid算法的执行流程：

1.初始化sort_buffer 放入要排序的字段和主键ID 2.走索引找到满足条件的主键ID 再通过主键ID 取出排序字段和ID 放到sort_buffer中  从索引找到下一个满足条件的主键ID 3.重复2 直到不满足条件 4.对sort_buffer中的值按照排序字段排序 5.回到原表取出所有查询的字段返回

注意一点 最后的结果集是逻辑上的概念 mysql从server段排序后的sort_buffer中取到ID 然后回到原表查其余的字段 不需要在服务端再存储的 是直接返回客户端的

我直接贴结果

可以看到sort_mode变了<sort_key,rowid>表示 参与排序只有要求排序的字段和主键ID  number_of_tmp_rows 变成了3 表示临时文件变少了 参与排序的行变小了 因此参与排序总量变小

第一种排序 vs rowid排序

mysql 认为内存不够 才会使用rowid排序 这样排序可以读取更多行 但需要回原表取一次数据  所以mysql会优先采用第一种排序

体现了mysql 设计思想 : 如果内存够 就要多利用内存 尽量减少磁盘访问

排序是个成本较高的操作 那么如果避免这个问题   从上面的过程我们可以发现mysql之所以需要生成临时表 并且在临时表上排序  是因为原来的数据都是无序的

上面的列子我们可以通过在city 和name上建立联合索引来解决 同时也可以用我们之前提到的覆盖索引来避免排序

现在分析一个问题  开发中有时候会遇到随机选取的问题 SQL可能是这样的

代码解读

复制代码

mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

select `word` from words order by rand() limit 3; 随机取出3个单词

我贴出explain 结果

Using temporary; 表示需要用临时表 上面意思就是需要的内存表上排序

问题来了 对于临时内存表来说选择那种算法

对于innoDB来说 对于内存表 回表的过程只是简单的根据数据行的位置 直接访问内存得到数据（可以理解为一个内存中的数据下标对找对应的元素） 根本不会有磁盘的访问所以优先考虑的是用于排序的行越小越好 mysql会选择rowid排序

我们来看上面那条SQL的执行流程

scss

代码解读

复制代码

    1.创建一个memory引擎的内存表 表里两个字段 一个是double字段 另一个是varchar（64）类型
    2.从Word表中 按主键顺序取出所有word的值 调用rand()函数 生成一个随机小数 存放到内存表中
    3.接下来按值排序
    4.初始化sort_buffer 里面有两个值 一个double类型 一个整型
    6 在sort_buffer中排序
    7.排序完成之后 直接选择前三个位置的数据 返回客户端

再来看个临时表不是内存表的情况 ===》转化为磁盘临时表

mysql中 'tmp_table_size' 这个配置限制了内存临时表的大小 默认是32M (mysql5.6 ) 看下图

磁盘临时表默认的引擎是 innoDB 可以用上面提到的 internal_tmp_disk_storage_engine 来控制 我们可以通过

代码解读

复制代码

1. 设置tmp_table_size 为1024kb
2. sort_buffer_size 为256k
3. max_length_for_sort_data 设置为16

接下来我直接贴出optimizer_trace的结果

从结果我们可以看到 采用的是rowid排序 但我们发现了结果中的 number_of_tmp_size 的值竟让是0 难道不需要临时文件吗？？？

对的确实没有用到临时文件 采用的mysql5.6 新引入的 优先队列排序算法(也就是堆排序)  我们上面的SQL只需要前面的3个有序即可 如果采用上面提到的归并排序就会多好多运算

转载来源：https://juejin.cn/post/6844903890085756942