如何优化使用子查询的MYSQL查询

Question

在我的项目中，我有一个用例，我们需要在每个月的第一天触发一个API，通过电子邮件向当月生日的用户发送礼品卡。

我有一个SQL查询，获取用户信息，他们的生日在给定的月份，还没有收到礼品卡。

注意:礼品卡每年只需要发给用户一次。

我在API端点调用的方法中编写了一个查询，但是由于大量用户满足标准，API给出了超时错误。此外，在获取用户数据后，繁重的写入操作正在执行，同时调用第三方服务向用户发送有关礼品码的电子邮件。

下面是需要优化的查询:

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

Answer 1

首先，我将重写您的子查询以删除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子查询的外部查询中的每条记录进行快速查找。

这个的join版本可能是:

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;