MySQL 8.0 新特性之降序索引

原文地址：MySQL 8.0 Reference Manual

我在慕课网发布的免费视频讲解 MySQL 8.0 版本新特性。

MySQL 8.0 支持降序索引（Descending index）：索引定义中的DESC不再被忽略，而是以降序方式存储索引键值。在之前的版本中，索引支持反向扫描，但是性能稍差一些。降序索引可以进行正向扫描，效率更高。当查询需要针对某些列升序排序，同时针对另一些列降序排序时，降序索引使得优化器对于可以使用多列混合索引扫描。

考虑以下表的定义，其中包含了 2 个列，以及这 2 个列上的 4 种升序和降序索引组合：

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)
);

表的定义中包含了 4 个不同的索引。优化器可以为不同的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

mysql> explain select * from t ORDER BY c1 ASC, c2 DESC;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx2 | 10      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

使用降序索引时需要满足以下条件：

• 降序索引只支持 InnoDB 存储引擎，并且存在以下限制：

  • 如果一个二级索引中包含降序的索引列，或者主键中包含降序索引列，不支持索引缓存的修改。

  • InnoDB 的 SQL 解析器不会使用降序索引。 对于 InnoDB 全文检索而言，意味着被索引表的 FTS_DOC_ID 列上创建的索引不能定义成降序索引。更多信息，可以参考官方文档 Section 15.6.2.4, “InnoDB FULLTEXT Indexes”。

• 降序索引支持的数据类型和升序索引相同。

• 降序索引支持普通（非计算）列和计算列（虚拟计算列和存储计算列）。

• DISTINCT可以利用任何包含查重字段的索引（包括含有降序列的索引）。

• 包含降序列的索引不会被用于那些使用了聚合函数，但没有GROUP BY子句的查询中的 MIN()/MAX() 函数优化。

• 降序索引支持 BTREE 索引，但不支持 HASH 索引。降序索引不支持 FULLTEXT 或者 SPATIAL 索引。

  对于 HASH、FULLTEXT 以及 SPATIAL 索引，显式指定ASC或DESC属性将会导致错误。

欢迎留言讨论！