这篇文章继续讨论优化 MySQL 索引。如果您还没有阅读过上一篇有关MySQL 索引优化的文章（第 1 部分）建议您在继续阅读本文之前先阅读一下。 #如何优化MySQL索引#

这篇博文的主要重点是讨论MySQL的Order By 功能背后的原理。

注意：下面提到的所有测试均在MySQL 5.7. 使用MySQL 8时结果可能会有所不同。

Order By优化

order by语句也遵循最左前缀原则，这意味着Order By 子句中指定的列是索引中最左边的列时，或者如果它位于 WHERE 语句句中指定的列之后，索引才能用于排序。

让我们创建一个新的测试表并在其上构建联合索引name_age_school：

CREATE TABLE `students` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL,
  `school` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_school` (`name`,`age`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

根据我们创建的索引，name列自然是有序的。当姓名值相同时，年龄列将进行排序。当年龄值相同时，对school列进行排序。为了确定 SQL 是否使用索引进行排序，让我们看一个简单的例子。

explain select id from students order by name;

看extra那列

如果运行EXPLAIN 语句时输出的Extra列中出现Using index，则表示正在使用覆盖索引。

如果运行 EXPLAIN 语句时输出的 Extra 列中出现“Using filesort”，则意味着将使用基于文件的排序而不是基于索引的排序。如果排序的数据量足够小，则在内存中进行排序，否则需要在磁盘上进行排序。

如果Extra列中没有Using filesort，则表示正在使用基于索引的排序。

让我们看一个使用基于文件的排序的 SQL 示例：

EXPLAIN SELECT name, age FROM students ORDER BY age DESC;

EXPLAIN 语句的输出将在 Extra 列中显示Using filesort，表示将使用基于文件的排序。

using index仅意味着正在使用索引，而using filesort仅意味着正在使用基于文件的排序。这是两件不同的事情。

例如，您可以使用索引来查找数据，但结果数据是使用基于文件的排序进行排序的。让我们继续看一些 SQL 示例。

SQL1

explain select * from students where name = 'n_18' order by age, school;

如果是基于索引的排序，age字段应位于name字段之后，school字段应位于age字段之后。

SQL2

explain select * from students where name = 'n_18' order by school, age;

SQL 语句将首先按school字段排序，然后按年龄age排序。但是，由于当名称字段相同时，school字段是无序的，因此需要基于文件的排序来执行排序。

SQL3

explain select * from students where name = 'n_18' and age = 18 order by school, age;

ORDER BY 子句之后的age字段实际上会优化为常量值，因为数据是按条件age=10 查询的。所以是基于索引的排序。

SQL4

explain select * from students where name = 'n_18' order by age asc, school desc;

使用索引对age字段进行排序，但school字段以相反的顺序排序,所以是基于文件的排序。

SQL5

explain select * from students where name = 'n_18' order by age desc, school desc;

这个语句可以使用索引排序，age和school字段都是降序排列，相当于从右向左遍历索引。

什么是文件排序？

文件排序是指当无法通过基于索引的排序完成时对数据进行排序的过程。这涉及到将数据读入内存进行排序，如果内存不足，则将数据写入磁盘进行排序，这称为文件排序。

在MySQL中，Using filesort的出现就表明需要进行文件排序。由于涉及大量的磁盘 I/O 操作，文件排序的效率通常比基于索引的排序慢。文件排序可分为单向排序和双向排序。

单向排序

Single-pass排序是一种将整个文件读入内存，排序，然后将排序结果写回磁盘的排序方法。该方法适用于数据量较小的情况。但是，如果数据量太大，内存无法容纳，则需要进行双向排序。

双向排序

双向排序也称为外部合并排序，将文件分成几个较小的块。每个块都可以读入内存进行排序，排序后，多个已排序的块将合并为一个已排序的文件。该方法适用于数据量较大，但需要多次磁盘I/O操作，效率相对较低的情况。

MySQL对排序做的优化

对于单向排序，MySQL 将排序分为两个阶段：内存排序和磁盘排序。在内存排序阶段，MySQL通过将数据读入内存来尽可能地对数据进行排序。如果内存不够，则将部分数据写入磁盘，并继续将剩余数据读入内存进行排序。

在磁盘排序阶段，MySQL将内存中已排序的数据块一一读取，并在磁盘上进行归并排序，减少了磁盘I/O操作，提高了排序效率。

对于双向排序，MySQL将数据分成多个小块，对每个块分别进行排序，然后对排序后的小块进行归并排序，得到整个文件的排序结果。

MySQL还对归并排序进行了优化，例如使用归并排序树来减少归并排序次数，提高排序效率。

MySQL通过系统变量max_length_for_sort_data（默认1024字节）和要查询的字段的总大小之间的比较来确定使用哪种排序模式。

如果字段的总长度小于max_length_for_sort_data，则使用单向排序模式。
如果字段的总长度大于max_length_for_sort_data，则使用双向排序模式。

总结

只要有可能，就使用索引排序。但是，如果无法使用索引排序，请勿强制使用。优先优化带索引的WHERE子句，因为过滤后的数据往往较小，排序成本较低。

如果无法使用索引排序且服务器内存足够，请调整max_length_for_sort_data参数，使MySQL使用单向排序。这样可以减少磁盘I/O操作，提高效率。

如果喜欢这篇文章，点赞支持一下，关注我第一时间查看更多内容！

如何优化 MySQL 索引：提高排序性能