MongoDB $or对自身的查询要快得多
MongoDB $or对自身的查询要快得多
提问于 2016-03-04 23:59:02
我有一个蒙戈的例子，里面有1600万份文件。我写了一个查询来搜索其中一个(索引)字段，我得到了一些奇怪的结果，我无法解释。
如果我直接执行以下查询：
find({ "$and" : [ { "ipAddr" : { "$regex" : "^01:172"}} , { "active" : true}]}).limit(100).sort({ "_id" : 1})甚至在查询中添加一个无意义的$or：
find({ "$and" : [ { "$or" : [ { "ipAddr" : { "$regex" : "^01:172"}}]} , { "active" : true}]}).limit(100).sort({ "_id" : 1})它返回71673 in中的3条记录。
但是，如果我对自己使用$or，比如：
find({ "$and" : [ { "$or" : [ { "ipAddr" : { "$regex" : "^01:172"}} , { "ipAddr" : { "$regex" : "^01:172"}}]} , { "active" : true}]}).limit(100).sort({ "_id" : 1})返回:以4ms为单位获取3条记录
所以性能差异很大。通过检查查询上的explain()，我无法确定为什么存在如此大的性能差异。有谁能说明我错过了什么，或者蒙戈在这两者之间做了什么不同的事情？
在单个$or上解释()，花费超过60万on
find({ "$and" : [ { "$or" : [ { "ipAddr" : { "$regex" : "^01:172"}}]} , { "active" : true}]}).limit(100).sort({ "_id" : 1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "CLS-TEST.Leases",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"active" : {
"$eq" : true
}
},
{
"ipAddr" : /^01:172/
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"active" : {
"$eq" : true
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"ipAddr" : 1
},
"indexName" : "ipAddr_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"ipAddr" : [
"[\"01:172\", \"01:173\")",
"[/^01:172/, /^01:172/]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"ipAddr" : /^01:172/
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"sessionId" : 1,
"updateTime" : 1
},
"indexName" : "active_1_sessionId_1_updateTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"sessionId" : [
"[MinKey, MaxKey]"
],
"updateTime" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"ipAddr" : /^01:172/
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"clientId" : 1,
"startTime" : -1,
"_id" : -1
},
"indexName" : "active_1_clientId_1_startTime_-1__id_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"clientId" : [
"[MinKey, MaxKey]"
],
"startTime" : [
"[MaxKey, MinKey]"
],
"_id" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"ipAddr" : 1,
"startTime" : -1,
"_id" : -1
},
"indexName" : "active_1_ipAddr_1_startTime_-1__id_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"ipAddr" : [
"[\"01:172\", \"01:173\")",
"[/^01:172/, /^01:172/]"
],
"startTime" : [
"[MaxKey, MinKey]"
],
"_id" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"ipAddr" : /^01:172/
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"macAddress" : 1,
"startTime" : -1,
"_id" : -1
},
"indexName" : "active_1_macAddress_1_startTime_-1__id_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"macAddress" : [
"[MinKey, MaxKey]"
],
"startTime" : [
"[MaxKey, MinKey]"
],
"_id" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"ipAddr" : /^01:172/
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"remoteId" : 1,
"startTime" : -1,
"_id" : -1
},
"indexName" : "active_1_remoteId_1_startTime_-1__id_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"remoteId" : [
"[MinKey, MaxKey]"
],
"startTime" : [
"[MaxKey, MinKey]"
],
"_id" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 100,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"active" : {
"$eq" : true
}
},
{
"ipAddr" : /^01:172/
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_id" : 1
},
"indexName" : "_id_",
"isMultiKey" : false,
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"_id" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
},
"serverInfo" : {
"host" : "",
"port" : 27017,
"version" : "3.2.3",
"gitVersion" : "b326ba837cf6f49d65c2f85e1b70f6f31ece7937"
},
"ok" : 1
}在$or上对自己解释()，它需要<50 on
find({ "$and" : [ { "$or" : [ { "ipAddr" : { "$regex" : "^01:172"}} , { "ipAddr" : { "$regex" : "^01:172"}}]} , { "active" : true}]}).limit(100).sort({ "_id" : 1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "CLS-TEST.Leases",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"$or" : [
{
"ipAddr" : /^01:172/
},
{
"ipAddr" : /^01:172/
}
]
},
{
"active" : {
"$eq" : true
}
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"active" : {
"$eq" : true
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"ipAddr" : 1
},
"indexName" : "ipAddr_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"ipAddr" : [
"[\"01:172\", \"01:173\")",
"[/^01:172/, /^01:172/]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"ipAddr" : /^01:172/
},
{
"ipAddr" : /^01:172/
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"sessionId" : 1,
"updateTime" : 1
},
"indexName" : "active_1_sessionId_1_updateTime_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"sessionId" : [
"[MinKey, MaxKey]"
],
"updateTime" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"ipAddr" : /^01:172/
},
{
"ipAddr" : /^01:172/
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"clientId" : 1,
"startTime" : -1,
"_id" : -1
},
"indexName" : "active_1_clientId_1_startTime_-1__id_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"clientId" : [
"[MinKey, MaxKey]"
],
"startTime" : [
"[MaxKey, MinKey]"
],
"_id" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"ipAddr" : /^01:172/
},
{
"ipAddr" : /^01:172/
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"ipAddr" : 1,
"startTime" : -1,
"_id" : -1
},
"indexName" : "active_1_ipAddr_1_startTime_-1__id_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"ipAddr" : [
"[MinKey, MaxKey]"
],
"startTime" : [
"[MaxKey, MinKey]"
],
"_id" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"ipAddr" : /^01:172/
},
{
"ipAddr" : /^01:172/
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"macAddress" : 1,
"startTime" : -1,
"_id" : -1
},
"indexName" : "active_1_macAddress_1_startTime_-1__id_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"macAddress" : [
"[MinKey, MaxKey]"
],
"startTime" : [
"[MaxKey, MinKey]"
],
"_id" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"ipAddr" : /^01:172/
},
{
"ipAddr" : /^01:172/
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"active" : 1,
"remoteId" : 1,
"startTime" : -1,
"_id" : -1
},
"indexName" : "active_1_remoteId_1_startTime_-1__id_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"active" : [
"[true, true]"
],
"remoteId" : [
"[MinKey, MaxKey]"
],
"startTime" : [
"[MaxKey, MinKey]"
],
"_id" : [
"[MaxKey, MinKey]"
]
}
}
}
}
},
{
"stage" : "LIMIT",
"limitAmount" : 100,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"$or" : [
{
"ipAddr" : /^01:172/
},
{
"ipAddr" : /^01:172/
}
]
},
{
"active" : {
"$eq" : true
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_id" : 1
},
"indexName" : "_id_",
"isMultiKey" : false,
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"_id" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
},
"serverInfo" : {
"host" : "",
"port" : 27017,
"version" : "3.2.3",
"gitVersion" : "b326ba837cf6f49d65c2f85e1b70f6f31ece7937"
},
"ok" : 1
}回答 1
Stack Overflow用户
回答已采纳
发布于 2016-03-05 00:25:37
您可能会注意到，没有一个索引选择包括"active"和"ipAddr"的组合，这将是在这里定义的有用的索引。
简而言之，“较慢”的查询只使用"ipAddr"的索引，因此需要更多的工作才能“过滤”{ "active": true }条目。
显然，当其他索引选择使用带有这些边界的"active"键时，传递给正则表达式模式的后续筛选器的结果较少。这里似乎有相当多的索引，其中没有一个是真正适合查询的。
我将为您提供至少在这两个查询上运行"explain“输出的支持，但是如果仔细观察，您会发现”慢速“查询”错误地“选择了"ipAddr"索引，认为它是最优的。可能不是，但对于优化者来说，考虑使用“锚定”正则表达式是一个合理的假设。
$or强制使用“索引相交”，当$or中只有“一个”参数时，不这样做是很明智的。“两个”参数使这种情况发生，优化器通过查找与其他查询条件( "active"值)前面的索引进行另一个“猜测”。
这是有意义的，因为现在运行的是“两个”查询，它将从其中“交叉”结果，因此，$or语句之外的任何条件都是最优选择索引的逻辑选择。
由于从这些结果返回的结果可能较小，因此“筛选”出regex匹配比查看所有regex结果和筛选出“活动”值更快。
因此，为该查询定义的“最佳”索引是：
.createIndex({ "active": 1, "ipAddr": 1 })然后，这两个查询的结果都是一致的，当然，优化器不会被其他索引混淆并选择那个索引。若要强制索引选择，请使用.hint()
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/35808234
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号