0

索引为

db.test_t.createIndex({“order_date”:-1, “status”: -1, “order_id”: -1}, {“name”: “index_date_status”, “background”: true})

查询语句为

db.test_t.find({“status”: {“$gte”: 2, “$lte”: 3}, “order_id”: {“$lte”: 170001}, “order_date”: {“$gte”: 20210704000000, “$lte”: 20220904000000}}).sort({“order_date”: -1}).explain(‘executionStats’)

总条数为70000条

执行计划结果为

“executionStats” : {
“executionSuccess” : true,
“nReturned” : 70000,
“executionTimeMillis” : 77,
“totalKeysExamined” : 70023,
“totalDocsExamined” : 70000,
“executionStages” : {
“stage” : “FETCH”,
“nReturned” : 70000,
“executionTimeMillisEstimate” : 8,
“works” : 70024,
“advanced” : 70000,
“needTime” : 23,
“needYield” : 0,
“saveState” : 70,
“restoreState” : 70,
“isEOF” : 1,
“docsExamined” : 70000,
“alreadyHasObj” : 0,
“inputStage” : {
“stage” : “IXSCAN”,
“nReturned” : 70000,
“executionTimeMillisEstimate” : 5,
“works” : 70024,
“advanced” : 70000,
“needTime” : 23,
“needYield” : 0,
“saveState” : 70,
“restoreState” : 70,
“isEOF” : 1,
“keyPattern” : {
“order_date” : -1.0,
“status” : -1.0,
“order_id” : -1.0
},
“indexName” : “index_date_status”,
“isMultiKey” : false,
“multiKeyPaths” : {
“order_date” : [],
“status” : [],
“order_id” : []
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“order_date” : [
“[20220904000000.0, 20210704000000.0]”
],
“status” : [
“[3.0, 2.0]”
],
“order_id” : [
“[170001.0, -inf.0]”
]
},
“keysExamined” : 70023,
“seeks” : 24,
“dupsTested” : 0,
“dupsDropped” : 0
}
}
}

理论上第一个范围查询order_date之后的条件不会走索引,status和order_id会根据order_date再进行一次过滤,但实际执行计划中看 status和order_id还是走了索引,为什么呢

已回答的问题