mysql sort by_四面阿里被问MySQL底层如何实现order by的，瞬间懵了

这是一个阳光明媚的日子，小A正在工作台上练习钓鱼技术。

突然收到产品的☎️，还有另一个需求？

刚才听到产品又开始胡言乱语了：我需要查询所有城市为“上海”的人的姓名，并按姓名排序返回前1000个人的姓名和年龄。

小A赶紧坐直了身子，从一堆数据库表中找到需要的表，并提取出它的建表语句：

先看表结构再看产品需求

感觉很简单，只要这样写SQL就可以了：

好吧，这个语句看上去简单朴实，似乎完美解决了一个需求。但是为了展现我强大的性能优化水平，考虑到避免全表扫描，我给city字段加了索引。建好索引之后，自然需要用它来验证一下：

 explain select city, name, age from citizen where city = '上海' order by name limit 1000;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | citizen | NULL       | ALL  | city          | NULL | NULL    | NULL |   32 |   100.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

Extra字段的Using表示需要进行排序，MySQL会为每个线程分配一块内存专门用于排序，这个叫做。

这时，魔鬼产品突然过来问：“给我看看你的代码是怎么写的，你真的了解MySQL底层是怎么执行order by的吗？”小A一下子清醒了，他从来没有想过这些事情。

产品经理冷笑：“你知道你的城市索引是什么样的吗？我自己建的，我怎么会不知道！”

，仔细看一下，这里的id_x到id_(x+n)的数据都满足city=’’。

产品：那你能告诉我这个SQL的执行过程吗？你不知道，我来告诉你：

初始化，确认放入name、city、age三个字段，从索引city中找到第一个满足city=‘上海’条件的主键id，也就是id_x；从id主键索引中取整行，取出name、city、age三个字段的值，存入索引city下一条记录的主键id中，重复3、4，直到city的值不满足查询条件，也就是主键id_y，按照name快速排序，取排序结果中的前1000行返回给客户端

这个就是全字段排序，执行过程如下：

按名称排序可以在内存中完成，也可能需要外部排序，具体取决于

产品又开始炫耀了，问：你知道排序语句什么时候会用到临时文件吗？这个？这真是触及了我的知识盲区！

mysql> SET optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
/* 使用 @a 保存 Innodb_rows_read 的初始值 */
mysql> select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)
mysql> select city, name,age from citizen where city='上海' order by name limit 1000;
+--------+------+-----+
| city   | name | age |
+--------+------+-----+
| 上海   | java |  22 |
...
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* 使用 @b 保存 Innodb_rows_read 的当前值 */
mysql> select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)
/* 计算 Innodb_rows_read 的差值 */
mysql> select @b-@a;

检查结果中的字段以查看是否使用了临时文件。

"filesort_execution": [
],
"filesort_summary": {
	"rows": 4000
	"examined_rows": 4000,
	"number_of_tmp_files": 12,
	"sort_buffer_size": 32664 ,
	"sort_mode": ""

如果数据量超出了需要排序的数据大小，那么就为0，也就是直接在内存中进行排序就可以了。

否则就需要在临时文件中排序，越小需要分割的部分越多， 的值就越大。

@b-@a的结果是4000，也就是说整个执行过程只扫描了4000行。

注意，为了避免干扰结论，我将其设置为 。否则，@b-@a 的结果将显示为 4001。因为查询表时，需要一个临时表，而 的默认值为 。如果使用 ，从临时表中取数据时， 的值就会加 1。

我惊奇地看着这个产品，就像看着一位伟人，你们为什么不继承我的代码，让我来做这个产品呢？

上面针对 rowid 排序的算法，只读取一次原表数据，其余操作都在临时文件中进行。但是存在一个问题：如果查询返回的字段很多，那么需要存储的字段数量就会很多，内存中能够同时存储的行数就会变少，这样就会被分成很多个临时文件，排序性能就会很差。因此，如果单行数据量很大，这种方法就不够高效了。

产品经理又开始捣乱了，你知道如果 MySQL 认为需要排序的单行长度过大，它会怎么做吗？

现在修改一个参数，让MySQL使用另一种算法。

SET max_length_for_sort_data = 16;

city、name、age三个字段的总长度定义为36，那么如果我将数据设置为16，会发生什么情况呢？

新的算法只是增加了需要排序的列（name字段）和主键id，但是由于缺少city和age字段值，无法直接返回排序结果，整个执行流程变成如下：

初始化，确保放入两个字段，分别是name和id。从city中找出第一个满足条件city=''的主键id，也就是图中的id_x到id。取出整行，取name和id两个字段，从city中存下一条记录的主键id。重复步骤3和4，直到不再满足city=''，也就是图中的id_y。根据字段名称对数据进行排序。遍历排序后的结果，取前1000行，并根据id的值返回原表取出city，name，age三个字段返回给客户端。

听完这话，我感觉好像明白了一些：品品，你别急，我先画一张rowid排序执行过程的示意图看看对不对？

大家可以看到这个和之前画的全字段排序图是不一样的，其实它多访问了一次表的主键索引，也就是第7步。

注意最后一个是逻辑上的概念，其实就是 MySQL 服务器从排序后的数据中一个一个的取出 id，然后去原表中查找 city、name、age 三个字段的结果，服务器端不需要消耗内存去存储结果，而是直接返回给客户端。

这时候再检查rowid排序的结果，看看和之前有什么不同

"filesort_execution": [
],
"filesort_summary": {
	"rows": 4000
	"examined_rows": 4000,
	"number_of_tmp_files": 10,
	"sort_buffer_size": 32728 ,
	"sort_mode": ""

该产品最终得出结论：

所以MySQL是：如果有足够的内存，就使用更多的内存，并尽量减少磁盘访问。

是的，rowid排序需要备份表，造成更多的磁盘读取，所以不会成为首选。所以MySQL排序是一个高成本的操作。

并不是所有的 order by 语句都需要排序。由于原始数据是乱序的，MySQL 需要生成一个临时表并对其进行排序。

因此可以建立城市、姓名联合索引：

alter table t add index citizen(city, name);

从索引(city,name)中查找第一个满足条件city=‘上海’的主键id。从主键id索引中取出整行，取出name、city、age三个字段的值，直接作为结果集的一部分返回。从索引(city,name)中取出下一条记录主键id。重复步骤2和3，直到查找到第1000条记录，或者不满足条件city=‘上海’时，循环结束。

可以看出这个查询过程不需要临时表，也不需要排序。

 explain select city, name, age from citizen where city = '上海' order by name limit 1000;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | citizen | NULL       | ref  | city,name       | name | 51    | const |   4000 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到Extra字段中没有Using，也就是说不需要排序。而且由于(city,name)联合索引是有序的，查询不需要读取全部4000行，只要找到前1000条符合条件的记录就可以退出。在这个例子中，只需要扫描1000次即可。

是否可以进一步简化该语句的执行流程？

根据覆盖索引，我们可以进一步优化此查询语句的执行过程，针对此查询，我们可以建立city、name、age的联合索引，对应的SQL语句为：

alter table t add index city_user_age(city, name, age);

此时，对于city字段值相同的行，仍然按照name字段的值进行升序排序，查询语句不再需要排序。这样，整个查询语句的执行流程就变成了：

从索引 (city, name, age) 中查找第一条满足条件 city='上海' 的记录，提取 city、name、age 三个字段的值，直接作为结果集的一部分返回。从索引 (city, name, age) 中获取下一条记录，同样提取三个字段的值，直接作为结果集的一部分返回。重复 2，直到查找到第 1000 条记录或者不满足 city='上海'。

引入(城市，姓名，年龄)联合索引，查询语句的执行过程

explain select city, name, age from citizen where city = '上海' order by name limit 1000;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | citizen | NULL       | ref  | city,name,age        | age | 51    | const |   4000 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

Extra字段中的“Using index”表示使用了覆盖索引，这样会提高性能。不过这并不意味着为了每次查询都使用覆盖索引，语句中涉及到的所有字段都要建立索引，毕竟索引占用空间很大，修改和添加也会导致索引的改变，还是具体业务场景具体分析比较好。

參考

“排序依据” 如何工作？

你每天都会写order by，但是你知道MySQL底层的执行原理吗？