TDengine小测试文档记录

一、模拟插入数据

1.创建超级表 空调类别表

CREATE STABLE kongtiao(collection_date timestamp, wendu float,shidu float,A float,B float,C float,D float,E float,F float,G float,H float,I float,J float,K float) TAGS (location binary(64), groupId int);

2.使用代码模拟创建五百张设备表

for (int i = 0; i < 500; i++) {
    String sql = "CREATE TABLE  kongtiaodevice" + i + " USING kongtiao TAGS (\"kongtiao." + i + "\", " + i + ");";
    System.out.println("insert data!");
    databaseMonitor.init().insertData(sql);
}

3.模拟插入数据

//模拟每分钟一次数据采集，一个设备一天采集1440次，保存数据1440行（若为mysql，保存的数据则是1440*13为18720行数据）
        //若模拟500个设备，共13个指标，每个设备一张表，一天总数据约为720000（1440*500）行
        //模拟已经运行一年的时间，则约为259,200,000行数据（360*720000）
        do {
            try {
                long runOnceTime = System.currentTimeMillis();
                runTime = calendar.getTimeInMillis();
                //加一分钟后保存数据
                calendar.add(Calendar.MINUTE, 1);
                System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(calendar.getTime()));
                StringBuilder insertSQL = new StringBuilder("INSERT INTO ");
                //遍历五百次加500个数据
                for (int i = 0; i < 500; i++) {
                    insertSQL.append("kongtiaodevice" + i + "  VALUES (" + calendar.getTime().getTime() + ", 15.2,25.1,15.5,15.6,15.7,18.5,52.0,520,125,211,1,5,4)");
​
                }
                insertSQL.append(";");
                //插入数据库
                databaseMonitor.init().insertData(insertSQL.toString());
                System.out.println("----->run once use:" + (System.currentTimeMillis() - runOnceTime) + " ms");
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + "----->!!!error!!!");
            }
​
        } while (runTime < nowTime);

5.插入数据耗时观察

//执行一次（一次五百个设备的13个指标的数据插入情况）的耗时部分截取如下
......
----->run once use:21 ms
----->run once use:20 ms
----->run once use:25 ms
----->run once use:20 ms
----->run once use:24 ms
----->run once use:26 ms
----->run once use:21 ms
----->run once use:24 ms
----->run once use:21 ms
----->run once use:21 ms
----->run once use:23 ms
----->run once use:29 ms
----->run once use:22 ms
......
----->run once use:21 ms
----->run once use:55 ms
----->run once use:22 ms
----->run once use:24 ms
----->run once use:20 ms
----->run once use:40 ms
----->run once use:21 ms
----->run once use:23 ms
----->run once use:23 ms
----->run once use:26 ms
----->run once use:24 ms
----->run once use:21 ms
----->run once use:21 ms
----->run once use:25 ms
----->run once use:21 ms
......
全部总用时约13257秒，220.9分钟，3.6小时，实际插入263520500行数据，
​
---->all use: 12232940ms

​

二、查询耗时观察

1.查询超级表数据总行数（执行三次结果对比）

select count(0) from kongtiao;
​
result: 263520500
​
Query OK, 1 row(s) in set (13.775320s)
Query OK, 1 row(s) in set (13.899532s)
Query OK, 1 row(s) in set (13.853991s)

2.查询设备表总行数（执行三次结果对比）

select count(0) from kongtiaodevice6;
​
result: 527041
​
Query OK, 1 row(s) in set (0.055528s)
Query OK, 1 row(s) in set (0.056793s)
Query OK, 1 row(s) in set (0.054600s)

3.查询超级表所有数据

超过七分钟，未继续等待结果

4.模拟场景范围查询

4.1 查询指定设备所有指标最新数据

4.1.1限定超级表查询条件查询（执行三次结果对比）

select * from kongtiao where location="kongtiao.3" and groupid=3  order by collection_date desc limit 0,1;
​
Query OK, 1 row(s) in set (0.197527s)
Query OK, 1 row(s) in set (0.162327s)
Query OK, 1 row(s) in set (0.162825s)

4.1.2从具体设备表查询（执行三次结果对比）

select * from kongtiaodevice3 order by collection_date desc limit 0,1;
​
Query OK, 1 row(s) in set (0.161188s)
Query OK, 1 row(s) in set (0.163021s)
Query OK, 1 row(s) in set (0.161021s)

4.2 查询指定设备所有指标10080（约七天数据，1440*7）条历史数据；

4.2.1限定超级表查询条件查询（执行三次结果对比）

select * from kongtiao where collection_date >= " 2022-11-30 00:00:00" and collection_date<="2022-12-06 23:59:59"  and  location="kongtiao.7" and groupid = 7  limit 0,10080;
​
result: ...
​
Query OK, 10080 row(s) in set (1.891665s)
Query OK, 10080 row(s) in set (1.846722s)
Query OK, 10080 row(s) in set (1.986305s)

4.2.2指定设备表查询条件查询（执行三次结果对比）

select * from kongtiaodevice8 where collection_date >= " 2022-11-30 00:00:00" and collection_date<="2022-12-06 23:59:59"   limit 0,10080;
​
result:
​
Query OK, 10080 row(s) in set (1.619627s)
Query OK, 10080 row(s) in set (1.501426s)
Query OK, 10080 row(s) in set (1.559705s)

4.3查询指定单个设备指定单个指标10080（约七天数据，1440*7）条历史条历史数据；

4.3.1.限定超级表查询设备条件查询（执行三次结果对比）

select shidu from kongtiao where collection_date >= " 2022-11-30 00:00:00" and collection_date<="2022-12-06 23:59:59"  and  location="kongtiao.9" and groupid = 9  limit 0,10080;
​
result: ...
​
Query OK, 10080 row(s) in set (0.084002s)
Query OK, 10080 row(s) in set (0.076584s)
Query OK, 10080 row(s) in set (0.078624s)

4.3.2指定单个设备表查询条件查询（执行三次结果对比）

select shidu from kongtiaodevice9 where collection_date >= " 2022-11-30 00:00:00" and collection_date<="2022-12-06 23:59:59"    limit 0,10080;
​
result: ...
​
Query OK, 10080 row(s) in set (0.304008s)
Query OK, 10080 row(s) in set (0.105621s)
Query OK, 10080 row(s) in set (0.104178s)

4.4查询指定多个设备指定多个指标从50400（约七天数据，1440*7 * 5，5为模拟设备数）条历史数据中聚合查询五分钟间隔数据；（执行三次结果对比）

select _wstart,max(wendu) from kongtiao  where collection_date >= " 2022-11-30 00:00:00" and collection_date<="2022-12-06 23:59:59" and ((location="kongtiao.5" and groupid = 5)  or   (location="kongtiao.1" and groupid = 1)  or   (location="kongtiao.2" and groupid 
= 2)  or   (location="kongtiao.3" and groupid = 3)  or  (location="kongtiao.4" and groupid = 4)   ) INTERVAL(300s)   limit 0,50400;
​
result:...
​
Query OK, 2016 row(s) in set (0.170980s)
Query OK, 2016 row(s) in set (0.179655s)
Query OK, 2016 row(s) in set (0.165498s)