01 前言
刚换了新工作，用了两周时间准备，在 3 天之内拿了 5 个 offer，最后选择了广州某互联网行业独角兽 offer，昨天刚入职。这几天刚好整理下在面试中被问到有意思的问题，也借此机会跟大家分享下。
这家企业的面试官有点意思，一面是个同龄小哥，一起聊了两个小时（聊到我嘴都干了）。二面是个从阿里出来的架构师，视频面试，我做完自我介绍之后，他一开场就问我：
对 MySQL 熟悉吗？
我一愣，随之意识到这是个坑。他肯定想问我某方面的原理了，恰好我研究过索引。就回
答：
对索引比较熟悉。
他：
order by 是怎么实现排序的？
还好我又复习，基本上排序缓冲区、怎么优化之类的都答到点子上。今天也跟大家盘一盘 order by，我将从原理讲到最终优化，给大家聊聊 order by，希望对你有所帮助。
国际惯例，先上思维导图。PS：文末有福利
1.2 先举个栗子
现在有一张订单表，结构是这样的：
CREATE TABLE `order` ( id INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主键', user_code VARCHAR ( 16 ) NOT NULL COMMENT '用户编号', goods_name VARCHAR ( 64 ) NOT NULL COMMENT '商品名称', order_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间', city VARCHAR ( 16 ) DEFAULT NULL COMMENT '下单城市', order_num INT ( 10 ) NOT NULL COMMENT '订单号数量', PRIMARY KEY ( `id` ) ) ENGINE = INNODB AUTO_INCREMENT = 100 DEFAULT CHARSET = utf8 COMMENT = '商品订单表';
造点数据：
// 第一步：创建函数 delimiter // DROP PROCEDURE IF EXISTS proc_buildata; CREATE PROCEDURE proc_buildata ( IN loop_times INT ) BEGIN DECLARE var INT DEFAULT 0; WHILE var < loop_times DO SET var = var + 1; INSERT INTO `order` ( `id`, `user_code`, `goods_name`, `order_date`, `city` , `order_num`) VALUES ( var, var + 1, '有线耳机', '2021-06-20 16:46:00', '杭州', 1 ); END WHILE; END // delimiter; // 第二步：调用上面生成的函数，即可插入数据，建议大家造点随机的数据。比如改改城市和订单数量 CALL proc_buildata(4000);
我生成的数据是这样的：
现有需求：查出 618 期间，广州的小伙伴的订单数量和用户编号，并按照订单数量升序，只要 1000 条。
根据需求可以得出以下 SQL，相信小伙伴都很熟悉了。
select city, order_num, user_code from `order` where city='广州' order by order_num limit 1000;
那这个语句是怎么执行的呢？有什么参数可以影响它的行为吗？
02 全字段排序
得到这个需求，我第一反应是先给 city 字段加上索引，避免全表扫描：
ALTER TABLE `order` ADD INDEX city_index ( `city` );
用 explain 看看执行情况
注意到最后一个 extra 字段的结果是：Using filesort，表示需要排序。 其实 MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
为了更直观了解排序的执行流程，我粗略画了个 city 索引的图示：
可见，现在满足 sql 条件的就是 ID-3 到 ID-X 这一段数据。sql 的整个流程是这样的：
- 1、初始化 sort_buffer，放入 city、order_num、user_code 这三个字段；
- 2、从索引 city 找到第一个满足 city=' 广州’条件的主键 id，也就是图中的 ID_3；
- 3、到主键 id 索引取出整行，取 city、order_num、user_code 三个字段的值，存入 sort_buffer 中；
- 4、从索引 city 取下一个记录的主键 id；
- 5、重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_X；
- 6、对 sort_buffer 中的数据按照字段 order_num 做快速排序；
- 7、按照排序结果取前 1000 行返回给客户端。
这个过程称之为全字段排序，画个图，长这样：
其中，按 order_num 排序这个步骤，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。
也就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存顶不住，就得磁盘临时文件辅助排序。
当然，在 MySQL5.7 以上版本可以用下面介绍的检测方法（后面都有用到），来查看一个排序语句是否使用了临时文件。PS：这里的语句直接复制到 navicat 执行即可，要一起执行（都复制进去，点下执行）
/* 打开optimizer_trace，只对本线程有效 */ SET optimizer_trace='enabled=on'; /* @a保存Innodb_rows_read的初始值 */ select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read'; /* 执行语句 */ select city, order_num, user_code from `order` where city='广州' order by order_num limit 1000; /* 查看 OPTIMIZER_TRACE 输出 */ SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`; /* @b保存Innodb_rows_read的当前值 */ select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read'; /* 计算Innodb_rows_read差值 */ select @b-@a;
执行完之后，可从 OPTIMIZER_TRACE 表的 TRACE 字段得到以下结果：
其中 examined_rows 表示需要排序的行数 6883；sort_buffer_size 就是排序缓冲区的大小；sort_buffer_size 就是我 MySQL 的排序缓冲区大小 256 KB。
另外，sort_mode 的值是 packed_additional_fields，它表示排序过程对数据做了优化，也就是数据占用多少就算多少内存。举个栗子：不存在数据定义长度 16，就按这个长度算，如果数据只占 2，只会按长度 2 分配内存。
number_of_tmp_files 代表的是用了几个外部文件来辅助排序。我这里是用了两个，内存放不下时，就使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解， MySQL 将需要排序的数据分成 2 份，每一份单独排序后存在这些临时文件中。然后把这 2 个有序文件再合并成一个有序的大文件。
最后一个查询语句，select @b-@a 的值是 6884，表示整个过程只扫描了 6883 行，为啥显示 6884？
因为查询 OPTIMIZER_TRACE 表时，需用到临时表；而 InnDB 引擎把数据从临时表取出时，Inndb_rows_read 值会加 1。
所以，把 internal_tmp_disk_storage_engine 设置为 MyISAM 可解决此问题。
