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