优化MySQL周环比趋势查询性能及扩展至全历史周数据

问题背景 #

我是SQL新手，想要获取周环比趋势以对比各项指标数据。当前查询需对全表扫描4次，仅支持最近4周，运行效率低下。使用的是MySQL数据库，需要优化该查询并获取所有历史周的指标数据。

样本数据 #

原查询问题分析 #

原查询通过4次独立SELECT+UNION实现，每次都触发全表扫描，效率极低；且硬编码时间范围，仅能获取最近4周数据，无法覆盖全历史周期。

原查询代码：

SELECT
    sum(metric_hits) as metric_hits_sum,
    sum(metric_total) as metrics_total_sum,
    avg(metric_value) as metric_value_avg
from metric_events
where timestamp >= DATEADD(DAY, -7-DATEPART(WEEKDAY, GETDATE()::date), GETDATE()::date) and timestamp < DATEADD(DAY, -DATEPART(WEEKDAY, GETDATE()::date), GETDATE()::date)
UNION
SELECT
    sum(metric_hits) as metric_hits_sum,
    sum(metric_total) as metrics_total_sum,
    avg(metric_value) as metric_value_avg
from metric_events
where timestamp >= DATEADD(DAY, -14-DATEPART(WEEKDAY, GETDATE()::date), GETDATE()::date) and timestamp < DATEADD(DAY, -7-DATEPART(WEEKDAY, GETDATE()::date), GETDATE()::date)
UNION
SELECT
    sum(metric_hits) as metric_hits_sum,
    sum(metric_total) as metrics_total_sum,
    avg(metric_value) as metric_value_avg
from metric_events
where timestamp >= DATEADD(DAY, -21-DATEPART(WEEKDAY, GETDATE()::date), GETDATE()::date) and timestamp < DATEADD(DAY, -14-DATEPART(WEEKDAY, GETDATE()::date), GETDATE()::date)
UNION
SELECT
    sum(metric_hits) as metric_hits_sum,
    sum(metric_total) as metrics_total_sum,
    avg(metric_value) as metric_value_avg
from metric_events
where timestamp >= DATEADD(DAY, -28-DATEPART(WEEKDAY, GETDATE()::date), GETDATE()::date) and timestamp < DATEADD(DAY, -21-DATEPART(WEEKDAY, GETDATE()::date), GETDATE()::date)

优化方案 #

1. 全历史周统计查询 #

通过按周分组聚合，仅需一次扫描即可获取所有历史周的指标统计，同时明确标注周起始日期（以周一为周起始，可根据需求调整）：

SELECT
    -- 将每条数据映射到对应周的周一，格式化为日期
    DATE_FORMAT(DATE_ADD(timestamp, INTERVAL -(WEEKDAY(timestamp)) DAY), '%Y-%m-%d') AS week_start,
    SUM(metric_hits) AS metric_hits_sum,
    SUM(metric_total) AS metric_total_sum,
    AVG(metric_value) AS metric_value_avg
FROM metric_events
GROUP BY week_start
ORDER BY week_start DESC;

2. 周环比计算 #

利用MySQL窗口函数LAG()，在上一步统计结果基础上直接计算周环比（当前周相对上周的变化率）：

WITH weekly_stats AS (
    SELECT
        DATE_FORMAT(DATE_ADD(timestamp, INTERVAL -(WEEKDAY(timestamp)) DAY), '%Y-%m-%d') AS week_start,
        SUM(metric_hits) AS metric_hits_sum,
        SUM(metric_total) AS metric_total_sum,
        AVG(metric_value) AS metric_value_avg
    FROM metric_events
    GROUP BY week_start
)
SELECT
    week_start,
    metric_hits_sum,
    metric_total_sum,
    metric_value_avg,
    -- 计算点击量周环比（百分比），保留2位小数
    ROUND((metric_hits_sum / LAG(metric_hits_sum) OVER(ORDER BY week_start) - 1) * 100, 2) AS hits_week_over_week_pct,
    -- 计算总量周环比
    ROUND((metric_total_sum / LAG(metric_total_sum) OVER(ORDER BY week_start) - 1) * 100, 2) AS total_week_over_week_pct,
    -- 计算均值周环比
    ROUND((metric_value_avg / LAG(metric_value_avg) OVER(ORDER BY week_start) - 1) * 100, 2) AS value_week_over_week_pct
FROM weekly_stats
ORDER BY week_start DESC;

3. 索引优化 #

为timestamp字段创建索引，避免全表扫描，大幅提升查询效率：

CREATE INDEX idx_metric_events_timestamp ON metric_events(timestamp);

优化优势 #

• 仅需一次扫描（索引扫描）即可获取所有历史周数据，效率远超原4次全表扫描
• 无需修改SQL即可覆盖所有历史周期，扩展性强
• 周环比计算逻辑清晰，通过窗口函数实现，可读性高

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