共计 2935 个字符，预计需要花费 8 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家介绍如何在 Mysql 中优化 order by 语句，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

MySQL 中的两种排序方式

1. 通过有序索引顺序扫描直接返回有序数据

因为索引的结构是 B + 树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN 分析查询时，Extra 显示为 Using index。

2.Filesort 排序，对返回的数据进行排序

所有不是通过索引直接返回排序结果的操作都是 Filesort 排序，也就是说进行了额外的排序操作。EXPLAIN 分析查询时，Extra 显示为 Using filesort。

ORDER BY 优化的核心原则

尽量减少额外的排序，通过索引直接返回有序数据。

ORDER BY 优化实战

用于实验的 customer 表的索引情况：

首先要注意：

MySQL 一次查询只能使用一个索引，如果要对多个字段使用索引，建立复合索引。

ORDER BY 优化

1. 查询的字段，应该只包含此次查询使用的索引字段和主键，其余的非索引字段和索引字段作为查询字段则不会使用索引。

只查询用于排序的索引字段，可以利用索引排序：

explain select store_id,email from customer order by store_id,email;

但是要注意，排序字段在多个索引中, 无法使用索引排序, 查询一次只能使用一个索引：

explain select store_id,email,last_name from customer order by store_id,email,last_name;

只查询用于排序的索引字段和主键，可以利用索引排序：

画外音：MySQL 默认的 InnoDB 引擎在物理上采用聚集索引这种方式，按主键进行搜索，所以 InnoDB 引擎要求表必须有主键，即使没有显式指定主键，InnoDB 引擎也会生成唯一的隐式主键，也就是说索引中必定有主键。

explain select customer_id,store_id,email from customer order by store_id,email;

查询用于排序的索引字段和主键之外的字段，不会利用索引排序：

explain select store_id,email,last_name from customer order by store_id,email;

explain select * from customer order by store_id,email;

WHERE + ORDER BY 优化

1. 排序字段在多个索引中, 无法利用索引排序

排序字段在多个索引（不在同一个索引）中, 无法利用索引排序：

explain select * from customer where last_name= swj order by last_name,store_id;

画外音：当排序字段不在同一个索引时，无法满足在一颗 B + 树中完成排序，必须再进行一次额外的排序

排序字段在一个索引中, 并且 WHERE 条件和 ORDER BY 使用相同的索引, 可以利用索引排序：

explain select * from customer where last_name= swj order by last_name;

当然组合索引也可以利用索引排序：

注意字段 store_id,email 在一个组合索引中

explain select * from customer where store_id = 5 order by store_id,email;

2. 排序字段顺序与索引列顺序不一致, 无法利用索引排序

画外音：这条是针对组合索引而言的，我们都知道使用组合索引必要要遵循最左原则，WHERE 子句必须有索引中第一列，虽然 ORDER BY 子句没有这个要求，但是也要求排序字段顺序和组合索引列顺序匹配。我们平常在使用组合索引的时候，一定要养成按照组合索引列顺序书写的好习惯。

排序字段顺序与索引列顺序不一致, 无法利用索引排序：

explain select * from customer where store_id 5 order by email,store_id;

应该确保排序字段顺序与索引列顺序一致, 这样可以利用索引排序：

explain select * from customer where store_id 5 order by store_id,email;

ORDER BY 子句不要求必须索引中第一列, 没有仍然可以利用索引排序。但是有个前提条件，只有在等值过滤时才可以，范围查询时不可以：

explain select * from customer where store_id = 5 order by email;

explain select * from customer where store_id 5 order by email;

画外音：

其原因其实也很简单，范围查询时，第一列 a 肯定是排序好的（默认是升序），而第二个字段 b 其实就不是排序的了。但是如果 a 字段有相同的值时，那么 b 字段就是排序的了。所以如果是范围查询，就只能对 b 做一次额外的排序。

3. 升降序不一致, 无法利用索引排序

ORDER BY 排序字段要么全部正序排序，要么全部倒序排序，否则无法利用索引排序。

explain select * from customer where store_id 5 order by store_id,email;

explain select * from customer where store_id 5 order by store_id desc,email desc;

explain select * from customer where store_id 5 order by store_id desc,email asc;

总结：

上面的优化其实可以汇总为：WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且 ORDER BY 的字段都是升序或者降序。否则肯定需要额外的排序操作，就会出现 Filesort。

Filesort 优化

通过创建合适的索引能够减少 Filesort 的出现，但是在某些情况下，无法完全让 Filesort 消失，此时只能想办法加快 Filesort 的操作。

Filesort 的两种排序算法：

1. 两次扫描算法

首先根据条件取出排序字段和行指针信息，之后在排序区 sort buffer 中排序。这种排序算法需要访问两次数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，第二次读取操作可能会导致大量随即 I / O 操作。优点是排序的时候内存开销较小。

2. 一次扫描算法

一次性取出满足条件的行的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。

根据两种排序算法的特性，适当加大系统变量 max_length_for_sort_data 的值，能够让 MySQL 选择更优化的 Filesort 排序算法。并且在书写 SQL 语句时，只使用需要的字段，而不是 SELECT * 所有的字段，这样可以减少排序区的使用，提高 SQL 性能。

关于如何在 Mysql 中优化 order by 语句就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。