TDengine 3.0 引入了 information_schema 和 performance_schema 两个临时表，这意味着之前很多能 SHOW 处理的信息必须通过 SQL 查询了，同时也使得查询更加方便。
如果不会使用，就不能就抱怨 TDengine 坑多了。

以下就是整理的运维中常用的 SQL。

taos> show databases;name              |
=================================information_schema             |performance_schema             |test                           |
Query OK, 3 row(s) in set (0.026800s)

目录

• • 0.按标签过滤查询子表数量
  • 1. 查询数据库或超级表下有多少子表
  • 2.查询数据库下有多少普通表
  • 3.查询数据库参数
  • 4.查询每个vnode下缓存的子表数量(last/last_row)
  • 5.查询耗时长的查询
  • • 扩展：杀死所有对 meters 表的查询
  • 6.锁定用户
  • 7.查询用户权限
  • • 授权和回收权限
  • 8.查看 SQL 执行计划

0.按标签过滤查询子表数量

将这个语句放在第一条，是因为 3.0 区别和 2.6 太大了，很多人都会踩 KENG 里面。

taos> select distinct tbname,groupid from test.meters where groupid=10;tbname             |   groupid   |
===============================================d2                             |          10 |d5008                          |          10 |d8755                          |          10 |
......d6672                          |          10 |d4177                          |          10 |Notice: The result shows only the first 100 rows.You can use the `LIMIT` clause to get fewer result to show.Or use '>>' to redirect the whole set of the result to a specified file.You can use Ctrl+C to stop the underway fetching.Query OK, 1047 row(s) in set (0.017102s)

1. 查询数据库或超级表下有多少子表

taos> select count(*) from information_schema.ins_tables where db_name="test";count(*)        |
========================10000 |
Query OK, 1 row(s) in set (0.012780s)taos> select count(*) from information_schema.ins_tables where db_name="test" and stable_name="meters";count(*)        |
========================10000 |
Query OK, 1 row(s) in set (0.011303s)

2.查询数据库下有多少普通表

taos> select count(*) from information_schema.ins_tables where db_name="test" and type="NORMAL_TABLE" ;count(*)        |
========================4 |
Query OK, 1 row(s) in set (0.025889s)

3.查询数据库参数

taos> select * from information_schema.ins_databases where name='test'\G;
*************************** 1.row ***************************name: testcreate_time: 2023-03-20 08:11:33.716vgroups: 4ntables: 10004replica: 1strict: onduration: 14400mkeep: 5256000m,5256000m,5256000mbuffer: 256pagesize: 4pages: 256minrows: 100maxrows: 4096comp: 2precision: msstatus: readyretentions: NULLsingle_stable: falsecachemodel: nonecachesize: 1wal_level: 1wal_fsync_period: 3000
wal_retention_period: 0wal_retention_size: 0wal_roll_period: 0wal_segment_size: 0stt_trigger: 1table_prefix: 0table_suffix: 0tsdb_pagesize: 4
Query OK, 1 row(s) in set (0.009516s)

效果和 show create database test \G; 相同

4.查询每个vnode下缓存的子表数量(last/last_row)

taos> select vgroup_id,`tables`,cacheload,`cacheTables` from information_schema.ins_vgroups where db_name='test';vgroup_id  |   tables    |  cacheload  | cacheTables |
========================================================2 |        2485 |      694960 |        2482 |3 |        2523 |      706160 |        2522 |4 |        2520 |      705600 |        2520 |5 |        2476 |      693280 |        2476 |
Query OK, 4 row(s) in set (0.006786s)

注意：部分列名为关键词，需要加反引号！！

5.查询耗时长的查询

taos> select `user`,`exec_usec`/1000000 cost_time,sql from performance_schema.perf_queries;user           |         cost_time         |              sql               |
========================================================================================root                     |               0.777875000 | select count(*) from test.m... |root                     |               0.799425000 | select count(*) from test.m... |root                     |               0.811605000 | select count(*) from test.m... |root                     |               0.793454000 | select count(*) from test.m... |
Query OK, 4 row(s) in set (0.110338s)

扩展：杀死所有对 meters 表的查询

有时某些 SQL 写的不合理，大批量打到数据库，会让整个数据库卡住，这是需要快速的将查询找到并 kill。
以下命令可以将所有对表 meters 的查询一次性杀死。慎用！！！

taos -uroot -ptaosdata -s "select kill_id from performance_schema.perf_queries where sql like '%meters%';" | \
grep '|' |grep -v ' kill_id '| awk '{print "kill query \""$1"\";"}' > kill.sql && taos -uroot -ptaosdata -f kill.sql

注：也可以对用户进行过滤

6.锁定用户

运维中，我们经常需要暂时锁定用户，3.0 终于支持了。

taos> alter user test enable 0;
Query OK, 0 row(s) affected (0.008728s)taos> q
[root@test1 ~]# taos -utest -ptst
Welcome to the TDengine Command Line Interface, Client Version:3.0.3.0
Copyright (c) 2022 by TDengine, all rights reserved.failed to connect to server, reason: User is disabled

taos> alter user test enable 1;
Query OK, 0 row(s) affected (0.005976s)taos> q
[root@test1 ~]# taos -utest -ptest
Welcome to the TDengine Command Line Interface, Client Version:3.0.3.0
Copyright (c) 2022 by TDengine, all rights reserved.******************************  Tab Completion  ***********************************   The TDengine CLI supports tab completion for a variety of items,             **   including database names, table names, function names and keywords.              **   The full list of shortcut keys is as follows:                                    **    [ TAB ]        ......  complete the current word                                **                   ......  if used on a blank line, display all supported commands  **    [ Ctrl + A ]   ......  move cursor to the st[A]rt of the line                   **    [ Ctrl + E ]   ......  move cursor to the [E]nd of the line                     **    [ Ctrl + W ]   ......  move cursor to the middle of the line                    **    [ Ctrl + L ]   ......  clear the entire screen                                  **    [ Ctrl + K ]   ......  clear the screen after the cursor                        **    [ Ctrl + U ]   ......  clear the screen before the cursor                       ***************************************************************************************Server is Enterprise trial Edition, ver:3.0.3.0 and will expire at 2023-05-20 10:36:32.

7.查询用户权限

taos> select * from information_schema.ins_user_privileges where user_name='test';user_name         | privilege  |          object_name           |
=========================================================================test                     | read       | test                           |test                     | write      | test                           |
Query OK, 2 row(s) in set (0.006765s)

授权和回收权限

和关系库一样，不赘述，看示例：

taos> select * from information_schema.ins_user_privileges where user_name='test';user_name         | privilege  |          object_name           |
=========================================================================test                     | read       | test                           |test                     | write      | test                           |
Query OK, 2 row(s) in set (0.005283s)taos> revoke write on test.* from test;
Query OK, 0 row(s) affected (0.004754s)taos> select * from information_schema.ins_user_privileges where user_name='test';user_name         | privilege  |          object_name           |
=========================================================================test                     | read       | test                           |
Query OK, 1 row(s) in set (0.004692s)taos> grant write on test.* to test;
Query OK, 0 row(s) affected (0.008323s)taos> select * from information_schema.ins_user_privileges where user_name='test';user_name         | privilege  |          object_name           |
=========================================================================test                     | read       | test                           |test                     | write      | test                           |
Query OK, 2 row(s) in set (0.004149s)

8.查看 SQL 执行计划

这个算是比较实用的功能了。

taos> explain select _wstart as ts,count(*) from test.meters interval(1h) order by ts desc\G;
*************************** 1.row ***************************
QUERY_PLAN: -> Merge Aligned Interval on Column #expr_1 (functions=2 width=16)
*************************** 2.row ***************************
QUERY_PLAN:    -> SortMerge (columns=2 width=16)
*************************** 3.row ***************************
QUERY_PLAN:       -> Data Exchange 1:1 (width=16)
*************************** 4.row ***************************
QUERY_PLAN:          -> Interval on Column ts (functions=2 width=16 input_order=asc output_order=desc)
*************************** 5.row ***************************
QUERY_PLAN:             -> Table Scan on meters (columns=1 width=8 order=[asc|1 desc|0])
*************************** 6.row ***************************
QUERY_PLAN:       -> Data Exchange 1:1 (width=16)
*************************** 7.row ***************************
QUERY_PLAN:          -> Interval on Column ts (functions=2 width=16 input_order=asc output_order=desc)
*************************** 8.row ***************************
QUERY_PLAN:             -> Table Scan on meters (columns=1 width=8 order=[asc|1 desc|0])
*************************** 9.row ***************************
QUERY_PLAN:       -> Data Exchange 1:1 (width=16)
*************************** 10.row ***************************
QUERY_PLAN:          -> Interval on Column ts (functions=2 width=16 input_order=asc output_order=desc)
*************************** 11.row ***************************
QUERY_PLAN:             -> Table Scan on meters (columns=1 width=8 order=[asc|1 desc|0])
*************************** 12.row ***************************
QUERY_PLAN:       -> Data Exchange 1:1 (width=16)
*************************** 13.row ***************************
QUERY_PLAN:          -> Interval on Column ts (functions=2 width=16 input_order=asc output_order=desc)
*************************** 14.row ***************************
QUERY_PLAN:             -> Table Scan on meters (columns=1 width=8 order=[asc|1 desc|0])
Query OK, 14 row(s) in set (0.001709s)