mysql - 避免多个内部联接从同一个表添加新列(信息)

Question

我想知道是否有办法为 user_id、sender_user_id 和 recipient_user_id 提供 name 信息来自 this fiddle 中的模式.

我现在唯一能想到的办法就是做这个嵌套的inner join，我觉得效率很低:

SELECT e.id
    ,msg_owner
    ,msg_owner_name
    ,sender_user_id
    ,sender_name
    ,recipient_user_id
    ,f.NAME AS recipient_name
    ,msg_body
    ,created_at
FROM (
    SELECT c.id
        ,msg_owner
        ,msg_owner_name
        ,sender_user_id
        ,d.NAME AS sender_name
        ,recipient_user_id
        ,msg_body
        ,created_at
    FROM (
        SELECT a.id
            ,user_id AS msg_owner
            ,NAME AS msg_owner_name
            ,sender_user_id
            ,recipient_user_id
            ,msg_body
            ,created_at
        FROM messages AS a
        INNER JOIN users AS b ON a.user_id = b.id
        ) AS c
    INNER JOIN users AS d ON c.sender_user_id = d.id
    ) AS e
INNER JOIN users AS f ON e.recipient_user_id = f.id

是否有任何(更有效的)方法可以为上述三列中的每一列引入 name 值？感谢您的回答/建议!

Answer 1

只使用连接，而不是嵌套连接。

按照您的方式，您强制数据库收集每个内部查询的所有结果，然后将其连接到另一个表。除非最内层查询的内容通过 WHERE 子句非常有限，否则这对性能总是不利的。

以下是使用连接的正确方法。它允许直接使用索引(如果存在)来加快速度。

SELECT m.id
    ,owner.id AS msg_owner
    ,owner.name AS msg_owner_name
    ,m.sender_user_id
    ,sender.name AS sender_name
    ,m.recipient_user_id
    ,recipient.name AS recipient_name
    ,m.body AS msg_body
    ,m.created_at
FROM messages m
INNER JOIN users sender
ON sender.id = m.sender_user_id
INNER JOIN users recipient
ON recipient.id = m.recipient_user_id
INNER JOIN users owner
ON owner.id = m.user_id