MySQL跨两张表统计每日回访访客数量的技术问题

问题背景 #

需要统计网站每日回访访客的数量，但相关数据分散在两张MySQL表中，现有查询语句返回结果与实际严重不符。

MySQL表结构 #

uniqueVisitors表（记录新访客基础信息） #

-- uniqueVisitors - 新访客首次访问时被写入本表
+--------------------+-------------+------+-----+---------+----------------+
| Field              | Type        | Null | Key | Default | Extra          |
+--------------------+-------------+------+-----+---------+----------------+
| uniqueVisitorID    | int         | NO   | PRI | NULL    | auto_increment |
| uuID               | varchar(36) | NO   |     | NULL    |                |
| websitePrivateHash | varchar(16) | NO   | MUL | NULL    |                |
| dateFirstSeen      | varchar(10) | NO   |     | NULL    |                |
| dateLastSeen       | varchar(10) | NO   |     | NULL    |                |
+--------------------+-------------+------+-----+---------+----------------+

visitorPageViews表（记录访客每日页面浏览行为） #

-- visitorPageViews - 跟踪访客每日的页面浏览记录
+--------------------+-------------+------+-----+---------+----------------+
| Field              | Type        | Null | Key | Default | Extra          |
+--------------------+-------------+------+-----+---------+----------------+
| visitorPageViewID  | int         | NO   | PRI | NULL    | auto_increment |
| websitePrivateHash | varchar(16) | NO   | MUL | NULL    |                |
| uuID               | varchar(36) | NO   |     | NULL    |                |
| date               | varchar(10) | NO   |     | NULL    |                |
| pageViews          | int         | NO   |     | 0       |                |
+--------------------+-------------+------+-----+---------+----------------+

需求说明 #

统计指定日期（如2023-12-28）有页面浏览记录，但首次访问日期不是该日期的访客数量（即回访访客）。

原查询的问题 #

原SQL语句返回数百万结果，与实际访客数不符：

SELECT COUNT(visitorPageViews.uuID) AS count 
FROM uniqueVisitors, visitorPageViews 
WHERE uniqueVisitors.websitePrivateHash = visitorPageViews.websitePrivateHash 
AND visitorPageViews.websitePrivateHash = 'r2d2****c3po****' 
AND visitorPageViews.date = '2023-12-28' 
AND uniqueVisitors.dateFirstSeen != '2023-12-28';

核心问题：

1. 遗漏了uniqueVisitors.uuID = visitorPageViews.uuID的关联条件，导致跨访客的错误关联；
2. 未对访客ID去重，同一个访客的多条页面浏览记录会被重复计数。

解决方案 #

方案1：修正关联逻辑+去重统计 #

SELECT COUNT(DISTINCT visitorPageViews.uuID) AS returnVisitorCount
FROM uniqueVisitors
JOIN visitorPageViews 
  ON uniqueVisitors.websitePrivateHash = visitorPageViews.websitePrivateHash
  AND uniqueVisitors.uuID = visitorPageViews.uuID
WHERE visitorPageViews.websitePrivateHash = 'r2d2****c3po****'
  AND visitorPageViews.date = '2023-12-28'
  AND uniqueVisitors.dateFirstSeen != '2023-12-28';

关键优化点：

• 用显式JOIN替代隐式连接，逻辑更清晰；
• 补充uuID的关联条件，确保只关联同一访客的数据；
• 使用COUNT(DISTINCT uuID)确保每个访客仅被统计一次。

方案2：先筛选再关联（性能更优） #

针对数据量较大的场景，先缩小当天访客范围再关联查询：

SELECT COUNT(DISTINCT pv.uuID) AS returnVisitorCount
FROM (
    SELECT uuID, websitePrivateHash
    FROM visitorPageViews
    WHERE websitePrivateHash = 'r2d2****c3po****'
      AND date = '2023-12-28'
) pv
JOIN uniqueVisitors uv
  ON pv.websitePrivateHash = uv.websitePrivateHash
  AND pv.uuID = uv.uuID
WHERE uv.dateFirstSeen != '2023-12-28';

优势：子查询先过滤出当天的访客数据，减少后续关联的数据量，提升查询效率。

可选数据结构优化建议 #

如果后续查询频繁，可考虑调整结构提升性能：

• 将dateFirstSeen、dateLastSeen、date字段改为DATE类型，避免字符串比较的性能损耗；
• 在visitorPageViews表建立复合索引(websitePrivateHash, date, uuID)，加速当天访客的筛选；
• 在uniqueVisitors表建立复合索引(websitePrivateHash, uuID, dateFirstSeen)，加速关联查询。

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