MySQL的order by -id和order by id desc之间的区别

Question

我希望从1百万行的表中获取最后10行。

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

有一种做法是 -

select * from test order by -id limit 10;

**10 rows in set (0.14 sec)**

另一种方法是 -

select * from test order by id desc limit 10;

**10 rows in set (0.00 sec)**

所以我对这些查询进行了'EXPLAIN' -

这是使用'order by desc'查询的结果

EXPLAIN select * from test order by id desc limit 10;

下面是使用'order by -id'查询的结果。

EXPLAIN select * from test order by -id limit 10;

我原本以为这两个是一样的，但似乎执行计划有所不同。

- PiyusG

---

你是在同一个会话中运行这两个查询吗？有时候这可能只是因为数据库缓存的原因。 - smn_onrocks

@smn_onrocks 你是指“数据库缓存”对吧？ - Giacomo1968

@JakeGould 是的，没错。 - smn_onrocks

Answer 1

关系型数据库管理系统（RDBMS）使用启发式算法计算执行计划，它们不能总是确定两个语句的语义等效性，因为这是一个太困难的问题（从理论和实践复杂性的角度来看）。

所以MySQL无法使用索引，因为您没有在“-id”上建立索引，该索引是应用于字段“id”的自定义函数。这似乎是微不足道的，但关系型数据库管理系统必须尽量减少计算计划所需的时间，因此它们会卡在简单的问题上。

当查询无法找到优化方案时（即使用索引），系统会退回到在任何情况下都有效的实现：全表扫描。

- Nicola Ferraro

Answer 2

从Explain结果中可以看到，

1 : order by id
MySQL在id上使用索引。所以它只需要迭代10行，因为已经被索引。并且在这种情况下，MySQL不需要使用filesort算法，因为它已经被索引了。

2 : order by -id
MySQL没有在id上使用索引。所以它需要迭代所有的行数（例如455952）来获得你期望的结果。在这种情况下，MySQL需要使用filesort算法，因为id没有被索引。所以它显然需要更多时间 :)

- Vishal Zanzrukia

Answer 3

当您需要按非关键列的表达式排序时，可以使用ORDER BY:

SELECT * FROM t1 ORDER BY ABS(key);

SELECT * FROM t1 ORDER BY -key;

您仅对ORDER BY子句中的列名前缀进行索引。在这种情况下，索引无法完全解析排序顺序。例如，如果您有一个CHAR（20）列，但只索引了前10个字节，则索引无法区分第10个字节后的值，并且需要使用文件排序。

使用的表索引类型不按顺序存储行。例如，对于MEMORY表中的HASH索引，这是真实的。

请查看此链接：http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

- Vaibhav Kumar