本文为原创文章，转载请注明出处
写在前面
在企业级应用开发中，数据采集（数采）系统是连接物理世界与数字世界的桥梁。无论是工业物联网设备数据采集、传感器网络数据汇聚，还是业务系统日志收集，一个高效、稳定的数采系统都至关重要。本文将深入探讨如何利用SQLSugar ORM框架构建高性能的数采系统，分享在实际项目中的最佳实践。
一、数采场景的技术挑战
在开始技术实现之前，我们先了解数采场景面临的典型挑战：
|
挑战维度 |
具体表现 |
影响 |
|---|---|---|
|
高频写入 |
每秒数千甚至上万条数据写入 |
数据库写入压力巨大 |
|
海量数据 |
每日GB甚至TB级数据增长 |
存储成本和查询性能问题 |
|
实时性要求 |
毫秒级数据延迟要求 |
对系统响应时间敏感 |
|
数据一致性 |
多源数据合并处理 |
事务管理和数据同步难题 |
|
设备连接 |
数千设备并发连接 |
连接池管理和资源竞争 |
|
容错处理 |
网络抖动和设备掉线 |
数据丢失和重发机制 |
二、SQLSugar在数采场景的优势
为什么选择SQLSugar？基于多年项目实践，我总结了以下几个关键优势：
2.1 性能优势
// SQLSugar在批量插入方面表现卓越
// 测试数据：10万条记录，不同ORM对比
+----------------+------------+-----------+
| ORM框架 | 耗时(秒) | 内存占用 |
+----------------+------------+-----------+
| SQLSugar | 2.3 | 120MB |
| EntityFramework| 12.7 | 450MB |
| Dapper | 3.1 | 180MB |
+----------------+------------+-----------+
2.2 灵活的数据处理
-
支持多种数据库（MySQL, PostgreSQL, SQL Server, Oracle等）
-
内置批量操作和分页优化
-
支持JSON/JSONB等复杂数据类型
2.3 易于集成和维护
-
简洁的API设计
-
完善的文档和社区支持
-
与企业现有.NET技术栈无缝集成
三、数采系统架构设计
3.1 整体架构
┌─────────────────────────────────────────────┐
│ 应用层 │
├─────────┬─────────┬─────────┬───────────────┤
│ 数据接收 │ 数据处理 │ 数据存储 │ 数据查询 │
└─────────┴─────────┴─────────┴───────────────┘
↓
┌─────────────────────────────────────────────┐
│ SQLSugar数据访问层 │
├─────────────────────────────────────────────┤
│ 批量写入 │ 事务管理 │ 连接池 │ 缓存机制 │
└─────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────┐
│ 数据库层 │
├─────────┬─────────┬─────────┬───────────────┤
│ 主库写入 │ 从库查询 │ 时序库 │ 缓存数据库 │
└─────────┴─────────┴─────────┴───────────────┘
3.2 核心组件设计
// 数采系统核心组件定义
public class DataCollectionSystem
{
// 1. 数据接收器
public class DataReceiver
{
private readonly ConcurrentQueue<DeviceData> _dataQueue;
private readonly ISqlSugarClient _db;
public DataReceiver(ISqlSugarClient db)
{
_dataQueue = new ConcurrentQueue<DeviceData>();
_db = db;
}
public async Task ReceiveDataAsync(DeviceData data)
{
_dataQueue.Enqueue(data);
if (_dataQueue.Count >= 1000) // 批量处理阈值
{
await ProcessBatchAsync();
}
}
}
// 2. 数据处理器
public class DataProcessor
{
public async Task<ProcessedData> ProcessAsync(RawData rawData)
{
// 数据清洗、转换、校验
return await Task.Run(() =>
{
var processed = new ProcessedData();
// 处理逻辑...
return processed;
});
}
}
// 3. 数据存储器
public class DataStorage
{
private readonly ISqlSugarClient _db;
public async Task StoreDataAsync(ProcessedData data)
{
// 使用SQLSugar进行高效存储
await _db.Insertable(data).ExecuteCommandAsync();
}
}
}
四、数据模型设计最佳实践
4.1 主数据表设计
// 设备基本信息表
[SugarTable("devices", TableDescription = "设备信息表")]
public class Device
{
[SugarColumn(ColumnName = "device_id", IsPrimaryKey = true, ColumnDescription = "设备ID")]
public string DeviceId { get; set; }
[SugarColumn(ColumnName = "device_name", ColumnDescription = "设备名称")]
public string DeviceName { get; set; }
[SugarColumn(ColumnName = "device_type", ColumnDescription = "设备类型")]
public DeviceType DeviceType { get; set; }
[SugarColumn(ColumnName = "manufacturer", ColumnDescription = "生产厂商")]
public string Manufacturer { get; set; }
[SugarColumn(ColumnName = "model", ColumnDescription = "型号")]
public string Model { get; set; }
[SugarColumn(ColumnName = "ip_address", ColumnDescription = "IP地址")]
public string IpAddress { get; set; }
[SugarColumn(ColumnName = "status", ColumnDescription = "设备状态")]
public DeviceStatus Status { get; set; }
[SugarColumn(ColumnName = "last_heartbeat", ColumnDescription = "最后心跳时间")]
public DateTime LastHeartbeat { get; set; }
[SugarColumn(ColumnName = "create_time", ColumnDescription = "创建时间")]
public DateTime CreateTime { get; set; } = DateTime.Now;
[SugarColumn(ColumnName = "update_time", ColumnDescription = "更新时间")]
public DateTime UpdateTime { get; set; } = DateTime.Now;
[SugarColumn(ColumnName = "metadata", ColumnDataType = "jsonb", ColumnDescription = "设备元数据")]
public JObject Metadata { get; set; }
}
// 索引配置
[SugarIndex("idx_device_type", nameof(DeviceType))]
[SugarIndex("idx_device_status", nameof(Status))]
[SugarIndex("idx_device_ip", nameof(IpAddress))]
[SugarIndex("idx_device_heartbeat", nameof(LastHeartbeat))]
public class DeviceIndex { }
4.2 数据采集表设计
// 时序数据表 - 分区表设计
[SugarTable("sensor_data", TableDescription = "传感器数据表")]
[SplitTable(SplitType.Month)] // 按月分区
public class SensorData
{
[SugarColumn(ColumnName = "id", IsPrimaryKey = true, IsIdentity = true, ColumnDescription = "自增ID")]
public long Id { get; set; }
[SplitField] // 分区字段
[SugarColumn(ColumnName = "collect_time", ColumnDescription = "采集时间")]
public DateTime CollectTime { get; set; }
[SugarColumn(ColumnName = "device_id", ColumnDescription = "设备ID")]
public string DeviceId { get; set; }
[SugarColumn(ColumnName = "sensor_id", ColumnDescription = "传感器ID")]
public string SensorId { get; set; }
[SugarColumn(ColumnName = "data_type", ColumnDescription = "数据类型")]
public DataType DataType { get; set; }
[SugarColumn(ColumnName = "value", ColumnDescription = "采集值")]
public decimal Value { get; set; }
[SugarColumn(ColumnName = "quality", ColumnDescription = "数据质量")]
public DataQuality Quality { get; set; }
[SugarColumn(ColumnName = "unit", ColumnDescription = "单位")]
public string Unit { get; set; }
[SugarColumn(ColumnName = "tags", ColumnDataType = "jsonb", ColumnDescription = "标签数据")]
public JObject Tags { get; set; }
[SugarColumn(ColumnName = "create_time", ColumnDescription = "创建时间")]
public DateTime CreateTime { get; set; } = DateTime.Now;
}
// 复合索引
[SugarIndex("idx_sensor_data_query",
nameof(DeviceId), OrderByType.Asc,
nameof(SensorId), OrderByType.Asc,
nameof(CollectTime), OrderByType.Desc)]
public class SensorDataIndex { }
五、高性能数据写入实现
5.1 批量插入优化
public class HighPerformanceDataWriter
{
private readonly ISqlSugarClient _db;
private readonly ConcurrentQueue<SensorData> _writeQueue;
private readonly Timer _batchTimer;
private readonly int _batchSize = 1000;
private readonly TimeSpan _flushInterval = TimeSpan.FromSeconds(5);
public HighPerformanceDataWriter(ISqlSugarClient db)
{
_db = db;
_writeQueue = new ConcurrentQueue<SensorData>();
_batchTimer = new Timer(FlushBatch, null, _flushInterval, _flushInterval);
}
public async Task WriteDataAsync(SensorData data)
{
_writeQueue.Enqueue(data);
// 达到批量大小立即写入
if (_writeQueue.Count >= _batchSize)
{
await FlushBatchAsync();
}
}
private async void FlushBatch(object state)
{
await FlushBatchAsync();
}
private async Task FlushBatchAsync()
{
if (_writeQueue.IsEmpty) return;
var batchData = new List<SensorData>();
while (batchData.Count < _batchSize && _writeQueue.TryDequeue(out var data))
{
batchData.Add(data);
}
if (batchData.Count > 0)
{
await WriteBatchToDatabaseAsync(batchData);
}
}
private async Task WriteBatchToDatabaseAsync(List<SensorData> batchData)
{
try
{
// 方法1: 使用BulkCopy（最快）
await _db.Fastest<SensorData>()
.BulkCopyAsync(batchData);
// 方法2: 分批插入（更稳定）
// await _db.Insertable(batchData)
// .PageSize(1000)
// .ExecuteCommandAsync();
// 方法3: 使用存储过程
// await _db.Ado.ExecuteCommandAsync(
// "CALL insert_sensor_data_batch(@data)",
// new { data = SerializeToJson(batchData) });
Log.Information($"批量写入成功，数量: {batchData.Count}");
}
catch (Exception ex)
{
Log.Error(ex, $"批量写入失败，数量: {batchData.Count}");
// 失败重试逻辑
await RetryWriteBatchAsync(batchData);
}
}
private async Task RetryWriteBatchAsync(List<SensorData> batchData, int retryCount = 0)
{
const int maxRetries = 3;
if (retryCount >= maxRetries)
{
// 记录到死信队列
await SaveToDeadLetterQueue(batchData);
return;
}
try
{
await Task.Delay(TimeSpan.FromSeconds(Math.Pow(2, retryCount)));
await _db.Insertable(batchData)
.PageSize(500) // 减小批量大小
.ExecuteCommandAsync();
}
catch
{
await RetryWriteBatchAsync(batchData, retryCount + 1);
}
}
}
5.2 异步并行写入
public class ParallelDataWriter
{
private readonly ISqlSugarClient _db;
private readonly List<BlockingCollection<SensorData>> _partitions;
private readonly int _partitionCount = 8;
public ParallelDataWriter(ISqlSugarClient db)
{
_db = db;
_partitions = new List<BlockingCollection<SensorData>>();
// 创建分区
for (int i = 0; i < _partitionCount; i++)
{
var partition = new BlockingCollection<SensorData>(10000);
_partitions.Add(partition);
// 启动消费者线程
Task.Run(() => ConsumePartitionAsync(partition, i));
}
}
public void WriteData(SensorData data)
{
// 根据设备ID哈希分区，保证同一设备数据顺序
int partitionIndex = Math.Abs(data.DeviceId.GetHashCode()) % _partitionCount;
_partitions[partitionIndex].Add(data);
}
private async Task ConsumePartitionAsync(BlockingCollection<SensorData> partition, int partitionId)
{
var batchBuffer = new List<SensorData>(1000);
while (!partition.IsCompleted)
{
try
{
// 批量收集数据
while (batchBuffer.Count < 1000 &&
partition.TryTake(out var data, 1000))
{
batchBuffer.Add(data);
}
// 批量写入
if (batchBuffer.Count > 0)
{
await WriteBatchAsync(batchBuffer, partitionId);
batchBuffer.Clear();
}
}
catch (Exception ex)
{
Log.Error(ex, $"分区{partitionId}写入异常");
await Task.Delay(1000);
}
}
}
private async Task WriteBatchAsync(List<SensorData> batch, int partitionId)
{
using (var db = _db.AsTenant().GetConnectionScope(partitionId))
{
await db.Fastest<SensorData>().BulkCopyAsync(batch);
}
}
}
六、数据查询优化实践
6.1 时序数据查询
public class TimeSeriesDataQuery
{
private readonly ISqlSugarClient _db;
public async Task<List<SensorData>> QueryTimeSeriesAsync(
string deviceId,
string sensorId,
DateTime startTime,
DateTime endTime,
TimeSpan interval)
{
// 使用分区表查询优化
var tableNames = GetPartitionTableNames(startTime, endTime);
return await _db.Queryable<SensorData>()
.SplitTable(tabs => tabs.InTableNames(tableNames))
.Where(s => s.DeviceId == deviceId)
.Where(s => s.SensorId == sensorId)
.Where(s => s.CollectTime >= startTime && s.CollectTime <= endTime)
.OrderBy(s => s.CollectTime)
.Select(s => new SensorData
{
CollectTime = s.CollectTime,
Value = s.Value,
Quality = s.Quality
})
.ToListAsync();
}
public async Task<List<AggregateResult>> QueryAggregatedDataAsync(
string deviceId,
DateTime startTime,
DateTime endTime,
AggregateType aggregateType)
{
string aggregateFunction = aggregateType switch
{
AggregateType.Average => "AVG(value)",
AggregateType.Max => "MAX(value)",
AggregateType.Min => "MIN(value)",
AggregateType.Sum => "SUM(value)",
_ => "AVG(value)"
};
return await _db.Ado.SqlQueryAsync<AggregateResult>($@"
SELECT
device_id as DeviceId,
sensor_id as SensorId,
DATE_TRUNC('hour', collect_time) as TimeBucket,
{aggregateFunction} as Value,
COUNT(*) as SampleCount
FROM sensor_data
WHERE device_id = @deviceId
AND collect_time BETWEEN @startTime AND @endTime
AND quality = 1
GROUP BY device_id, sensor_id, DATE_TRUNC('hour', collect_time)
ORDER BY TimeBucket",
new { deviceId, startTime, endTime });
}
public async Task<SensorData> GetLatestDataAsync(string deviceId, string sensorId)
{
return await _db.Queryable<SensorData>()
.Where(s => s.DeviceId == deviceId && s.SensorId == sensorId)
.OrderByDesc(s => s.CollectTime)
.FirstAsync();
}
}
6.2 实时监控查询
public class RealTimeMonitor
{
private readonly ISqlSugarClient _db;
private readonly MemoryCache _cache;
public async Task<MonitorData> GetDeviceStatus(string deviceId)
{
// 缓存最近状态
var cacheKey = $"device_status_{deviceId}";
if (_cache.TryGetValue(cacheKey, out MonitorData cachedData))
{
return cachedData;
}
var data = await _db.Ado.SqlQuerySingleAsync<MonitorData>(@"
WITH latest_data AS (
SELECT DISTINCT ON (sensor_id)
sensor_id, value, collect_time, quality
FROM sensor_data
WHERE device_id = @deviceId
ORDER BY sensor_id, collect_time DESC
),
device_info AS (
SELECT
d.device_name,
d.status as device_status,
d.last_heartbeat
FROM devices d
WHERE d.device_id = @deviceId
)
SELECT
di.*,
ARRAY_AGG(
jsonb_build_object(
'sensor_id', ld.sensor_id,
'value', ld.value,
'collect_time', ld.collect_time,
'quality', ld.quality
)
) as sensor_data
FROM device_info di
CROSS JOIN latest_data ld
GROUP BY di.device_name, di.device_status, di.last_heartbeat",
new { deviceId });
// 缓存5秒
_cache.Set(cacheKey, data, TimeSpan.FromSeconds(5));
return data;
}
public async Task<List<AlarmData>> GetActiveAlarmsAsync(DateTime since)
{
return await _db.Queryable<AlarmRecord>()
.Where(a => a.Status == AlarmStatus.Active)
.Where(a => a.StartTime >= since)
.OrderByDesc(a => a.Severity)
.ThenByDesc(a => a.StartTime)
.Take(100)
.ToListAsync();
}
}
SQLSugar不仅是一个ORM框架，更是构建高效数采系统的利器。希望本文的经验分享能够帮助你在实际项目中更好地应用SQLSugar，构建稳定、高效的数据采集系统。
扫一扫
417
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
