實踐分享 - MySQL優化 - 查詢去年待流失客戶的交易額

背景

業務方要求獲取2018年，系統中待流失客戶的交易額

資料模型

• t_customer_pool表儲存的是待流失客戶，每個月1號都會有定時任務按照規則重新整理表中的資料
• 如果一個客戶，一年到頭都是待流失客戶，那麼這個客戶在表中會有12條記錄

查詢SQL

錯誤例子 V1.0

SELECT
	c.cust_id "客戶ID",
	c.cust_name "客戶名稱",
	sum(o.amount) "交易金額"
FROM
	t_order o
INNER JOIN t_customer c ON o.cust_id = c.cust_id
WHERE
	o.pay_time >= '2018-01-01 00:00:00'
AND o.pay_time <= '2018-12-31 23:59:59'
AND c.cust_id IN (
	SELECT DISTINCT
    	cust_id
	FROM
    	t_customer_pool
	WHERE
    	create_time >= '2018-01-01 00:00:00'
	AND create_time <= '2018-12-31 23:59:59'
)
GROUP BY
	c.cust_id;
複製程式碼

問題：

1. 不應該使用t_order表驅動t_customer表（大表join小表），應該使用t_customer表驅動t_order表（小表join大表）
2. 待流失客戶有可能是沒有交易的，在t_order表中沒有記錄，所以應該使用left join

按照存在的問題，去修改，得出錯誤例子 V2.0

錯誤例子 V2.0

SELECT
	c.cust_id "客戶ID",
	c.cust_name "客戶名稱",
	sum(o.amount) "交易金額"
FROM
	t_customer c
LEFT JOIN t_order o ON o.cust_id = c.cust_id
AND o.pay_time >= '2018-01-01 00:00:00'
AND o.pay_time <= '2018-12-31 23:59:59'
WHERE
	c.cust_id IN (
    	SELECT DISTINCT
    		cust_id
    	FROM
    		t_customer_pool
    	WHERE
    		create_time >= '2018-01-01 00:00:00'
    	AND create_time <= '2018-12-31 23:59:59'
	)
GROUP BY
	c.cust_id;
複製程式碼

備註

• left join某個表後，該表的篩選條件不要寫在where條件後面，不然left join 會變成 inner join
• 信心滿滿把這條SQL交給DBA執行（測試環境驗證通過），結果DBA說SQL執行了很長時間沒得出結果，只能kill掉
• 線上環境的資料量是測試環境的幾十倍，資料量一上來，隱藏問題就被放大了

問題：

1. t_order表會被全表掃描，應該使用子查詢，先查出2018年內的訂單
2. join表的on語句上最好只新增連線表的條件，篩選條件寫在where後面，避免寫在on條件後面
3. group by 是先分組，後排序，應該加上order by null

正確例子

SELECT
	c.cust_id "客戶ID",
	c.cust_name "客戶名稱",
	sum(o.amount) "交易金額"
FROM
	t_customer c
LEFT JOIN (
	SELECT
    	order_id,
    	amount,
    	cust_id
	FROM
    	t_order
	WHERE
    	pay_time >= '2018-01-01 00:00:00'
	AND pay_time <= '2018-12-31 23:59:59'
) o ON o.cust_id = c.cust_id
WHERE
	c.cust_id IN (
    	SELECT DISTINCT
    		cust_id
    	FROM
    		t_customer_pool
    	WHERE
    		create_time >= '2018-01-01 00:00:00'
    	AND create_time <= '2018-12-31 23:59:59'
	)
GROUP BY
	c.cust_id
ORDER BY
	NULL;
複製程式碼

如果大家有更好的方法，歡迎在文章下面評論