优化Azure CosmosDB中含大量AVG运算的传感器数据SQL查询

问题场景 #

我在Azure CosmosDB中存储了数GB的传感器数据，单条数据结构如下：

{
    "id": "41322a50-a5ba-41a5-97cf-1b3c26e0b0f8",
    "Time": 637591000753940400,
    "Temperature": 21.338543,
    "Humidity": 51.168198,
    "Pressure": 997.39777,
    "PM10": 17,
    "PM2_5": 17,
    "PM1": 12,
    "Lux": 91.6434,
}

我目前用的查询语句如下，用来统计指定时间范围内的数据条目数以及各指标的平均值：

SELECT COUNT(c.Time) as Items, AVG(c.Temperature) as Temperature, AVG(c.Humidity) as Humidity, AVG(c.Lux) as Lux, AVG(c.PM10) as PM10, AVG(c.PM2_5) as PM2_5, AVG(c.PM1) as PM1 
FROM SensorData c 
WHERE c.Time BETWEEN @startTime AND @endTime

这个查询大量使用AVG聚合函数，我希望在不新增缓存等额外基础设施的前提下优化查询性能。我配置了如下索引策略：

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/\"_etag\"/?"
        }
    ],
    "compositeIndexes": [
        [
            {
                "path": "/Time",
                "order": "ascending"
            },
            {
                "path": "/Temperature",
                "order": "ascending"
            }
        ],
        [
            {
                "path": "/Time",
                "order": "ascending"
            },
            {
                "path": "/Humidity",
                "order": "ascending"
            }
        ],
        [
            {
                "path": "/Time",
                "order": "ascending"
            },
            {
                "path": "/Lux",
                "order": "ascending"
            }
        ],
        [
            {
                "path": "/Time",
                "order": "ascending"
            },
            {
                "path": "/PM10",
                "order": "ascending"
            }
        ],
        [
            {
                "path": "/Time",
                "order": "ascending"
            },
            {
                "path": "/PM2_5",
                "order": "ascending"
            }
        ],
        [
            {
                "path": "/Time",
                "order": "ascending"
            },
            {
                "path": "/PM1",
                "order": "ascending"
            }
        ],
        [
            {
                "path": "/Time",
                "order": "ascending"
            },
            {
                "path": "/Pressure",
                "order": "ascending"
            }
        ]
    ]
}

但在500MB的测试数据子集上运行时，索引查找耗时仍然很高，请问我的索引策略是否可优化？还有哪些策略可以提升该查询的性能？

回答 #

索引策略可做如下优化

你当前的索引设计和查询模式不匹配，存在明显的冗余开销：

• 现有7组仅包含Time和单个指标的复合索引会导致查询引擎需要执行7次独立的索引扫描才能完成所有聚合计算，直接把索引扫描的开销翻了数倍。可以直接替换为单个包含Time+所有需要聚合的指标字段的复合索引，Time放在最前面，其余字段顺序无强制要求，就能把索引扫描次数降到1次。
• 可以缩小includedPaths的范围，不需要设置全路径/*索引，仅保留你实际查询用到的字段路径即可，既降低索引维护开销，也能减少查询时的索引检索成本。
  优化后的索引策略参考如下：

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {"path": "/Time/?"},
        {"path": "/Temperature/?"},
        {"path": "/Humidity/?"},
        {"path": "/Pressure/?"},
        {"path": "/PM10/?"},
        {"path": "/PM2_5/?"},
        {"path": "/PM1/?"},
        {"path": "/Lux/?"},
        {"path": "/id/?"}
    ],
    "excludedPaths": [
        {"path": "/*"}
    ],
    "compositeIndexes": [
        [
            {"path": "/Time", "order": "ascending"},
            {"path": "/Temperature", "order": "ascending"},
            {"path": "/Humidity", "order": "ascending"},
            {"path": "/Lux", "order": "ascending"},
            {"path": "/PM10", "order": "ascending"},
            {"path": "/PM2_5", "order": "ascending"},
            {"path": "/PM1", "order": "ascending"},
            {"path": "/Pressure", "order": "ascending"}
        ]
    ]
}

其他性能优化策略

• 优化分区键设计：如果容器当前的分区键不是时间维度相关的字段，建议把按时间粒度拆分的字段（比如按天/小时截取的Time值）作为分区键，查询时可以指定分区键范围，避免跨所有分区扫描数据，性能提升幅度最大。如果查询的时间范围跨多个分区，可以开启并行跨分区查询配置，降低多分区查询的耗时。
• 拆分大范围查询：如果单次查询的时间范围过大，可以把大范围拆分为多个小时间片的子查询并行执行，最后在应用侧聚合结果，避免单查询扫描数据量过大触发限流或者高延迟。
• 内置预聚合：可以通过Cosmos DB的变更馈送功能，按固定时间粒度（比如5分钟、1小时）预计算各指标的sum和count值，存储到另外的容器中，查询的时候直接读取预聚合的结果计算平均值，不需要扫描原始明细数据，性能可以提升几个数量级。该方案仅使用Cosmos DB自身能力，不需要引入额外基础设施。
• 调整一致性级别：如果业务对数据一致性要求不高，可以把查询的一致性级别从默认的会话一致性调整为最终一致性，查询的RU消耗和延迟都会有所降低。

内容的提问来源于stack exchange，提问作者Whosdatdev