关于联合索引的升序降序和order by关系以及失效问题

Question

Mysql: 之前看过一个说法，说order by后的各项如果排序不一致会导致联合索引失效，譬如 order by a ASC, b DESC, c DESCa升序，b降序，排序不一致，索引（a,b,c）失效但是建索引的时候也可以指定排序如果建立一个索引(a ASC, b DESC, c DESC)那 order by a ASC, b DESC, c DESC 语句索引还失效吗？

Answer 1

mysql 文档中对于索引定义中，ASC 和 DESC 的描述

MySQL < 8.0

A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage.
Currently, they are parsed but ignored; index values are always stored in ascending order.

MySQL >= 8.0

A key_part specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order. The default is ascending if no order specifier is given. ASC and DESC are not permitted for HASH indexes. As of MySQL 8.0.12, ASC and DESC are not permitted for SPATIAL indexes.

所以，在8.0之前的版本中, DESC 是无效的，索引 (a ASC, b DESC, c DESC) 等于 (a ASC, b ASC, c ASC)，故而无法使用整个联合索引进行排序。

Answer 2

8.0之后允许索引降序，抛开 sql 优化等细节，只要 order by 顺序和索引顺序一致，那么还是可以用到索引排序的。

(8.0)CREATE INDEX Syntax
(5.7)CREATE INDEX Syntax