面试官：order by 是怎样排序的？怎么优化？

01 前言

刚换了新工作，用了两周时间准备，在 3 天之内拿了 5 个 offer，最后选择了广州某互联网行业独角兽 offer，昨天刚入职。这几天刚好整理下在面试中被问到有意思的问题，也借此机会跟大家分享下。

这家企业的面试官有点意思，一面是个同龄小哥，一起聊了两个小时（聊到我嘴都干了）。二面是个从阿里出来的架构师，视频面试，我做完自我介绍之后，他一开场就问我：

对 MySQL 熟悉吗？

我一愣，随之意识到这是个坑。他肯定想问我某方面的原理了，恰好我研究过索引。就回答：

对索引比较熟悉。

他：

order by 是怎么实现排序的？

还好我又复习，基本上排序缓冲区、怎么优化之类的都答到点子上。今天也跟大家盘一盘 order by，我将从原理讲到最终优化，给大家聊聊 order by，希望对你有所帮助。

1.1 先举个栗子

现在有一张订单表，结构是这样的：

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 索引的图示：