MySQL :: MySQL 9.0 参考手册 :: 10.3.13 降序索引

版本 9.0

8.4 当前
8.0

8.0 日语

MySQL 9.0 参考手册 / ... / 降序索引

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 可以使用包含匹配列的任何索引，包括降序键部分。
对于调用聚合函数但不包含 GROUP BY 子句的查询，具有降序键部分的索引不用于 MIN()/MAX() 优化。
BTREE 索引支持降序索引，但 HASH 索引不支持。 FULLTEXT 或 SPATIAL 索引不支持降序索引。
为 HASH、FULLTEXT 和 SPATIAL 索引显式指定 ASC 和 DESC 说明符会导致错误。

您可以在 EXPLAIN 输出的 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 额外信息。