MySQL order by 执行流程和索引优化

日常开发中，经常遇到排序的场景，比如列表页按创建时间分页排序等。那 order by 在MySQL中的执行流程是什么样的呢？

准备

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

主键id 和 普通索引city

order by 执行流程？

这样的 SQL 查询语句：

select city,name,age from t where city='杭州' order by name limit 1000;

只加city索引

mysql> explain select city,name,age from t where city='杭州' order by name limit 1000;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ref  | city          | city | 66      | const |    1 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Extra字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

通常情况下，这个语句执行流程如下所示 ：

1. 初始化sort_buffer，确定放入name、city、age这三个字段；
2. 从索引city找到第一个满足city=‘杭州’条件的主键id；
3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
4. 从索引city取下一个记录的主键id；
5. 重复步骤3、4直到city的值不满足查询条件为止；(所有city条件的都要查出来)
6. 对sort_buffer中的数据按照字段name做快速排序；
7. 按照排序结果取前1000行返回给客户端。

我们暂且把这个排序过程，称为全字段排序。

“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。

优化 添加(city, name) 联合索引

如上MySQL 内部的执行流程中，要把所有满足city的条件记录全查出来，整体排序后，再取1000返回给客户端。那为什么要全查出来呢？

因为根据city 取出来的这些记录，其name是无序的，那排序获取top N 就得都查出来再截取。

有什么办法不全取出来排序呢？对，要利用索引。根据 B+ 树索引知识，在一个联合索引中，左边相同时，右边的就自然有序了。

加(city, name) 联合索引：city相同时，name是有序的。这样只需要按索引顺序取1000即可，不再全部查出。

这也是为什么我们在索引优化中经常听说 order by 的字段，要建在联合索引的后边 的原因。

alter table t add index `idx_city_name` (city, name);

mysql> explain select city,name,age from t where city='杭州' order by name limit 1000;
+----+-------------+-------+------------+------+--------------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys      | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref  | city,idx_city_name | idx_city_name | 66      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------+---------------+---------+-------+------+----------+-------+

这样整个查询过程的流程是：

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

继续优化 覆盖索引

如上步骤2、3 还需要根据主键id 去主键索引中回表找出行记录，其实就是为了多取出来 age 这个字段值。

如果想要查询性能更好，可使用覆盖索引，免去回表操作，当表中记录很多时，建立覆盖索引优化效果是很不错的。

稍多造些数

delimiter ;;
create procedure myproce_1()
begin
  declare i int;
  set i=1;
  while(i<=20000)do
    insert into t values(i, '杭州', '', 1, 1);
    set i=i+1;
  end while;
end;;
delimiter ;
call myproce_1();

添加覆盖索引

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

explain 查看

使用了覆盖索引 idx_city_name_age，查询性能要更好。

mysql> explain select city,name,age from t where city='杭州' order by name limit 1000;
+----+-------------+-------+------------+------+--------------------------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                        | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ref  | city,idx_city_name,idx_city_name_age | idx_city_name_age | 66      | const | 9871 |   100.00 | Using index |
+----+-------------+-------+------------+------+--------------------------------------+-------------------+---------+-------+------+----------+-------------+

in 和 order by 怎么导致联合索引失效的？

假设还是联合索引(city, name)，查询 SQL 中 city in 多个：

select * from t where city in ('杭州', "苏州") order by name limit 1000;

explain 查看

mysql> explain select * from t where city in ('杭州', "苏州") order by name limit 1000;
+----+-------------+-------+------------+------+--------------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys      | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+--------------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | city,idx_city_name | NULL | NULL    | NULL | 19742 |    50.01 | Using where; Using filesort |
+----+-------------+-------+------------+------+--------------------+------+---------+------+-------+----------+-----------------------------+

发现Extra 中又出现了 Using filesort 排序。 为什么用了in ，索引就失效了呢？

从索引结构中我们知道：city相同时，name是有序的，但city不同时，name就不是全局有序了。 因此 in多个时，order by上的索引就失效了。

这种索引失效的case在实际开发中还是会遇到的，一种方案是：把 in 拆开，走索引；然后各取 limit 1000，应用程序内再给合并。

总结

• 理解 order by 在 MySQL内部执行流程
• order by 的字段加索引，联合索引时放在后边
• 覆盖索引的优化
• in 和 order by 导致索引失效的case

参考

• 《MySQL 45讲》

• MySQL
• 数据库