如何在 MySQL 中创建 DESC 索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10109108/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create a DESC index in MySQL?
提问by rid
I have a table from which I need to obtain rows ordered by a field in descending order. When running an EXPLAINquery like the following:
我有一个表，我需要从中获取按字段降序排列的行。运行如下EXPLAIN查询时：
EXPLAIN SELECT ... FROM table WHERE ... ORDER BY field DESC
I get Using where; Using filesortin the Extracolumn. So I try to create a DESCindex:
我得到Using where; Using filesort的Extra列。所以我尝试创建一个DESC索引：
CREATE INDEX name ON table (field DESC);
But when I run EXPLAINagain, I get the same Using where; Using filesortin the Extracolumn and the performance is pretty much the same.
但是当我EXPLAIN再次运行时，我Using where; Using filesort在Extra列中得到相同的结果并且性能几乎相同。
What am I doing wrong?
我究竟做错了什么？
回答by a_horse_with_no_name
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 结尾。这些关键字被允许用于未来扩展以指定升序或降序索引值存储。目前，它们被解析但被忽略；索引值始终按升序存储“
回答by phil_w
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.
当然，这种更改并不总是容易完成……取决于现有代码等。
回答by Maxim Masiutin
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.
MySQL 从版本 8 开始支持 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)
你可以在 MySQL 8 之前的版本中用这个参数创建一个索引，但它实际上是悄悄创建的 (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 列使用未索引（文件排序）。
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
