如何在MySQL中创建DESC索引?

I have a table from which I need to obtain rows ordered by a field in descending order. When running an EXPLAIN query like the following:

我有一个表，需要从中获取按字段降序排列的行。当运行如下所示的EXPLAIN查询时:

EXPLAIN SELECT ... FROM table WHERE ... ORDER BY field DESC

I get Using where; Using filesort in the Extra column. So I try to create a DESC index:

我使用的地方;在额外的列中使用filesort。所以我尝试创建一个DESC索引:

CREATE INDEX name ON table (field DESC);

But when I run EXPLAIN again, I get the same Using where; Using filesort in the Extra column and the performance is pretty much the same.

但是当我再次运行EXPLAIN时，我得到了相同的用法;在额外的列中使用filesort，性能基本相同。

What am I doing wrong?

我做错了什么?

3 个解决方案

#1

That's one of those MySQL "features" where it silently ignores your request to do something because it's simply not implemented:

这是MySQL的一个“特性”，它会默默地忽略你的请求，因为它根本没有实现:

From http://dev.mysql.com/doc/refman/5.5/en/create-index.html

从http://dev.mysql.com/doc/refman/5.5/en/create-index.html

"An index_col_name 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"

index_col_name规范可以以ASC或DESC结尾。目前，它们被解析但被忽略;索引值总是按升序存储"

#2

As mentioned the feature is not implemented but some workarounds may be of interest:

如前所述，该特性尚未实现，但一些解决方案可能会有兴趣:

One possibility is to store the field in a negated or reverse value.

一种可能是将字段存储在一个否定的或反向的值中。

If it is a number you can store (-n) or (MAXVAL -n) if unsigned

如果是一个数字，则可以存储(-n)或(MAXVAL -n)如果未签名。

If it's a date or timestamp, some would advocate to store a number instead and use functions such as FROM_UNIXTIME()

如果是日期或时间戳，有些人会主张存储一个数字，并使用FROM_UNIXTIME()等函数

Of course such change is not always easily done... depends on existing code etc.

当然，这种改变并不总是容易做到的……取决于现有的代码等等。

#3

MySQL starting from version 8 supports DESC indexes. Before that DESC was silently ignored. This was not a problem for (a) single-column indexes or (b) for multi-column indexes where all columns had one direction: either all ASC or all DESC -- since indexes are bidirectional.

从版本8开始的MySQL支持DESC索引。在此之前，DESC被默默地忽略了。对于(a)单列索引或(b)所有列都有一个方向的多列索引，这不是问题:要么是所有ASC，要么是所有DESC——因为索引是双向的。

But if you need a multi-column index where column directions is different, e.g. you run multiple quires like that:

但是，如果您需要一个列方向不同的多列索引，例如，您运行多个这样的查询:

SELECT * from MyTable WHERE ColumnA = 1 ORDER BY ColumnB ASC, ColumnC DESC

you needed the following index: (ColumnA, ColumnB ASC, ColumnC DESC)

您需要以下索引:(ColumnA, ColumnB ASC, ColumnC DESC)

You could create an index with this parameters in MySQL prior to version 8, but it created sliently in fact (ColumnA ASC, ColumnB ASC, ColumnC ASC)

在版本8之前，您可以在MySQL中创建一个具有这些参数的索引，但实际上它创建得很随意(ColumnA ASC、ColumnB ASC、ColumnC ASC)

So your query couldn't fully use that index - it only took columns A and B from the index, while using unindexed (filesort) for the column C.

因此，查询不能完全使用该索引——它只从索引中获取A和B列，而对C列使用未索引(filesort)。

This will no longer be an issue in MySQL 8.0 and later version. See https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

这将不再是MySQL 8.0和后续版本中的问题。参见https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

#1