8.7 秒到 231 毫秒：保留统计口径的 MySQL 窗口函数优化

发表于 分类于

有索引的统计 SQL 也可能很慢。这次优化把两次全历史最新状态判断合并为一次窗口计算，生产只读对比快约 37.6 倍；真正难的是保留重复键、空时间和跨天在场的原有口径。

🎧 文章导读

先限定这次“快了 37.6 倍”的范围

人流量统计页面等待约 8 秒，浏览器内容下载却不到 1 毫秒。这个信号说明主要时间在服务端响应之前，但不能据此直接认定数据库就是唯一原因。

随后单独测量 SQL，发现两条全历史状态查询确实占了主要时间。在同一生产数据只读快照、固定统计时刻下，对比结果如下：

查询 耗时
旧在场及滞留统计 4605.58 ms
旧累计离场统计 4089.68 ms
旧两条合计 8695.26 ms
新合并统计 231.31 ms

约 37.6 倍是这组统计 SQL 的耗时对比，不是完整接口已从 8.7 秒降到 231 毫秒。原接口共有 5 条查询，改造后为 4 条；服务发布后仍要验证完整响应。

重复反查与一次状态计算
图 1：优化的核心是减少重复计算，不是给已有索引再起一个新名字。

索引已经存在，慢在哪里

现场 MySQL 为 8.0.27，流水表按通行时间做 RANGE 分区，共 97 个分区，元数据估算约 395 万行。已有人员、有效标记与时间相关索引，因此“缺人员索引”不是正确结论。

旧查询对每条候选流水做一次 NOT EXISTS 判断：同一人是否还有更晚的有效记录？如果没有，就把这条记录视为当前状态。在场统计做一遍，离场统计又做一遍。

下面是去掉业务过滤条件后的逻辑骨架。实际等价改写必须保证内外层使用原有的设备、有效状态及删除条件：

1
2
3
4
5
6
7
8
9
10
11
SELECT r.*
FROM access_record r
WHERE NOT EXISTS (
    SELECT 1
    FROM access_record newer
    WHERE newer.person_id = r.person_id
      AND (
          newer.event_time > r.event_time
          OR (newer.event_time = r.event_time AND newer.id > r.id)
      )
);

执行计划显示，最慢的两条查询涉及全部分区，反关联虽使用了索引，仍需反复定位和过滤。表已经分区不代表查询就能裁剪到当天；而业务本来要统计跨天仍在场的人，也不能为了裁剪，直接把候选数据限制成当天。

这是优化的第一条边界：查询慢可以改实现，统计口径不能顺手改掉。

一次排名，复用同一批最新状态

新实现对有效候选记录按人员分组，按时间、ID 倒序排名，再从最新记录同时聚合在场、离场和滞留分桶。

下面的 SQL 使用通用表名和方向字段展示算法，省略业务设备集合；生产实现的进出方向来自设备映射，而非这个示例字段：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH ranked AS (
    SELECT person_id, id, event_time, direction,
           DENSE_RANK() OVER (
               PARTITION BY person_id
               ORDER BY event_time DESC, id DESC
           ) AS latest_rank
    FROM access_record
    WHERE person_id IS NOT NULL
      AND valid_flag = '1'
      AND recognition_status = '0'
      AND delete_flag = '0'
), latest AS (
    SELECT direction,
           TIMESTAMPDIFF(HOUR, event_time, @as_of) AS stay_hours
    FROM ranked
    WHERE latest_rank = 1 OR event_time IS NULL
)
SELECT COALESCE(SUM(direction = 'IN'), 0) AS present,
       COALESCE(SUM(direction = 'OUT'), 0) AS exited,
       COALESCE(SUM(direction = 'IN' AND stay_hours < 12), 0) AS stay_lt_12,
       COALESCE(SUM(direction = 'IN' AND stay_hours >= 12
                    AND stay_hours < 24), 0) AS stay_12_to_24,
       COALESCE(SUM(direction = 'IN' AND stay_hours >= 24), 0) AS stay_ge_24,
       COALESCE(SUM(direction = 'IN' AND stay_hours IS NULL), 0) AS unknown_time
FROM latest;

@as_of 要在对比前固定，例如 SET @as_of = '2026-10-22 17:00:00'。真实接口可使用当前时刻；新旧比较时不应让时间边界随两次查询漂移。

Service 从这次结果读取新增的内部离场聚合字段，去掉独立的离场查询调用。外部响应字段保持不变，本次也没有新增索引或表结构迁移。

最难的两处语义：并列记录与 NULL

为什么不用最常见的 ROW_NUMBER()？因为原表的 id 在整个分区表范围内不是单列唯一约束。原查询里，如果两条记录的时间和 ID 都相同，它们互相都不属于“更晚记录”，因此可能同时保留。

ROW_NUMBER() 会对并列行分别编号；DENSE_RANK() 则为同排序值的行赋予同一排名。这个差异见 MySQL 8.0 窗口函数说明。在此次兼容优化中，保留并列行才符合原口径。

空时间也不能丢。旧反关联中的时间比较遇到 NULL，不会得到 TRUE，因而这些记录可能全部通过 NOT EXISTS。只保留 latest_rank = 1 会漏掉某些空时间记录，所以新实现还显式保留 event_time IS NULL。

必须保留的统计语义
图 2：并列、空时间与跨天状态决定了优化是否等价，不能仅比较普通样本。

场景 必须保持的行为
同人、不同时间 按最新时间决定状态
同人、同时间、不同 ID 用 ID 决定先后
时间与 ID 都相同 保留原有并列记录
空时间 保留原 NULL 语义，进入未知时间分桶
昨天进场、今天没有流水 仍可能计入当前在场
恰好 12 / 24 小时 分别进入 [12,24)、[24,+∞)

因此，“在场人数”这个产品名称，在历史异常数据下可能仍体现记录计数语义。若要强制“一人一条”或治理空时间，应作为另一项业务调整，不能藏在性能优化里。

用同一快照证明结果，再谈性能

验证顺序是先构造边界样本，再比较真实数据。本地相关用例覆盖空集、方向变化、跨天、时间边界、重复键、空时间、无效及删除记录，同时约束 Service 不再调用独立离场查询。

真实数据对比采用固定时刻和同一只读快照。下面只展示事务边界，需将完整旧、新查询放到相应位置；运行前应先评估查询负载与限时：

1
2
3
4
5
SET @as_of = '2026-10-22 17:00:00';
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
-- 执行旧统计，并保留各分桶及耗时。
-- 执行新统计，逐字段比较，而非仅比较总人数。
COMMIT;

当时生产样本的在场 296、离场 510，以及各滞留分桶新旧完全一致，脚本输出 all_counts_match = 1。本地另有 5 万条模拟流水对比，结果同样一致；模拟性能不能替代生产性能。

按原笔记，11 个相关测试通过，代码已推送并打包，但当时尚未发布服务。本篇整理没有重新运行生产查询，也没有把这组数字写成所有数据规模下的保证。

窗口函数不是天然比反关联快。它同样有排序和中间结果成本，数据分布改变后仍需看执行计划。这次有效，是因为它把两次重复的全历史最新状态判断合并成一次，并且用边界测试与同快照对比守住了原有统计语义。

延伸阅读

本文根据 2026 年 9 月 22 日至 10 月 22 日的工程笔记整理。账号、凭证、地址和业务样本已脱敏；通用示例已在正文标明，历史验证结果以原记录为准。