mongodb $in 查询语句性能优化问题

Question

数据：大概650万条，实际查询花费12秒，该做的索引都做了，12秒太长了。查询语句：
db.attach.find({data_id:
{'$in':
[ObjectId('595b7d3c0b192c196eb84f87'),
ObjectId('595b7d29a2a75c18af7eff2d'),
ObjectId('595b7d21adb12b0f86224acc'),
ObjectId('595b7d120031d40f881634b8'),
ObjectId('595b7d2c88ce6d18afd34dab'),
ObjectId('595b7d2a36f526176ba21156'),
ObjectId('595b7d1b47c347196d6d26f5'),
ObjectId('595b7d1372321718a937f649'),
ObjectId('595b7d25fda0d80f289ed24b'),
ObjectId('595b7d28ad1a8118a85ff575'),
ObjectId('595b7d268d9cfb17b285dff3'),
ObjectId('595b7d18adb12b0f8622472d'),
ObjectId('595b7d130630360f7f8ded79'),
ObjectId('595b7d22b5077e1a668c189d'),
ObjectId('595b7d23ab191119de23b9a3'),
ObjectId('595b7d1eadb12b0f86224a6d'),
ObjectId('595b7d1c72cf8718a10beec9'),
ObjectId('595b7d10ad1a8118a85fed1e'),
ObjectId('595b7d218c7ff00f9d1ebe2f'),
ObjectId('595b7d2547c347196d6d2a80'),
ObjectId('595b7d27c7ed9e0f13402dc3'),
ObjectId('595b7d18e184ae18b5bfe689'),
ObjectId('595b7d178c7ff00f9d1eba66'),
ObjectId('595b7d198d9cfb17b285d8a1'),
ObjectId('595b7d0ffda0d80f289ec90b')]}}).explain()
执行结果：
[
{
queryPlanner:{
plannerVersion:1,
namespace:"admin.attach",
indexFilterSet:false,
parsedQuery:{
data_id:{
$in:[
ObjectId("595b7d0ffda0d80f289ec90b"),
ObjectId("595b7d10ad1a8118a85fed1e"),
ObjectId("595b7d120031d40f881634b8"),
ObjectId("595b7d130630360f7f8ded79"),
ObjectId("595b7d1372321718a937f649"),
ObjectId("595b7d178c7ff00f9d1eba66"),
ObjectId("595b7d18adb12b0f8622472d"),
ObjectId("595b7d18e184ae18b5bfe689"),
ObjectId("595b7d198d9cfb17b285d8a1"),
ObjectId("595b7d1b47c347196d6d26f5"),
ObjectId("595b7d1c72cf8718a10beec9"),
ObjectId("595b7d1eadb12b0f86224a6d"),
ObjectId("595b7d218c7ff00f9d1ebe2f"),
ObjectId("595b7d21adb12b0f86224acc"),
ObjectId("595b7d22b5077e1a668c189d"),
ObjectId("595b7d23ab191119de23b9a3"),
ObjectId("595b7d2547c347196d6d2a80"),
ObjectId("595b7d25fda0d80f289ed24b"),
ObjectId("595b7d268d9cfb17b285dff3"),
ObjectId("595b7d27c7ed9e0f13402dc3"),
ObjectId("595b7d28ad1a8118a85ff575"),
ObjectId("595b7d29a2a75c18af7eff2d"),
ObjectId("595b7d2a36f526176ba21156"),
ObjectId("595b7d2c88ce6d18afd34dab"),
ObjectId("595b7d3c0b192c196eb84f87")
]
}
},
winningPlan:{
stage:"FETCH",
inputStage:{
stage:"IXSCAN",
keyPattern:{
data_id:1.0
},
indexName:"data_id_1.0",
isMultiKey:false,
isUnique:false,
isSparse:false,
isPartial:false,
indexVersion:1,
direction:"forward",
indexBounds:{
data_id:[
"[ObjectId('595b7d0ffda0d80f289ec90b'),ObjectId('595b7d0ffda0d80f289ec90b')]",
"[ObjectId('595b7d10ad1a8118a85fed1e'),ObjectId('595b7d10ad1a8118a85fed1e')]",
"[ObjectId('595b7d120031d40f881634b8'),ObjectId('595b7d120031d40f881634b8')]",
"[ObjectId('595b7d130630360f7f8ded79'),ObjectId('595b7d130630360f7f8ded79')]",
"[ObjectId('595b7d1372321718a937f649'),ObjectId('595b7d1372321718a937f649')]",
"[ObjectId('595b7d178c7ff00f9d1eba66'),ObjectId('595b7d178c7ff00f9d1eba66')]",
"[ObjectId('595b7d18adb12b0f8622472d'),ObjectId('595b7d18adb12b0f8622472d')]",
"[ObjectId('595b7d18e184ae18b5bfe689'),ObjectId('595b7d18e184ae18b5bfe689')]",
"[ObjectId('595b7d198d9cfb17b285d8a1'),ObjectId('595b7d198d9cfb17b285d8a1')]",
"[ObjectId('595b7d1b47c347196d6d26f5'),ObjectId('595b7d1b47c347196d6d26f5')]",
"[ObjectId('595b7d1c72cf8718a10beec9'),ObjectId('595b7d1c72cf8718a10beec9')]",
"[ObjectId('595b7d1eadb12b0f86224a6d'),ObjectId('595b7d1eadb12b0f86224a6d')]",
"[ObjectId('595b7d218c7ff00f9d1ebe2f'),ObjectId('595b7d218c7ff00f9d1ebe2f')]",
"[ObjectId('595b7d21adb12b0f86224acc'),ObjectId('595b7d21adb12b0f86224acc')]",
"[ObjectId('595b7d22b5077e1a668c189d'),ObjectId('595b7d22b5077e1a668c189d')]",
"[ObjectId('595b7d23ab191119de23b9a3'),ObjectId('595b7d23ab191119de23b9a3')]",
"[ObjectId('595b7d2547c347196d6d2a80'),ObjectId('595b7d2547c347196d6d2a80')]",
"[ObjectId('595b7d25fda0d80f289ed24b'),ObjectId('595b7d25fda0d80f289ed24b')]",
"[ObjectId('595b7d268d9cfb17b285dff3'),ObjectId('595b7d268d9cfb17b285dff3')]",
"[ObjectId('595b7d27c7ed9e0f13402dc3'),ObjectId('595b7d27c7ed9e0f13402dc3')]",
"[ObjectId('595b7d28ad1a8118a85ff575'),ObjectId('595b7d28ad1a8118a85ff575')]",
"[ObjectId('595b7d29a2a75c18af7eff2d'),ObjectId('595b7d29a2a75c18af7eff2d')]",
"[ObjectId('595b7d2a36f526176ba21156'),ObjectId('595b7d2a36f526176ba21156')]",
"[ObjectId('595b7d2c88ce6d18afd34dab'),ObjectId('595b7d2c88ce6d18afd34dab')]",
"[ObjectId('595b7d3c0b192c196eb84f87'),ObjectId('595b7d3c0b192c196eb84f87')]"
]
}
}
},
rejectedPlans:[
]
},
executionStats:{
executionSuccess:true,
nReturned:14842,
executionTimeMillis:5881,
totalKeysExamined:14867,
totalDocsExamined:14842,
executionStages:{
stage:"FETCH",
nReturned:14842,
executionTimeMillisEstimate:20,
works:14867,
advanced:14842,
needTime:24,
needYield:0,
saveState:116,
restoreState:116,
isEOF:1,
invalidates:0,
docsExamined:14842,
alreadyHasObj:0,
inputStage:{
stage:"IXSCAN",
nReturned:14842,
executionTimeMillisEstimate:10,
works:14867,
advanced:14842,
needTime:24,
needYield:0,
saveState:116,
restoreState:116,
isEOF:1,
invalidates:0,
keyPattern:{
data_id:1.0
},
indexName:"data_id_1.0",
isMultiKey:false,
isUnique:false,
isSparse:false,
isPartial:false,
indexVersion:1,
direction:"forward",
indexBounds:{
data_id:[
"[ObjectId('595b7d0ffda0d80f289ec90b'),ObjectId('595b7d0ffda0d80f289ec90b')]",
"[ObjectId('595b7d10ad1a8118a85fed1e'),ObjectId('595b7d10ad1a8118a85fed1e')]",
"[ObjectId('595b7d120031d40f881634b8'),ObjectId('595b7d120031d40f881634b8')]",
"[ObjectId('595b7d130630360f7f8ded79'),ObjectId('595b7d130630360f7f8ded79')]",
"[ObjectId('595b7d1372321718a937f649'),ObjectId('595b7d1372321718a937f649')]",
"[ObjectId('595b7d178c7ff00f9d1eba66'),ObjectId('595b7d178c7ff00f9d1eba66')]",
"[ObjectId('595b7d18adb12b0f8622472d'),ObjectId('595b7d18adb12b0f8622472d')]",
"[ObjectId('595b7d18e184ae18b5bfe689'),ObjectId('595b7d18e184ae18b5bfe689')]",
"[ObjectId('595b7d198d9cfb17b285d8a1'),ObjectId('595b7d198d9cfb17b285d8a1')]",
"[ObjectId('595b7d1b47c347196d6d26f5'),ObjectId('595b7d1b47c347196d6d26f5')]",
"[ObjectId('595b7d1c72cf8718a10beec9'),ObjectId('595b7d1c72cf8718a10beec9')]",
"[ObjectId('595b7d1eadb12b0f86224a6d'),ObjectId('595b7d1eadb12b0f86224a6d')]",
"[ObjectId('595b7d218c7ff00f9d1ebe2f'),ObjectId('595b7d218c7ff00f9d1ebe2f')]",
"[ObjectId('595b7d21adb12b0f86224acc'),ObjectId('595b7d21adb12b0f86224acc')]",
"[ObjectId('595b7d22b5077e1a668c189d'),ObjectId('595b7d22b5077e1a668c189d')]",
"[ObjectId('595b7d23ab191119de23b9a3'),ObjectId('595b7d23ab191119de23b9a3')]",
"[ObjectId('595b7d2547c347196d6d2a80'),ObjectId('595b7d2547c347196d6d2a80')]",
"[ObjectId('595b7d25fda0d80f289ed24b'),ObjectId('595b7d25fda0d80f289ed24b')]",
"[ObjectId('595b7d268d9cfb17b285dff3'),ObjectId('595b7d268d9cfb17b285dff3')]",
"[ObjectId('595b7d27c7ed9e0f13402dc3'),ObjectId('595b7d27c7ed9e0f13402dc3')]",
"[ObjectId('595b7d28ad1a8118a85ff575'),ObjectId('595b7d28ad1a8118a85ff575')]",
"[ObjectId('595b7d29a2a75c18af7eff2d'),ObjectId('595b7d29a2a75c18af7eff2d')]",
"[ObjectId('595b7d2a36f526176ba21156'),ObjectId('595b7d2a36f526176ba21156')]",
"[ObjectId('595b7d2c88ce6d18afd34dab'),ObjectId('595b7d2c88ce6d18afd34dab')]",
"[ObjectId('595b7d3c0b192c196eb84f87'),ObjectId('595b7d3c0b192c196eb84f87')]"
]
},
keysExamined:14867,
dupsTested:0,
dupsDropped:0,
seenInvalidated:0
}
},
allPlansExecution:[
]
}
}
]

Answer 1

好像并不需要优化。我在服务器上面查询，executionTimeMillis的值，刚刚还5000，现在24了。几乎秒出了。我真怀疑速度慢，只是因为网络的问题。毕竟，查询出来1万条。数据库和服务器之间的通过网络进行数据传输，慢并不是因为查询慢，而是数据传输慢。。。当然，这只是我的猜测。