MySQL 支持降序索引：在索引定义中的 DESC 不再被忽略，而是导致键值按降序存储。以前，索引可以逆序扫描，但会有性能上的损失。降序索引可以按正序扫描，这更高效。降序索引还使优化器能够在最有效的扫描顺序中，为某些列按升序、为其他列按降序时使用多列索引。MySQL supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others.

考虑以下表定义，它包含两列以及四个两列索引定义，用于列上升序和降序组合的各种情况：Consider the following table definition, which contains two columns and four two-column index definitions for the various combinations of ascending and descending indexes on the columns:

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

表定义产生了四个不同的索引。优化器可以对每个 ORDER BY 子句执行正向索引扫描，并且不需要使用 filesort 操作：The table definition results in four distinct indexes. The optimizer can perform a forward index scan for each of the ORDER BY clauses and need not use a filesort operation:

ORDER BY c1 ASC, c2 ASC    -- optimizer can use idx1
ORDER BY c1 DESC, c2 DESC  -- optimizer can use idx4
ORDER BY c1 ASC, c2 DESC   -- optimizer can use idx2
ORDER BY c1 DESC, c2 ASC   -- optimizer can use idx3

使用降序索引受以下条件限制：Use of descending indexes is subject to these conditions:

您可以在 EXPLAIN 输出的 Extra 列中看到，优化器能够使用降序索引，如下所示：You can see in the Extra column of the output of EXPLAIN that the optimizer is able to use a descending index, as shown here:

mysql> CREATE TABLE t1 (
    -> a INT, 
    -> b INT, 
    -> INDEX a_desc_b_asc (a DESC, b ASC)
    -> );

mysql> EXPLAIN SELECT * FROM t1 ORDER BY a ASC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: a_desc_b_asc
      key_len: 10
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Backward index scan; Using index

在 EXPLAIN FORMAT=TREE 输出中，使用降序索引通过在索引名称后添加 (reverse) 来表示，如下所示：In EXPLAIN FORMAT=TREE output, use of a descending index is indicated by the addition of (reverse) following the name of the index, like this:

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 ORDER BY a ASC\G 
*************************** 1. row ***************************
EXPLAIN: -> Index scan on t1 using a_desc_b_asc (reverse)  (cost=0.35 rows=1)

另请参见 EXPLAIN 额外信息。See also EXPLAIN Extra Information.