TDengine安装与测试

1.起因

目前公司主要是做物联网方面的业务，之前为了快速开发和方便维护，设备（传感器）的数据也使用了MySQL进行存储。起初项目数据量小，系统并无问题，但随着传感器的数据越来越多，目前单表已经有了7700w，查询、关联等操作变得很慢，也在不断的优化索引、SQL语句甚至读写分离与迁移数据，但毕竟我不是专业的DBA，维护这些东西很吃力。偶然的机会下，首先是接触到了IotDB这种时序性数据库，发现这种时序性数据库非常锲合我们的业务背景（其实应该是锲合所有的物联网项目），然后我开始了解市场上现在都有哪几种时序性数据库，包括有IotDB、InfluxDB等，也了解了列式存储的数据库，如：ClickHouse。在之前研究EMQX的时候，论坛有个老哥加了我的微信，和我探讨了一些问题，他说他们是要利用EMQX的WebHook调用TDengine的Restful接口进行设备消息存储，在和他的探讨下，我也了解了一下关于TDengine这个数据库，了解了以后我深深叹了一口气，不得不佩服TDengine的强大。之前因为一些原因，我离开了公司一段时间，目前又回来了，哈哈，也算二进宫了，之后的话会考虑把传感器数据放到TDengine数据库中，道阻且长。

官网地址：https://www.taosdata.com/cn/

2.安装TDengine数据库

我准备了一台云服务器，配置算是入门配置吧，正好测试一下TDengine数据库在配置低的情况下的运行效果

我们从官网上下载安装包

会要求填一个邮箱号，然后会把下载链接发到邮箱里，我这里直接给大家贴一个出来吧

https://www.taosdata.com/assets-download/TDengine-server-2.0.20.12-Linux-x64.rpm

下载完成后上传到服务器上，进行安装

rpm -ivh TDengine-server-2.0.20.12-Linux-x64.rpm

中途遇到需要输入的地方直接回车即可，这样我们就安装完成了，然后我们查看一下TDengine目前的状态

systemctl status taosd

然后我们启动一下

systemctl start taosd

3.简单测试

我们安装的这个版本内置客户端，直接在命令行使用taos即可进入交互页面，然后我们做几个简单操作

taos> show databases;
              name              |      created_time       |   ntables   |   vgroups   | replica | quorum |  days  |   keep0,keep1,keep(D)    |  cache(MB)  |   blocks    |   minrows   |   maxrows   | wallevel |    fsync    | comp | cachelast | precision | update |   status   |
====================================================================================================================================================================================================================================================================================
 log                            | 2021-08-13 14:51:31.150 |           4 |           1 |       1 |      1 |     10 | 30,30,30                 |           1 |           3 |         100 |        4096 |        1 |        3000 |    2 |         0 | us        |      0 | ready      |
Query OK, 1 row(s) in set (0.001006s)
taos> create database water;
Query OK, 0 of 0 row(s) in database (0.003380s)
taos> use water;
Database changed.
taos> create table 0001 (ts timestamp,pressure double);
DB error: invalid SQL: invalid table name (0.000306s)
taos> create table 0001 (ts timestamp,pressure double);
DB error: invalid SQL: invalid table name (0.000595s)
taos> create table water.1 (ts timestamp,pressure double);
DB error: syntax error near ".1 (ts timestamp,pressure double);" (0.000124s)
taos> create table water1 (ts timestamp,pressure double);
Query OK, 0 of 0 row(s) in database (0.016558s)
taos> show tables;
           table_name           |      created_time       | columns |          stable_name           |          uid          |     tid     |    vgId     |
==========================================================================================================================================================
 water1                         | 2021-08-13 14:57:46.659 |       2 |                                |       844424946914236 |           1 |           3 |
Query OK, 1 row(s) in set (0.001993s)
taos> insert into t values ('2020-01-01 00:00:00', 12.3);
DB error: Table does not exist (0.000351s)
taos> insert into water1 values ('2020-01-01 00:00:00', 12.3);
Query OK, 1 of 1 row(s) in database (0.000634s)
taos> insert into water1 values (now, 17.3);
Query OK, 1 of 1 row(s) in database (0.000367s)
taos> select * from water1;
           ts            |         pressure          |
======================================================
 2020-01-01 00:00:00.000 |              12.300000000 |
 2021-08-13 14:58:59.853 |              17.300000000 |
Query OK, 2 row(s) in set (0.001054s)
taos> select * from water.water1;
           ts            |         pressure          |
======================================================
 2020-01-01 00:00:00.000 |              12.300000000 |
 2021-08-13 14:58:59.853 |              17.300000000 |
Query OK, 2 row(s) in set (0.000838s)
taos> select * from water.water1 order by ts desc;
           ts            |         pressure          |
======================================================
 2021-08-13 14:58:59.853 |              17.300000000 |
 2020-01-01 00:00:00.000 |              12.300000000 |
Query OK, 2 row(s) in set (0.001758s)
taos> drop table water1;
Query OK, 0 of 0 row(s) in database (0.006572s)
taos> drop database water;
Query OK, 0 of 0 row(s) in database (0.007274s)
taos> exit

对表名的起名是有规范的，我一开始不知道

而且我们这个直接使用taos进入命令行这种操作是不是有点……不安全，等我再仔细研究一下试试
其实用taos直接进入是使用用户名root 密码taosdata进入的，我们可以使用命令行修改密码

alter user root pass 'zym123456,.' ;

然后我们使用taos进入数据库

可以看到已经登不上去了，我们用新密码登录试试

这样就可以解决大家担心的直接使用taos就可进入数据库的操作

官方也给出了一些参数

4.TDengine的真正强大之处

官方提供了一个demo，我们跑起来玩一下

启动TDengine的服务，在Linux终端执行taosdemo

该命令将在数据库 test 下面自动创建一张超级表 meters，该超级表下有 1 万张表，表名为 “d0” 到 “d9999”，每张表有 1 万条记录，每条记录有 (ts, col1, col2, col3) 四个字段，时间戳从 “2017-07-14 10:40:00 000” 到 “2017-07-14 10:40:09 999”，每张表带有标签 location 和 groupId，groupId 被设置为 1 到 10， location 被设置为 “beijing” 或者 “shanghai”。

[root@VM-4-8-centos ~]# taosdemo;
taosdemo is simulating data generated by power equipments monitoring...
host:                       127.0.0.1:6030
user:                       root
password:                   taosdata
configDir:                  
resultFile:                 ./output.txt
thread num of insert data:  10
thread num of create table: 10
top insert interval:        0
number of records per req:  30000
max sql length:             1048576
database count:             1
database[0]:
  database[0] name:      test
  drop:                  yes
  replica:               1
  precision:             ms
  super table count:     1
  super table[0]:
      stbName:           meters
      autoCreateTable:   no
      childTblExists:    no
      childTblCount:     10000
      childTblPrefix:    d
      dataSource:        rand
      iface:             taosc
      insertRows:        10000
      interlaceRows:     0
      disorderRange:     1000
      disorderRatio:     0
      maxSqlLen:         1048576
      timeStampStep:     1
      startTimestamp:    2017-07-14 10:40:00.000
      sampleFormat:      
      sampleFile:        
      tagsFile:          
      columnCount:       3
column[0]:FLOAT column[1]:INT column[2]:FLOAT 
      tagCount:            2
        tag[0]:INT tag[1]:BINARY(16) 
         Press enter key to continue or Ctrl-C to stop
create database test success!
已杀死

已杀死……这配置跑不起来，换虚拟机……

我在虚拟机用docker跑起来的，直接执行下面命令即可

docker run -d tdengine/tdengine

然后我们进入容器内执行taosdemo即可

docker exec -it tdengine /bin/bash

root@a92fbfa78c9f:~/TDengine-server-2.1.0.0# taosdemo
host:                       127.0.0.1:6030
user:                       root
password:                   taosdata
configDir:                  
resultFile:                 ./output.txt
thread num of insert data:  10
thread num of create table: 10
top insert interval:        0
number of records per req:  30000
max sql length:             1048576
database count:             1
database[0]:
  database[0] name:      test
  drop:                  yes
  replica:               1
  precision:             ms
  super table count:     1
  super table[0]:
      stbName:           meters
      autoCreateTable:   no
      childTblExists:    no
      childTblCount:     10000
      childTblPrefix:    t
      dataSource:        rand
      insertMode:        taosc
      childTblOffset:    0
      insertRows:        10000
      interlaceRows:     0
      disorderRange:     1000
      disorderRatio:     0
      maxSqlLen:         1048576
      timeStampStep:     1
      startTimestamp:    2017-07-14 10:40:00.000
      sampleFormat:      
      sampleFile:        
      tagsFile:          
      columnCount:       4
column[0]:INT column[1]:INT column[2]:INT column[3]:INT 
      tagCount:            2
        tag[0]:INT tag[1]:BINARY(16) 
Press enter key to continue
create database test success!
Spent 2.0440 seconds to create 10000 tables with 10 thread(s)
thread[0] has currently inserted rows: 2730000, affected rows: 2730000
thread[6] has currently inserted rows: 2620000, affected rows: 2620000
thread[8] has currently inserted rows: 2750000, affected rows: 2750000
thread[5] has currently inserted rows: 2750000, affected rows: 2750000
thread[2] has currently inserted rows: 2770000, affected rows: 2770000
thread[9] has currently inserted rows: 2690000, affected rows: 2690000
thread[7] has currently inserted rows: 2680000, affected rows: 2680000
thread[4] has currently inserted rows: 2760000, affected rows: 2760000
thread[3] has currently inserted rows: 2750000, affected rows: 2750000
thread[1] has currently inserted rows: 2690000, affected rows: 2690000
thread[0] has currently inserted rows: 5490000, affected rows: 5490000
thread[8] has currently inserted rows: 5610000, affected rows: 5610000
thread[6] has currently inserted rows: 5420000, affected rows: 5420000
thread[5] has currently inserted rows: 5410000, affected rows: 5410000
thread[4] has currently inserted rows: 5520000, affected rows: 5520000
thread[9] has currently inserted rows: 5520000, affected rows: 5520000
thread[7] has currently inserted rows: 5440000, affected rows: 5440000
thread[2] has currently inserted rows: 5490000, affected rows: 5490000
thread[3] has currently inserted rows: 5630000, affected rows: 5630000
thread[1] has currently inserted rows: 5430000, affected rows: 5430000
thread[0] has currently inserted rows: 8140000, affected rows: 8140000
thread[2] has currently inserted rows: 8240000, affected rows: 8240000
thread[6] has currently inserted rows: 8200000, affected rows: 8200000
thread[5] has currently inserted rows: 8050000, affected rows: 8050000
thread[8] has currently inserted rows: 8300000, affected rows: 8300000
thread[7] has currently inserted rows: 8170000, affected rows: 8170000
thread[9] has currently inserted rows: 8190000, affected rows: 8190000
thread[4] has currently inserted rows: 8240000, affected rows: 8240000
thread[3] has currently inserted rows: 8430000, affected rows: 8430000
thread[1] has currently inserted rows: 8230000, affected rows: 8230000
====thread[3] completed total inserted rows: 10000000, total affected rows: 10000000====
====thread[8] completed total inserted rows: 10000000, total affected rows: 10000000====
====thread[6] completed total inserted rows: 10000000, total affected rows: 10000000====
====thread[2] completed total inserted rows: 10000000, total affected rows: 10000000====
====thread[1] completed total inserted rows: 10000000, total affected rows: 10000000====
====thread[4] completed total inserted rows: 10000000, total affected rows: 10000000====
====thread[7] completed total inserted rows: 10000000, total affected rows: 10000000====
====thread[9] completed total inserted rows: 10000000, total affected rows: 10000000====
====thread[0] completed total inserted rows: 10000000, total affected rows: 10000000====
====thread[5] completed total inserted rows: 10000000, total affected rows: 10000000====
Spent 109.88 seconds to insert rows: 100000000, affected rows: 100000000 with 10 thread(s) into test.meters. 910125 records/second
insert delay, avg:      92.67ms, max: 328ms, min: 9ms

耗时110秒，插入了一亿条数据，我惊呆了。。。

然后我们去各种维度的查询一下看看时间

首先看一下表里面都有哪些字段，我们查询超级表字段即可

1.查询1亿条数据的col0的最大值，col1的平均值，col2的最大值，col3的最小值

2.查询从第十万条开始，往后查询一千条数据


可以看到是非常快的

3.聚合查询，根据t1字段进行分组查询col1的平均值

体验完这次demo后，大家都能看到TDengine的强大之处，无论是怎么样查询，都可以保证数据在极快的时间内返回结果，在大数据量面前甩开MySQL几条街了~