MySQL指定日期范围多表计数查询实现与性能优化

• writers_info：作者基础资料表，存储姓名、地址等作者档案数据
• writer_contacts：作者联系人表，存储作者的联系人信息，包含datetime类型的记录新增时间字段date_added
• writer_notes：作者笔记表，存储作者自行记录的笔记内容，包含datetime类型的记录新增时间字段note_date
• writer_reminders：作者提醒表，存储作者的任务提醒信息及对应提醒时间，包含datetime类型的时间字段reminder_date

统计指定日期范围内，产生过联系人、笔记或提醒任意一类数据的作者（这类作者约占总作者数的10%，无需返回无任何操作的作者），返回字段如下：

1. user_id：作者ID
2. author_name：作者姓名
3. #of Contacts：指定时间范围内的联系人新增数量
4. #of Notes：指定时间范围内的笔记新增数量
5. #of Reminders：指定时间范围内的提醒新增数量

最初编写的查询虽然已为所有相关字段创建索引，但执行时会出现服务器超时甚至崩溃的问题，初始语句如下：

SELECT DISTINCT w.user_id, CONCAT(w.author_fname,' ',w.author_lname) AS author_name, 
  (SELECT COUNT(*) FROM writer_contacts WHERE writer_id = w.user_id AND date_added BETWEEN '2022-06-01 00:00:00' AND '2022-06-22 23:59:59' ) AS contacts, 
  (SELECT COUNT(*) FROM writer_notes WHERE writer_id = w.user_id AND note_date BETWEEN '2022-06-01 00:00:00' AND '2022-06-22 23:59:59' ) AS notes, 
  (SELECT COUNT(*) FROM writer_reminders WHERE writer_id = w.user_id AND reminder_date BETWEEN '2022-06-01 00:00:00' AND '2022-06-22 23:59:59' ) AS reminders 
FROM writers_info w, writer_contacts c, writer_notes n, writer_reminders r
WHERE (w.user_id = c.writer_id AND c.date_added BETWEEN '2022-06-01 00:00:00' AND '2022-06-22 23:59:59')
OR (w.user_id = n.writer_id AND n.note_date BETWEEN '2022-06-01 00:00:00' AND '2022-06-22 23:59:59')
OR (w.user_id = r.writer_id AND r.reminder_date BETWEEN '2022-06-01 00:00:00' AND '2022-06-22 23:59:59')
ORDER BY w.user_id

故障核心原因是使用逗号分隔多表的隐式连接写法，默认会先生成四张表的笛卡尔积中间结果；叠加OR连接的过滤条件无法让所有表的关联条件持续生效，例如满足联系人表过滤条件时，笔记、提醒表会和结果集做全量交叉连接，最终中间结果的数据量会膨胀到表行数的乘积级别，直接耗尽服务器内存、CPU资源。

排查出问题后，改用LEFT JOIN替代隐式交叉连接，调整WHERE子句的逻辑顺序，即可得到可稳定运行的查询，语句如下：

SELECT w.user_id, CONCAT(w.author_fname,' ',w.author_lname) AS author_name,
 (SELECT COUNT(*) FROM writer_contacts WHERE w.user_id = writer_id AND date_added BETWEEN '2022-05-01' AND '2022-06-21' ) AS contacts, 
 (SELECT COUNT(*) FROM writer_notes WHERE w.user_id = writer_id AND note_date BETWEEN '2022-05-01 00:00:00' AND '2022-06-21 23:59:59' ) AS notes, 
 (SELECT COUNT(*) FROM writer_reminders WHERE writer_id = w.user_id AND reminder_date BETWEEN '2022-05-01' AND '2022-06-21' ) AS reminders 
FROM writers_info w
LEFT JOIN writer_contacts c ON w.user_id = c.writer_id
LEFT JOIN writer_notes n ON w.user_id = n.writer_id
LEFT JOIN writer_reminders r ON w.user_id = r.writer_id
WHERE (c.date_added BETWEEN '2022-05-01' AND '2022-06-21' OR n.note_date BETWEEN '2022-05-01' AND '2022-06-21' OR r.reminder_date BETWEEN '2022-05-01' AND '2022-06-21')
GROUP BY w.user_id

性能优化提示：如果数据量进一步增长，可以先分别聚合三张业务表在指定时间范围内的作者ID与对应计数，再和作者基础表做关联，避免多表LEFT JOIN产生重复行，进一步提升时间字段索引的过滤效率。

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