選擇與左連接內的子查詢

通常我必須在獲取其他不相關行的查詢中返回某些行的計數。

例如一個表使用者一個表評論和一個表圖片

User:
id
nickname

Review:
id
to_user_id
from_user_id
rating

Picture:
id:
user_id
url

假設我想在一個查詢中檢索“給定”使用者 ID 的所有圖片 url 的暱稱以及查看該使用者的人數。

我在執行此查詢時認為的第一個也是簡單的方法是：

SELECT
 u.nickname
 (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount,
 p.url
FROM user
LEFT JOIN picture ON p.user_id = u.id
WHERE 
 u.id = 1

這樣做的另一種方法是不使用該子選擇，並在正確的 user_id 上加入評論表

SELECT 
u.nickname,
r.reviewCount,
p.url
FROM user u 
LEFT JOIN (
   SELECT to_user_id, count(*) reviewCount FROM review GROUP BY to_user_id
) r ON r.to_user_id = u.id
LEFT JOIN picture ON p.user_id = u.id 
WHERE u.id = 1;

我不是數據庫查詢性能和調整方面的專家。如果一個解決方案比另一個更好，有人可以解釋我嗎？（或者如果有其他更好的解決方案）？

編輯： 抱歉忘了提。我正在使用最新的 MySQL

您沒有指定正在使用的 RDBMS。我在這裡寫的大部分內容應該是相當獨立的，但我主要有 MySQL 方面的經驗，所以也許不同的系統允許一些其他優化。

這(SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount是一個依賴子查詢- 它將針對結果中的每一行執行。即使一次執行速度很快，也可能有數千次執行會使其變慢。

中的一個JOIN是派生表- 它只會執行一次並具體化為一個臨時表，然後將其連接到您的其他表。如果查詢速度很快（可以使用 index on (to_user_id)），那很好。但在這種情況下，即使對於沒有真正顯示在結果中的使用者，也會計算計數。但是..您可以將條件推入其中（to_user_id = 1而不是 GROUP BY）。

但是為了讓事情變得不那麼簡單，在較新的版本中存在一些優化。通過在 MariaDB 10（和 IIRC MySQL 5.7，但我沒有驗證）中使用子查詢記憶體，可以使依賴子查詢更快。這意味著在您的情況下，結果中的所有行都有u.id = 1->to_user_id = 1並且子查詢實際上只會執行一次，然後將使用記憶體的結果。如果可用，則兩個版本之間的差異將很小。

就我個人而言，大多數時候我更喜歡您的第二個版本，但在某些情況下，第一個版本會更快 - 我曾經有一個查詢，其中不能簡單地以正確的方式限制 JOINED 子查詢中的行，而是切換到從屬子查詢實際上只讀取了幾個唯一的組合。

引用自：https://dba.stackexchange.com/questions/137917