MySQL 使用 ORDER BY 时查询速度慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/884661/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Slow query when using ORDER BY
提问by
Here's the query (the largest table has about 40,000 rows)
这是查询（最大的表大约有 40,000 行）
SELECT
Course.CourseID,
Course.Description,
UserCourse.UserID,
UserCourse.TimeAllowed,
UserCourse.CreatedOn,
UserCourse.PassedOn,
UserCourse.IssuedOn,
C.LessonCnt
FROM
UserCourse
INNER JOIN
Course
USING(CourseID)
INNER JOIN
(
SELECT CourseID, COUNT(*) AS LessonCnt FROM CourseSection GROUP BY CourseID
) C
USING(CourseID)
WHERE
UserCourse.UserID = 8810
If I run this, it executes very quickly (.05 seconds roughly). It returns 13 rows.
如果我运行它，它执行得非常快（大约 0.05 秒）。它返回 13 行。
When I add an ORDER BYclause at the end of the query (ordering by any column) the query takes about 10 seconds.
当我ORDER BY在查询末尾添加一个子句（按任何列排序）时，查询大约需要 10 秒。
I'm using this database in production now, and everything is working fine. All my other queries are speedy.
我现在在生产中使用这个数据库，一切正常。我所有的其他查询都很快。
Any ideas of what it could be? I ran the query in MySQL's Query Browser, and from the command line. Both places it was dead slow with the ORDER BY.
关于它可能是什么的任何想法？我在 MySQL 的查询浏览器和命令行中运行了查询。这两个地方都用ORDER BY.
EDIT:Tolgahan ALBAYRAK solution works, but can anyone explain why it works?
编辑：Tolgahan ALBAYRAK 解决方案有效，但谁能解释它为什么有效？
采纳答案by Tolgahan Albayrak
maybe this helps:
也许这有帮助：
SELECT * FROM (
SELECT
Course.CourseID,
Course.Description,
UserCourse.UserID,
UserCourse.TimeAllowed,
UserCourse.CreatedOn,
UserCourse.PassedOn,
UserCourse.IssuedOn,
C.LessonCnt
FROM
UserCourse
INNER JOIN
Course
USING(CourseID)
INNER JOIN
(
SELECT CourseID, COUNT(*) AS LessonCnt FROM CourseSection GROUP BY CourseID
) C
USING(CourseID)
WHERE
UserCourse.UserID = 8810
) ORDER BY CourseID
回答by ceejayoz
Is the column you're ordering by indexed?
您按索引排序的列是否已编入索引？
Indexing drastically speeds up ordering and filtering.
索引大大加快了排序和过滤的速度。
回答by Guy
You are selecting from "UserCourse" which I assume is a joining table between courses and users (Many to Many). You should index the column that you need to order by, in the "UserCourse" table.
您正在从“ UserCourse”中进行选择，我认为它是课程和用户（多对多）之间的连接表。您应该在“UserCourse”表中索引您需要排序的列。
Suppose you want to "order by CourseID", then you need to index it on UserCoursetable.
假设您想“按 CourseID 排序”，那么您需要在UserCourse表上对其进行索引。
Ordering by any other column that is not present in the joining table (i.e. UserCourse) may require further denormalization and indexing on the joining tableto be optimized for speed; In other words, you need to have a copy of that column in the joining table and index it.
按连接表（即 UserCourse）中不存在的任何其他列进行排序可能需要对连接表进行进一步的非规范化和索引以优化速度；换句话说，您需要在连接表中拥有该列的副本并对其进行索引。
P.S.The answer given by Tolgahan Albayrak, although correct for this question, would not produce the desired result, in cases where one is doing a "LIMIT x" query.
PSTolgahan Albayrak 给出的答案虽然对这个问题是正确的，但在执行“LIMIT x”查询的情况下不会产生预期的结果。
回答by aronp
Realise answer is too late, however I have just had a similar problem, adding order by increased the query time from seconds to 5 minutes and having tried most other suggestions for speeding it up, noticed that the /tmp files where getting to be 12G for this query. Changed the query such that a varchar(20000) field being returned was "trim("ed and performance dramatically improved (back to seconds). So I guess its worth checking whether you are returning large varchars as part of your query and if so, process them (maybe substring(x, 1, length(x))?? if you dont want to trim them. Query was returning 500k rows and the /tmp file indicated that each row was using about 20k of data.
意识到答案为时已晚，但是我刚刚遇到了类似的问题，通过将查询时间从几秒增加到 5 分钟来添加订单，并尝试了大多数其他加快速度的建议，注意到 /tmp 文件的大小为 12G这个查询。更改了查询，使得返回的 varchar(20000) 字段为“trim(”ed，性能显着提高（回到秒）。所以我想值得检查您是否将大 varchars 作为查询的一部分返回，如果是这样，处理它们（也许是 substring(x, 1, length(x))?? 如果你不想修剪它们。查询返回 500k 行，/tmp 文件表明每行使用了大约 20k 数据。
回答by Fry
Have you updated the statistics on your database? I ran into something similar on mine where I had 2 identical queries where the only difference was a capital letter and one returned in 1/2 a second and the other took nearly 5 minutes. Updating the statistics resolved the issue
您是否更新了数据库的统计信息？我遇到了类似的问题，我有 2 个相同的查询，唯一的区别是一个大写字母，一个在 1/2 秒内返回，另一个用了近 5 分钟。更新统计信息解决了问题
回答by northpole
回答by Dominik Jungowski
Today I was running into a same kind of problem. As soon as I was sorting the resultset by a field from a joined table, the whole query was horribly slow and took more than a hundred seconds.
今天我遇到了同样的问题。一旦我按照连接表中的字段对结果集进行排序，整个查询就非常缓慢，耗时超过一百秒。
The server was running MySQL 5.0.51a and by chance I noticed that the same query was running as fast as it should have always done on a server with MySQL 5.1. When comparing the explains for that query I saw that obviously the usage and handling of indexes has changed a lot (at least from 5.0 -> 5.1).
该服务器正在运行 MySQL 5.0.51a，我偶然注意到相同的查询在运行 MySQL 5.1 的服务器上的运行速度与它应该始终运行的速度一样快。在比较该查询的解释时，我发现索引的使用和处理显然发生了很大变化（至少从 5.0 -> 5.1）。
So if you encounter such a problem, maybe your resolution is to simply upgrade your MySQL
所以如果你遇到这样的问题，也许你的解决方案是简单地升级你的MySQL
