为何C#中MongoDB.Driver查询远慢于MongoDB Compass？

问题概述 #

我设计了一个MongoDB数据库，使用C#脚本执行查询时速度极慢，多次测试平均耗时约6116ms，但相同查询在MongoDB Compass中瞬间出结果。切换ToListAsync()方法后性能无改善，不清楚问题所在。

测试代码如下：

using System;
using System.Diagnostics;
using MongoDB.Bson;
using MongoDB.Driver;

class Program
{
    static void Main()
    {
        // Connect to MongoDB
        var connectionString = "mongodb://localhost:27017"; // Update with your MongoDB connection string
        var client = new MongoClient(connectionString);
        var databaseName = "Database"; // Update with your database name
        var collectionName = "MachineLog"; // Update with your collection name
        var database = client.GetDatabase(databaseName);
        var collection = database.GetCollection<BsonDocument>(collectionName);

        // Set limit of results and number of iterations
        var limit = 691200; // Update with desired limit of results
        var numIterations = 3; // Update with desired number of iterations

        // Define filter criteria
        var filter = Builders<BsonDocument>.Filter.Eq("MachineId", "64355f06d1478b55c0446d7c"); // Update with your filter criteria
        //var filter = Builders<BsonDocument>.Filter.Empty;

        // Perform connection establishment outside of stopwatch
        collection.Find(filter).Limit(1).ToList(); // Perform a dummy query to establish the connection

        // Perform multiple iterations and calculate average time
        var stopwatch = new Stopwatch();
        long totalElapsedTime = 0;

        for (int i = 0; i < numIterations; i++)
        {
            // Start timing
            stopwatch.Start();

            // Perform database operation(s) here
            // Example: Query documents with limit and filter
            var documents = collection.Find(filter).Limit(limit).ToList();

            // Stop timing
            stopwatch.Stop();
            var elapsedTime = stopwatch.ElapsedMilliseconds;
            totalElapsedTime += elapsedTime;

            // Reset stopwatch for next iteration
            stopwatch.Reset();

            // Print elapsed time for this iteration
            Console.WriteLine($"Iteration {i + 1}: Elapsed Time: {elapsedTime} ms");
        }

        // Calculate average time
        var averageElapsedTime = totalElapsedTime / numIterations;
        Console.WriteLine($"Average Elapsed Time: {averageElapsedTime} ms");
    }
}

测试结果：

Iteration 1: Elapsed Time: 6279 ms
Iteration 2: Elapsed Time: 6056 ms
Iteration 3: Elapsed Time: 6014 ms
Average Elapsed Time: 6116 ms

可能的原因及解决方法 #

1. 索引未生效 #

Compass会自动选择最优索引，但C#驱动可能因查询构造问题未命中索引。先在MongoDB Shell中执行执行计划：

db.MachineLog.explain("executionStats").find({MachineId: "64355f06d1478b55c0446d7c"}).limit(691200)

查看executionStats.totalDocsExamined，如果数值远大于返回的691200条，说明未走索引，立即给MachineId创建索引：

db.MachineLog.createIndex({MachineId: 1})

2. BsonDocument序列化开销过大 #

使用BsonDocument的序列化/反序列化开销远高于强类型实体，试试定义对应实体类：

public class MachineLog
{
    public ObjectId Id { get; set; }
    public string MachineId { get; set; }
    // 补充其他字段
}

然后将集合替换为GetCollection<MachineLog>，减少序列化耗时。

3. 一次性拉取海量数据 #

Compass不会一次性加载所有数据，而是分页渲染；但你的代码直接将69万条数据全量拉取到内存，包含了数据传输和内存分配的时间。尝试分批查询：

var skip = 0;
var batchSize = 1000;
while (true)
{
    var batch = collection.Find(filter).Skip(skip).Limit(batchSize).ToList();
    if (batch.Count == 0) break;
    skip += batchSize;
    // 处理当前批次数据
}

对比分批查询的总耗时，会比一次性拉取更合理。

4. 驱动版本或连接配置问题 #

• 检查MongoDB C#驱动是否为最新版本，旧版本可能存在性能缺陷；
• 优化连接字符串配置，比如调整连接池大小：

  mongodb://localhost:27017?maxPoolSize=100&minPoolSize=10
  

5. 计时逻辑未区分查询与数据传输 #

当前计时包含了从服务器拉取所有数据到本地的完整过程，而Compass的"瞬间"仅加载了前几页数据。拆分计时明确瓶颈：

stopwatch.Start();
var cursor = collection.Find(filter).Limit(limit); // 仅构造查询，未执行
stopwatch.Stop();
Console.WriteLine($"查询构造耗时: {stopwatch.ElapsedMilliseconds} ms");
stopwatch.Reset();

stopwatch.Start();
var documents = cursor.ToList(); // 实际拉取数据
stopwatch.Stop();
Console.WriteLine($"数据拉取耗时: {stopwatch.ElapsedMilliseconds} ms");

内容的提问来源于stack exchange，提问作者Nick de Boer