Postgresql

如何联合 2 個子查詢以減少從繁瑣的表中檢索數據?

  • January 13, 2016

有 3 個表:userconversationmessage

user

  • ID,
  • 登錄,
  • last_login_time(最後一個使用者登錄的時間),

conversation

  • ID,
  • user1_id,
  • user2_id,
  • message_last_read_id_user1 - 使用者 user1_id 上次閱讀消息的 ID,
  • message_last_write_id_user1 - 使用者 user1_id 最後寫入消息的 id,
  • message_last_read_id_user2 - 使用者 user2_id 上次閱讀消息的 ID
  • message_last_write_id_user2 - 使用者 user2_id 最後寫入消息的 id,

message

  • ID,
  • 對話ID,
  • poster_id - 發送該消息的使用者 ID,
  • 味精,

目的是選擇在過去一小時內登錄的使用者,並為每個使用者選擇未讀消息的數量:

SELECT u.id, u.login, u.last_login_time, 
( 
 SELECT count ( * ) FROM conversation c, message m 
 WHERE c.user1_id = u.id AND m.conversation_id = c.id AND
       COALESCE(c.message_last_read_id_user1, 0) < COALESCE(message_last_write_id_user2, 0) AND 
       m.id > COALESCE(message_last_read_id_user1, 0) AND m.id <= COALESCE(message_last_write_id_user2, 0) AND 
       m.poster_id = c.user2_id
) AS unread_user1, 
( 
 SELECT count ( * ) FROM conversation c, message m 
 WHERE user2_id = u.id AND m.conversation_id = c.id AND 
       COALESCE(message_last_read_id_user2, 0) < COALESCE(message_last_write_id_user1, 0) AND 
   c.user1_id > 0 AND m.id > COALESCE(message_last_read_id_user2, 0) AND m.id <= COALESCE(message_last_write_id_user1, 0) AND 
       m.poster_id != c.user2_id 
) AS unread_user2
FROM user u WHERE u.last_login_time >= 1452504418;

unread_user1 或 unread_user2 之一總是為 0。查詢計劃在這裡http://explain.depesz.com/s/VT6

在該查詢中,兩個子查詢具有交叉連接conversation c, message m,這是非常繁重的操作。那麼也許有人知道如何將這些子查詢合併為一個?

一種想法是使用表達式進行計數:

SELECT u.id, u.login, u.last_login_time
    , COUNT(CASE WHEN COALESCE(c.message_last_read_id_user1, 0) 
                    < COALESCE(message_last_write_id_user2, 0) 
                  AND m.id > COALESCE(message_last_read_id_user1, 0) 
                  AND m.id <= COALESCE(message_last_write_id_user2, 0) 
                  AND m.poster_id = c.user2_id
                 THEN 1
            END) as unread_user1
    , COUNT(CASE WHEN COALESCE(message_last_read_id_user2, 0) 
                    < COALESCE(message_last_write_id_user1, 0) 
                  AND c.user1_id > 0 
                  AND m.id > COALESCE(message_last_read_id_user2, 0) 
                  AND m.id <= COALESCE(message_last_write_id_user1, 0) 
                  AND m.poster_id <> c.user2_id
                 THEN 1
            END) as unread_user2   
FROM user u 
JOIN conversation c
   ON c.user1_id = u.id
JOIN message m
   ON m.conversation_id = c.id
WHERE u.last_login_time >= 1452504418
GROUP BY u.id, u.login, u.last_login_time;

我可能在重寫時遺漏了一些部分,但希望你能得到一些想法。

如果較新的消息在表中始終具有message比舊消息更大的 ID 值,也許您可以通過刪除涉及的檢查來簡化子查詢中的過濾條件message_last_write_id_*。這些檢查似乎限制了結果集中的行數(因此可能會加速子查詢),但實際上,如果一條消息與對話匹配並且其 ID 大於上次讀取的 ID 並且發帖人不是user.id但對方,則為未讀消息user.id

所以,這就是你可以計算的方式unread_user1

SELECT
 COUNT(*)
FROM
 message AS m
 INNER JOIN conversation AS c
   ON  m.conversation_id = c.id
   AND m.poster_id = c.user2_id
WHERE
 c.user1_id = u.id
 AND m.id > COALESCE(c.message_last_read_id_user1, 0)

您還可以嘗試用m.id > COALESCE(c.message_last_read_id_user1, 0)析取替換謂詞,看看它是否可以為整個查詢帶來更有效的執行計劃:

 …
 AND (c.message_last_read_id_user1 IS NULL
   OR m.id > c.message_last_read_id_user1)

同樣,這將是子查詢unread_user2

SELECT
 COUNT(*)
FROM
 message AS m
 INNER JOIN conversation AS c
   ON  m.conversation_id = c.id
   AND m.poster_id = c.user1_id
WHERE
 c.user2_id = u.id
 AND m.id > COALESCE(c.message_last_read_id_user2, 0)
 -- or like this:
 -- AND (c.message_last_read_id_user2 IS NULL
 --   OR m.id > c.message_last_read_id_user2)

引用自:https://dba.stackexchange.com/questions/125881