In this query, the index on (key_part1, key_part2) enables the optimizer to avoid sorting:在这个查询中，(key_part1, key_part2)上的索引使优化器能够避免排序：

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

However, the query uses SELECT *, which may select more columns than key_part1 and key_part2. 但是，查询使用SELECT *，它可以选择比key_part1和key_part2更多的列。In that case, scanning an entire index and looking up table rows to find columns not in the index may be more expensive than scanning the table and sorting the results. 在这种情况下，扫描整个索引并查找表行以查找不在索引中的列可能比扫描表并对结果进行排序更昂贵。If so, the optimizer probably does not use the index. 如果是这样，优化器可能不使用索引。If SELECT * selects only the index columns, the index is used and sorting avoided.如果SELECT *只选择索引列，则使用了索引，避免了排序。

If t1 is an InnoDB table, the table primary key is implicitly part of the index, and the index can be used to resolve the ORDER BY for this query:如果t1是InnoDB表，则表主键隐式地是索引的一部分，该索引可用于解析此查询的ORDER BY：

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

In this query, key_part1 is constant, so all rows accessed through the index are in key_part2 order, and an index on (key_part1, key_part2) avoids sorting if the WHERE clause is selective enough to make an index range scan cheaper than a table scan:在该查询中，key_part1是常量，因此通过索引访问的所有行都是按key_part2排序，如果WHERE子句的选择性足以使索引范围扫描比表扫描便宜，则(key_part1, key_part2)上的索引可以避免排序：

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

In the next two queries, whether the index is used is similar to the same queries without DESC shown previously:在接下来的两个查询中，是否使用索引与前面未显示DESC的相同查询类似：

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2 DESC;

Two columns in an ORDER BY can sort in the same direction (both ASC, or both DESC) or in opposite directions (one ASC, one DESC). ORDER BY中的两列可以按相同方向（两个ASC或两个DESC）排序，也可以按相反方向（一个ASC，一个DESC）排序。A condition for index use is that the index must have the same homogeneity, but need not have the same actual direction.使用索引的条件是索引必须具有相同的同质性，但不必具有相同的实际方向。

If a query mixes ASC and DESC, the optimizer can use an index on the columns if the index also uses corresponding mixed ascending and descending columns:如果查询混合了ASC和DESC，那么如果索引还使用相应的升序和降序混合列，则优化器可以在列上使用索引：

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 ASC;

The optimizer can use an index on (key_part1, key_part2) if key_part1 is descending and key_part2 is ascending. 如果key_part1是降序，而key_part2<是升序，则优化器可以使用(key_part1, key_part2)上的索引。It can also use an index on those columns (with a backward scan) if key_part1 is ascending and key_part2 is descending. 如果key_part1为升序，key_part2为降序，它还可以在这些列上使用索引（向后扫描）。See Section 8.3.13, “Descending Indexes”.请参阅第8.3.13节，“降序索引”。

In the next two queries, key_part1 is compared to a constant. 在接下来的两个查询中，将key_part1与一个常量进行比较。The index is used if the WHERE clause is selective enough to make an index range scan cheaper than a table scan:如果WHERE子句的选择性足以使索引范围扫描比表扫描便宜，则使用索引：

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

In the next query, the ORDER BY does not name key_part1, but all rows selected have a constant key_part1 value, so the index can still be used:在下一个查询中，ORDER BY没有命名key_part1，但所有选中的行都有一个常量key_part1值，因此仍可使用索引：

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

The query uses ORDER BY on different indexes:查询在不同索引上使用ORDER BY：

SELECT * FROM t1 ORDER BY key1, key2;

The query uses ORDER BY on nonconsecutive parts of an index:查询对索引的非连续部分使用ORDER BY：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

The index used to fetch the rows differs from the one used in the ORDER BY:用于获取行的索引与ORDER BY中使用的索引不同，具体如下：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

The query uses ORDER BY with an expression that includes terms other than the index column name:查询将ORDER BY与包含索引列名以外的术语的表达式一起使用：

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

The query joins many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. 该查询连接了许多表，ORDER BY中的列并不都来自用于检索行的第一个非常量表。(This is the first table in the EXPLAIN output that does not have a const join type.)（这是EXPLAIN输出中第一个没有const联接类型的表。）

The query has different ORDER BY and GROUP BY expressions.查询具有不同的ORDER BY和GROUP BY表达式。

There is an index on only a prefix of a column named in the ORDER BY clause. 只有在ORDER BY子句中命名的列的前缀上有索引。In this case, the index cannot be used to fully resolve the sort order. 在这种情况下，索引不能用于完全解析排序顺序。For example, if only the first 10 bytes of a CHAR(20) column are indexed, the index cannot distinguish values past the 10th byte and a filesort is needed.例如，如果仅对CHAR(20)列的前10个字节进行索引，则索引无法区分超过第10个字节的值，因此需要一个filesort。

The index does not store rows in order. 索引不按顺序存储行。For example, this is true for a HASH index in a MEMORY table.例如，这对于MEMORY表中的HASH索引是正确的。

Increase the sort_buffer_size variable value. 增加sort_buffer_size变量值。Ideally, the value should be large enough for the entire result set to fit in the sort buffer (to avoid writes to disk and merge passes).理想情况下，该值应足够大，以使整个结果集能够放入排序缓冲区（以避免写入磁盘和合并过程）。

Take into account that the size of column values stored in the sort buffer is affected by the max_sort_length system variable value. 考虑到存储在排序缓冲区中的列值的大小受max_sort_length系统变量值的影响。For example, if tuples store values of long string columns and you increase the value of max_sort_length, the size of sort buffer tuples increases as well and may require you to increase sort_buffer_size.例如，如果元组存储长字符串列的值，并且您增加了max_sort_length的值，则排序缓冲区元组的大小也会增加，并且可能需要您增加sort_buffer_size。

To monitor the number of merge passes (to merge temporary files), check the Sort_merge_passes status variable.要监视合并过程的数量（要合并临时文件），请选中Sort_merge_passes状态变量。

Increase the read_rnd_buffer_size variable value so that more rows are read at a time.增加read_rnd_buffer_size变量值，以便一次读取更多行。

Change the tmpdir system variable to point to a dedicated file system with large amounts of free space. 将tmpdir系统变量更改为指向具有大量可用空间的专用文件系统。The variable value can list several paths that are used in round-robin fashion; you can use this feature to spread the load across several directories. 变量值可以列出以循环方式使用的多个路径；您可以使用此功能将负载分散到多个目录。Separate the paths by colon characters (:) on Unix and semicolon characters (;) on Windows. 在Unix上用冒号字符（:）分隔路径，在Windows上用分号字符（;）分隔路径。The paths should name directories in file systems located on different physical disks, not different partitions on the same disk.路径应该命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

If the Extra column of EXPLAIN output does not contain Using filesort, the index is used and a filesort is not performed.如果EXPLAIN输出的Extra列不包含Using filesort，则使用索引，而不执行filesort。

If the Extra column of EXPLAIN output contains Using filesort, the index is not used and a filesort is performed.如果EXPLAIN输出的Extra列包含Using filesort，则不使用索引，而执行filesort。

<sort_key, rowid>: This indicates that sort buffer tuples are pairs that contain the sort key value and row ID of the original table row. 这表示排序缓冲区元组是包含原始表行的排序键值和行ID的对。Tuples are sorted by sort key value and the row ID is used to read the row from the table.元组按排序键值排序，行ID用于从表中读取行。

<sort_key, additional_fields>: This indicates that sort buffer tuples contain the sort key value and columns referenced by the query. 这表示排序缓冲区元组包含排序键值和查询引用的列。Tuples are sorted by sort key value and column values are read directly from the tuple.元组按排序键值排序，列值直接从元组读取。

<sort_key, packed_additional_fields>: Like the previous variant, but the additional columns are packed tightly together instead of using a fixed-length encoding.与前一个变体类似，但是附加列紧密地打包在一起，而不是使用固定长度的编码。