Postgresql
如何联合 2 個子查詢以減少從繁瑣的表中檢索數據?
有 3 個表:
user
、conversation
、message
。表
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)