首页 > 编程开发 > Flux 多表查询小实验
2024
12-05

Flux 多表查询小实验

Flux 多表查询小实验 - 第1张  | Weiguang的博客
当需要使用 Flux 语言对 InfluxDB 进行多测量表查询时,有以下几种写法:

写法一 将测量表名写入数组

measurements = ["field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9", "field10"]

from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => contains(set: measurements, value: r._measurement))
  |> group(columns: ["_measurement"])
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

写法二 使用 or 连接多个测量表

from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field1" or 
                       r._measurement == "field2" or 
                       r._measurement == "field3" or 
                       r._measurement == "field4" or 
                       r._measurement == "field5" or
                       r._measurement == "field6" or
                       r._measurement == "field7" or
                       r._measurement == "field8" or
                       r._measurement == "field9" or
                       r._measurement == "field10"
                       )
  |> group(columns: ["_measurement"])
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

写法三 分别查询再合并

field1 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field1")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

field2 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field2")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

field3 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field3")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

field4 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field4")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

field5 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field5")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

field6 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field6")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

field7 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field7")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

field8 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field8")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

field9 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field9")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

field10 = from(bucket: "bucket22")
  |> range(start: -100s)
  |> filter(fn: (r) => r._measurement == "field10")
  |> last()
  |> timeShift(duration: 8h)
  |> keep(columns: ["_time", "_value", "_measurement"])

union(tables: [field1, field2, field3, field4, field5, field6, field7, field8, field9, field10])

查询时间对比

在 Postman 中使用三种方法分别查询 10 次,实验结果如下表所示:

表1 将测量表名写入数组

编号 1 2 3 4 5 6 7 8 9 10 平均时间
查询时间(单位:ms) 378 392 384 369 365 378 379 332 273 368 361.8

表2 使用 or 连接多个测量表

编号 1 2 3 4 5 6 7 8 9 10 平均时间
查询时间(单位:ms) 23 22 17 24 35 37 24 21 23 31 25.7

表3 分别查询再合并

编号 1 2 3 4 5 6 7 8 9 10 平均时间
查询时间(单位:ms) 35 33 30 33 29 33 24 34 21 34 30.6

分析实验结果可以得出如下结论:

  • 将测量表名写入数组的方法虽然在实现上最优雅,但是性能非常差,查询平均用时超过另外两种方法 10 倍。
  • 使用 or 连接多个测量表和本别查询再合并虽然不够优雅,但是查询性能更好。
最后编辑:
作者:lwg0452
这个作者貌似有点懒,什么都没有留下。
捐 赠如果您觉得这篇文章有用处,请支持作者!鼓励作者写出更好更多的文章!

留下一个回复

你的email不会被公开。