mongodb 搜索速度_mongoDB各种查询方式执行效率测试

最近正在使用mongoDB，使用官方的C# Drivers，对于不同的方式执行的效率(时间)不一样的。

下面我们开始进入主题 ：

启动本地mongoDB数据库

> mongod -dbpath data

现在数据库有一个User表，大约有100万行数据。

>db.myuser.count()> 1000005

其中，age字段已设置为索引

> db.entities.ensureIndex({"age":1})

我们一起来统计某些用户的最大年龄，符合条件的有21845条记录

> db.entities.find({"age":{$lt:12}})> 21845

以下使用c# drivers的方式：

第一种方式：

TestMethod(testCount, () =>{var querty = Query.LT(c => c.Age, 12);var collection1 = database.GetCollection("entities");

collection1.Find(querty).Max(c=>c.Age);

});

语句监控：

{ "op" : "query", "ns" : "test.entities", "query" : { "age" : { "$lt" : 12 } }, "cursorid" : 9328463997967889, "ntoreturn" : 0, "ntoskip" : 0, "nscanned" : 102, "keyUpdates" : 0, "numYield" : 0, "lockStats" : { "timeLockedMicros" : { "r" : 321, "w" : 0 }, "timeAcquiringMicros" : { "r" : 5, "w" : 2 } }, "nreturned" : 101, "responseLength" : 7595, "millis" : 0, "ts" : { "$date" : 1370402901213 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : { "age" : { "$lt" : 12 } }, "cursorid" : 9328463997967889, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 34, "lockStats" : { "timeLockedMicros" : { "r" : 61549, "w" : 0 }, "timeAcquiringMicros" : { "r" : 57279, "w" : 2 } }, "nreturned" : 21744, "responseLength" : 1630820, "millis" : 59, "ts" : { "$date" : 1370402901309 }, "client" : "127.0.0.1", "allUsers" : [], "user" : "" }

执行结果：test total:10 max:2251.1288ms min:1061.0607ms avg:1791.50248ms

第二种方式 ：

TestMethod(testCount, () =>{var querty = Query.LT(c => c.Age, 12);var collection2 = database.GetCollection("entities");

(from a in collection2.Find(querty) select a).Max(c =>c.Age);

});

语句监控：

{ "op" : "query", "ns" : "test.entities", "query" : { "age" : { "$lt" : 12 } }, "cursorid" : 10027822751969126, "ntoreturn" : 0, "ntoskip" : 0, "nscanned" : 102, "keyUpdates" : 0, "numYield" : 0, "lockStats" : { "timeLockedMicros" : { "r" : 316, "w" : 0 }, "timeAcquiringMicros" : { "r" : 3, "w" : 2 } }, "nreturned" : 101, "responseLength" : 7595, "millis" : 0, "ts" : { "$date" : 1370403065009 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : { "age" : { "$lt" : 12 } }, "cursorid" : 10027822751969126, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 153, "lockStats" : { "timeLockedMicros" : { "r" : 81511, "w" : 0 }, "timeAcquiringMicros" : { "r" : 166793, "w" : 2 } }, "nreturned" : 21744, "responseLength" : 1630820, "millis" : 167, "ts" : { "$date" : 1370403065220 }, "client" : "127.0.0.1", "allUsers" : [], "user" : "" }

执行结果：test total:10 max:2318.1326ms min:1224.07ms avg:1843.20543ms

第三种方式：

TestMethod(testCount, () =>{var querty = Query.LT(c => c.Age, 12);var collection2 = database.GetCollection("entities");

(from a in collection2.Find(querty).ToList() select a).Max(c =>c.Age);

});

语句监控：

{ "op" : "query", "ns" : "test.entities", "query" : { "age" : { "$lt" : 12 } }, "cursorid" : 10180173527525485, "ntoreturn" : 0, "ntoskip" : 0, "nscanned" : 102, "keyUpdates" : 0, "numYield" : 0, "lockStats" : { "timeLockedMicros" : { "r" : 338, "w" : 0 }, "timeAcquiringMicros" : { "r" : 5, "w" : 3 } }, "nreturned" : 101, "responseLength" : 7595, "millis" : 0, "ts" : { "$date" : 1370403100720 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : { "age" : { "$lt" : 12 } }, "cursorid" : 10180173527525485, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 71, "lockStats" : { "timeLockedMicros" : { "r" : 71335, "w" : 0 }, "timeAcquiringMicros" : { "r" : 89772, "w" : 3 } }, "nreturned" : 21744, "responseLength" : 1630820, "millis" : 93, "ts" : { "$date" : 1370403100862 }, "client" : "127.0.0.1", "allUsers" : [], "user" : "" }

执行结果：test total:10 max:2349.1344ms min:1079.0618ms avg:1824.10434ms

第四种方式：

TestMethod(testCount, () =>{var collection3 = database.GetCollection("entities");

(from a in collection3.FindAll() where a.Age < 12 select a).Max(c =>c.Age);

});

语句监控:

{ "op" : "query", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "ntoskip" : 0, "nscanned" : 102, "keyUpdates" : 0, "numYield" : 0, "lockStats" : { "timeLockedMicros" : { "r" : 101, "w" : 0 }, "timeAcquiringMicros" : { "r" : 4, "w" : 2 } }, "nreturned" : 101, "responseLength" : 7595, "millis" : 0, "ts" : { "$date" : 1370403130218 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 208, "lockStats" : { "timeLockedMicros" : { "r" : 36522, "w" : 0 }, "timeAcquiringMicros" : { "r" : 224943, "w" : 2 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 225, "ts" : { "$date" : 1370403130487 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 354, "lockStats" : { "timeLockedMicros" : { "r" : 69849, "w" : 0 }, "timeAcquiringMicros" : { "r" : 372646, "w" : 6 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 373, "ts" : { "$date" : 1370403131520 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 263, "lockStats" : { "timeLockedMicros" : { "r" : 57171, "w" : 0 }, "timeAcquiringMicros" : { "r" : 275690, "w" : 8 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 287, "ts" : { "$date" : 1370403132514 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 118, "lockStats" : { "timeLockedMicros" : { "r" : 42943, "w" : 0 }, "timeAcquiringMicros" : { "r" : 126115, "w" : 8 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 142, "ts" : { "$date" : 1370403133385 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 118, "lockStats" : { "timeLockedMicros" : { "r" : 54060, "w" : 0 }, "timeAcquiringMicros" : { "r" : 139954, "w" : 4 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 140, "ts" : { "$date" : 1370403134246 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 182, "lockStats" : { "timeLockedMicros" : { "r" : 49463, "w" : 0 }, "timeAcquiringMicros" : { "r" : 192366, "w" : 8 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 205, "ts" : { "$date" : 1370403135151 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 233, "lockStats" : { "timeLockedMicros" : { "r" : 47115, "w" : 0 }, "timeAcquiringMicros" : { "r" : 241311, "w" : 6 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 253, "ts" : { "$date" : 1370403136126 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 374, "lockStats" : { "timeLockedMicros" : { "r" : 63443, "w" : 0 }, "timeAcquiringMicros" : { "r" : 390961, "w" : 5 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 391, "ts" : { "$date" : 1370403137212 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 220, "lockStats" : { "timeLockedMicros" : { "r" : 49987, "w" : 0 }, "timeAcquiringMicros" : { "r" : 226603, "w" : 7 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 246, "ts" : { "$date" : 1370403138151 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 174, "lockStats" : { "timeLockedMicros" : { "r" : 51115, "w" : 0 }, "timeAcquiringMicros" : { "r" : 189192, "w" : 7 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 195, "ts" : { "$date" : 1370403139076 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 0, "lockStats" : { "timeLockedMicros" : { "r" : 12058, "w" : 0 }, "timeAcquiringMicros" : { "r" : 4, "w" : 5 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 12, "ts" : { "$date" : 1370403139769 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 58, "lockStats" : { "timeLockedMicros" : { "r" : 19060, "w" : 0 }, "timeAcquiringMicros" : { "r" : 59983, "w" : 5 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 69, "ts" : { "$date" : 1370403140528 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 80, "lockStats" : { "timeLockedMicros" : { "r" : 47191, "w" : 0 }, "timeAcquiringMicros" : { "r" : 99542, "w" : 4 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 99, "ts" : { "$date" : 1370403141285 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 220, "lockStats" : { "timeLockedMicros" : { "r" : 48911, "w" : 0 }, "timeAcquiringMicros" : { "r" : 228575, "w" : 6 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 245, "ts" : { "$date" : 1370403142248 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 233, "lockStats" : { "timeLockedMicros" : { "r" : 58459, "w" : 0 }, "timeAcquiringMicros" : { "r" : 251144, "w" : 4 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 254, "ts" : { "$date" : 1370403143181 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 182, "lockStats" : { "timeLockedMicros" : { "r" : 54177, "w" : 0 }, "timeAcquiringMicros" : { "r" : 200802, "w" : 4 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 203, "ts" : { "$date" : 1370403144116 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 115, "lockStats" : { "timeLockedMicros" : { "r" : 36174, "w" : 0 }, "timeAcquiringMicros" : { "r" : 121411, "w" : 8 } }, "nreturned" : 55924, "responseLength" : 4194320, "millis" : 136, "ts" : { "$date" : 1370403144950 }, "client" : "127.0.0.1", "allUsers" : [], "user" : ""}

{"op" : "getmore", "ns" : "test.entities", "query" : {}, "cursorid" : 10306109926130251, "ntoreturn" : 0, "keyUpdates" : 0, "numYield" : 306, "lockStats" : { "timeLockedMicros" : { "r" : 38883, "w" : 0 }, "timeAcquiringMicros" : { "r" : 315199, "w" : 6 } }, "nreturned" : 49191, "responseLength" : 3689345, "millis" : 321, "ts" : { "$date" : 1370403145984 }, "client" : "127.0.0.1", "allUsers" : [], "user" : "" }

执行结果：test total:10 max:87233.9895ms min:70624.0395ms avg:81305.95043ms

第五种方式：

TestMethod(testCount, () =>{var collection3 = database.GetCollection("entities");

(from a in collection3.AsQueryable() where a.Age < 12 select a).Max(c =>c.Age);

});

语句监控：

{ "op" : "query", "ns" : "test.entities", "query" : { "$query" : { "age" : { "$lt" : 12 } }, "$orderby" : { "age" : -1 } }, "ntoreturn" : 1, "ntoskip" : 0, "nscanned" : 1, "keyUpdates" : 0, "numYield" : 0, "lockStats" : { "timeLockedMicros" : { "r" : 216, "w" : 0 }, "timeAcquiringMicros" : { "r" : 4, "w" : 3 } }, "nreturned" : 1, "responseLength" : 95, "millis" : 0, "ts" : { "$date" : 1370403374870 }, "client" : "127.0.0.1", "allUsers" : [], "user" : "" }

执行结果：test total:10 max:449.0256ms min:327.0187ms avg:407.5233ms

测试完毕，我们一起讨论一下结果:

第一种方式：test total:10 max:2251.1288ms min:1061.0607ms avg:1791.50248ms

第二种方式：test total:10 max:2318.1326ms min:1224.07ms avg:1843.20543ms

第三种方式：test total:10 max:2349.1344ms min:1079.0618ms avg:1824.10434ms

第四种方式：test total:10 max:87233.9895ms min:70624.0395ms avg:81305.95043ms

第五种方式：test total:10 max:449.0256ms min:327.0187ms avg:407.5233ms

第一种，第二种和第三种是差不多的。

第四种是最慢的，把全部的查找出来当然是最慢的。

第五种是最快的，我反复测试了很多次，真的有点不敢相信。

结论：建议尽量使用第五种方式；不建议使用第四种方式。

以上是从使用的角度来测试。如有不同观点，支持回复指点，谢谢。

以上测试的源码：

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingMongoDB.Driver;usingMongoDB.Driver.Builders;usingMongoDB.Driver.Linq;usingMongoDB.Bson;usingMongoDB.Bson.Serialization.Attributes;namespaceMongoDBTest

{classProgram

{static void Main(string[] args)

{

TestMongoDB();

Console.ReadKey();

}///

///测试数据库///

public static voidTestMongoDB()

{var connectionString = "mongodb://localhost";var client = newMongoClient(connectionString);var server =client.GetServer();var database = server.GetDatabase("test");var testCount = 10;

TestMethod(testCount, ()=>{var querty = Query.LT(c => c.Age, 12);var collection1 = database.GetCollection("entities");

collection1.Find(querty).Max(c=>c.Age);

});//TestMethod(testCount, () =>//{//var querty = Query.LT(c => c.Age, 12);//var collection2 = database.GetCollection("entities");//(from a in collection2.Find(querty) select a).Max(c => c.Age);//});//TestMethod(testCount, () =>//{//var querty = Query.LT(c => c.Age, 12);//var collection2 = database.GetCollection("entities");//(from a in collection2.Find(querty).ToList() select a).Max(c => c.Age);//});//TestMethod(testCount, () =>//{//var collection3 = database.GetCollection("entities");//(from a in collection3.FindAll() where a.Age < 12 select a).Max(c => c.Age);//});//TestMethod(testCount, () =>//{//var collection3 = database.GetCollection("entities");//(from a in collection3.AsQueryable() where a.Age < 12 select a).Max(c => c.Age);//});

}///

///测试方法///

///

///

private static void TestMethod(inttestCount, Action fun)

{var list = new List();for (int i = 0; i < testCount; i++)

{new System.Threading.Thread(new System.Threading.ThreadStart(() =>{var sd =DateTime.Now;

fun.Invoke();

list.Add(DateTime.Now.Subtract(sd).TotalMilliseconds);if (list.Count() >=testCount)

{var max = (from a in list selecta).Max();var min = (from a in list selecta).Min();var avg = (from a in list selecta).Average();var result = string.Format("test total:{0} max:{1}ms min:{2}ms avg:{3}ms", testCount, max, min, avg);

WriteText(result);

System.Diagnostics.Process.GetCurrentProcess().Kill();

}

})).Start();

}

}///

///写文件文本///

///

private static void WriteText(stringresult)

{var f = new System.IO.StreamWriter("result.txt", false);

f.WriteLine(result);

f.Close();

}

}public classUser

{public ObjectId Id { get; set; }

[BsonElement("name")]public string Name { get; set; }

[BsonElement("age")]public int Age { get; set; }

[BsonElement("pwd")]public string Pwd { get; set; }

[BsonIgnoreIfNull]

[BsonElement("addr")]public string Address { get; set; }

[BsonIgnoreIfDefault]

[BsonElement("wei")]public int Weight { get; set; }

}

}

View Code