mysql - 增强 SQL 查询的性能

Question

我正在三个表 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) 上添加了新的复合索引，现在解释查询显示了以下内容:

由于该查询需要花费大量时间，因此如何通过添加所需索引(如果有)来优化此查询？

Answer 1

• 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。