如何优化带子查询的MySQL查询 解决大用户量下接口超时问题

两个版本SQL性能差的核心问题：

• 所有时间字段都用DATE_FORMAT()函数包裹，数据库无法使用字段上的普通索引，只能走全表扫描，数据量越大执行越慢
• 你写的NOT EXISTS语句没有对应联合索引支撑，关联查询时要做大量回表判断，性能反而比NOT IN更差
• 业务设计本身存在缺陷：把大批量数据查询、批量写库、第三方邮件接口调用全放在同步API链路里，数据量上来后超时是必然结果，和SQL本身快慢没有绝对关系。

第一步：创建支撑索引 #

先建两个联合索引，避免全表扫描：

-- 用户表索引：覆盖生日、状态、邮箱三个过滤/返回字段
CREATE INDEX idx_user_birth_status_email ON `user`(date_of_birth, status, email);
-- 礼品卡表索引：覆盖发送时间、状态、消息内容、收件邮箱字段
CREATE INDEX idx_gc_time_status_msg_receiver ON gift_card(scheduled_at, status, message, recipient_email);

第二步：改写SQL #

去掉字段上的函数计算，用LEFT JOIN + IS NULL的方式替代NOT IN和低效的NOT EXISTS，以查询2022年6月应发用户为例：

SELECT 
  u.id,
  u.first_name,
  u.last_name,
  u.email,
  u.date_of_birth
FROM `user` u
LEFT JOIN gift_card gc
  ON gc.recipient_email = u.email
  -- 时间条件改范围匹配，可命中索引
  AND gc.scheduled_at >= '2022-06-01 00:00:00'
  AND gc.scheduled_at < '2022-07-01 00:00:00'
  AND gc.message = 'Happy birthday! from BURST'
  AND gc.status = 1
WHERE 
  -- 生日匹配6月，避免用DATE_FORMAT套字段
  u.date_of_birth LIKE '%-06-%'
  AND u.email IS NOT NULL 
  AND u.status != 0
  -- 筛选未发过卡的用户
  AND gc.recipient_email IS NULL;

性能优化提示：如果用户表数据量过百万，建议新增birth_month TINYINT类型字段，写入用户数据时同步存储生日月份值，查询时直接用u.birth_month = 6过滤，性能比LIKE模糊匹配高一个数量级。

仅优化SQL无法完全解决超时问题，必须把同步流程改成异步：

• 每月1日的触发API只负责任务启动，收到请求后立刻返回成功，不执行任何实际查询、写库、发邮件逻辑
• 实际处理逻辑放到后台异步任务中执行，用游标分批拉取数据，每次只处理100-500条用户，避免一次性加载全量数据导致内存溢出或数据库压力过高
• 每批用户处理流程：
  • 批量生成本批用户的礼品卡记录，批量写入数据库
  • 把待发送邮件的任务投递到消息队列
  • 记录当前处理位点，任务中断重启后可以从断点继续处理，避免重复扫描
• 邮件发送逻辑单独做消费服务，从队列取任务调用第三方邮件接口，发送失败的任务自动进入重试队列，不影响主处理流程
• 因为SQL本身已经做了已发卡记录过滤，天然支持幂等，就算任务重复触发也不会给用户重复发礼品卡，符合每年发一次的业务规则。

内容的提问来源于stack exchange，提问作者Piyush Keshari