iT邦幫忙

2024 iThome 鐵人賽

DAY 12
0
Software Development

從身邊神人大大身上學到的那些事系列 第 12

Use-the-index-luke 讀後筆記-7(order by / limit/ where)

  • 分享至 

  • xImage
  •  

在訂單系統中,如果我想要列出出某間商店所有商品最近購買的使用者的列表
這個需求很直覺的會寫出這樣的語法

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';

上一篇
Use-the-index-luke 讀後筆記-6(order by / pagination)
下一篇
Use-the-index-luke 讀後筆記-8(誰應該要了解index)
系列文
從身邊神人大大身上學到的那些事30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言