MySQL :: MySQL 8.3 Reference Manual :: 10.3.13 Descending Indexes

version 8.3

8.0 current

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Descending Indexes

10.3.13 降序索引

MySQL 支持降序索引：DESC 在索引定义中不再被忽略，而是以降序存储键值。以前，索引可以以反向顺序扫描，但性能会受到影响。降序索引可以以正向顺序扫描，这更高效。降序索引还使优化器能够使用多列索引时混合升序和降序顺序的列。

考虑以下表定义，其中包含两个列和四个两列索引定义，用于各种升序和降序索引的组合：

Press CTRL+C to copy
 
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 操作：

Press CTRL+C to copy
 
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

降序索引的使用受以下条件限制：

降序索引仅支持 InnoDB 存储引擎，以下是限制：
- 更改缓冲区不支持辅助索引，如果索引包含降序索引键列或主键包含降序索引列。
- InnoDB SQL 解析器不使用降序索引。对于 InnoDB 全文搜索，这意味着索引表的 FTS_DOC_ID 列上的索引不能定义为降序索引。更多信息，请参阅第 17.6.2.4 节，“InnoDB 全文索引”。
降序索引支持所有数据类型，其中升序索引可用。
降序索引支持普通（非生成）和生成列（包括 VIRTUAL 和 STORED）。
DISTINCT 可以使用任何包含匹配列的索引，包括降序键部分。
具有降序键部分的索引不用于 MIN()/MAX() 优化查询，除非查询包含 GROUP BY 子句。
降序索引支持 BTREE 但不支持 HASH 索引。降序索引不支持 FULLTEXT 或 SPATIAL 索引。

明确指定的 ASC 和 DESC 设计符对于 HASH、FULLTEXT 和 SPATIAL 索引将导致错误。

您可以在 Extra 列中看到优化器能够使用降序索引，如下所示：

Press CTRL+C to copy
 
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) 后缀形式显示，例如：

Press CTRL+C to copy
 
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 额外信息。

PREV HOME UP NEXT