如何让CosmosDB跨分区查询单次（或最少）访问数据库获取全量数据？

我尝试用单条查询从指定的几个分区获取多个文档，但发现查询会针对每个分区发起额外的数据库访问。

查询语句（分区键为AltairCode） #

select c.AltairCode, c.ReportedDate, c.UpdatedDate, c.TimesheetStatus, c.DurationValue, c.DurationPercentageValue from c 
where (not is_defined(c.IsDeleted) or c.IsDeleted = false) and 
((c.AltairCode = '10128003' and c.ReportedDate >= '2023-12-01') or --返回4条文档
(c.AltairCode = '10125130' and c.ReportedDate >= '2023-11-01') or --返回9条文档
(c.AltairCode = '10127661' and c.ReportedDate >= '2023-06-01')) --返回5条文档

执行查询的.NET代码（已简化硬编码查询） #

public async Task<CosmosResultsResponseMessage<T>> QueryItems<T>(string sql, List<(string name, object value)> parameters = null)
{
    var container = _cosmosClient.GetContainer(_databaseName, _containerName);

    sql = "select c.AltairCode, c.ReportedDate, c.UpdatedDate, c.TimesheetStatus, c.DurationValue, c.DurationPercentageValue from c where (not is_defined(c.IsDeleted) or c.IsDeleted = false) and ((c.AltairCode = '10128003' and c.ReportedDate >= '2023-12-01') or (c.AltairCode = '10125130' and c.ReportedDate >= '2023-11-01') or (c.AltairCode = '10127661' and c.ReportedDate >= '2023-06-01'))";
    QueryDefinition query = new QueryDefinition(sql);

    var allItems = new List<T>();
    var result = new CosmosResultsResponseMessage<T>();
    
    using (var streamResultSet = container.GetItemQueryStreamIterator(query))
    {
        while (streamResultSet.HasMoreResults)
        {
            using (var responseMessage = await streamResultSet.ReadNextAsync())
            {
                result.IsSuccessStatusCode = responseMessage.IsSuccessStatusCode;
                result.StatusCode = responseMessage.StatusCode;
                result.ErrorMessage = responseMessage.ErrorMessage;

                if (responseMessage.IsSuccessStatusCode)
                {
                    var streamResponse = FromStream<dynamic>(responseMessage.Content);
                    List<T> items = streamResponse.Documents.ToObject<List<T>>();
                    allItems.AddRange(items);
                }
                else
                {
                    break;
                }
            }
        }
    }

    result.ResultItems = allItems;
    return result;
}

问题现象 #

这段代码会访问数据库3次而非1次，总共返回18条文档，3次访问均发生在streamResultSet.ReadNextAsync()调用时，且streamResultSet.HasMoreResults三次都返回true。

提问 #

如何让这个查询只做单次数据库访问？或者有没有其他方法可以从多个分区单次获取数据？

问题根源 #

当查询涉及多个分区时，Cosmos DB默认会针对每个目标分区发起独立的子查询，每个子查询的结果作为一个批次返回，这就是你看到3次访问的原因——对应3个分区的结果批次。

优化方案 #

1. 显式指定分区键列表并调整批次参数

通过QueryRequestOptions明确指定要查询的分区键值，同时设置MaxItemCount = -1允许服务返回尽可能多的结果（只要不超过单批次限制），让Cosmos DB并行处理分区查询并合并结果：

public async Task<CosmosResultsResponseMessage<T>> QueryItems<T>(string sql, List<(string name, object value)> parameters = null)
{
    var container = _cosmosClient.GetContainer(_databaseName, _containerName);

    sql = "select c.AltairCode, c.ReportedDate, c.UpdatedDate, c.TimesheetStatus, c.DurationValue, c.DurationPercentageValue from c where (not is_defined(c.IsDeleted) or c.IsDeleted = false) and ((c.AltairCode = '10128003' and c.ReportedDate >= '2023-12-01') or (c.AltairCode = '10125130' and c.ReportedDate >= '2023-11-01') or (c.AltairCode = '10127661' and c.ReportedDate >= '2023-06-01'))";
    QueryDefinition query = new QueryDefinition(sql);

    // 添加查询选项
    var queryOptions = new QueryRequestOptions
    {
        // 显式指定要查询的分区键值列表
        PartitionKey = new PartitionKey(new[] { "10128003", "10125130", "10127661" }),
        // 设置为-1允许服务返回所有符合条件的结果（不限制单批次数量）
        MaxItemCount = -1
    };

    var allItems = new List<T>();
    var result = new CosmosResultsResponseMessage<T>();
    
    // 传入查询选项
    using (var streamResultSet = container.GetItemQueryStreamIterator(query, requestOptions: queryOptions))
    {
        while (streamResultSet.HasMoreResults)
        {
            using (var responseMessage = await streamResultSet.ReadNextAsync())
            {
                result.IsSuccessStatusCode = responseMessage.IsSuccessStatusCode;
                result.StatusCode = responseMessage.StatusCode;
                result.ErrorMessage = responseMessage.ErrorMessage;

                if (responseMessage.IsSuccessStatusCode)
                {
                    var streamResponse = FromStream<dynamic>(responseMessage.Content);
                    List<T> items = streamResponse.Documents.ToObject<List<T>>();
                    allItems.AddRange(items);
                }
                else
                {
                    break;
                }
            }
        }
    }

    result.ResultItems = allItems;
    return result;
}

说明：显式指定分区键列表可以避免全分区扫描，MaxItemCount = -1让服务在单个响应中返回所有18条文档（远低于4MB的单响应大小限制），从而实现单次数据库访问获取全部结果。

2. 并行执行多个单分区查询

分别对每个分区发起单分区查询，通过Task.WhenAll并行执行，最后合并结果。这种方式虽然发起3次请求，但都是并行处理，总耗时可能比跨分区查询更短：

public async Task<CosmosResultsResponseMessage<T>> QueryItems<T>(string sql, List<(string name, object value)> parameters = null)
{
    var container = _cosmosClient.GetContainer(_databaseName, _containerName);
    var allItems = new List<T>();
    var result = new CosmosResultsResponseMessage<T>();

    // 定义每个分区的查询参数
    var partitionTasks = new List<Task<List<T>>>
    {
        FetchPartitionData<T>(container, "10128003", "2023-12-01"),
        FetchPartitionData<T>(container, "10125130", "2023-11-01"),
        FetchPartitionData<T>(container, "10127661", "2023-06-01")
    };

    // 并行执行所有分区查询
    var partitionResults = await Task.WhenAll(partitionTasks);
    allItems.AddRange(partitionResults.SelectMany(r => r));

    result.IsSuccessStatusCode = true;
    result.StatusCode = System.Net.HttpStatusCode.OK;
    result.ResultItems = allItems;
    return result;
}

// 封装单分区查询逻辑
private async Task<List<T>> FetchPartitionData<T>(Container container, string altairCode, string reportedDate)
{
    var query = new QueryDefinition(@"
        select c.AltairCode, c.ReportedDate, c.UpdatedDate, c.TimesheetStatus, c.DurationValue, c.DurationPercentageValue 
        from c 
        where (not is_defined(c.IsDeleted) or c.IsDeleted = false) 
        and c.AltairCode = @altairCode 
        and c.ReportedDate >= @reportedDate")
        .WithParameter("@altairCode", altairCode)
        .WithParameter("@reportedDate", reportedDate);

    var options = new QueryRequestOptions { PartitionKey = new PartitionKey(altairCode) };
    var items = new List<T>();

    using (var iterator = container.GetItemQueryStreamIterator(query, requestOptions: options))
    {
        while (iterator.HasMoreResults)
        {
            using (var response = await iterator.ReadNextAsync())
            {
                if (response.IsSuccessStatusCode)
                {
                    var streamResponse = FromStream<dynamic>(response.Content);
                    items.AddRange(streamResponse.Documents.ToObject<List<T>>());
                }
            }
        }
    }

    return items;
}

说明：这种方式逻辑更清晰，能精准控制每个分区的查询条件，且并行执行的效率更可控，适合需要针对不同分区设置不同查询参数的场景。

额外注意事项 #

• Cosmos DB单响应的大小上限为4MB，即使设置MaxItemCount = -1，如果结果超过这个限制，仍会拆分为多个批次。但你的场景中18条文档远低于此限制，无需担心。
• 显式指定分区键列表是跨分区查询的最佳实践，能避免不必要的分区扫描，降低RU消耗。

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