mongoDB各种查询方式执行效率测试

lygyh9985825

　　最近正在使用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
　　第一种，第二种和第三种是差不多的。
　　第四种是最慢的，把全部的查找出来当然是最慢的。
　　第五种是最快的，我反复测试了很多次，真的有点不敢相信。
　　结论：建议尽量使用第五种方式；不建议使用第四种方式。
　　
　　以上是从使用的角度来测试。如有不同观点，支持回复指点，谢谢。
　　
　　以上测试的源码：





using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MongoDB.Driver;
using MongoDB.Driver.Builders;
using MongoDB.Driver.Linq;
using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
namespace MongoDBTest
{
class Program
{
static void Main(string[] args)
{
TestMongoDB();
Console.ReadKey();
}
///
/// 测试数据库
///
public static void TestMongoDB()
{
var connectionString = "mongodb://localhost";
var client = new MongoClient(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(int testCount, 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 select a).Max();
var min = (from a in list select a).Min();
var avg = (from a in list select a).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(string result)
{
var f = new System.IO.StreamWriter("result.txt", false);
f.WriteLine(result);
f.Close();
}
}
public class User
{
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