在訂單系統中,如果我想要列出出某間商店所有商品最近購買的使用者的列表
這個需求很直覺的會寫出這樣的語法
SELECT
p.product_id,
p.name AS product_name,
u.user_id,
u.name AS user_name
FROM
product p
JOIN LATERAL (
SELECT
o.user_id
FROM
order_item oi
JOIN order o ON oi.order_id = o.order_id
WHERE
oi.product_id = p.product_id
ORDER BY
o.order_date DESC
LIMIT 1
) latest_order ON TRUE
JOIN users u ON u.user_id = latest_order.user_id
WHERE
p.store_id = :store_id;
先解釋一下join lateral是個什麼樣的函數
子查詢中的內容p是外面找出來的每一筆商品,然後對這每一筆商品都進行一次子查詢,並將結果回傳
如果用寫出來的話,應該會像是
var ids = Product.getAll()
sql := ""
for _, id := range ids {
sql += `SELECT
o.user_id
FROM
order_item oi
JOIN order o ON oi.order_id = o.order_id
WHERE
oi.product_id = `+id+`
ORDER BY
o.order_date DESC
LIMIT 1`
sql += `union
`
}
sql = removeLastUnion(sql)
result := db.Raw(sql).Run()
透過上面的解釋,我們可以發現,我們需要針對每個產品進行一次order by,找到最近的一筆產品
但問題來了
如果某個商品完全沒有訂單,然後這個訂單的資料表又超級大,這時候如果某個商品他的上次購買日期是三年前,每年有1000萬筆資料,這樣每次在下這個語法的時候,就要因為一筆訂單,而拖累整段語法的時間
解決方案有很多,但如果說要在不能更動資料庫的索引跟設定下,最直觀的做法應該就是限制使用者的查詢時間,比如限定只能找三個月內的訂單
這樣一來,就能大大的減少需要搜尋的範圍,進而加快query的速度
SELECT
p.product_id,
p.name AS product_name,
u.user_id,
u.name AS user_name
FROM
product p
JOIN LATERAL (
SELECT
o.user_id
FROM
order_item oi
JOIN order o ON oi.order_id = o.order_id
WHERE
oi.product_id = p.product_id
ORDER BY
o.order_date DESC
LIMIT 1
) latest_order ON TRUE
JOIN users u ON u.user_id = latest_order.user_id
WHERE
p.store_id = :store_id
AND o.order_date >= NOW() - INTERVAL '90 days';