方法:
1、创建索引,根据索引查询 效果:查询时间由675毫秒降低为1毫秒
2、优化查询条件顺序 效果:查询时间可以大幅降低
mongodb 创建索引的命令(默认创建可重复索引):
db.bj_T_RealTimeData.ensureIndex({"transformer_Id":1}) //建立索引
显示已经创建的索引命令:
db.bj_T_RealTimeData.getIndexes()
删除索引命令:
db.bj_T_RealTimeData.dropIndexs( {"transformer_Id":1}
测试方法:
var time1 = DateTime.Now; //测试
var pipeline = new[] { match, match3, match4 };
MongoCollection dbCollection = mymongodb.getCollection("bj_T_RealTimeData");
var result = dbCollection.Aggregate(pipeline);
if (result.ResultDocuments.Select(BsonSerializer.Deserialize<bj_T_RealTimeData>).Count() > 0)
{
var dbCollectiondata = result.ResultDocuments.Select(BsonSerializer.Deserialize<bj_T_RealTimeData>).First();
var time2 = DateTime.Now; //测试
var timeD = time2 - time1;//数据查询时间
测试用例:
1、创建索引
var match = new BsonDocument //mongodb 查询语句 等价于 select * from where temperature_e=1
{
{
"$match",
new BsonDocument
{
{"transformer_Id",strcon}
}
}
};
var match3 = new BsonDocument //mongodb 查询语句 等价于 select * from order by _id asc
{
{
"$sort",
new BsonDocument
{
{"CalDate",-1} //-1降序,1升序
//{"_id",-1} //-1降序,1升序
}
}
};
var match4 = new BsonDocument //mongodb 查询语句 等价于 select * from order by _id asc
{
{
"$limit",1
}
};
var pipeline = new[] { match, match3, match4 };
var time3 = DateTime.Now; //测试
var result_realtimedata = dbCollection1.Aggregate(pipeline);
var time4 = DateTime.Now; //测试
var num_realtimedata = result_realtimedata.ResultDocuments.Select(BsonSerializer.Deserialize<bj_T_RealTimeData>).Count();
var time5 = time4 - time3;//测试
把CalDate创建为索引,根据CalDate降序排列,可以大幅降低查询时间,由675ms减少为1ms
2、优化查询条件顺序
var match5 = new BsonDocument //mongodb 查询语句 等价于 select * from where temperature_e=1
{
{
"$match",
new BsonDocument
{
{"transformer_Id",strcon}
}
}
};
var match6 = new BsonDocument //mongodb 查询语句 等价于 select * from where CalDate =dbCollectiondata.CalDate
{
{
"$match",
new BsonDocument
{
{"CalDate",dbCollectiondata.CalDate}
}
}
};
pipeline_realtimedata = new BsonDocument[] { match5, match6};
var result_realtimedata = dbCollection1.Aggregate(pipeline_realtimedata); //耗时605ms
pipeline_realtimedata = new BsonDocument[] { match6, match5 };
var result_realtimedata = dbCollection1.Aggregate(pipeline_realtimedata); //耗时1ms
以上说明多条件查询时,条件顺序影响查询速度。原因是:数据库中满足match5条件的数据较多,满足match6条件的数据只有几条,当查询条件{ match5, match6}这样排列,查询时先把满足match5的数据筛选出来(得到的数据较多),再从筛选的数据查询满足matc6的数据。当查询条件{ match6, match5}这样排列,查询时先把满足match6的数据筛选出来(得到的数据较少),再从筛选的数据查询满足matc5的数据,这样查询速度就快了。
var match1 = new BsonDocument //mongodb 查询语句 等价于 select * from where CalDate > sTime
{
{
"$match",
new BsonDocument
{
{"CalDate", new BsonDocument
{
{
"$gt", timeS1
}
}}
}
}
};
var match2 = new BsonDocument //mongodb 查询语句 等价于 select * from where CalDate > sTime
{
{
"$match",
new BsonDocument
{
{"CalDate", new BsonDocument
{
{
"$lt", timeE1
}
}}
}
}
};
var match3 = new BsonDocument //mongodb 查询语句 等价于 select * from where temperature_e=1
{
{
"$match",
new BsonDocument
{
{"transformer_Id",sName}
}
}
};
var match4 = new BsonDocument
{
{
"$project",
new BsonDocument
{
{"_id",0},
{"CalDate",1},
{"temperature_o",1},
{"cal_Temperature_Hp",1},
{"temperature_e",1},
{"kh",1},
{"k",1},
{"kl",1}
}
}
};
var match5 = new BsonDocument //mongodb 查询语句 等价于 select * from order by _id asc
{
{
"$sort",
new BsonDocument
{
{"CalDate",1} //-1降序,1升序
}
}
};
var pipeline = new[] { match1, match2}; //查询条件按这个顺序排序,查询效率较高 253ms
//var pipeline = new[] { match2, match1}; //查询条件按这个顺序排序,查询效率较低 2070ms
pipeline_realtimedata = new BsonDocument[] { match5, match6};
//实验条件:时间段timeS1 timeE1是今天,原因是:{ match1, match2}查询时先把满足match1的数据筛选出来(得到的数据较少),再筛选满足match2的数据,消耗的时间较短;而 { match2, match1}查询时先把满足match1的数据筛选出来(得到的数据较多),再筛选满足match2的数据,消耗的时间较长。