Flux 多表查询小实验

当需要使用 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 连接多个测量表和本别查询再合并虽然不够优雅，但是查询性能更好。