Azure Cosmos DB性能疑问：ReadNextAsync为何耗时近6秒？

我在试用Azure Cosmos DB时遇到性能问题，代码中某一步骤速度极慢。初始SQL调用仅需毫秒级，但FeedResponse<Message> response = await queryResultSetIterator.ReadNextAsync();这一行耗时近6秒。Cosmos容器约有15000条记录，本次需拉取1000条数据，分区键为deviceID。以下是示例数据、代码及控制台输出，恳请帮忙解析原因。

容器示例项 #

{
"id": "0",
"deviceID": "-6477270294724479467",
"MessageString": "N8P95FVW6B087IWV",
"_rid": "cmYXAPuQpaEBAAAAAAAAAA==",
"_self": "dbs/cmYXAA==/colls/cmYXAPuQpaE=/docs/cmYXAPuQpaEBAAAAAAAAAA==/",
"_etag": "\"5e009882-0000-0700-0000-62d537c60000\"",
"_attachments": "attachments/",
"_ts": 1658140614
}

代码片段 #

public async Task<List<Message>?> ReadMultipleFromCosmosAsync(string partitionKey, string numrecords)
{
    var tms = Stopwatch.StartNew();
    Container container = cosmosClient.GetDatabase(this.databaseId).GetContainer(containerId);
    List<Message> messages = new List<Message>();
    Console.WriteLine("Container accessed in {0}ms", tms.ElapsedMilliseconds);
    var pms = Stopwatch.StartNew();
    var sqlQueryText = "SELECT TOP " + numrecords + " * FROM c WHERE c.deviceID = '" + partitionKey + "' ORDER BY c._ts DESC";
    QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
    using FeedIterator<Message> queryResultSetIterator = container.GetItemQueryIterator<Message>(queryDefinition);
    Console.WriteLine("SQL executed and iterator read through SQL query results in {0}ms", pms.ElapsedMilliseconds);
    while (queryResultSetIterator.HasMoreResults)
    {
            var rms = Stopwatch.StartNew();
            FeedResponse<Message> response = await queryResultSetIterator.ReadNextAsync();
            Console.WriteLine("Messages copied to FeedResponse in {0}ms", rms.ElapsedMilliseconds);
            var fms = Stopwatch.StartNew();
            foreach (var item in response)
            {
                item.MessageDate = epoch2string(item._ts);
                messages.Add(item);
            }
            Console.WriteLine("Individual messages added to List<T> in {0}ms", fms.ElapsedMilliseconds);
    }
    return messages;
}

控制台输出 #

Starting read
Creating Cosmos client
Cosmos client created in 777ms
Container accessed in 9ms
SQL executed and iterator read through SQL query results in 10ms
Messages copied to FeedResponse in 5599ms
Individual messages added to List in 6ms
Returnstring received by Program
1000 records retrieved from DB in 6450ms

可能的原因及优化建议 #

• 未显式指定分区键导致跨分区查询：尽管SQL语句过滤了分区键deviceID，但创建FeedIterator时未指定分区键值，Cosmos DB会默认执行跨分区查询，扫描多个分区拖慢性能。
  优化方案：通过QueryRequestOptions指定分区键：

  var queryRequestOptions = new QueryRequestOptions { PartitionKey = new PartitionKey(partitionKey) };
  using FeedIterator<Message> queryResultSetIterator = container.GetItemQueryIterator<Message>(queryDefinition, requestOptions: queryRequestOptions);
  

• 缺少_ts字段的复合索引：查询需要按_ts降序排序，若未针对deviceID+_ts创建复合索引，Cosmos DB需在查询后额外排序，增加耗时。
  优化方案：在容器索引策略中添加复合索引：

  {
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [{"path": "/*"}],
    "excludedPaths": [],
    "compositeIndexes": [
      [
        {"path": "/deviceID", "order": "ascending"},
        {"path": "/_ts", "order": "descending"}
      ]
    ]
  }
  

• SQL字符串拼接存在风险且未利用查询缓存：直接拼接SQL不仅有注入风险，也不利于Cosmos DB缓存查询计划。
  优化方案：使用参数化查询：

  var sqlQueryText = "SELECT TOP @numrecords * FROM c WHERE c.deviceID = @partitionKey ORDER BY c._ts DESC";
  QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText)
      .WithParameter("@numrecords", int.Parse(numrecords))
      .WithParameter("@partitionKey", partitionKey);
  

• 容器RU/s设置不足导致限流：若容器的请求单位（RU/s）设置过低，查询会因资源限制被限流，表现为耗时增加。可在Cosmos DB门户查看监控指标，确认是否出现429错误。
  优化方案：临时提高容器RU/s，或设置为自动缩放RU/s适配业务需求。

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