mysql - 比较基础的JOIN和GROUP BY查询的优化

Question

我有一个相对基本的查询，可以获取每个对话的最新消息:

SELECT `message`.`conversation_id`, MAX(`message`.`add_time`) AS `max_add_time`
FROM `message` 
LEFT JOIN `conversation` ON `message`.`conversation_id` = `conversation`.`id` 
WHERE ((`conversation`.`receiver_user_id` = 1 AND `conversation`.`status` != -2)
OR (`conversation`.`sender_user_id` = 1 AND `conversation`.`status` != -1))
GROUP BY `conversation_id` 
ORDER BY `max_add_time` DESC
LIMIT 12

message 表包含超过 911000 条记录，conversation 表包含大约 680000 条记录。此查询的执行时间在 4 到 10 秒之间变化，具体取决于加载到服务器上。这太长了。

下面是EXPLAIN结果的屏幕截图:

原因显然是 MAX 和/或 GROUP BY，因为以下类似的查询只需要 10ms:

SELECT COUNT(*) 
FROM `message` 
LEFT JOIN `conversation` ON `message`.`conversation_id` = `conversation`.`id` 
WHERE (`message`.`status`=0) 
AND (`message`.`user_id` <> 1) 
AND ((`conversation`.`sender_user_id` = 1 OR `conversation`.`receiver_user_id` = 1))

对应的EXPLAIN结果:

我尝试向两个表添加不同的索引，但没有任何改进，例如: message 上的 conv_msg_idx(add_time,conversation_id) 似乎是根据第一个使用的EXPLAIN 结果，但是查询仍然需要大约 10 秒的时间来执行。

任何改进索引或查询以缩短执行时间的帮助将不胜感激。

编辑:

我已更改查询以使用INNER JOIN:

SELECT `message`.`conversation_id`, MAX(`message`.`add_time`) AS `max_add_time`
FROM `message` 
INNER JOIN `conversation` ON `message`.`conversation_id` = `conversation`.`id` 
WHERE ((`conversation`.`receiver_user_id` = 1 AND `conversation`.`status` != -2)
OR (`conversation`.`sender_user_id` = 1 AND `conversation`.`status` != -1))
GROUP BY `conversation_id` 
ORDER BY `max_add_time` DESC
LIMIT 12

但执行时间仍然约为 6 秒。

Answer 1

您应该在 WHERE 子句中以及要 SELECT 的列上创建多列索引(conversation_id 除外)。 (reference)conversation_id 应该是两个表中的索引。