mysql订单按-id和id desc顺序

I wish to fetch the last 10 rows from the table of 1 M rows.

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

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

One way of doing this is -

一种方法是-

select * from test order by -id limit 10;

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

Another way of doing this is -

另一种方法是-。

select * from test order by id desc limit 10;

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

So I did an 'EXPLAIN' on these queries -

所以我对这些问题做了一个“解释”

Here is the result for the query where I use 'order by desc'

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

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

And here is the result for the query where I use 'order by -id'

这是查询的结果这里我用了'order by -id'

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

I thought this would be same but is seems there are differences in the execution plan.

我原以为这是一样的，但似乎执行计划有差异。

3 个解决方案

#1

RDBMS use heuristics to calculate the execution plan, they cannot always determine the semantic equivalence of two statements as it is a too difficult problem (in terms of theoretical and practical complexity).

RDBMS使用启发式来计算执行计划，它们不能总是确定两个语句的语义等价性，因为这是一个非常困难的问题(在理论和实际的复杂性方面)。

So MySQL is not able to use the index, as you do not have an index on "-id", that is a custom function applied to the field "id". Seems trivial, but the RDBMSs must minimize the amount of time needed to compute the plans, so they get stuck with simple problems.

因此MySQL不能使用索引，因为“-id”上没有索引，这是一个应用于“id”字段的自定义函数。看起来很琐碎，但是rdbms必须最小化计算计划所需的时间，因此他们会陷入简单的问题中。

When an optimization cannot be found for a query (i.e. using an index) the system fall back to the implementation that works in any case: a scan of the full table.

当无法为查询(即使用索引)找到一个优化时，系统会返回到在任何情况下工作的实现:对整个表的扫描。

#2

As you can see in Explain results,

正如你在解释结果中看到的，

1 : order by id
MySQL is using indexing on id. So it need to iterate only 10 rows as it is already indexed. And also in this case MySQL don't need to use filesort algorithm as it is already indexed.

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

2 : order by -id
MySQL is not using indexing on id. So it needs to iterate all the rows.( e.g. 455952) to get your expected results. In this case MySQL needs to use filesort algorithm as id is not indexed. So it will obviously take more time :)

2: order by -id MySQL没有在id上使用索引，所以需要遍历所有的行。(例如:455952)以得到您期望的结果。在这种情况下，MySQL需要使用文件集算法，因为id没有被索引。所以这显然需要更多的时间

#3

You use ORDER BY with an expression that includes terms other than the key column name:

您使用ORDER BY和一个表达式，该表达式包含除键列名称之外的其他术语:

SELECT * FROM t1 ORDER BY ABS(key);

SELECT * FROM t1 ORDER BY -key;

You index only a prefix of a column named in the ORDER BY clause. In this case, the index cannot be used to fully resolve the sort order. For example, if you have a CHAR(20) column, but index only the first 10 bytes, the index cannot distinguish values past the 10th byte and a filesort will be needed.

您仅索引ORDER BY子句中命名的列的前缀。在这种情况下，索引不能用于完全解析排序顺序。例如，如果您有一个CHAR(20)列，但是只索引前10个字节，那么索引就不能识别超过10字节的值，并且需要一个文件集。

The type of table index used does not store rows in order. For example, this is true for a HASH index in a MEMORY table.

使用的表索引类型不按顺序存储行。例如，对于内存表中的散列索引，这是正确的。

Please follow this link: http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

请遵循这个链接:http://devmy.sql.com/doc/refman/5.7/en/orderder -by-optimization z.html