Cosmos DB SDK查询速度远慢于Azure门户，求排查问题原因

问题背景 #

为测试Cosmos DB搭建了POC环境：

• 容器含500个分区，每个分区对应120条数据，总计60000份5KB的文档
• 文档结构：

{
    "id": // 文档ID
    "SteamId": // 字符串类型的分区键
    "Profile": {
        // 对象内容
    }
}

• 定制索引策略仅包含SteamId字段：

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/SteamId/?"
        }
    ],
    "excludedPaths": [
        {
            "path": "/*"
        },
        {
            "path": "/\"_etag\"/?"
        }
    ]
}

在Azure门户执行查询SELECT c.Profile FROM c where c.SteamId = "76561198053126645"时，耗时仅1ms，需下载数据500KB；但用C# SDK（版本3.36，同区域部署）执行相同查询却耗时10秒，代码如下：

public async Task<IActionResult> test(string steamid)
{
    CosmosClient client = new(
        accountEndpoint: "endpoint",
        tokenCredential: new DefaultAzureCredential()
    );
    
    Database database = client.GetDatabase("items");
    Container container = database.GetContainer("items3");

    var stopwatch = new Stopwatch();
    var queryDefinition = new QueryDefinition($"SELECT c.Profile FROM c where c.SteamId = {steamid}");
    var iterator = container.GetItemQueryIterator<aaa>(queryDefinition,
        requestOptions: new QueryRequestOptions()
        {
            PartitionKey = new PartitionKey(steamid),
        });

    var results = new List<aaa>();

    stopwatch.Start();

    while (iterator.HasMoreResults)
    {
        var result = await iterator.ReadNextAsync();
        results.AddRange(result.Resource);
    }
    stopwatch.Stop();
    

    return Ok(new { items = results, time = stopwatch.ElapsedMilliseconds });
}

问题原因与修复 #

1. SQL字符串拼接导致语法错误，触发跨分区扫描 #

直接用$"SELECT c.Profile FROM c where c.SteamId = {steamid}"拼接查询语句，会生成不带引号的SQL（比如SELECT c.Profile FROM c where c.SteamId = 76561198053126645），Cosmos DB无法识别这是字符串类型的分区键值，即便指定了PartitionKey参数，也无法精准定位到目标分区，只能执行全表跨分区扫描，这是性能暴跌的核心原因。

修复方法：使用参数化查询，避免字符串拼接的语法问题：

var queryDefinition = new QueryDefinition("SELECT c.Profile FROM c where c.SteamId = @steamId")
    .WithParameter("@steamId", steamid);

2. 重复创建CosmosClient实例 #

每次请求都新建CosmosClient，而CosmosClient是线程安全的单例对象，重复创建会产生大量连接初始化、认证的额外开销，直接拉长请求耗时。

修复方法：将CosmosClient注册为单例，通过依赖注入复用实例：

// 在Program.cs中注册单例
builder.Services.AddSingleton<CosmosClient>(sp => 
    new CosmosClient("endpoint", new DefaultAzureCredential()));

// 在控制器中注入
private readonly CosmosClient _cosmosClient;
public YourController(CosmosClient cosmosClient)
{
    _cosmosClient = cosmosClient;
}

// 后续直接使用注入的_cosmosClient，不再重复创建

修复后的完整代码 #

private readonly CosmosClient _cosmosClient;

public YourController(CosmosClient cosmosClient)
{
    _cosmosClient = cosmosClient;
}

public async Task<IActionResult> test(string steamid)
{
    Database database = _cosmosClient.GetDatabase("items");
    Container container = database.GetContainer("items3");

    var stopwatch = new Stopwatch();
    // 参数化查询
    var queryDefinition = new QueryDefinition("SELECT c.Profile FROM c where c.SteamId = @steamId")
        .WithParameter("@steamId", steamid);
    
    var iterator = container.GetItemQueryIterator<aaa>(queryDefinition,
        requestOptions: new QueryRequestOptions()
        {
            PartitionKey = new PartitionKey(steamid),
        });

    var results = new List<aaa>();

    stopwatch.Start();

    while (iterator.HasMoreResults)
    {
        var result = await iterator.ReadNextAsync();
        results.AddRange(result.Resource);
    }
    stopwatch.Stop();
    

    return Ok(new { items = results, time = stopwatch.ElapsedMilliseconds });
}

额外优化建议 #

• 调整索引策略：当前索引仅包含SteamId，查询返回的Profile字段不在索引中，Cosmos DB需要读取完整文档后再提取内容。可以将/Profile/*加入includedPaths，让查询直接从索引返回结果，减少IO开销。
• 查看诊断信息：通过ReadNextAsync返回的FeedResponse.Diagnostics属性，查看查询的执行细节，确认是否已实现分区内精准查询。

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