KWDB 慢查询治理实录：抓出 3 条典型慢 SQL，优化前后执行时间对比

今天想和大家分享我曾遇到的一次比较典型的慢查询场景。当时我们团队负责的数据库，连接池被占满、Grafana 监控上查询延迟从 1 秒狂飙至 20 秒时，很多人的第一反应是去调大连接池上限，但我们并没有这么做。

这次事故是发生在一个城市轨道交通车辆监测平台上。诡异的是，故障发生时，应用日志里接口毫无报错，告警也没有集中爆发，但线路振动趋势和未处理车辆告警两个页面却出现了加载超时。我们通过抓取慢 SQL -> 分析执行计划 -> 同批数据复跑的路径直击现场，最终揪出了 3 条典型慢 SQL。

有意思的是，这 3 条 SQL 的问题并不相同：一条缺少时间边界，一条在告警表上做了全表扫描，最后一条在跨模连接时把大量明细数据过早带进了 Join。以下是具体的优化前后执行时间对比。

一、先把现场还原出来

平台管理 256 列列车，共 2,048 个车载监测点，按线路划分为 4 个运营组。每个监测点每分钟上报一次振动、轴承温度和门控状态，时序表保存 2026 年 3 月 1 日至 4 月 30 日的数据；车辆告警表保存同一时期产生的告警事件。

import statistics
import time

import psycopg2


SQL = """
SELECT sensor_id,
       date_trunc('hour', ts) AS hour_bucket,
       avg(vibration_mm_s) AS avg_vibration
FROM metro_telemetry
WHERE line_id = 'LINE-07'
  AND ts >= TIMESTAMP '2026-04-01 00:00:00'
  AND ts <  TIMESTAMP '2026-05-01 00:00:00'
GROUP BY sensor_id, date_trunc('hour', ts)
"""

conn = psycopg2.connect(
    host='127.0.0.1', port=26257,
    user='app_reader', password='***', dbname='metro_ops'
)

with conn.cursor() as cur:
    samples = []
    for _ in range(3):
        start = time.perf_counter()
        cur.execute(SQL)
        cur.fetchall()
        samples.append((time.perf_counter() - start) * 1000)

print('runs(ms):', [round(v, 1) for v in samples])
print('median(ms):', round(statistics.median(samples), 1))
conn.close()

二、第一条慢 SQL：时序聚合没有时间边界

1. 页面上的现象

“线路振动趋势”默认展示最近 30 天。接口代码却只传了线路编号，时间条件由前端拿到结果后再处理。LINE-07 有 512 个车载监测点，按理说不应该拖慢整张时序表，但查询计划显示扫描了该线路两个月内的全部明细。

原始 SQL 如下：

SELECT
    sensor_id,
    date_trunc('hour', ts) AS hour_bucket,
    avg(vibration_mm_s) AS avg_vibration,
    max(vibration_mm_s) AS max_vibration
FROM metro_telemetry
WHERE line_id = 'LINE-07'
GROUP BY sensor_id, date_trunc('hour', ts);


EXPLAIN (OPT, VERBOSE) 中能看到 ts scan 和 tag filter，但没有落在 ts 上的有效范围。扫描行数约 4,490 万，聚合节点需要在内存中保留两个月的分组状态。

2. 改写方式

时间条件直接下推到原始时间列，使用左闭右开的区间，避免把 5 月 1 日的零点重复算进来：

SELECT
    sensor_id,
    date_trunc('hour', ts) AS hour_bucket,
    avg(vibration_mm_s) AS avg_vibration,
    max(vibration_mm_s) AS max_vibration
FROM metro_telemetry
WHERE line_id = 'LINE-07'
  AND ts >= TIMESTAMP '2026-04-01 00:00:00'
  AND ts <  TIMESTAMP '2026-05-01 00:00:00'
GROUP BY sensor_id, date_trunc('hour', ts);


这次计划中的扫描范围缩到约 2,210 万行，聚合输入减少了一半。没有给时间列额外创建普通标签索引，原因是时序索引适用于标签列，时间范围应交给时序扫描路径处理。

3. 前后结果

三、第二条慢 SQL：车辆告警列表被全表扫描拖住

1. 先看查询计划

告警页面只需要最新的 100 条未处理车辆告警，SQL 里已经有状态、时间和排序条件：

SELECT
    alarm_id,
    sensor_id,
    event_time,
    alarm_code
FROM rail_alarms
WHERE status = 'OPEN'
  AND event_time >= TIMESTAMP '2026-04-01 00:00:00'
  AND event_time <  TIMESTAMP '2026-05-01 00:00:00'
ORDER BY event_time DESC
LIMIT 100;


但是 EXPLAIN 显示的是：

scan
  table: rail_alarms@primary
  spans: FULL SCAN
sort
limit: 100


表里约有 280 万条记录，OPEN 状态只占很小一部分。数据库先读完主索引，再过滤状态和时间，最后排序取前 100 条。这个执行方式和页面需求正好相反。

2. 用过滤列和排序列组织索引

CREATE INDEX idx_alarm_status_time
ON rail_alarms (status, event_time DESC)
STORING (sensor_id, alarm_code);


status 放在前面，是为了缩小候选范围；event_time DESC 与页面的排序方向一致；STORING 让结果需要的两列直接从索引中取出，避免再回表读取。


索引创建后先检查结构：

SHOW INDEX FROM rail_alarms;


再次执行 EXPLAIN，计划变成有限范围扫描，排序节点也不再处理 280 万行：

scan
  table: rail_alarms@idx_alarm_status_time
  spans: [/OPEN - 2026-05-01)
limit: 100

3. 前后结果

四、第三条慢 SQL：跨模连接把明细数据带得太早

这条 SQL 同时访问关系表 vehicle_registry 和时序表 metro_telemetry，用于计算某条线路每个车载监测点的小时平均振动值。

1. 原始写法

SELECT
    d.line_id,
    m.sensor_id,
    date_trunc('hour', m.ts) AS hour_bucket,
    avg(m.vibration_mm_s) AS avg_vibration
FROM vehicle_registry d
JOIN metro_telemetry m
  ON m.sensor_id = d.sensor_id
WHERE d.line_id = 'LINE-07'
  AND date(m.ts) >= DATE '2026-04-01'
  AND date(m.ts) <  DATE '2026-05-01'
GROUP BY
    d.line_id,
    m.sensor_id,
    date_trunc('hour', m.ts);
    

问题有两个。第一，date(m.ts) 对时间列做了函数转换，时间范围不容易直接下推到时序扫描。第二，监测点过滤和小时聚合都发生在连接之后，BatchLookupJoin 需要处理大量明细行。

2. 先筛监测点，再限制时间，再聚合

WITH target_sensors AS (
    SELECT sensor_id
    FROM vehicle_registry
    WHERE line_id = 'LINE-07'
),
hourly_metrics AS (
    SELECT
        m.sensor_id,
        date_trunc('hour', m.ts) AS hour_bucket,
        avg(m.vibration_mm_s) AS avg_vibration
    FROM metro_telemetry m
    JOIN target_sensors d
      ON d.sensor_id = m.sensor_id
    WHERE m.ts >= TIMESTAMP '2026-04-01 00:00:00'
      AND m.ts <  TIMESTAMP '2026-05-01 00:00:00'
    GROUP BY m.sensor_id, date_trunc('hour', m.ts)
)
SELECT
    d.line_id,
    h.sensor_id,
    h.hour_bucket,
    h.avg_vibration
FROM hourly_metrics h
JOIN vehicle_registry d
  ON d.sensor_id = h.sensor_id;
  

EXPLAIN (OPT, VERBOSE) 中，优化前的连接输入约 4,490 万行；改写后先根据线路得到 512 个监测点，再把时间条件和监测点集合传给时序扫描，聚合输入约 310 万行。实际计划是否出现 BatchLookupJoin 或 HashTagScan，还要以数据分布和统计信息为准，不能只看 SQL 的表面写法。

3. 前后结果

五、把执行计划变成日常排查流程

三条 SQL 修完后，应用接口的 P95 从 18.4 秒降到 2.1 秒。单条语句的结果如下：

1. 先从 Grafana 慢查询面板确认 SQL 指纹和出现时间，再用 SHOW QUERIES 查看当前仍在执行的语句。
2. 用 EXPLAIN (OPT, VERBOSE) 看扫描节点、过滤条件、连接方式和估算统计，不把 cost 当成毫秒数。
3. 对需要深入定位的语句执行 EXPLAIN ANALYZE (DEBUG)，下载 Statement Diagnostics 文件，核对实际执行路径。
4. 改写 SQL 或调整索引后，用同一批参数重复执行，记录扫描行数、返回行数和中位耗时。
5. 数据批量导入或分布明显变化后，还要刷新并检查统计信息：
6. 
   

SHOW STATISTICS FOR TABLE rail_alarms;


如果优化器长期拿不到正确的数据分布，索引已经存在，计划仍可能选错。统计信息检查应和索引检查放在同一套发布流程里。

六、这次复盘留下的三条规则

第一，所有面向时序表的报表查询都必须带原始时间列的半开区间，时间范围由服务端生成，不交给前端补过滤。

第二，关系表查询先看 WHERE 和 ORDER BY 的组合，再决定复合索引列顺序。索引创建后用 SHOW INDEX 和 EXPLAIN 双重确认，不能只看建索引语句是否执行成功。

第三，跨模查询先缩小监测点集合和时间范围，再做连接与聚合。能在时序侧完成的聚合，不要把明细行全部搬到连接节点。

这次故障没有靠增加机器解决。三条 SQL 的共同问题，是数据在不该扩大的地方扩大了：时间范围扩大、候选告警扩大、跨模连接输入扩大。把执行计划摊开看，优化动作就从猜参数变成了可以验证的改动。