强制`group by`对DESC进行排序会加快代码速度，还是降低代码速度？

In MySQL group by does an implicit order by ASC.
This is great if you wanted to add an ORDER BY ASC because then the results are already ordered.

在MySQL group by中由ASC执行隐式排序。如果你想添加一个ORDER BY ASC，这很好，因为结果已经被排序了。

But if you want to ORDER BY .. DESC MySQL has to order the resultset exactly the other way round.

但是如果你想ORDER BY .. DESC MySQL必须完全相反地对结果集进行排序。

Will this trick speed up the select, slow it down, or do nothing at all

这个技巧会加快选择速度，降低速度，或者什么都不做

SELECT field1, field2 FROM atable 
GROUP BY -mydate   -- group by trick to force a `group by ... desc`
ORDER BY mydate DESC

I know I can just time the code, but I'm looking to gain some deeper insight into the issues at hand.
All the relevant indexes are in place naturally, because it would be silly to optimize without indexes.

我知道我可以为代码计时，但我希望能够深入了解手头的问题。所有相关的索引都是自然而然的，因为没有索引进行优化是很愚蠢的。

3 个解决方案

#1

From my tests, adding any sort a modifier to group by like - to change the sort order slows things down.

从我的测试中，添加任何类型的修改器以进行分组 - 更改排序顺序会减慢速度。

However you are allowed to specify:

但是您可以指定：

SELECT id, name, sum(amount) FROM customers GROUP BY id DESC

And MySQL will happily order the results in DESC order without needing an extra order by clause. This will not incur the extra runtime that adding the - does.

MySQL将很乐意按DESC顺序排序结果，而无需额外的order by子句。这不会产生添加 - 的额外运行时间。

#2

I think you're mistaken: GROUP BY doesn't sort data. It's the default MySQL behaviour that does, as MySQL adds the same ORDER BY as the GROUP BY you've set, as you've mentioned in your first sentence.

我认为你错了：GROUP BY不对数据进行排序。这是默认的MySQL行为，因为MySQL添加了与您设置的GROUP BY相同的ORDER BY，正如您在第一句中所提到的那样。

So, if you disable the sort, by using ORDER BY NULL, there's no sorting at all. The GROUP BY will only group rows together, using indexes if possible. Hence the «trick» is wrong, as you'll remove the ability to use an index on mydate. GROUP BY performs great as long as the index is good for it.

因此，如果通过使用ORDER BY NULL禁用排序，则根本不进行排序。如果可能，GROUP BY将仅使用索引将行组合在一起。因此，“技巧”是错误的，因为你将删除在mydate上使用索引的能力。只要索引对它有好处，GROUP BY就会表现很好。

So:

所以：

SELECT field1, field2 FROM atable 
GROUP BY mydate
ORDER BY NULL

should be really fast if you have an index on (mydate), and

如果你有一个索引（mydate），那应该真的很快

SELECT field1, field2 FROM atable 
GROUP BY mydate
ORDER BY mydate DESC

should be as fast (depending on the table structure, MyISAM is a little bit slower in reverse order).

应该一样快（取决于表结构，MyISAM以相反的顺序稍慢）。

If you have a WHERE clause, check that you've added the columns in the index, for example:

如果您有WHERE子句，请检查是否已在索引中添加了列，例如：

SELECT field1, field2 FROM atable 
WHERE fied1 = 5
GROUP BY mydate
ORDER BY mydate DESC

will need an index on (field1,mydate).

将需要一个索引（field1，mydate）。

#3

Slow It Down

慢下来

What's happening here is that you are asking MySQL to sort the records based on a (probably) non-indexed column mydate.

这里发生的是你要求MySQL根据（可能）非索引列mydate对记录进行排序。

Any sort takes time, but sorts on indexed columns are blazing fast compared to non-indexed ones.

任何排序都需要时间，但与非索引列相比，对索引列的排序非常快。

Here's some additional reading: http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

这里有一些额外的阅读：http：//www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

#1