并发计数请求优化：特定时间大楼内人数统计查询性能优化

这个问题我之前做企业门禁系统的人数统计模块时踩过一模一样的坑——当时间点数组和原始记录量都上来后，全量关联的笛卡尔积直接把数据库拖垮，分享几个亲测有效的优化思路：

1. 把进出事件预处理成「人员在楼时间段」，避免全量关联 #

原来的逻辑大概率是把每个统计时间点和所有签到签退记录做关联，这会产生恐怖的笛卡尔积（比如1000个时间点×10万条记录=1亿条临时数据）。换个思路：

• 用窗口函数（比如SQL里的LEAD()）给每个人的每一次checkin配对对应的checkout时间，生成一张(person_id, enter_time, exit_time)的时间段表
• 统计某个时间点t的在楼人数，就变成统计有多少个时间段满足enter_time ≤ t且(exit_time IS NULL OR exit_time > t)
• 预处理可以离线定时跑（比如每小时更新一次时间段表），或者用物化视图实时计算，离线预处理的性能提升最明显

举个SQL预处理的例子：

WITH person_stays AS (
    SELECT 
        person_id,
        check_time AS enter_time,
        -- 取同一个人下一条记录的时间作为签退时间（如果是签退的话）
        LEAD(check_time) OVER(PARTITION BY person_id ORDER BY check_time) AS exit_time
    FROM access_log
    WHERE action_type = 'checkin' -- 只处理签到事件，签退对应下一条记录
)
-- 统计给定时间点的在楼人数
SELECT 
    tp.time_point,
    COUNT(ps.person_id) AS in_building_count
FROM time_points tp
LEFT JOIN person_stays ps
    ON tp.time_point >= ps.enter_time 
    AND (ps.exit_time IS NULL OR tp.time_point < ps.exit_time)
GROUP BY tp.time_point;

2. 给核心字段加针对性的复合索引 #

如果必须实时计算，一定要给签到签退表加合适的索引，避免全表扫描：

• 如果你是按人员维度预处理，创建(person_id, check_time, action_type)的复合索引，能快速定位每个人的进出事件顺序
• 如果是按时间范围查询，创建(check_time, action_type, person_id)的索引，过滤时间相关记录时能直接缩小扫描范围
• 注意：别乱加索引，要根据你的实际查询语句的过滤、排序条件来创建，冗余索引反而会拖慢写入性能

3. 用「事件流累加」替代逐条关联（适合连续时间点统计） #

如果你的统计需求是固定周期的时间点（比如每小时整点），可以把签到/签退转成+1/-1的事件，然后用窗口函数累加：

1. 把所有签到事件标记为change = 1，签退事件标记为change = -1，按时间排序
2. 把需要统计的时间点也插入到这个事件流中（标记为change = 0，表示统计点）
3. 用SUM(change) OVER(ORDER BY event_time)的窗口函数，按时间顺序累加人数，遇到统计点时记录当前的累加值

• 这种方式只需要遍历一次事件数据，时间复杂度是O(n log n)（主要是排序开销），比全量关联的O(n*m)高效太多

4. 批量计算+缓存结果，把计算压力前置 #

如果统计的时间点是固定周期（比如每小时、每天整点），完全可以把计算提前：

• 定时（比如每小时结束后5分钟）计算该小时整点的在楼人数，把结果存在单独的building_population_stats表中
• 用户查询历史时间点时，直接从统计结果表读数据；查询最新未统计的时间点时，再实时计算一小部分数据
• 对于“当前在楼人数”这种高频查询，可以单独维护一张current_in_building表，每次有人签到/签退时实时更新计数，查询时直接读这个表的数值即可

5. 缩小数据扫描范围，只查必要的数据 #

• 不要每次查询都扫描全表，比如统计昨天的每小时人数，一定要加check_time BETWEEN '昨天0点' AND '今天0点'的过滤条件
• 对于还没签退的人员（exit_time IS NULL），可以单独维护一个临时表，统计当前时间点时直接查这个表的计数，不用再扫描所有历史记录

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