SQL索引性能 - ASC与DESC [英] SQL Index Performance - ASC vs DESC

问题描述

我的自动增量int列上有一个用户表，看起来像这样：

I've got a user table keyed on an auto-increment int column that looks something like this:

CREATE TABLE `user_def` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) NOT NULL,
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name_UNIQUE` (`user_name`),
) ENGINE=MyISAM

使用DESC索引（主键）而不是默认ASC是否有任何实际的性能优势？

Are there any practical performance advantages to using a DESC index (primary key) rather than the default ASC?

我的怀疑/推理是如下：
我假设更近期的用户会更活跃（即更频繁地访问该表），从而使索引更有效率。

My suspicion / reasoning is as follows: I'm assuming that more recent users are going to be more active (i.e. accessing the table more often), therefore making the index more efficient.

我的理解是否正确？

推荐答案

MySQL 8.0的更新答案

正如Kazimieras Aliulis在评论中所述，在MySQL 8.0中添加了对降序索引的支持：

MySQL支持降序索引：索引定义中的DESC不再被忽略
，但会导致按键降序存储键值。
之前，索引可以按相反顺序扫描，但性能损失为
。可以按前转
顺序扫描降序索引，这样效率更高。当
最有效的扫描顺序混合某些列的升序和
降序为其他列时，降序索引也使优化器可以使用多列索引
。

MySQL supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others.

---

早期版本的原始答案

< a href =http://dev.mysql.com/doc/refman/5.0/en/create-index.html =nofollow noreferrer> DESC 索引目前尚未在MySQL中实现...引擎忽略提供的排序并始终使用 ASC ：

---

Original Answer for Earlier Versions

DESC indexing is not currently implemented in MySQL... the engine ignores the provided sort and always uses ASC:

index_col_name规范可以以ASC或DESC结尾。这些
关键字允许用于将来的扩展，以指定升序
或降序索引值存储。目前，他们被解析，但
被忽略;索引值始终按升序存储。

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.

对于另一个实现此功能的RBDMS，例如SQL Server， DESC 规范仅在按化合物排序时有用索引 ...并且不会对新创建的用户与旧用户的查找时间产生影响。

For another RBDMS that does implement this feature, such as SQL Server, the DESC specification is only beneficial when sorting by compound indexes... and won't have an impact on the lookup time for newly created users versus older users.

这篇关于SQL索引性能 - ASC与DESC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！