MySQL 在mysql中使用临时，使用文件排序是个坏主意？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1382260/
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
Using temporary, using filesort a bad idea in mysql?
提问by shergill
I am trying to optimize my mysql queries to avoid 'using temporary, using filesort'. I could use some help. First; here is the explain
我正在尝试优化我的 mysql 查询以避免“使用临时，使用文件排序”。我可以使用一些帮助。第一的; 这是解释
Here is the Query
这是查询
select pf.*,m.login,m.avatar
from profile_friends pf, members m
where pf.friend_id = m.id and pf.member_id = 16586
order by m.lastLogin desc
limit 0,24;
mysql> EXPLAIN select pf.*,m.login,m.avatar from profile_friends pf, members m where pf.friend_id = m.id and pf.member_id = 16586 order by m.lastLogin desc limit 0,24;
+----+-------------+-------+--------+-----------------------------------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+
| 1 | SIMPLE | pf | ref | member_id_index,friend_id_index | member_id_index | 4 | const | 160 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | m | eq_ref | PRIMARY,member_id_privacy_index,id_last_login_index | PRIMARY | 4 | mydb.pf.friend_id | 1 | Using where |
There are 2 tables involved. ProfileFriends (pf), and Members (m). This query is just trying to find the 'recent' 24 friends for this particular member id. Recent means sort by LastLogin date.
有2个表涉及。个人资料好友 (pf) 和会员 (m)。此查询只是尝试为该特定成员 ID 查找“最近”的 24 个朋友。最近意味着按上次登录日期排序。
Thanks
谢谢
回答by Charles
It is a problem? Yeah.
这是一个问题吗？是的。
Is it a problem when you're dealing with 160 rows? Nope.
当您处理 160 行时会出现问题吗？不。
"Filesort" is a method, not the actual creation of a file and sorting it. If we were talking about 160,000 rows instead of 160 rows, then there'd probably be reason to consider further optimizations.
“Filesort”是一种方法，而不是实际创建文件并对其进行排序。如果我们谈论的是 160,000 行而不是 160 行，那么可能有理由考虑进一步优化。
Edit: Also, you omitted the actual query running time. You're hitting indexes and only working with a handful of rows. If this query is taking more than a fraction of a fraction of a second, it's probably not worth even looking at for optimization.
编辑：另外，您省略了实际的查询运行时间。您正在访问索引并且只处理少数几行。如果此查询花费的时间超过几分之一秒，则可能甚至不值得考虑进行优化。
回答by Josh Davis
I think you should be able to avoid the temporary/filesort with an index on members (id,lastLogin)—in that order—but for that type of queries it's overkill and judging by your EXPLAIN it seems you've already tried that?
我认为您应该能够避免使用索引的临时/文件排序members (id,lastLogin)- 按该顺序 -但对于这种类型的查询，它是矫枉过正的，根据您的 EXPLAIN 判断，您似乎已经尝试过了？
You might complement it with a PRIMARY/UNIQUE KEY on profile_friends (member_id,friend_id)and see how it works.
你可以用 PRIMARY/UNIQUE KEY 来补充它，profile_friends (member_id,friend_id)看看它是如何工作的。
In last resort, if that query is executed so often and with so many record that you must have the fastest SELECT possible, you could denormalize your table and add a copy of your members.lastLogincolumn to profile_friendswith an index on (member_id,lastLogin). With that, you'd have no join, no filesort, no nothing. On the other hand, you'd have big UPDATE queries everytime anyone with many friends logs in. Again, this seems completely overkill for the kind of numbers you're talking about.
作为最后的手段，如果该查询执行得如此频繁且记录如此之多，以至于您必须尽可能使用最快的 SELECT，您可以对您的表进行非规范化并添加您的members.lastLogin列的副本，profile_friends并在 上建立索引(member_id,lastLogin)。有了它，你就没有加入，没有文件排序，什么都没有。另一方面，每次有很多朋友的人登录时，您都会有大量的 UPDATE 查询。同样，对于您正在谈论的那种数字，这似乎完全是矫枉过正。
I almost forgot to address the original question:
我差点忘了回答最初的问题：
Using temporary, using filesort a bad idea in mysql?
在mysql中使用临时，使用文件排序是个坏主意？
No it's not. If you can optimize it away easily then you should always seek "filesort-free" queries, but otherwise unless they pose a real performance problem you shouldn't worry about it. Filesort is part of the normal execution of a query.
不，这不对。如果您可以轻松优化它，那么您应该始终寻求“无文件排序”查询，否则除非它们造成真正的性能问题，否则您不必担心。Filesort 是正常执行查询的一部分。
回答by Eric
That's the most efficient way to write that query.
这是编写该查询的最有效方法。
Make sure that pf.friend_id, pf.member_id, and m.idhave indices on them. Then it will use the indices to join the tables and filter the results.
确保pf.friend_id，pf.member_id和m.id对他们的指标。然后它将使用索引连接表并过滤结果。
That sort is going to come up, anyway, because of your order by.
无论如何，由于您的order by.
