問題描述
使用pymongo在mongodb中多次查詢單個字段的執行速度 (Querying single field multiple times execution speed in mongodb using pymongo)
pymongo 中的兩個查詢的執行時間是否存在差異：
db.collection.find({'date': {'$gte': datetime(2000, 01, 01), '$lt': datetime(2016, 11, 23)}})
db.collection.find({'date': {'$gte': datetime(2000, 01, 01)}, 'date': {'$lt': datetime(2016, 11, 23)}})
在第二種情況下，我查詢同一個字段兩次。我使用 mongo shell 檢查並運行 .explain("executionStats") 唯一的區別是查詢 ("queryPlanner.parsedQuery") 是第一個查詢：
"$and" : [
{
"date" : {
"$lt" : ISODate("2016‑11‑23T00:00:00Z")
}
},
{
"date" : {
"$gte" : ISODate("2000‑01‑01T00:00:00Z")
}
}
]
這是第二個：
"date" : {
"$lt" : ISODate("2016‑11‑23T00:00:00Z")
}
我正在查詢一個包含數万個文檔的集合，並且被多次查詢。所以，我需要優化查詢。
參考解法
方法 1:
If you look closely at your second query ‑
{'date': {'$gte': datetime(2000, 01, 01)}, 'date': {'$lt': datetime(2016, 11, 23)}}
You should observe that this is an object(dict in python) having keys date and date, which of course makes no sense. You are trying to create an object with same keys. Thus, only one of them will be valid (the second one in this case). Thus the query to mongo is {date': {'$lt': datetime(2016, 11, 23)}} which wont give you the desired output.
I would suggest that you should define the query in terms of logical operators as clearly as possible. This would be the output of the explain stage in this case. Thus using $and to express two different conditions should be the best way to query.
{"$and" : [
{"date" : {"$lt" : ISODate("2016‑11‑23T00:00:00Z")}},
{"date" : {"$gte" : ISODate("2000‑01‑01T00:00:00Z")}}
]}
Coming to the topic of how to make the query faster, you should be using an index on date. Defining this index as a individual index {date: 1} would help narrow down to the relevant docs.
(by Lokesh Meher、hyades)