MySQL SELECT查询优化及邮件队列表性能优化方案求助

关于归档已发送邮件的方案可行性 #

你提出的新建表存储已发送邮件、发送完成后迁移记录并删除原表数据的方案完全可行，这是队列类表常用的归档策略，能有效控制主队列表的规模，避免因数据量过大导致的查询性能下降，是解决当前问题的核心手段之一。

具体优化思路 #

1. 优先优化查询性能（最快见效） #

• 给sent_at字段添加索引：
  当前查询SELECT * FROM mail_queue WHERE sent_at IS NULL是全表扫描，5.5万条数据会占用大量服务器资源。执行以下语句创建索引，让MySQL快速定位未发送的记录：

  CREATE INDEX idx_mail_queue_sent_at ON mail_queue(sent_at);
  

• 避免SELECT *，只查询必要字段：
  发送邮件通常只需要收件人、主题、内容等字段，不要查询所有列，减少数据传输和内存消耗，示例：

  SELECT id, recipient, subject, content, attachment_url FROM mail_queue WHERE sent_at IS NULL LIMIT 100;
  

• 限制单次查询的记录数：
  不要一次取出所有未发送邮件，分批次处理，比如每次取100条，避免单次查询和发送操作占用过多资源。

2. 落地归档策略，维护主表规模 #

• 原子化迁移记录：
  发送完成后，用事务保证数据迁移的原子性，避免数据丢失，示例：

  START TRANSACTION;
  -- 假设归档表名为mail_queue_archive，结构与原表一致
  INSERT INTO mail_queue_archive SELECT * FROM mail_queue WHERE id = ?;
  DELETE FROM mail_queue WHERE id = ?;
  COMMIT;
  

• 批量归档减少写操作：
  不要每条邮件发送完成都执行迁移，可定期（比如每天凌晨）批量迁移已发送超过1天的记录，降低频繁写操作的开销，示例：

  -- 批量插入归档表
  INSERT INTO mail_queue_archive SELECT * FROM mail_queue WHERE sent_at IS NOT NULL AND sent_at < DATE_SUB(NOW(), INTERVAL 1 DAY) LIMIT 1000;
  -- 批量删除原表数据
  DELETE FROM mail_queue WHERE sent_at IS NOT NULL AND sent_at < DATE_SUB(NOW(), INTERVAL 1 DAY) LIMIT 1000;
  

  注意：加LIMIT避免单次操作锁表时间过长，影响其他业务。

3. 优化CRON任务与发送逻辑 #

• 避免重复处理：
  新增processing_at字段（datetime类型，默认NULL），查询时只取未发送且未在处理中的记录，查询后立即标记为处理中，防止多个CRON进程（意外重复启动）同时处理同一条邮件：

  -- 先查询待处理记录
  SELECT id, recipient, subject, content FROM mail_queue WHERE sent_at IS NULL AND processing_at IS NULL LIMIT 100;
  -- 立即标记为处理中
  UPDATE mail_queue SET processing_at = NOW() WHERE id IN (/* 上面查询到的ID列表 */);
  

• 调整CRON执行频率：
  如果5分钟一次的频率导致压力过大，可以缩短间隔（比如2分钟一次）但减少单次取数（比如50条），均衡服务器负载。

4. 表结构与存储引擎优化 #

• 切换存储引擎为InnoDB：
  如果当前用的是MyISAM，改成InnoDB，InnoDB的行锁机制比MyISAM的表锁更适合频繁写操作的场景，减少锁等待时间。
• 清理无效数据：
  检查是否存在超过7天甚至更久的未发送邮件，确认无需发送后直接删除，进一步减小表的规模。

内容的提问来源于stack exchange，提问作者Kelison Bessa