mysql - SQL 索引性能 - ASC 与 DESC

Question

我有一个用户表，它键入一个自动递增的 int 列，看起来像这样:

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 是否有任何实际的性能优势？

我的怀疑/推理如下:我假设最近的用户会更活跃(即更频繁地访问表)，因此使索引更有效率。

我的理解正确吗？

Answer 1

MySQL 8.0 的更新答案

正如 Kazimieras Aliulis 在评论中指出的那样，support for descending indexes is being added in MySQL 8.0 :

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.

---

早期版本的原始答案

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

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，the DESC specification is only beneficial when sorting by compound indexes ...并且不会对新创建用户与旧用户的查找时间产生影响。