在我的專案中，我有一個用例，我們需要在每個月的第一天觸發一個 API，以通過電子郵件向當月生日的用戶發送禮品卡。
我有一個 SQL 查詢，它獲取用戶在給定月份的生日資訊，但尚未收到禮品卡。
注意：禮品卡每年只需發送給用戶一次。
我在 API 端點呼叫的方法中撰寫了一個查詢，但是由于大量用戶滿足條件，API 給出了超時錯誤。此外，在獲取用戶資料后，正在執行大量寫入操作，同時呼叫 3rd 方服務向用戶發送有關禮品代碼的電子郵件。
下面是需要優化的查詢：
SELECT id,
first_name,
last_name,
email,
date_of_birth
FROM `user`
WHERE DATE_FORMAT(date_of_birth, '%m') = 6
AND email IS NOT NULL AND status != 0
AND email NOT IN
(
SELECT recipient_email FROM gift_card WHERE
DATE_FORMAT(scheduled_at, '%m') = 6
AND
DATE_FORMAT(scheduled_at, '%Y') = 2022
AND
message = 'Happy birthday! from BURST'
AND
status = 1
);
我嘗試使用NOT EXISTS它需要更多時間。
SELECT id,
first_name,
last_name,
email,
date_of_birth
FROM `user`
WHERE DATE_FORMAT(date_of_birth, '%m') = 6
AND email IS NOT NULL AND status != 0
AND NOT EXISTS
(
SELECT 1 FROM gift_card WHERE
DATE_FORMAT(scheduled_at, '%m') = 6
AND
DATE_FORMAT(scheduled_at, '%Y') = 2022
AND
message = 'Happy birthday! from BURST'
AND
status = 1
AND
recipient_email = email
);
uj5u.com熱心網友回復：
首先，我會將您的子查詢重寫為 remove DATE_FORMAT，這將阻止使用任何索引：
SELECT id, first_name, last_name, email, date_of_birth
FROM user
WHERE DATE_FORMAT(date_of_birth, '%m') = 6 AND
email IS NOT NULL AND status != 0 AND
NOT EXISTS
(
SELECT 1
FROM gift_card
WHERE scheduled_at >= '2022-06-01' AND scheduled_at < '2022-07-01' AND
message = 'Happy birthday! from BURST' AND
status = 1 AND
recipient_email = email
);
您可以嘗試將以下索引添加到gift_card表中：
CREATE INDEX idx ON gift_card (scheduled_at, message, status, recipient_email);
如果使用，上述索引應該允許 MySQL 對子查詢的外部查詢中的每條記錄進行快速查找gift_card。
這個的加入版本可能是：
SELECT u.id, u.first_name, u.last_name, u.email, u.date_of_birth
FROM user u
LEFT JOIN gift_card g
ON g.scheduled_at >= '2022-06-01' AND g.scheduled_at < '2022-07-01' AND
g.message = 'Happy birthday! from BURST' AND
g.status = 1 AND
g.recipient_email = u.email
WHERE
DATE_FORMAT(u.date_of_birth, '%m') = 6 AND
u.email IS NOT NULL AND u.status != 0 AND
g.recipient_email IS NULL;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/491396.html