mysql - 增强SQL查询的性能

我在三个表messages，message_recipients和users上运行查询。

messages表的表结构：

id int pk
message_id int
message text
user_id int
...

该表的索引在user_id，message_id和id上。

message_recipients表的表结构：

id int pk
message_id int
read_date datetime
user_id int
...

索引位于id，message_id和user_id上。

users表的表结构：

id int pk
display_name varchar
...

索引在id上。

我正在对这些表运行以下查询：

SELECT
    m.*,
    if(m.user_id = 0, 'Campus Manager', u.display_name) AS name,
    mr.read_date,
    IF(m1.message_id > 0 and m1.user_id=1, true, false) as replied
FROM
    messages m
JOIN
    message_recipients mr
ON
    mr.message_id = m.id
LEFT JOIN
    users u
ON
    u.UID = m.user_id
LEFT JOIN
    messages m1
ON
    m1.message_id = m.id
WHERE
    mr.user_id = 1
AND
    m.published = 1
GROUP BY
    mr.message_id
ORDER BY
    m.created DESC

EXPLAIN返回此查询的以下数据：


更新
正如@ e4c5所建议的那样，我在（published，user_id，created）上添加了新的复合索引，现在说明查询显示了这一点：


由于需要大量时间，因此如何通过添加所需的索引（如果有）来优化此查询？

最佳答案

GROUP BY需要列出所有未聚合的列。我怀疑那会是一团糟。为什么根本需要GROUP BY？
为什么要将messages.id链接到messages_id？这是一个分层表，但是列名与'parent_id'不同吗？
“索引位于id，message_id和user_id上” –是一个复合索引还是3个单列索引？ （这有很大的不同。）最好向我们显示SHOW CREATE TABLE而不是模棱两可的措辞。
user_id = 1多产吗？也就是说，您是否期望成千上万的行？这个查询对他来说只是一个问题吗？
使用LEFT JOIN意味着m1.message_id可以是NULL，但是对其的引用似乎忽略了这种可能性。
如果这是一个包含消息线程的表-有关该线程的主要信息和各个响应，那么我建议这是一个错误的设计。 （我曾经犯过这个错误。）我认为最好有一个表，每个线程一行，另一个表每个注释一行。 1个主题：很多评论。因此，注释表中将有一个thread_id。

关于mysql - 增强SQL查询的性能，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41591009/