MySQL的"group by"查询非常缓慢

Question

我有一个查询在一个大约有10万条记录的表中，它运行得非常缓慢（3-4秒），当我去掉group时，速度就快多了（不到0.5秒）。我很困惑该怎么修复这个问题：

SELECT msg.id,
       msg.thread_id,
       msg.senderid,
       msg.recipientid, 
       from_user.username AS from_name,
       to_user.username AS to_name
FROM msgtable AS msg
LEFT JOIN usertable AS from_user ON msg.senderid = from_user.id
LEFT JOIN usertabe AS to_user ON msg.recipientid = to_user.id
GROUP BY msg.thread_id
ORDER BY msg.id desc

msgtable 在 thread_id、id、senderid 和 recipientid 上都有索引。

explain 返回：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  msg ALL NULL    NULL    NULL    NULL    162346  Using temporary; Using filesort
1   SIMPLE  from_user   eq_ref  PRIMARY PRIMARY 4   db.msg.senderid 1    
1   SIMPLE  to_user eq_ref  PRIMARY PRIMARY 4   db.msg.recipientid  1

有什么方法可以加快速度，同时返回相同的结果（每个主题有多条消息，我想在此查询中仅返回一条消息）。

提前致谢。

- Sherif Buzz

---

关于 usertable 的索引呢？您能否运行 EXPLAIN <query> 并发布结果？ - Frankie

通常情况下，如果SELECT语句中的某些列没有被聚合函数（如COUNT、SUM、MIN、MAX等）所封装，则必须在GROUP BY中声明这些列。在这种情况下，使用DISTINCT是否更适合您呢？ - OMG Ponies

为什么要使用左连接？每个消息不都需要有收件人和发件人吗？ - John Hartsock

是的，一条消息需要收件人和发件人，因此不需要左连接。 - Sherif Buzz

此外，请粘贴“show create table msgtable”的输出。 - Glen Solsberry

@Sherif Buzz，当您回复评论时，例如在添加“EXPLAIN”时，您可以使用@Frankie @OMG Ponies标记提问的人，以便他们知道有新内容。谢谢！ - Frankie

Answer 1

尝试这个：

select m.thread_id, m.id, m.senderid, m.recipientid, 
       f.username as from_name, t.username as to_name
from msgtable m
join usertable f on m.senderid = f.id
join usertable t on m.recipientid = t.id
where m.id = (select MAX(id) from msgtable where thread_id = m.thread_id)

或者这样：

select m.thread_id, m.id, m.senderid, m.recipientid, 
       (select username from usertable where id = m.senderid) as from_name,
       (select username from usertable where id = m.recipientid) as to_name
from msgtable m
where m.id = (select MAX(id) from msgtable where thread_id = m.thread_id)

为什么用户表是左连接的？消息是否可能缺少发件人或收件人？

- Fosco

---

非常感谢，我尝试了两个选项——第一个选项大约需要1.5秒，第二个选项大约需要2秒。还有什么其他方法可以让它更快？ - Sherif Buzz

@Sherif 嗯，你真的需要一次性获取所有线程吗？是否有一个日期时间列可以用来减少所需的数据？ - Fosco

@Forsco，事实上，这个查询被翻译成一个带分页类的select count(*)查询 - 是的，我需要所有线程，因为这是为了管理功能... - Sherif Buzz

@Sherif，你需要真实的数据还是只需要计数？还是结果被缓存然后分页呢？ - Fosco

@Sherif 好的，只是确认一下。很高兴我们能够缩短时间，不确定我还能为您做更多。 - Fosco

Answer 2

最大的问题在于您在msgtable上没有可用的索引。在senderid和recipientid上创建一个索引，这将有助于您的查询速度，因为它将限制需要扫描的结果数量。至少要创建一个这样的索引。

- Glen Solsberry