MySQL数据库索引orderby排序精讲-木庄网络博客

本文整理自网络，侵删。

事情的起因

现在假设有一张用户的朋友表：

CREATE TABLE `user` (
  `id` int(10) AUTO_INCREMENT,
  `user_id` int(10),
  `friend_addr` varchar(1000),
  `friend_name` varchar(100),  
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB;

表中目前有两个点需要关注下：

用户的 user_id ，朋友的姓名 friend_name、朋友的地址 friend_addr
user_id 是有索引的

有一天，有个初级开发工程师小猿，收到了来自初级产品经理小汪的需求：
小汪：小猿同志，现在需要在后台加个功能，这个功能要支持根据用户 id 能查到他所有的朋友姓名和地址，并且要求朋友的姓名是按照字典排序的。
小猿：好的，这个功能简单，我马上就上线。

于是小猿书写了这样的sql：

select friend_name，friend_addr from user where user_id=? order by name

在电光石火的瞬间，小猿趾高气昂的上线了，这一切都很顺利，直到有一天有个运营同学导致了这样的查询：

select friend_name，friend_addr from user where user_id=10086 order by name

然而，这个查询竟然比平时慢很多，数据库报了慢查询，小猿此时慌的一b：这是怎么回事？user_id 明明有索引啊，而且机智地我还只用了 select friend_name,friend_addr，并没有用 select *呀。小猿此时不停地安慰自己，要淡定要淡定，然后突然想到有个explain命令，用explain来查看下那条sql的执行计划吧，当小猿用了explain之后，发现extra字段里面有个看起来很危险的字眼：using filesort。

“这个查询竟然用到了传说中的文件排序，但是如果一个人朋友不是很多，就算了用了文件排序，应该也很快吧”，除非这个user_id=10086的朋友很多，后来小猿去查了下，这个用户的朋友竟然有10w多个～。

陷入了沉思的小猿心想：这个锅看来是背定了，10w数据是有点大了，还有这个 using filesort 到底是怎么个排序原理？

解剖文件排序

有人可能说上面的问题是10w数据太大了，就算不排序也慢，这个其实是有道理的，10w数据一次性查出来，无论是MySQL内存缓冲区的占用，还是网络带宽的消耗都是非常大的，那如果我加了limit 1000呢？网络带宽的问题肯定是解决了，因为数据包整体变小了，但是 using filesort 的问题其实还是没有解决，看到这里你可能会有疑问，using filesort 难道是在文件中排序的？在文件中到底是怎么排序的？或者我这样问：如果给你来设计排序你会怎么处理？带着这些疑问和思考我们来看看 using filesort 会涉及到哪些技术难点以及是如何解决的？

首先我们的 user_id 是有索引的，所以会先在 user_id 索引树上检索我们的目标数据，即 user_id=10086 的数据，但是我们要查询的是 friend_name 和 friend_addr 字段，很不幸，光靠 user_id 索引是找不到这两个字段值的
于是需要回表，通过 user_id 对应的主键去主键索引树上去查找，ok，我们找到了第一条 user_id=10086 的 friend_name 和 friend_addr 字段
这时该怎么办？直接返回回去肯定不对，因为我需要对 friend_name 排序，如何排？数据都还没找全，那么就得把查到的数据先放在一个地方，这个地方就是 sort_buffer，看到名字我想你应该猜出来，没错，sort_buffer 就是用于这种情况下排序用的缓冲区，这里需要注意的是每个线程都会有一个单独的 sort_buffer，这么做的目的主要是为了避免多个线程对同一块内存进行操作带来锁竞争的问题。
当第一条数据的 friend_name 和 friend_addr 已经放入 sort_buffer 中，这当然没完，会一直重复同步的步骤，直至把所有 user_id=10086 的 friend_name 和 friend_addr 都放入到 sort_buffer 中才结束
sort_buffer 中的数据已经放入完毕，接下来就该排序了，这里 MySQL 会对 friend_name 进行快排，通过快排后，sort_buffer 中 friend_name 就是有序的了
最后返回 sort_buffer 中的前1000条，结束。

一切看起来很丝滑，但是 sort_buffer 占用的是内存空间，这就尴尬了，内存本身就不是无限大的，它肯定是有上限的，当然 sort_buffer 也不能太小，太小的话，意义不大。在 InnoDB 存储引擎中，这个值是默认是256K。

mysql> show variables  like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+

也就是说，如果要放进 sort_buffer 中的数据是大于256K的话，那么采用在 sort_buffer 中快排的方式肯定是行不通的，这时候，你可能会问：MySQL难道不能根据数据大小自动扩充吗？额，MySQL是多线程模型，如果每个线程都扩充，那么分给其他功能buffer就小了（比如change buffer等），就会影响其他功能的质量。

阅读剩余部分