🤔 你是不是也踩过这个坑？

做工控或物联网项目的时候，最头疼的事情之一就是数据库表设计。设备每秒上报十几条数据，一天下来几十万行，查询一卡就是好几秒；告警和正常数据混在一张表里，想统计日均值得写一堆聚合 SQL；更别说后期维护的时候，改一个字段牵一发动全身。

这种问题，几乎每个做过 WPF + SQLite 数据采集项目的开发者都遇到过。

核心矛盾其实就一个：采集频率高、数据量大，但查询分析的需求又五花八门——实时监控要快、历史趋势要准、告警响应要及时。把所有需求塞进一张表，注定是条死路。

读完这篇文章，你将掌握：

• 原始表 / 汇总表 / 告警表三表分离的设计思路
• 每张表的字段设计原则与索引策略
• WPF 端如何配合写入、查询与清理

废话不多说，直接进入正题。

---

🔍 问题深度剖析：一张表为什么撑不住？

数据的"三种性格"

采集系统里的数据，本质上有三种截然不同的使用场景：

第一种是原始流水数据。 每隔 500ms 或 1s 采集一次，记录设备的实时状态值。这类数据写入频率极高，但查询通常只看"最近一段时间"，过了保留周期就可以归档或删除。它的核心诉求是写快、存短、查近。

第二种是汇总统计数据。 用于趋势分析、报表生成，比如每小时的平均值、最大值、最小值。这类数据量小，但查询频繁，往往需要跨天、跨月聚合。它的核心诉求是查快、存久、算准。

第三种是告警事件数据。 当某个采集值超阈值或设备异常时触发，需要记录触发时间、恢复时间、告警级别、处理状态。这类数据量最小，但业务逻辑最复杂，经常需要关联查询和状态更新。它的核心诉求是状态可追踪、响应要及时。

把这三种"性格"完全不同的数据塞进一张表，就像让仓库、收银台和客服台共用同一个工位——互相干扰，效率极低。

量化一下问题有多严重

在一个典型的单设备、1秒采集一次的场景下：

时间跨度	原始数据行数	混合查询耗时（无索引）
1天	~86,400 行	~120ms
7天	~600,000 行	~850ms
30天	~2,500,000 行	~3,500ms

（测试环境：i5-10400 / 8GB RAM / SQLite 3.42 / SSD）

超过 3 秒的查询响应，在 WPF 界面上基本等同于"卡死"。用户体验直接崩塌。

---

💡 核心要点提炼：三表分离的设计哲学

表的职责边界要清晰

好的表设计，每张表只做一件事。原始表负责"忠实记录"，汇总表负责"聚合提炼"，告警表负责"事件追踪"。三者之间通过 device_id 和时间戳关联，但各自独立存储、独立索引、独立清理。

索引是性能的命脉

SQLite 的查询性能高度依赖索引设计。对于时序数据，时间戳 + 设备 ID 的复合索引几乎是标配。原始表以写为主，索引不宜过多；汇总表以读为主，可以适当增加聚合字段的索引。

数据生命周期要提前规划

原始数据保留 7~30 天，汇总数据保留 1~2 年，告警数据永久保留（或按业务需求归档）。这个生命周期策略直接影响表的分区方式和清理任务的设计。

---

🏗️ 解决方案设计：三表结构详解

方案一：原始数据表（raw_data）

原始表是整个系统的"数据入口"，设计原则是轻量、高写入、短保留。

csharp
// SQLite 建表 DDL
public async Task InitializeDatabaseAsync()
{
    await using var conn = new SqliteConnection(_connectionString);
    await conn.OpenAsync();

    var sql = """
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = NORMAL;

        CREATE TABLE IF NOT EXISTS raw_data (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id     TEXT    NOT NULL,
            channel       TEXT    NOT NULL,
            value         REAL    NOT NULL,
            unit          TEXT,
            quality       INTEGER NOT NULL DEFAULT 1,
            collected_at  TEXT    NOT NULL
        );

        CREATE INDEX IF NOT EXISTS idx_raw_device_time
            ON raw_data(device_id, collected_at DESC);

        CREATE TABLE IF NOT EXISTS summary_hourly (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id     TEXT    NOT NULL,
            channel       TEXT    NOT NULL,
            hour_start    TEXT    NOT NULL,
            avg_value     REAL,
            max_value     REAL,
            min_value     REAL,
            sample_count  INTEGER,
            created_at    TEXT    DEFAULT (datetime('now'))
        );

        CREATE UNIQUE INDEX IF NOT EXISTS idx_summary_unique
            ON summary_hourly(device_id, channel, hour_start);

        CREATE INDEX IF NOT EXISTS idx_summary_query
            ON summary_hourly(device_id, hour_start DESC);

        CREATE TABLE IF NOT EXISTS alarm_events (
            id             INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id      TEXT    NOT NULL,
            channel        TEXT    NOT NULL,
            alarm_type     TEXT    NOT NULL,
            alarm_level    INTEGER NOT NULL,
            threshold      REAL,
            trigger_value  REAL,
            triggered_at   TEXT    NOT NULL,
            recovered_at   TEXT,
            status         INTEGER DEFAULT 0,
            remark         TEXT
        );

        CREATE INDEX IF NOT EXISTS idx_alarm_device_status
            ON alarm_events(device_id, status, triggered_at DESC);

        CREATE INDEX IF NOT EXISTS idx_alarm_active
            ON alarm_events(status, triggered_at DESC)
            WHERE status = 0;
        """;

    await using var cmd = conn.CreateCommand();
    cmd.CommandText = sql;
    await cmd.ExecuteNonQueryAsync();
}

字段设计说明：

• channel 用字符串而不是枚举，是为了兼容动态扩展的采集点，不需要改表结构就能新增参数。
• quality 字段很容易被忽略，但在工控场景里极其重要——传感器断线、通讯超时都会产生"脏数据"，打上质量标记后查询时可以过滤。
• 不建议在原始表上建太多索引，写入性能会明显下降。

WPF 端批量写入示例：

csharp
public async Task BulkInsertRawDataAsync(IEnumerable<RawDataRecord> records)
{
    var batch = records as IList<RawDataRecord> ?? records.ToList();
    if (batch.Count == 0)
    {
        return;
    }

    await using var conn = new SqliteConnection(_connectionString);
    await conn.OpenAsync();
    await using var dbTransaction = await conn.BeginTransactionAsync();
    var tx = (SqliteTransaction)dbTransaction;

    var cmd = conn.CreateCommand();
    cmd.Transaction = tx;
    cmd.CommandText = """
        INSERT INTO raw_data (device_id, channel, value, unit, quality, collected_at)
        VALUES ($deviceId, $channel, $value, $unit, $quality, $collectedAt);
        """;

    var deviceParam = cmd.Parameters.Add("$deviceId", SqliteType.Text);
    var channelParam = cmd.Parameters.Add("$channel", SqliteType.Text);
    var valueParam = cmd.Parameters.Add("$value", SqliteType.Real);
    var unitParam = cmd.Parameters.Add("$unit", SqliteType.Text);
    var qualityParam = cmd.Parameters.Add("$quality", SqliteType.Integer);
    var collectedParam = cmd.Parameters.Add("$collectedAt", SqliteType.Text);

    foreach (var r in batch)
    {
        deviceParam.Value = r.DeviceId;
        channelParam.Value = r.Channel;
        valueParam.Value = r.Value;
        unitParam.Value = r.Unit ?? string.Empty;
        qualityParam.Value = r.Quality;
        collectedParam.Value = ToSqlTime(r.CollectedAt);
        await cmd.ExecuteNonQueryAsync();
    }

    await dbTransaction.CommitAsync();
}

踩坑预警： 不要每条数据单独开一个事务。1秒采集1次的场景下，单条事务写入比批量事务写入慢 10~20 倍。建议攒够 50~100 条再批量提交，或者按固定时间间隔（如每 2 秒）提交一次。

---

方案二：汇总统计表（summary_hourly）

汇总表是原始数据的"精华提炼"，由后台定时任务从原始表聚合生成，WPF 界面的趋势图、报表统计优先查这张表。

csharp
// 建表 DDL
conn.Execute(@"
    CREATE TABLE IF NOT EXISTS summary_hourly (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id   TEXT    NOT NULL,
        channel     TEXT    NOT NULL,
        hour_start  DATETIME NOT NULL,   -- 该小时的起始时间（整点）
        avg_value   REAL,                -- 小时均值
        max_value   REAL,                -- 小时最大值
        min_value   REAL,                -- 小时最小值
        sample_count INTEGER,            -- 有效样本数
        created_at  DATETIME DEFAULT (datetime('now'))
    );

    CREATE UNIQUE INDEX IF NOT EXISTS idx_summary_unique
        ON summary_hourly(device_id, channel, hour_start);

    CREATE INDEX IF NOT EXISTS idx_summary_query
        ON summary_hourly(device_id, hour_start DESC);
");

定时汇总任务（每小时执行一次）：

csharp
public async Task AggregateLastHourAsync()
{
    var now = DateTime.UtcNow;
    var hourStart = new DateTime(now.Year, now.Month, now.Day, now.Hour, 0, 0, DateTimeKind.Utc).AddHours(-1);
    await AggregateHourAsync(hourStart);
}

WPF 趋势图查询示例：

csharp
// 查询某设备某通道最近 24 小时的小时均值，用于绑定折线图
public List<(DateTime Hour, double AvgValue)> GetHourlyTrend(
    string deviceId, string channel, int hours = 24)
{
    using var conn = new SQLiteConnection(_connectionString);
    var since = DateTime.UtcNow.AddHours(-hours);

    var result = conn.Query<(string hour, double avg)>(@"
        SELECT hour_start, avg_value
        FROM summary_hourly
        WHERE device_id  = @deviceId
          AND channel    = @channel
          AND hour_start >= @since
        ORDER BY hour_start ASC",
        new { deviceId, channel, since = since.ToString("yyyy-MM-dd HH:mm:ss") });

    return result
        .Select(r => (DateTime.Parse(r.hour), r.avg))
        .ToList();
}

踩坑预警： UNIQUE INDEX 加在 (device_id, channel, hour_start) 上，配合 INSERT OR REPLACE，是保证汇总任务可以安全重跑的关键。如果任务因为异常中断后重新执行，不会产生重复数据，也不需要先查后写。

---

方案三：告警事件表（alarm_events）

告警表记录的是离散事件，不是连续时序数据，结构上更接近"工单"而不是"日志"。

csharp
// 建表 DDL
conn.Execute(@"
    CREATE TABLE IF NOT EXISTS alarm_events (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id       TEXT    NOT NULL,
        channel         TEXT    NOT NULL,
        alarm_type      TEXT    NOT NULL,    -- 告警类型：OverHigh/UnderLow/Offline/Custom
        alarm_level     INTEGER NOT NULL,    -- 告警级别：1=提示 2=警告 3=严重
        threshold       REAL,               -- 触发阈值
        trigger_value   REAL,               -- 触发时的实际值
        triggered_at    DATETIME NOT NULL,  -- 告警触发时间
        recovered_at    DATETIME,           -- 告警恢复时间（NULL=未恢复）
        status          INTEGER DEFAULT 0,  -- 0=未处理 1=已确认 2=已关闭
        remark          TEXT                -- 处理备注
    );

    CREATE INDEX IF NOT EXISTS idx_alarm_device_status
        ON alarm_events(device_id, status, triggered_at DESC);

    CREATE INDEX IF NOT EXISTS idx_alarm_active
        ON alarm_events(status, triggered_at DESC)
        WHERE status = 0;   -- 部分索引，专门加速"未处理告警"查询
");

告警触发与恢复逻辑：

csharp
public async Task CheckAndRecordAlarmAsync(
        string deviceId,
        string channel,
        double value,
        double highThreshold,
        int alarmLevel = 2,
        DateTime? triggeredAtUtc = null)
{
    var now = triggeredAtUtc?.ToUniversalTime() ?? DateTime.UtcNow;

    await using var conn = new SqliteConnection(_connectionString);
    await conn.OpenAsync();
    await using var dbTransaction = await conn.BeginTransactionAsync();
    var tx = (SqliteTransaction)dbTransaction;

    if (value > highThreshold)
        {
            var existsCmd = conn.CreateCommand();
            existsCmd.Transaction = tx;
            existsCmd.CommandText = """
                SELECT id
                FROM alarm_events
                WHERE device_id = $deviceId
                  AND channel = $channel
                  AND alarm_type = 'OverHigh'
                  AND status = 0
                  AND recovered_at IS NULL
                LIMIT 1;
                """;
            existsCmd.Parameters.AddWithValue("$deviceId", deviceId);
            existsCmd.Parameters.AddWithValue("$channel", channel);
            var existing = await existsCmd.ExecuteScalarAsync();

            if (existing is null)
            {
                var insertAlarm = conn.CreateCommand();
                insertAlarm.Transaction = tx;
                insertAlarm.CommandText = """
                    INSERT INTO alarm_events
                    (device_id, channel, alarm_type, alarm_level, threshold, trigger_value, triggered_at, status)
                    VALUES
                    ($deviceId, $channel, 'OverHigh', $alarmLevel, $threshold, $value, $triggeredAt, 0);
                    """;
                insertAlarm.Parameters.AddWithValue("$deviceId", deviceId);
                insertAlarm.Parameters.AddWithValue("$channel", channel);
                insertAlarm.Parameters.AddWithValue("$alarmLevel", alarmLevel);
                insertAlarm.Parameters.AddWithValue("$threshold", highThreshold);
                insertAlarm.Parameters.AddWithValue("$value", value);
                insertAlarm.Parameters.AddWithValue("$triggeredAt", ToSqlTime(now));
                await insertAlarm.ExecuteNonQueryAsync();
            }
        }
    else
        {
            var recover = conn.CreateCommand();
            recover.Transaction = tx;
            recover.CommandText = """
                UPDATE alarm_events
                SET recovered_at = $now,
                    status = 2
                WHERE device_id = $deviceId
                  AND channel = $channel
                  AND alarm_type = 'OverHigh'
                  AND recovered_at IS NULL;
                """;
            recover.Parameters.AddWithValue("$now", ToSqlTime(now));
            recover.Parameters.AddWithValue("$deviceId", deviceId);
            recover.Parameters.AddWithValue("$channel", channel);
            await recover.ExecuteNonQueryAsync();
        }

    await dbTransaction.CommitAsync();
}

踩坑预警： 告警触发时一定要做"去重检查"，否则在高频采集场景下，一个持续超阈值的状态会在告警表里写入几千条重复记录。上面代码里的 WHERE recovered_at IS NULL 查询就是这个防重入口。

---

🧹 数据清理：别让原始表撑爆磁盘

原始表的数据清理任务建议在 WPF 后台定时执行，配合 SQLite 的 VACUUM 释放磁盘空间：

csharp
public async Task<int> CleanupRawDataAsync(int retentionDays)
{
    var cutoff = ToSqlTime(DateTime.UtcNow.AddDays(-retentionDays));

    await using var conn = new SqliteConnection(_connectionString);
    await conn.OpenAsync();

    await using var cmd = conn.CreateCommand();
    cmd.CommandText = "DELETE FROM raw_data WHERE collected_at < $cutoff;";
    cmd.Parameters.AddWithValue("$cutoff", cutoff);
    var deleted = await cmd.ExecuteNonQueryAsync();

    if (deleted > 10_000)
    {
        await using var vacuum = conn.CreateCommand();
        vacuum.CommandText = "VACUUM;";
        await vacuum.ExecuteNonQueryAsync();
    }

    return deleted;
}

注意： VACUUM 会锁定整个数据库，执行期间其他写入会被阻塞。建议放在业务低峰期（如凌晨）执行，或者使用 PRAGMA auto_vacuum = INCREMENTAL 让 SQLite 自动增量回收。

UI 样式

xml
<Window x:Class="AppWpf202607.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        mc:Ignorable="d"
        Title="WPF SQLite 三表功能演示"
        Width="1100"
        Height="700"
        WindowStartupLocation="CenterScreen">

    <Grid Margin="12">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto" />
            <RowDefinition Height="Auto" />
            <RowDefinition Height="Auto" />
            <RowDefinition Height="*" />
            <RowDefinition Height="130" />
        </Grid.RowDefinitions>

        <StackPanel Grid.Row="0" Margin="0,0,0,8">
            <TextBlock Text="SQLite 三表分离（原始 / 汇总 / 告警）" FontSize="20" FontWeight="SemiBold" />
            <TextBlock x:Name="DbPathText" FontSize="12" Foreground="Gray" />
        </StackPanel>

        <WrapPanel Grid.Row="1" Margin="0,0,0,8">
            <StackPanel Margin="0,0,10,0">
                <TextBlock Text="设备ID" />
                <TextBox x:Name="DeviceIdTextBox" Width="140" Text="Device-001" />
            </StackPanel>
            <StackPanel Margin="0,0,10,0">
                <TextBlock Text="通道" />
                <ComboBox x:Name="ChannelComboBox" Width="140" />
            </StackPanel>
            <StackPanel Margin="0,0,10,0">
                <TextBlock Text="窗口(小时)" />
                <TextBox x:Name="HoursTextBox" Width="100" Text="24" />
            </StackPanel>
            <StackPanel>
                <TextBlock Text="采样间隔(秒)" />
                <TextBox x:Name="IntervalTextBox" Width="100" Text="10" />
            </StackPanel>
        </WrapPanel>

        <StackPanel Grid.Row="2" Margin="0,0,0,8">
            <WrapPanel Margin="0,0,0,6">
                <Button Content="初始化" Click="InitializeDb_Click" Margin="0,0,8,8" Padding="10,4" />
                <Button x:Name="GenerateDataButton" Content="生成测试数据" Click="GenerateData_Click" Margin="0,0,8,8" Padding="10,4" />
                <Button x:Name="CancelGenerateButton" Content="取消生成" Click="CancelGenerate_Click" Margin="0,0,8,8" Padding="10,4" IsEnabled="False" />
                <Button Content="执行汇总" Click="Aggregate_Click" Margin="0,0,8,8" Padding="10,4" />
                <Button Content="查询趋势" Click="QueryTrend_Click" Margin="0,0,8,8" Padding="10,4" />
                <Button Content="查询告警" Click="QueryAlarms_Click" Margin="0,0,8,8" Padding="10,4" />
                <Button Content="清理原始(7天)" Click="Cleanup_Click" Margin="0,0,8,8" Padding="10,4" />
            </WrapPanel>
            <Grid>
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="*" />
                    <ColumnDefinition Width="130" />
                </Grid.ColumnDefinitions>
                <ProgressBar x:Name="GenerateProgressBar" Grid.Column="0" Height="16" Minimum="0" Maximum="100" Value="0" />
                <TextBlock x:Name="ProgressText" Grid.Column="1" Margin="8,0,0,0" VerticalAlignment="Center" Text="空闲" />
            </Grid>
        </StackPanel>

        <Grid Grid.Row="3" Margin="0,0,0,8">
            <Grid.ColumnDefinitions>
                <ColumnDefinition Width="250" />
                <ColumnDefinition Width="*" />
            </Grid.ColumnDefinitions>

            <GroupBox Header="统计" Grid.Column="0" Margin="0,0,8,0">
                <StackPanel Margin="8">
                    <TextBlock Text="原始表记录数" />
                    <TextBlock x:Name="RawCountText" FontSize="20" FontWeight="Bold" Margin="0,0,0,10" />
                    <TextBlock Text="汇总表记录数" />
                    <TextBlock x:Name="SummaryCountText" FontSize="20" FontWeight="Bold" Margin="0,0,0,10" />
                    <TextBlock Text="活动告警数" />
                    <TextBlock x:Name="ActiveAlarmCountText" FontSize="20" FontWeight="Bold" />
                </StackPanel>
            </GroupBox>

            <TabControl Grid.Column="1">
                <TabItem Header="趋势">
                    <DataGrid x:Name="TrendDataGrid" AutoGenerateColumns="False" IsReadOnly="True">
                        <DataGrid.Columns>
                            <DataGridTextColumn Header="小时(UTC)" Binding="{Binding Hour}" Width="*" />
                            <DataGridTextColumn Header="均值" Binding="{Binding AvgValue, StringFormat=F2}" Width="120" />
                        </DataGrid.Columns>
                    </DataGrid>
                </TabItem>
                <TabItem Header="告警">
                    <DataGrid x:Name="AlarmDataGrid" AutoGenerateColumns="False" IsReadOnly="True">
                        <DataGrid.Columns>
                            <DataGridTextColumn Header="ID" Binding="{Binding Id}" Width="60" />
                            <DataGridTextColumn Header="设备" Binding="{Binding DeviceId}" Width="120" />
                            <DataGridTextColumn Header="通道" Binding="{Binding Channel}" Width="120" />
                            <DataGridTextColumn Header="类型" Binding="{Binding AlarmType}" Width="100" />
                            <DataGridTextColumn Header="级别" Binding="{Binding AlarmLevel}" Width="80" />
                            <DataGridTextColumn Header="阈值" Binding="{Binding Threshold, StringFormat=F2}" Width="80" />
                            <DataGridTextColumn Header="触发值" Binding="{Binding TriggerValue, StringFormat=F2}" Width="90" />
                            <DataGridTextColumn Header="触发时间(UTC)" Binding="{Binding TriggeredAt}" Width="*" />
                        </DataGrid.Columns>
                    </DataGrid>
                </TabItem>
            </TabControl>
        </Grid>

        <GroupBox Grid.Row="4" Header="日志">
            <TextBox x:Name="LogTextBox"
                     IsReadOnly="True"
                     TextWrapping="Wrap"
                     VerticalScrollBarVisibility="Auto" />
        </GroupBox>
    </Grid>
</Window>

---

🎯 三点核心总结

第一，按数据"使用方式"而不是"数据来源"来拆表。 原始数据、汇总数据、告警事件的读写模式完全不同，混在一起只会两边都慢。

第二，索引设计要跟着查询走。 原始表写多读少，索引要精简；汇总表读多写少，可以适当增加；告警表用部分索引（WHERE status = 0）专门加速"未处理"查询，这个技巧很多人不知道。

第三，数据生命周期管理是设计的一部分，不是事后补救。 建表的时候就规划好清理策略，原始表保留 7 天、汇总表保留 2 年、告警表永久保留，这样系统才能长期稳定运行。

---

💬 欢迎在评论区聊聊

你在做 WPF 数据采集项目时，数据库这块遇到过哪些棘手的问题？是写入性能不够、查询太慢，还是数据量膨胀导致磁盘告急？欢迎分享你的实际经历和解决思路，也许正好能帮到有同样困扰的开发者。

另外，如果你的项目采集点超过 100 个、设备超过 10 台，SQLite 的单文件模式可能就到瓶颈了——这时候要不要迁移到 InfluxDB 或 TimescaleDB，是个值得单独聊的话题。

---

#C#开发 #WPF #SQLite #数据库设计 #性能优化 #工控软件 #物联网