1-TDengine安装与初体验

taos> insert into t values (‘2019-07-15 00:00:00’, 10);

Query OK, 1 of 1 row(s) in database (0.002749s)

taos> insert into t values (now, 100);

Query OK, 1 of 1 row(s) in database (0.000982s)

taos> select count(*) from t;

count(*) |

========================

2 |

Query OK, 1 row(s) in set (0.000957s)

taos> select * from t;

ts | speed |

========================================

2019-07-15 00:00:00.000 | 10 |

2021-07-20 16:28:21.813 | 100 |

Query OK, 2 row(s) in set (0.002184s)

taos> select * from db.t;

ts | speed |

========================================

2019-07-15 00:00:00.000 | 10 |

2021-07-20 16:28:21.813 | 100 |

Query OK, 2 row(s) in set (0.003076s)

taos> select * from t limit 1;

ts | speed |

========================================

2019-07-15 00:00:00.000 | 10 |

Query OK, 1 row(s) in set (0.001250s)

taos> select * from t order by ts desc;

ts | speed |

========================================

2021-07-20 16:28:21.813 | 100 |

2019-07-15 00:00:00.000 | 10 |

Query OK, 2 row(s) in set (0.002486s)

taos> drop table t;

Query OK, 0 of 0 row(s) in database (0.115054s)

taos> show tables;

Query OK, 0 row(s) in set (0.002069s)

taos> drop database db;

Query OK, 0 of 0 row(s) in database (0.010900s)

taos> exit

[root@hadoop1 local]#

Note：

1. 若通过其他主机的客户端访问TDengine，则需要指定主机名：taos -h ip/hostname

2. 我们注意到在通过taos连接时，并没有指定用户名与密码，这个简直了。。其实，TDengine在用户未指定认证信息时，默认为root, taosdata

参考官方文档，常用的几个命令行参数：

-c, --config-dir: 指定配置文件目录，默认为/etc/taos

-h, --host: 指定服务的FQDN，默认为本地服务

-s, --commands: 在不进入终端的情况下运行TDengine命令

-u, --user: 连接TDengine服务器的用户名，缺省为root

-p, --password: 连接TDengine服务器的密码，缺省为taosdata

-?, --help: 打印出所有命令行参数

经过此番体验，乍看起来， TDengine 与我们用过的关系型数据库 MySQL 没啥区别呀，且慢，这只是用类SQL语句操作了下 TDengine ，下面我们继续看下与传统SQL不太一样的地方，同时体验下 TDengine 的写入、查询效率。

进阶体验

TDengine 自带了一个 taosdemo 的程序，该命令将在数据库 test 下面自动创建一张超级表 meters ，该超级表下有1万张表，表名为"t0"到"t9999"，每张表有1万条记录，每条记录有 (ts, col0, col1, col2, col3) 五个字段，时间戳从 “2017-07-14 10:40:00 000” 到 “2017-07-14 10:40:09 999”，每张表带有标签t0和t1，t0被设置为0到9999，t1被设置为"beijing"或者"shanghai"，即最后共插入1亿条记录。

• 建表、写数据

Linux命令行直接执行taosdmeo

[root@hadoop1 local]# taosdemo

输出内容如下（同时会将该内容写入文件： /root/output.txt ）：

host: 127.0.0.1:6030

user: root

configDir:

resultFile: ./output.txt

thread num of insert data: 10

thread num of create table: 10

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

iface: taosc

insertRows: 10000

interlace rows: 0

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)

Spent 3.2100 seconds to create 10000 tables with 10 thread(s)

Spent 59.48 seconds to insert rows: 100000000, affected rows: 100000000 with 10 thread(s) into test.meters. 1681350.46 records/second

insert delay, avg: 47.24ms, max: 244ms, min: 7ms

可以看到创建了一个数据库 test ，一张超级表 meters ，10000张测点（表），每张表里写入10000条数据。

开启了10个线程，建库+建表+插入数据总耗时约60s，总记录100000000条，每秒写入数据记录1681350.46条。

从写入效果看，让人既震撼又兴奋~

• 验证

taos> use test;

Database changed.

taos> describe meters;

Field | Type | Length | Note |

=================================================================================

ts | TIMESTAMP | 8 | |

col0 | INT | 4 | |

col1 | INT | 4 | |

col2 | INT | 4 | |

col3 | INT | 4 | |

t0 | INT | 4 | TAG |

t1 | BINARY | 16 | TAG |

Query OK, 7 row(s) in set (0.000135s)

taos> show tables;

table_name | created_time | columns | stable_name | uid | tid | vgId |

==========================================================================================================================================================

t5114 | 2021-06-15 14:32:05.078 | 5 | meters | 1407376679002899 | 107 | 5 |

t6915 | 2021-06-15 14:32:07.348 | 5 | meters | 1125944788123835 | 2675 | 4 |

t5999 | 2021-06-15 14:32:06.711 | 5 | meters | 1407403557282797 | 1709 | 5 |

t1474 | 2021-06-15 14:32:06.830 | 5 | meters | 1688873836557746 | 1429 | 6 |

…

Query OK, 10000 row(s) in set (1.042923s)

taos> select count(tbname) from meters;

count(tbname) |

========================

10000 |

Query OK, 1 row(s) in set (0.006129s)

taos> select count(*) from meters;

count(*) |

========================

100000000 |

Query OK, 1 row(s) in set (0.210428s)

taos> select count(*) from t0;

count(*) |

========================

10000 |

Query OK, 1 row(s) in set (0.002336s)

taos> select count(*) from test.t9999;

count(*) |

========================

10000 |

Query OK, 1 row(s) in set (0.002720s)

taos> select distinct t1 from meters;

t1 |

===================

beijing |

shanghai |

Query OK, 2 row(s) in set (0.109290s)

taos> select count(t0) from meters;

count(t0) |

========================

10000 |

Query OK, 1 row(s) in set (0.009314s)

taos> select *, t0, t1 from t9999 limit 10;

ts | col0 | col1 | col2 | col3 | t0 | t1 |

===================================================================================================================

2017-07-14 10:40:00.000 | 3413 | 2118 | 19236 | 34372 | 9999 | beijing |

2017-07-14 10:40:00.001 | 39459 | 58510 | 1896 | 137 | 9999 | beijing |

2017-07-14 10:40:00.002 | 46560 | 16696 | 57671 | 52867 | 9999 | beijing |

2017-07-14 10:40:00.003 | 24451 | 41162 | 19182 | 17327 | 9999 | beijing |

2017-07-14 10:40:00.004 | 2421 | 10378 | 18471 | 24160 | 9999 | beijing |

2017-07-14 10:40:00.005 | 11240 | 39221 | 16868 | 53291 | 9999 | beijing |

2017-07-14 10:40:00.006 | 49797 | 61202 | 47328 | 17810 | 9999 | beijing |

2017-07-14 10:40:00.007 | 41747 | 61545 | 10914 | 21212 | 9999 | beijing |

2017-07-14 10:40:00.008 | 53202 | 18229 | 57033 | 7533 | 9999 | beijing |

2017-07-14 10:40:00.009 | 55927 | 64343 | 46090 | 30498 | 9999 | beijing |

Query OK, 10 row(s) in set (0.003072s)

• 查询

查询1亿条记录的平均值、最大值、最小值等，第一次查询比较耗时

taos> select max(col0), avg(col1), max(col2), min(col3) from test.meters;

max(col0) | avg(col1) | max(col2) | min(col3) |

======================================================================

65534 | 32746.001712370 | 65534 | 0 |

Query OK, 1 row(s) in set (16.479990s)

查询1亿条记录的平均值、最大值、最小值等，第二次查询，非常快

taos> select max(col0), avg(col1), max(col2), min(col3) from test.meters;

max(col0) | avg(col1) | max(col2) | min(col3) |

======================================================================

65534 | 32746.001712370 | 65534 | 0 |

Query OK, 1 row(s) in set (0.063418s)

查询t1="beijing"的记录总条数

taos> select count(*) from test.meters where t1=“beijing”;

count(*) |

========================

50000000 |

Query OK, 1 row(s) in set (0.016695s)

查询t1="shanghai"的记录总条数

taos> select count(*) from test.meters where t1=“shanghai”;

count(*) |

========================

50000000 |

Query OK, 1 row(s) in set (0.017321s)

查询t1=“shanghai”、"beijing"的记录总条数

taos> select count(*) from test.meters where t1=“beijing” or t1=“shanghai”;

count(*) |

========================

100000000 |

查询t0=100的记录总条数

taos> select count(*) from test.meters where t0=100;

count(*) |

========================

10000 |

Query OK, 1 row(s) in set (0.002740s)

查询1亿条记录的前10条

taos> select * from test.meters limit 10;

ts | col0 | col1 | col2 | col3 | t0 | t1 |

===================================================================================================================

2017-07-14 10:40:00.000 | 20738 | 17079 | 28835 | 20955 | 0 | shanghai |

2017-07-14 10:40:00.001 | 9521 | 9092 | 16912 | 18897 | 0 | shanghai |

2017-07-14 10:40:00.002 | 63838 | 42129 | 52379 | 50840 | 0 | shanghai |

2017-07-14 10:40:00.003 | 36661 | 52292 | 5025 | 15506 | 0 | shanghai |

2017-07-14 10:40:00.004 | 988 | 47682 | 56909 | 12187 | 0 | shanghai |

2017-07-14 10:40:00.005 | 44303 | 50954 | 56846 | 11266 | 0 | shanghai |

2017-07-14 10:40:00.006 | 32445 | 39137 | 16212 | 40621 | 0 | shanghai |

2017-07-14 10:40:00.007 | 54210 | 50140 | 54717 | 56829 | 0 | shanghai |

2017-07-14 10:40:00.008 | 53177 | 40921 | 15058 | 10413 | 0 | shanghai |

2017-07-14 10:40:00.009 | 39718 | 14091 | 47696 | 51869 | 0 | shanghai |

Query OK, 10 row(s) in set (0.033425s)

利用缓存机制，查询t1='beijing’的最新一条数据值

taos> select last_row(col3) from meters where t1=‘beijing’;

last_row(col3) |

=================

30630 |

Query OK, 1 row(s) in set (0.037200s)

聚合查询：查询col3平均值，按照t1分组

taos> select avg(col3) from meters group by t1;

avg(col3) | t1 |

===============================================

32744.678155980 | beijing |

32752.362831000 | shanghai |

Query OK, 2 row(s) in set (0.049459s)

降采样：对表t1110按10s进行平均值、最大值和最小值聚合统计

taos> select avg(col1), max(col2), min(col3) from test.t1110 interval(10s);

ts | avg(col1) | max(col2) | min(col3) |

==================================================================================

2017-07-14 10:40:00.000 | 32770.574900000 | 65527 | 3 |

Query OK, 1 row(s) in set (0.002239s)

查询t1="shanghai"且指定时间范围内记录总数

taos> select count(*) from test.meters where t1=“shanghai” and ts > ‘2017-07-14 10:40:00 000’ and ts < ‘2017-07-14 10:40:01 000’;

count(*) |

========================

4995000 |

Query OK, 1 row(s) in set (0.562990s)

降采样：查询t1="shanghai"且在指定时间范围内col1的平均值，1s一个点

taos> select avg(col1) from test.meters where t1=“shanghai” and ts > ‘2017-07-14 10:40:00 000’ and ts < ‘2017-07-14 10:40:05 000’ interval(1s);