性能超越Clickhouse物联网场景中的毫秒级查询案例

　　1物联网应用场景简介
　　物联网（InternetofThings，简称IoT）是指通过各种信息传感、通信和IT技术来实时连接、采集、监管海量的传感设备，从而实现对现实世界的精确感知和快速响应，继而实现自动化、智能化管理。在查询IoT设备状态的场景下，吞吐量和时延是两个重要的性能指标。
　　在工业物联网中，常见有以下几种设备时序数据的查询需求：案例1：查询某个设备最近的记录案例2：查询某个租户所有设备的最近一条记录案例3：查询某个设备最近5分钟的统计信息案例4：查询某个设备最近一天的秒级数据
　　本教程通过一个工业物联网的案例，来演示DolphinDB的序列查询性能，并对比测试了DolphinDBTSDB引擎、OLAP引擎，以及ClickHouseMergeTree引擎在上述查询案例上的时延指标。总体来说，DolphinDBTSDB引擎的性能（时延）对比DolphinDBOLAP引擎和ClickHouseMergeTree引擎有显著优势。2案例数据准备2。1数据集说明
　　本教程参考了某工业物联网SaaS平台服务商的数据集，模拟并使用一份高度仿真的数据。该SaaS服务商的主要业务是监控各个地区的噪声情况。表结构如下：
　　序号
　　字段名称
　　字段类型
　　注释
　　1hrtenantId
　　INT
　　租户ID
　　2hrdeviceId
　　INT
　　设备ID
　　3hrsoundPressureLevel
　　DOUBLE
　　声音分贝
　　4hrsoundPowerLevel
　　DOUBLE
　　声音功率值
　　5hrts
　　TIMESTAMP
　　数据采集时间戳
　　6hrdate
　　DATE
　　日期
　　一行数据包含租户ID、设备ID、声压、噪声功率、采集时间戳和日期共计6列数据。每行记录占用36字节。该案例数据包含100个租户，每个租户管理100个噪声监控设备，记录了从20220101至20220112，12亿的噪声数据，共计40G。2。2库表设计及数据模拟
　　使用DolphinDBTSDB引擎，创建一个名为NoiseDB的数据库，存储噪声数据。TSDB引擎是DolphinDB自2。00版本起，专门为物联网场景设计研发的数据存储引擎，具备优秀的写入和序列查询性能。
　　在噪声监控的SaaS服务中，较为频繁的查询场景是以租户为维度，查询某一天某个设备的状态信息。因此设计noise表按日期、租户ID进行分区，可以有效利用分区剪枝。同时使用区分度较高的设备ID和数据采集时间戳作为排序键（查询索引），使查询时能够快速定位对应设备的数据，提升查询性能。具体实现脚本如下。db1database（，VALUE，1000。。2000）db2database（，VALUE，2022。01。01。。2022。12。30）TSDBforiotdbNoisedatabase（dfs：NoiseDB，COMPO，〔db1，db2〕，engineTSDB）createtabledfs：NoiseDB。noise（tenantIdINT，deviceIdINT，soundPressureLevelINT，soundPowerLevelDOUBLE，tsTIMESTAMP，dateDATE）partitionedbytenantId，datesortColumns〔deviceId，ts〕
　　库表创建完成后，模拟20220101至20220112的数据，具体代码详见附录DolphinDB脚本。
　　可以通过SQL查询验证下数据集大小：selectcount（）fromloadTable（database（dfs：NoiseDB），noise）wheredatebetween2022。01。01：2022。01。1021260010000
　　导入完成后，每个分区下生成3个level0file，未满足自动合并条件（大于等于10个levelFile），需要进行手动合并。chunkIdsexecchunkIdfromgetChunksMeta（）wheretype1for（xinchunkIds）｛triggerTSDBCompaction（x）｝
　　完成后将案例数据导出数据至csv文件，以便后续导入OLAP引擎、ClickHouse。在ClickHouse中使用OPTIMIZETABLEnoise合并下mergeTree。具体过程参照附录ClickHouse脚本。3SQL查询
　　在DolphinDB中，可以使用SQL快速实现4个设备状态查询需求，并且代码十分简洁。案例1：查询某个设备最近的100条记录：noiseloadTable（database（dfs：NoiseDB），noise）selectfromnoisewheredate2022。01。01andtenantId1055anddeviceId10067orderbytsdesclimit100timer（10）select。。。Timeelapsed：24。33ms
　　脚本的where条件语句中指定了分区列date和tenantId进行过滤，便于DolphinDB系统通过分区剪枝快读定位到对应的分区。同时指定了数据库的sortkey（deviceId）作为过滤字段，利用TSDB的索引机制，可以快速定位到数据块，并按时间顺序取回最新的100条记录。平均一次查询耗时2ms，未命中缓存的首次查询耗时14ms。案例2：查询某个租户所有设备最新状态noiseloadTable（database（dfs：NoiseDB），noise）selectfromnoisewheredate2022。01。01andtenantId1055contextbydeviceIdcsorttsdesclimit1timer（10）select。。。Timeelapsed：246。619ms
　　该脚本在where条件语句中同样指定了分区列以快速定位到对应的数据分区。通过contextby子句来根据设备ID将数据进行分组，每组数据通过csort子句按时间倒序排列（考虑到物联网存在消息乱序的情况，必须使用csort将数据按采集时间排序）。使用limit1获取每个窗口内的最新的一条记录，从而获取该租户当日所有设备的最新状态。平均一次查询耗时25ms，首次查询耗时121ms。案例3：查询某个设备5分钟内的噪声统计值noiseloadTable（database（dfs：NoiseDB），noise）selectmin（ts）asstartTs，max（ts）asendTs，max（soundPressureLevel），avg（soundPressureLevel），max（soundPowerLevel），avg（soundPowerLevel）fromnoisewheredate2022。01。01andtenantId1055anddeviceId10067andtsbetween2022。01。01T00：50：15。518：2022。01。01T00：55：15。518groupbytenantId，deviceIdtimer（10）select。。。Timeelapsed：22。168ms
　　该脚本首先根据where指定的过滤条件定位并扫描数据块，取出对应时间段的数据，并按tenantId，deviceId进行聚合计算，以获取声音分贝、功率的统计值。平均一次查询耗时2ms，首次查询耗时13ms。案例4：查询某个设备最近一天的明细数据noiseloadTable（database（dfs：NoiseDB），noise）selectfromnoisewheredate2022。01。01andtenantId1055anddeviceId10067orderbytstimer（10）select。。。Timeelapsed：23。261ms
　　该脚本首先根据where指定的过滤条件定位并扫描数据块，取出对应时间段的明细数据，并按采集时间排序。平均一次查询耗时2ms，首次查询耗时16ms。
　　注：首次查询指未命中数据库缓存及操作系统缓存的查询。4对比测试
　　进一步测试DolphinDBTSDB引擎与OLAP引擎，以及ClickHouseMergeTree引擎在上述数据集的时序查询性能。测试过程中尽可能地保持环境变量相同，以保证科学有效。具体测试脚本详见附录。4。1测试环境测试机器配置
　　操作系统：CentOS7
　　CPU：2cores
　　内存：10G
　　磁盘：SSD核心测试参数
　　对测试中影响性能的关键参数，保持对等一致。
　　软件信息
　　核心参数
　　库表设计
　　DolphinDB：2。00。6单节点
　　memSize8GTSDB引擎OLAP引擎
　　partitionedbytenantId，datesortColumns〔deviceId，ts〕
　　ClickHouse：22。6。1单节点
　　maxservermemoryusage8GMergeTree引擎
　　partitionbytenantId，dateorderbydeviceId，ts
　　测试时，DolphinDB和ClickHouse均采用单节点，并分配8G最大内存。在引擎方面，DolphinDBTSDB引擎，ClickHouseMergeTree引擎的内部实现都采用了LSMtree。并保持库表设计完全一致。时间衡量标准
　　由于端到端的时间，容易受到网络抖动和客户端实现性能的影响，因此本次测试的测量时间设定为从查询引擎接收到请求至计算出结果为止。
　　4。2测试结果
　　三者的具体测试结果为下表，表中数值为平均耗时首次查询耗时（单位ms），平均耗时的计算逻辑为：
　　平均耗时（首次耗时9次缓存命中耗时）10
　　测试用例
　　场景
　　DolphinDBTSDB
　　DolphinDBOLAP
　　ClickHouse
　　case1
　　查询某个设备最新100条记录
　　214
　　3451
　　14150
　　case2
　　查询某个租户所有设备的最新状态
　　25121
　　62170
　　73400
　　case3
　　查询某个设备5min的噪声统计值
　　213
　　15136
　　1282
　　case4
　　查询某个设备最近一天的明细数据
　　216
　　24220
　　22200
　　可以看出，OLAP引擎和ClickHouse在不同的查询场景下性能各有其优势和劣势。
　　而TSDB引擎性能均优于ClickHouse，在相对复杂的点查场景性能差距更大。在场景4下，DolphinDBTSDB引擎比ClickHouse的性能高12。5倍，首次查询高13倍。在该场景中，TSDB引擎需要读取对应设备的10000条记录，压缩后的存储大小约为90K。存储在6个连续的Block中，读取效率非常高效。而ClickHouse则是scan了该分区下1000000条记录的数据块，因此两者的首次查询性能差距较大，而缓存后的性能差距主要取决于两者在计算性能上的差别。5总结
　　DolphinDBTSDB引擎在物联网场景有着卓越的点查性能，可以以毫秒级延时迅速响应设备的状态信息，其性能更优于ClickHouse的MergeTree引擎。6附录跳转中。。。
　　跳转中。。。