访问统计表设计与查询

「查询用户当天访问最多次数 / 某个时间段的次数」是高频场景题。核心思路：明细表只存事实，结果放汇总表。

表设计（MySQL / InnoDB）

-- 用户表
CREATE TABLE `user` (
	`id`            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	`username`      VARCHAR(64)  NOT NULL,
	`password_hash` VARCHAR(100) NOT NULL COMMENT 'BCrypt 哈希',
	`nickname`      VARCHAR(64)  DEFAULT NULL,
	`status`        TINYINT      NOT NULL DEFAULT 1 COMMENT '1正常 0禁用',
	`created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 访问明细表：一条访问一行
CREATE TABLE `user_visit_log` (
	`id`         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	`user_id`    BIGINT UNSIGNED NOT NULL,
	`visit_time` DATETIME        NOT NULL,
	`ip`         VARCHAR(45)     DEFAULT NULL,
	`uri`        VARCHAR(255)    DEFAULT NULL,
	KEY `idx_user_time` (`user_id`, `visit_time`),   -- 核心：等值列在前，范围列在后
	KEY `idx_time` (`visit_time`)                    -- 支撑按时间段的全局统计
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户访问明细表';

-- 按天汇总表：定时任务从明细聚合，查询不再扫明细
CREATE TABLE `user_visit_stat_daily` (
	`id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	`user_id`     BIGINT UNSIGNED NOT NULL,
	`stat_date`   DATE            NOT NULL,
	`visit_count` INT UNSIGNED    NOT NULL DEFAULT 0,
	UNIQUE KEY `uk_user_date` (`user_id`, `stat_date`),
	KEY `idx_date_cnt` (`stat_date`, `visit_count`)  -- 支撑「当天 TOP N」
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户访问按天汇总表';

统计查询 SQL

-- ① 某用户【当天】访问次数（明细表 COUNT，命中 idx_user_time）
SELECT COUNT(*) AS cnt
FROM user_visit_log
WHERE user_id = 1001
	AND visit_time >= CURDATE()
	AND visit_time <  CURDATE() + INTERVAL 1 DAY;

-- ② 某用户【某个时间段】访问次数（例如 10 月整月）
SELECT COUNT(*) AS cnt
FROM user_visit_log
WHERE user_id = 1001
	AND visit_time >= '2026-10-01 00:00:00'
	AND visit_time <  '2026-11-01 00:00:00';

-- ③ 【当天】访问次数最多的用户 TOP 10（全局统计，命中 idx_time）
SELECT user_id, COUNT(*) AS cnt
FROM user_visit_log
WHERE visit_time >= CURDATE()
	AND visit_time <  CURDATE() + INTERVAL 1 DAY
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10;

-- ④ 数据量大后：改读按天汇总表，近似 O(1)
SELECT visit_count
FROM user_visit_stat_daily
WHERE user_id = 1001 AND stat_date = '2026-10-27';

-- ⑤ 月度 TOP：汇总表再 SUM，避免扫海量明细
SELECT user_id, SUM(visit_count) AS total
FROM user_visit_stat_daily
WHERE stat_date >= '2026-10-01' AND stat_date <= '2026-10-31'
GROUP BY user_id
ORDER BY total DESC
LIMIT 10;

设计要点

• 联合索引 (user_id, visit_time)：遵循最左前缀原则，user_id 等值 + visit_time 范围，一条索引同时服务「单用户按时间统计」。
• 范围条件用 >= + <：边界清晰、可预测。左闭右开写法永远不担心毫秒/边界重复计数，比 BETWEEN 的闭区间语义更安全。
• 自增主键：聚簇索引顺序写入，避免页分裂。

:::tip 查询慢先看什么？ EXPLAIN：确认是否走 idx_user_time（type = range，key 不为 NULL），rows 扫描量是否合理。COUNT(*) 与 COUNT(1) 在 InnoDB 下等价且都会挑最小的索引来数；COUNT(列) 会过滤 NULL，语义不同别混用。 :::

面试常问

• 明细表和汇总表怎么保持同步？ 定时任务增量聚合（visit_time >= 上次聚合位点），或实时计数先走 Redis INCR、再定时刷入汇总表；崩溃最多丢一个周期，业务可接受。
• 为什么不让明细表直接承担统计？ COUNT + GROUP BY 的代价随数据量线性增长；汇总表把「计算」提前到写入侧分摊，查询侧只做 O(1) 读取——这就是预聚合思想。