基于时区的Alarms表按日统计告警启停数量的优化方案咨询

针对你的问题，我来梳理几个适配MySQL 5.1.73和MariaDB 5.5.50环境的高效方案，同时解答时区相关的疑问：

你的核心痛点是会话强制UTC但应用需按目标时区统计，且要避免全表扫描、大量UNION的性能损耗。下面分场景给出具体方案：

方案一：无表结构修改，基于时区转换+索引优化 #

1. 固定偏移时区（如UTC+8、UTC+9，无夏令时） #

这种场景无需依赖MySQL的tzinfo时区表，直接用固定时间偏移转换即可，同时保证索引能被正常利用：

步骤1：创建数字辅助表（生成日期范围）

因为MySQL 5.1没有CTE递归能力，我们需要一个小的数字表来生成过去1年的所有日期：

CREATE TABLE IF NOT EXISTS nums (n INT UNSIGNED NOT NULL PRIMARY KEY);
-- 插入0-365的数字（覆盖1年的日期范围）
INSERT INTO nums (n) 
SELECT a.n + b.n*10 + c.n*100 FROM 
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) c
WHERE a.n + b.n*10 + c.n*100 <= 365;

步骤2：高效统计查询

将目标时区的日期范围反向转换为UTC时间，利用原表的StartedAt和Key5 (Ended, EndedAt)索引避免全表扫描：

SET @time_offset = INTERVAL 8 HOUR; -- 替换为你的目标时区偏移，如UTC+8
SET @one_day = INTERVAL 1 DAY;

SELECT
    d.alarm_date,
    COALESCE(s.start_count, 0) AS daily_started,
    COALESCE(e.end_count, 0) AS daily_ended
FROM
    -- 生成过去1年的目标时区日期
    (SELECT DATE(CURDATE() - INTERVAL n DAY) AS alarm_date FROM nums
     WHERE CURDATE() - INTERVAL n DAY >= DATE(CURDATE() - INTERVAL 1 YEAR)) d
LEFT JOIN
    -- 统计当日启动的告警数（目标时区）
    (SELECT
        DATE(StartedAt + @time_offset) AS alarm_date,
        COUNT(*) AS start_count
     FROM Alarms
     GROUP BY alarm_date) s ON d.alarm_date = s.alarm_date
LEFT JOIN
    -- 统计当日结束的告警数（目标时区）
    (SELECT
        DATE(EndedAt + @time_offset) AS alarm_date,
        COUNT(*) AS end_count
     FROM Alarms
     WHERE Ended = TRUE
     GROUP BY alarm_date) e ON d.alarm_date = e.alarm_date
WHERE
    -- 过滤出有活跃告警的日期：告警时间跨度覆盖目标日期
    EXISTS (
        SELECT 1 FROM Alarms
        WHERE
            StartedAt <= (d.alarm_date + @one_day) - @time_offset
            AND (
                Ended = FALSE
                OR EndedAt >= d.alarm_date - @time_offset
            )
    )
ORDER BY d.alarm_date DESC;

2. 夏令时时区（如America/New_York，偏移随季节变化） #

这种场景必须解决MySQL的tzinfo依赖，否则日期转换会出现错误：

步骤1：导入tzinfo时区表

在CentOS系统上执行以下命令，将系统时区数据导入MySQL：

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

导入后重启MySQL/MariaDB服务，验证时区表是否生效：

SELECT * FROM mysql.time_zone LIMIT 1;

步骤2：适配夏令时的统计查询

将上面的查询中的+ @time_offset替换为CONVERT_TZ函数即可：

SET @target_tz = 'America/New_York'; -- 替换为你的目标时区名
SET @one_day = INTERVAL 1 DAY;

SELECT
    d.alarm_date,
    COALESCE(s.start_count, 0) AS daily_started,
    COALESCE(e.end_count, 0) AS daily_ended
FROM
    (SELECT DATE(CURDATE() - INTERVAL n DAY) AS alarm_date FROM nums
     WHERE CURDATE() - INTERVAL n DAY >= DATE(CURDATE() - INTERVAL 1 YEAR)) d
LEFT JOIN
    (SELECT
        DATE(CONVERT_TZ(StartedAt, 'UTC', @target_tz)) AS alarm_date,
        COUNT(*) AS start_count
     FROM Alarms
     GROUP BY alarm_date) s ON d.alarm_date = s.alarm_date
LEFT JOIN
    (SELECT
        DATE(CONVERT_TZ(EndedAt, 'UTC', @target_tz)) AS alarm_date,
        COUNT(*) AS end_count
     FROM Alarms
     WHERE Ended = TRUE
     GROUP BY alarm_date) e ON d.alarm_date = e.alarm_date
WHERE
    EXISTS (
        SELECT 1 FROM Alarms
        WHERE
            StartedAt <= CONVERT_TZ(d.alarm_date + @one_day, @target_tz, 'UTC')
            AND (
                Ended = FALSE
                OR EndedAt >= CONVERT_TZ(d.alarm_date, @target_tz, 'UTC')
            )
    )
ORDER BY d.alarm_date DESC;

方案二：预计算时区日期（性能最优，需修改表结构） #

如果允许修改Alarms表结构，建议新增两个预计算字段，将时区转换的开销转移到写入阶段，查询时直接利用索引：

步骤1：新增字段并创建索引 #

ALTER TABLE Alarms 
ADD COLUMN StartedDate DATE COMMENT 'StartedAt转换为目标时区的日期',
ADD COLUMN EndedDate DATE COMMENT 'EndedAt转换为目标时区的日期（未结束则为NULL）';

-- 创建索引加速统计查询
CREATE INDEX idx_started_date ON Alarms(StartedDate);
CREATE INDEX idx_ended_date ON Alarms(EndedDate);

步骤2：用触发器自动计算时区日期 #

DELIMITER //
-- 插入告警时自动计算StartedDate
CREATE TRIGGER trg_alarms_insert BEFORE INSERT ON Alarms
FOR EACH ROW
BEGIN
    SET NEW.StartedDate = DATE(CONVERT_TZ(NEW.StartedAt, 'UTC', 'Asia/Shanghai')); -- 替换为你的目标时区
END //

-- 告警结束时自动计算EndedDate
CREATE TRIGGER trg_alarms_update BEFORE UPDATE ON Alarms
FOR EACH ROW
BEGIN
    IF NEW.Ended = TRUE AND OLD.Ended = FALSE THEN
        SET NEW.EndedDate = DATE(CONVERT_TZ(NEW.EndedAt, 'UTC', 'Asia/Shanghai'));
    END IF;
END //
DELIMITER ;

步骤3：高效查询 #

此时查询无需再做时区转换，直接基于预计算字段统计：

SELECT
    d.alarm_date,
    COALESCE(s.start_count, 0) AS daily_started,
    COALESCE(e.end_count, 0) AS daily_ended
FROM
    (SELECT DATE(CURDATE() - INTERVAL n DAY) AS alarm_date FROM nums
     WHERE CURDATE() - INTERVAL n DAY >= DATE(CURDATE() - INTERVAL 1 YEAR)) d
LEFT JOIN
    (SELECT StartedDate AS alarm_date, COUNT(*) AS start_count FROM Alarms GROUP BY StartedDate) s ON d.alarm_date = s.alarm_date
LEFT JOIN
    (SELECT EndedDate AS alarm_date, COUNT(*) AS end_count FROM Alarms WHERE Ended = TRUE GROUP BY EndedDate) e ON d.alarm_date = e.alarm_date
WHERE
    EXISTS (
        SELECT 1 FROM Alarms
        WHERE StartedDate <= d.alarm_date
        AND (EndedDate >= d.alarm_date OR Ended = FALSE)
    )
ORDER BY alarm_date DESC;

• 如果你的目标时区涉及夏令时（偏移随季节变化）：必须解决，否则在夏令时切换日期的统计会出现错误，比如告警日期被归到错误的天。
• 如果你的目标时区是固定偏移（如UTC+8）：可以不用解决，直接用固定时间偏移的方式即可，更简单且不依赖系统时区表。

内容的提问来源于stack exchange，提问作者Lightness Races in Orbit