MySQL 8.4 Release Notes
10.3.13 Descending 索引
MySQL 支持降序索引:DESC
在索引定义中不再被忽略,导致键值的存储顺序为降序。之前,索引可以反向扫描,但性能会下降。降序索引可以在正向顺序扫描,效率更高。降序索引还使得优化器能够使用多列索引,当某些列升序,其他列降序时。
考虑以下表定义,它包含两个列和四个二列索引定义,对于各种升降序索引组合:
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
操作:
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
可以使用包含匹配列的任何索引,包括降序键部分。 -
降序索引支持BTREE索引,但不支持HASH索引。降序索引不支持FULLTEXT或SPATIAL索引。
明确指定
ASC
和DESC
设计符对HASH、FULLTEXT和SPATIAL索引结果报错。
可以在EXPLAIN
的Extra
列中看到优化器是否使用降序索引,例如:
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)
表示,如下所示:
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)