如何优化含JOIN、COUNT、WHERE的MySQL查询：识别特定页面新用户

首先得指出你原查询里的几个关键问题，不然运行会报错或者得到错误结果：

• 语法错误：=! 不是有效的SQL运算符，这会直接导致查询失败
• 逻辑错误：visitorId BETWEEN '2017-05-10' AND '2018-04-27' 完全不对——visitorId是用户标识，不是日期字段，你要判断的是这个用户是首次在过去两周访问站点，也就是在2018-04-26之前没有任何会话记录
• 计数问题：直接COUNT(visitorId)会重复统计同一用户的多次访问，应该用COUNT(DISTINCT visitorId)来统计独立新用户数
• 排序字段错误：ORDER BY sessionsDate 应该是sessions.sessionDate，字段名写错了

接下来给你两种更高效的写法，核心思路是先过滤再连接（因为两张表有12个月的数据，先缩小时间范围能大幅减少连接的数据量），同时准确识别新用户：

写法一：用NOT EXISTS判断新用户（推荐，性能更优） #

SELECT 
  pv.pageType,
  s.sessionDate,
  s.deviceType,
  COUNT(DISTINCT s.visitorId) AS new_user_count
FROM sessions s
INNER JOIN pageviews pv 
  ON s.sessionId = pv.sessionId
WHERE 
  -- 筛选目标页面
  pv.pageType = 'Page1'
  -- 限定过去两周的会话时间
  AND s.sessionDate BETWEEN '2018-04-26' AND '2018-05-08'
  -- 核心：判断该用户在这段时间之前没有任何会话记录（即新用户）
  AND NOT EXISTS (
    SELECT 1 
    FROM sessions s_past
    WHERE s_past.visitorId = s.visitorId
      AND s_past.sessionDate < '2018-04-26'
  )
-- 按分组字段聚合，确保统计逻辑正确
GROUP BY pv.pageType, s.sessionDate, s.deviceType
ORDER BY s.sessionDate;

为什么这个写法更高效？ #

• NOT EXISTS是数据库优化器很擅长处理的逻辑，它会做快速的存在性检查，比关联子查询性能更好
• 先通过s.sessionDate过滤出过去两周的会话，再和pageviews连接，避免了全表扫描12个月的数据
• COUNT(DISTINCT visitorId)保证每个新用户只被统计一次，不会因为同一用户多次访问Page1而重复计数

写法二：如果有首次会话日期字段（简化版） #

如果你的sessions表中已经存储了每个用户的首次会话日期（比如first_session_date字段），可以直接用这个字段判断新用户，写法更简洁：

SELECT 
  pv.pageType,
  s.sessionDate,
  s.deviceType,
  COUNT(DISTINCT s.visitorId) AS new_user_count
FROM sessions s
INNER JOIN pageviews pv 
  ON s.sessionId = pv.sessionId
WHERE 
  pv.pageType = 'Page1'
  AND s.sessionDate BETWEEN '2018-04-26' AND '2018-05-08'
  -- 首次会话时间在过去两周内，即为新用户
  AND s.first_sessionDate BETWEEN '2018-04-26' AND '2018-05-08'
GROUP BY pv.pageType, s.sessionDate, s.deviceType
ORDER BY s.sessionDate;

额外性能优化建议 #

为了让这个查询在12个月的大表上跑得更快，建议给以下字段创建复合索引：

• sessions(sessionDate, visitorId)：加速时间范围过滤和用户匹配
• sessions(visitorId, sessionDate)：加速NOT EXISTS子查询中的用户历史会话检查
• pageviews(sessionId, pageType)：加速会话ID的连接和页面类型的筛选

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