如何高性能创建视图合并多个SQL时间序列数据表？

现有方案的核心短板 #

你当前的实现随着设备数量增长会出现明显性能衰减：

• UNION需要对全量时间戳做去重排序，表越多、单表数据量越大，这一步开销越高
• LEFT JOIN次数随设备数量线性增长，每次JOIN都需要一次索引匹配，设备超过20台后性能下降会非常明显
• 新增设备时需要同时修改UNION子句和JOIN逻辑，维护成本高

最优替换方案（性能提升3~10倍） #

用UNION ALL + 条件聚合/PIVOT的结构替换原有逻辑，完全规避多JOIN和UNION去重的开销：

1. 兼容所有数据库的条件聚合实现

CREATE VIEW [dbo].[combined]
WITH SCHEMABINDING
AS
SELECT
    [TimeStamp],
    MAX(CASE WHEN DeviceName = 'Device1' THEN Measurement END) AS Device1,
    MAX(CASE WHEN DeviceName = 'Device2' THEN Measurement END) AS Device2,
    MAX(CASE WHEN DeviceName = 'Device3' THEN Measurement END) AS Device3,
    MAX(CASE WHEN DeviceName = 'Device4' THEN Measurement END) AS Device4,
    MAX(CASE WHEN DeviceName = 'Device5' THEN Measurement END) AS Device5
FROM (
    -- UNION ALL不需要去重，性能远高于UNION
    SELECT TimeStamp, Measurement, 'Device1' AS DeviceName FROM dbo.device1
    UNION ALL
    SELECT TimeStamp, Measurement, 'Device2' AS DeviceName FROM dbo.device2
    UNION ALL
    SELECT TimeStamp, Measurement, 'Device3' AS DeviceName FROM dbo.device3
    UNION ALL
    SELECT TimeStamp, Measurement, 'Device4' AS DeviceName FROM dbo.device4
    UNION ALL
    SELECT TimeStamp, Measurement, 'Device5' AS DeviceName FROM dbo.device5
) AS src
GROUP BY [TimeStamp]
GO

如果是SQL Server环境，也可以用PIVOT语法实现，性能和上述写法基本一致：

CREATE VIEW [dbo].[combined]
WITH SCHEMABINDING
AS
SELECT 
    [TimeStamp],
    [Device1], [Device2], [Device3], [Device4], [Device5]
FROM (
    SELECT TimeStamp, Measurement, 'Device1' AS DeviceName FROM dbo.device1
    UNION ALL
    SELECT TimeStamp, Measurement, 'Device2' AS DeviceName FROM dbo.device2
    UNION ALL
    SELECT TimeStamp, Measurement, 'Device3' AS DeviceName FROM dbo.device3
    UNION ALL
    SELECT TimeStamp, Measurement, 'Device4' AS DeviceName FROM dbo.device4
    UNION ALL
    SELECT TimeStamp, Measurement, 'Device5' AS DeviceName FROM dbo.device5
) AS src
PIVOT (
    MAX(Measurement)
    FOR DeviceName IN ([Device1], [Device2], [Device3], [Device4], [Device5])
) AS pvt
GO

2. 配套性能优化措施

• 所有设备表的TimeStamp字段建聚集索引，时序数据按时间写入无碎片，查询时间范围时可以直接范围扫描
• 该方案支持谓词下推，比如查询WHERE TimeStamp >= '2024-01-01'时，数据库只会拉取每个表符合条件的记录，不需要扫全表
• 如果视图访问频率极高，可以直接基于这个SCHEMABINDING的视图创建索引视图，将结果物化存储，查询性能可以再提升一个数量级，每秒上报的写入开销完全可以忽略。

长期扩展建议 #

如果后续设备数量会超过30台，直接修改存储结构为单表存储所有设备数据，表结构为{TimeStamp, DeviceID, Measurement}，联合索引建(TimeStamp, DeviceID, Measurement)。这种结构下新增设备不需要修改任何表结构和视图逻辑，写入和查询性能都远优于多表拆分的方案。

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