点击上方蓝字关注我们
一、导语
Mon Dec 7 15:43:15.832 I COMMAND [conn72] command test_schema.test_list_info command: find { find: "test_list_info", filter: { xxxCodeXxx: "DB2C8D44F726", pushXtime: { $gte: new Date(1604166860000), $lt: new Date(1604678346860) }, xxxCodeXxxBelong: { $in: [ "2D4B761B7D72", "EC1321FF6AF7", "6F2DE0CDA4DE", "9148E85DD884", "1FB8011CEC78", "36471362D702", "013BDA4B799A", "DFA0AA339CB3", "1FFB9F0C0AF9", "78EFFB2F9D81", "368D14A6A470", "1AD3433D726B" ] }, pushXstatus: { $in: [ 0, 3, 4 ] }, $or: [ { basicXxxResult.basicXxxCode: "6733" } ] }, projection: { xxxId: true, insureArea: true, xxxCodeXxx: true, xxXNameXxx: true, xxxCodeXxxBelong: true, xxXNameXxxBelong: true, businessType: true, nameType: true, sysSource: true, listType: true, xxxUcid: true, licenceXno: true, userid: true, pushXtime: true, pushXstatus: true, noSendReason: true, xxxResult: true, basicXxxResult: true, combeXxxResult: true, quoteXxxGroup: true, $sortKey: { $meta: "sortKey" } }, sort: { pushXtime: -1 }, limit: 10 } planSummary: IXSCAN { xxxCodeXxx: 1.0, pushXtime: 1.0 } keysExamined:268005 docsExamined:268005 fromMultiPlanner:1 replanned:1 cursorExhausted:1 keyUpdates:0 writeConflicts:0 numYields:14482 nreturned:10 reslen:5941 locks:{ Global: { acquireCount: { r: 28966 } }, Database: { acquireCount: { r: 14483 } }, Collection: { acquireCount: { r: 14483 } } } protocol:op_command 64694ms
查看集合test_list_info信息如下：
mongos> db.test_list_info.stats(){"sharded" : false,"primary" : "cmgo-hs36uakv_0","ns" : "test_schema.test_list_info","count" : 81478522,"size" : 284936444991,"avgObjSize" : 3497,"storageSize" : 91633369088,"capped" : false,…….."nindexes" : 15,"totalIndexSize" : 10344464384,"indexSizes" : {"_id_" : 1117822976,"xxxId" : 1546698752,"userid" : 581955584,"sday" : 257048576,"idx_company_car" : 1358323712,"pushXstatus_1" : 278659072,"pushXtime_-1" : 566034432,"xxxCodeXxxBelong_-1" : 366555136,"licenceXno" : 422629376,"businessType_-1" : 260706304,"nameType_-1" : 271507456,"idx_iarea_ptime" : 710279168,"sysUtime_-1" : 1190211584,"idx_xxxUcid" : 587911168,"idx_ccode_pushXtime" : 828121088},"ok" : 1}
对于一个文档数量为81478522的大集合，每次查询扫描28w+的索引和文档还是很慢的，所以分析慢查之后，对字段xxxCodeXxx+ xxxCodeXxxBelong+ pushXtime添加联合索引idx_ccode_cbelong，并删除xxxCodeXxx+pushXtime组成的联合索引idx_ccode_pushXtime（冗余索引），我们分析下查询执行计划如下：
mongos> db.test_list_info.explain('executionStats').find({ xxxCodeXxx: "DB2C8D44F726", pushXtime: { $gte: new Date(1604166860000), $lt: new Date(1604678346860) }, xxxCodeXxxBelong: { $in: [ "2D4B761B7D72", "EC1321FF6AF7", "6F2DE0CDA4DE", "9148E85DD884", "1FB8011CEC78", "36471362D702", "013BDA4B799A", "DFA0AA339CB3", "1FFB9F0C0AF9", "78EFFB2F9D81", "368D14A6A470", "1AD3433D726B" ] }, pushXstatus: { $in: [ 0, 3, 4 ] }, "$or" : [ { "basicXxxResult.basicXxxCode" : { "$in" : [ "ADBA" , "D5EC" , "0C95" , "79D5" , "F7EC" , "6733" , "3D3D" , "7350" , "F31D" , "2DF2" , "DB8B" , "159D"]}} , { "combeXxxResult.specialRuleCode" : { "$in" : [ "28F9" , "928F" , "CC4B" , "3134" , "6860" , "5584" , "f9cd"]}} , { "noSendReason" : { "$in" : [ 1 , 2 , 7 , 8 , 9 , 10 , 11]}} , { "xxxResult" : { "$in" : [ 1 , 2 , 3 , 4 , 5]}}]},{xxxId: true, insureArea: true, xxxCodeXxx: true, xxXNameXxx: true, xxxCodeXxxBelong: true, xxXNameXxxBelong: true, businessType: true, nameType: true, sysSource: true, listType: true, xxxUcid: true, licenceXno: true, userid: true, pushXtime: true, pushXstatus: true, noSendReason: true, xxxResult: true, basicXxxResult: true, combeXxxResult: true, quoteXxxGroup: true, pushXtime: true}).sort({ pushXtime: -1 }).limit(10)
mongos> db.test_list_info.getIndexes()[{"v" : 1,"key" : {"_id" : 1},"name" : "_id_","ns" : "test_schema.test_list_info"},{"v" : 1,"key" : {"xxxId" : 1},"name" : "xxxId","ns" : "test_schema.test_list_info"},{"v" : 1,"key" : {"userid" : 1},"name" : "userid","ns" : "test_schema.test_list_info"},{"v" : 1,"key" : {"sday" : 1},"name" : "sday","ns" : "test_schema.test_list_info"},{"v" : 1,"key" : {"xxxCodeXxx" : 1,"xxxUcid" : 1},"name" : "idx_company_car","ns" : "test_schema.test_list_info","background" : true},{"v" : 1,"key" : {"pushXstatus" : 1},"name" : "pushXstatus_1","ns" : "test_schema.test_list_info","background" : true},{"v" : 1,"key" : {"pushXtime" : -1},"name" : "pushXtime_-1","ns" : "test_schema.test_list_info","background" : true},{"v" : 1,"key" : {"xxxCodeXxxBelong" : -1},"name" : "xxxCodeXxxBelong_-1","ns" : "test_schema.test_list_info","background" : true},{"v" : 1,"key" : {"licenceXno" : 1},"name" : "licenceXno","ns" : "test_schema.test_list_info"},{"v" : 1,"key" : {"businessType" : -1},"name" : "businessType_-1","ns" : "test_schema.test_list_info","background" : true},{"v" : 1,"key" : {"nameType" : -1},"name" : "nameType_-1","background" : true,"ns" : "test_schema.test_list_info"},{"v" : 1,"key" : {"insureArea" : 1,"pushXtime" : 1},"name" : "idx_iarea_ptime","background" : true,"ns" : "test_schema.test_list_info"},{"v" : 1,"key" : {"sysUtime" : -1},"name" : "sysUtime_-1","ns" : "test_schema.test_list_info"},{"v" : 1,"key" : {"xxxUcid" : 1},"name" : "idx_xxxUcid","ns" : "test_schema.test_list_info","background" : true},{"v" : 1,"key" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"name" : "idx_ccode_cbelong","ns" : "test_schema.test_list_info","background" : true}]
db.test_list_info.find({ xxxCodeXxx: "DB2C8D44F726", pushXtime: { $gte: new Date(1604166860000), $lt: new Date(1604678346860) }, xxxCodeXxxBelong: { $in: [ "2D4B761B7D72", "EC1321FF6AF7", "6F2DE0CDA4DE", "9148E85DD884", "1FB8011CEC78", "36471362D702", "013BDA4B799A", "DFA0AA339CB3", "1FFB9F0C0AF9", "78EFFB2F9D81", "368D14A6A470", "1AD3433D726B" ] }, pushXstatus: { $in: [ 0, 3, 4 ] }, "basicXxxResult.basicXxxCode" : "6733"},{xxxId: true, insureArea: true, xxxCodeXxx: true, xxXNameXxx: true, xxxCodeXxxBelong: true, xxXNameXxxBelong: true, businessType: true, nameType: true, sysSource: true, listType: true, xxxUcid: true, licenceXno: true, userid: true, pushXtime: true, pushXstatus: true, noSendReason: true, xxxResult: true, basicXxxResult: true, combeXxxResult: true, quoteXxxGroup: true, pushXtime: true}).sort({ pushXtime: -1 }).limit(10)
db.test_list_info.find({ xxxCodeXxx: "DB2C8D44F726", pushXtime: { $gte: new Date(1604166860000), $lt: new Date(1604678346860) }, xxxCodeXxxBelong: { $in: [ "2D4B761B7D72", "EC1321FF6AF7", "6F2DE0CDA4DE", "9148E85DD884", "1FB8011CEC78", "36471362D702", "013BDA4B799A", "DFA0AA339CB3", "1FFB9F0C0AF9", "78EFFB2F9D81", "368D14A6A470", "1AD3433D726B" ] }, pushXstatus: { $in: [ 0, 3, 4 ] }, "basicXxxResult.basicXxxCode" : "6733"},{xxxId: true, insureArea: true, xxxCodeXxx: true, xxXNameXxx: true, xxxCodeXxxBelong: true, xxXNameXxxBelong: true, businessType: true, nameType: true, sysSource: true, listType: true, xxxUcid: true, licenceXno: true, userid: true, pushXtime: true, pushXstatus: true, noSendReason: true, xxxResult: true, basicXxxResult: true, combeXxxResult: true, quoteXxxGroup: true, pushXtime: true}).sort({ pushXtime: -1 }).limit(10)
2、查询二
db.test_list_info.find({ xxxCodeXxx: "DB2C8D44F726", pushXtime: { $gte: new Date(1604166860000), $lt: new Date(1604678346860) }, xxxCodeXxxBelong: { $in: [ "2D4B761B7D72", "EC1321FF6AF7", "6F2DE0CDA4DE", "9148E85DD884", "1FB8011CEC78", "36471362D702", "013BDA4B799A", "DFA0AA339CB3", "1FFB9F0C0AF9", "78EFFB2F9D81", "368D14A6A470", "1AD3433D726B" ] }, pushXstatus: { $in: [ 0, 3, 4 ] }, "basicXxxResult.basicXxxCode" : { "$in" : [ "ADBA" , "D5EC" , "0C95" , "79D5" , "F7EC" , "6733" , "3D3D" , "7350" , "F31D" , "2DF2" , "DB8B" , "159D"]}},{xxxId: true, insureArea: true, xxxCodeXxx: true, xxXNameXxx: true, xxxCodeXxxBelong: true, xxXNameXxxBelong: true, businessType: true, nameType: true, sysSource: true, listType: true, xxxUcid: true, licenceXno: true, userid: true, pushXtime: true, pushXstatus: true, noSendReason: true, xxxResult: true, basicXxxResult: true, combeXxxResult: true, quoteXxxGroup: true, pushXtime: true}).sort({ pushXtime: -1 }).limit(10)
查询都选择了联合索引idx_ccode_cbelong，对比字段发现部分字段值相差较大，开始继续阅读之前，先带大家解释下相关重要字段信息，说明如下：
"executionTimeMillis" ：查询计划选择和查询执行所需的总时间（以毫秒为单位）"totalKeysExamined" : 扫描的索引条目数"totalDocsExamined" : 扫描的文档数"works" : 查询执行阶段执行的“工作单元”的数量，查询执行阶段将其工作分为小单元，“工作单位”可能包括检查单个索引键，从集合中提取单个文档，将投影应用于单个文档或执行内部记账"advanced" : 返回的中间结果数量或者返回到父阶段的结果数"needTime" : 未将中间结果返回给其父级的工作循环数。例如，索引扫描阶段可以花费一个工作周期来寻找索引中的新位置而不是返回索引键; 这个工作周期将计入explain.executionStats.executionStages.needTime而不是explain.executionStats.executionStages.advanced"needYield" : 查询阶段暂停处理和让锁导致的存储层请求次数 （此锁机制可防止高优先级请求等待过长时间）"saveState" : 查询阶段暂停处理和保存它当前的执行状态次数，例如准备让出它持有的锁（yield）"restoreState" : 查询阶段恢复一个保存的执行状态次数，例如恢复之前让出的锁（yield），类似协程机制"isEOF" : 执行阶段是否已到达流的结尾，代表查询结束
mongos> db.test_list_info.getPlanCache().listQueryShapes()[{"query" : {"xxxCodeXxx" : "DB2C8D44F726","xxxUcid" : "b3265911b31648f3ad71a8e14434a7c9"},"sort" : {},"projection" : {"_id" : true,"quoteVersion" : true}},{"query" : {"xxxCodeXxx" : "DB2C8D44F726","xxxUcid" : "b3265911b31648f3ad71a8e14434a7c9"},"sort" : {},"projection" : {}},{"query" : {"xxxCodeXxx" : "DB2C8D44F726","pushXtime" : {"$gte" : ISODate("2020-10-31T16:00:00Z"),"$lt" : ISODate("2020-11-06T15:59:00Z")},"xxxCodeXxxBelong" : {"$in" : ["2D4B761B7D72","EC1321FF6AF7","6F2DE0CDA4DE","9148E85DD884","1FB8011CEC78","36471362D702","013BDA4B799A","DFA0AA339CB3","1FFB9F0C0AF9","78EFFB2F9D81","368D14A6A470","1AD3433D726B"]},"pushXstatus" : {"$in" : [0,3,4]},"basicXxxResult.basicXxxCode" : {"$in" : ["ADBA","D5EC","0C95","79D5","F7EC","6733","3D3D","7350","F31D","2DF2","DB8B","159D"]}},"sort" : {"pushXtime" : -1},"projection" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true}},{"query" : {"xxxCodeXxx" : "DB2C8D44F726","pushXtime" : {"$gte" : ISODate("2020-10-31T16:00:00Z"),"$lt" : ISODate("2020-11-06T15:59:00Z")},"xxxCodeXxxBelong" : {"$in" : ["2D4B761B7D72","EC1321FF6AF7","6F2DE0CDA4DE","9148E85DD884","1FB8011CEC78","36471362D702","013BDA4B799A","DFA0AA339CB3","1FFB9F0C0AF9","78EFFB2F9D81","368D14A6A470","1AD3433D726B"]},"pushXstatus" : {"$in" : [0,3,4]},"basicXxxResult.basicXxxCode" : "6733"},"sort" : {"pushXtime" : -1},"projection" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true}}]
我们先看一下查询一缓存的查询计划信息：
mongos> db.test_list_info.getPlanCache().getPlansByQuery({... "query" : {... "xxxCodeXxx" : "DB2C8D44F726",... "pushXtime" : {... "$gte" : ISODate("2020-10-31T16:00:00Z"),... "$lt" : ISODate("2020-11-06T15:59:00Z")... },... "xxxCodeXxxBelong" : {... "$in" : [... "2D4B761B7D72",... "EC1321FF6AF7",... "6F2DE0CDA4DE",... "9148E85DD884",... "1FB8011CEC78",... "36471362D702",... "013BDA4B799A",... "DFA0AA339CB3",... "1FFB9F0C0AF9",... "78EFFB2F9D81",... "368D14A6A470",... "1AD3433D726B"... ]... },... "pushXstatus" : {... "$in" : [... 0,... 3,... 4... ]... },... "basicXxxResult.basicXxxCode" : "6733"... },... "sort" : {... "pushXtime" : -1... },... "projection" : {... "xxxId" : true,... "insureArea" : true,... "xxxCodeXxx" : true,... "xxXNameXxx" : true,... "xxxCodeXxxBelong" : true,... "xxXNameXxxBelong" : true,... "businessType" : true,... "nameType" : true,... "sysSource" : true,... "listType" : true,... "xxxUcid" : true,... "licenceXno" : true,... "userid" : true,... "pushXtime" : true,... "pushXstatus" : true,... "noSendReason" : true,... "xxxResult" : true,... "basicXxxResult" : true,... "combeXxxResult" : true,... "quoteXxxGroup" : true... }... })[{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf { xxxCodeXxx: 1.0, xxxCodeXxxBelong: 1.0, pushXtime: 1.0 }, pos: 0\n---Leaf { xxxCodeXxx: 1.0, xxxCodeXxxBelong: 1.0, pushXtime: 1.0 }, pos: 2\n---Leaf { xxxCodeXxx: 1.0, xxxCodeXxxBelong: 1.0, pushXtime: 1.0 }, pos: 2\n---Leaf { xxxCodeXxx: 1.0, xxxCodeXxxBelong: 1.0, pushXtime: 1.0 }, pos: 1\n---Leaf \n)"},"reason" : {"score" : 1.f9cd38351170725,"stats" : {"stage" : "LIMIT","nReturned" : 10,"executionTimeMillisEstimate" : 769,"works" : 42794,"advanced" : 10,"needTime" : 42784,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 1,"invalidates" : 0,"limitAmount" : 10,"inputStage" : {"stage" : "PROJECTION","nReturned" : 10,"executionTimeMillisEstimate" : 769,"works" : 42794,"advanced" : 10,"needTime" : 42784,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 769,"works" : 42794,"advanced" : 0,"needTime" : 42784,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"basicXxxResult.basicXxxCode" : {"$eq" : "6733"}},{"pushXstatus" : {"$in" : [0,3,4]}}]},"nReturned" : 10,"executionTimeMillisEstimate" : 769,"works" : 42793,"advanced" : 10,"needTime" : 42783,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 21391,"alreadyHasObj" : 0,"inputStage" : {"stage" : "SORT_MERGE","nReturned" : 21391,"executionTimeMillisEstimate" : 90,"works" : 42793,"advanced" : 21391,"needTime" : 21402,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"sortPattern" : {"pushXtime" : -1},"dupsTested" : 21400,"dupsDropped" : 0,"inputStages" : [{"stage" : "IXSCAN","nReturned" : 595,"executionTimeMillisEstimate" : 0,"works" : 595,"advanced" : 595,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"1AD3433D726B\", \"1AD3433D726B\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 595,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 2781,"executionTimeMillisEstimate" : 0,"works" : 2781,"advanced" : 2781,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"368D14A6A470\", \"368D14A6A470\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 2781,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 263,"executionTimeMillisEstimate" : 0,"works" : 263,"advanced" : 263,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"78EFFB2F9D81\", \"78EFFB2F9D81\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 263,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 668,"executionTimeMillisEstimate" : 0,"works" : 668,"advanced" : 668,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"1FFB9F0C0AF9\", \"1FFB9F0C0AF9\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 668,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 3500,"executionTimeMillisEstimate" : 10,"works" : 3500,"advanced" : 3500,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"DFA0AA339CB3\", \"DFA0AA339CB3\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 3500,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 2471,"executionTimeMillisEstimate" : 0,"works" : 2471,"advanced" : 2471,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"013BDA4B799A\", \"013BDA4B799A\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 2471,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 1402,"executionTimeMillisEstimate" : 0,"works" : 1402,"advanced" : 1402,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"36471362D702\", \"36471362D702\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 1402,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 2764,"executionTimeMillisEstimate" : 10,"works" : 2764,"advanced" : 2764,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"1FB8011CEC78\", \"1FB8011CEC78\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 2764,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 3761,"executionTimeMillisEstimate" : 0,"works" : 3761,"advanced" : 3761,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"9148E85DD884\", \"9148E85DD884\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 3761,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 1,"advanced" : 0,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 1,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"EC1321FF6AF7\", \"EC1321FF6AF7\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 0,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 3195,"executionTimeMillisEstimate" : 10,"works" : 3195,"advanced" : 3195,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"6F2DE0CDA4DE\", \"6F2DE0CDA4DE\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 3195,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 1,"advanced" : 0,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 1,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"2D4B761B7D72\", \"2D4B761B7D72\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 0,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}]}}}}}},"feedback" : {"nfeedback" : 0,"scores" : [ ]},"filterSet" : false},{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf { pushXtime: -1.0 }, pos: 0\n---Leaf { pushXtime: -1.0 }, pos: 0\n---Leaf \n---Leaf \n)"},"reason" : {"score" : 1.686004673552367,"stats" : {"stage" : "LIMIT","nReturned" : 0,"executionTimeMillisEstimate" : 190,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"limitAmount" : 10,"inputStage" : {"stage" : "PROJECTION","nReturned" : 0,"executionTimeMillisEstimate" : 190,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 190,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"basicXxxResult.basicXxxCode" : {"$eq" : "6733"}},{"xxxCodeXxx" : {"$eq" : "DB2C8D44F726"}},{"xxxCodeXxxBelong" : {"$in" : ["2D4B761B7D72","6F2DE0CDA4DE","EC1321FF6AF7","9148E85DD884","1FB8011CEC78","36471362D702","013BDA4B799A","DFA0AA339CB3","1FFB9F0C0AF9","78EFFB2F9D81","368D14A6A470","1AD3433D726B"]}},{"pushXstatus" : {"$in" : [0,3,4]}}]},"nReturned" : 0,"executionTimeMillisEstimate" : 180,"works" : 42793,"advanced" : 0,"needTime" : 42793,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 42793,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 42793,"executionTimeMillisEstimate" : 30,"works" : 42793,"advanced" : 42793,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"pushXtime" : -1},"indexName" : "pushXtime_-1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 42793,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}}}},"feedback" : {},"filterSet" : false},{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf \n---Leaf \n---Leaf \n---Leaf { pushXstatus: 1.0 }, pos: 0\n)"},"reason" : {"score" : 1.6860023367761837,"stats" : {"stage" : "PROJECTION","nReturned" : 0,"executionTimeMillisEstimate" : 320,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT","nReturned" : 0,"executionTimeMillisEstimate" : 320,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"sortPattern" : {"pushXtime" : -1},"memUsage" : 0,"memLimit" : 33554432,"limitAmount" : 10,"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 320,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"basicXxxResult.basicXxxCode" : {"$eq" : "6733"}},{"xxxCodeXxx" : {"$eq" : "DB2C8D44F726"}},{"pushXtime" : {"$lt" : ISODate("2020-11-06T15:59:00Z")}},{"pushXtime" : {"$gte" : ISODate("2020-10-31T16:00:00Z")}},{"xxxCodeXxxBelong" : {"$in" : ["2D4B761B7D72","6F2DE0CDA4DE","EC1321FF6AF7","9148E85DD884","1FB8011CEC78","36471362D702","013BDA4B799A","DFA0AA339CB3","1FFB9F0C0AF9","78EFFB2F9D81","368D14A6A470","1AD3433D726B"]}}]},"nReturned" : 0,"executionTimeMillisEstimate" : 320,"works" : 42793,"advanced" : 0,"needTime" : 42793,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 42793,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 42793,"executionTimeMillisEstimate" : 20,"works" : 42793,"advanced" : 42793,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"pushXstatus" : 1},"indexName" : "pushXstatus_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"pushXstatus" : ["[0.0, 0.0]","[3.0, 3.0]","[4.0, 4.0]"]},"keysExamined" : 42793,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}}}},"feedback" : {},"filterSet" : false},{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf \n---Leaf \n---Leaf { xxxCodeXxxBelong: -1.0 }, pos: 0\n---Leaf \n)"},"reason" : {"score" : 1.6860023367761837,"stats" : {"stage" : "PROJECTION","nReturned" : 0,"executionTimeMillisEstimate" : 1573,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT","nReturned" : 0,"executionTimeMillisEstimate" : 1573,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"sortPattern" : {"pushXtime" : -1},"memUsage" : 0,"memLimit" : 33554432,"limitAmount" : 10,"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 1563,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"basicXxxResult.basicXxxCode" : {"$eq" : "6733"}},{"xxxCodeXxx" : {"$eq" : "DB2C8D44F726"}},{"pushXtime" : {"$lt" : ISODate("2020-11-06T15:59:00Z")}},{"pushXtime" : {"$gte" : ISODate("2020-10-31T16:00:00Z")}},{"pushXstatus" : {"$in" : [0,3,4]}}]},"nReturned" : 0,"executionTimeMillisEstimate" : 1563,"works" : 42793,"advanced" : 0,"needTime" : 42793,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 42793,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 42793,"executionTimeMillisEstimate" : 100,"works" : 42793,"advanced" : 42793,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxxBelong" : -1},"indexName" : "xxxCodeXxxBelong_-1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"xxxCodeXxxBelong" : ["[\"1AD3433D726B\", \"1AD3433D726B\"]","[\"368D14A6A470\", \"368D14A6A470\"]","[\"78EFFB2F9D81\", \"78EFFB2F9D81\"]","[\"1FFB9F0C0AF9\", \"1FFB9F0C0AF9\"]","[\"DFA0AA339CB3\", \"DFA0AA339CB3\"]","[\"013BDA4B799A\", \"013BDA4B799A\"]","[\"36471362D702\", \"36471362D702\"]","[\"1FB8011CEC78\", \"1FB8011CEC78\"]","[\"9148E85DD884\", \"9148E85DD884\"]","[\"EC1321FF6AF7\", \"EC1321FF6AF7\"]","[\"6F2DE0CDA4DE\", \"6F2DE0CDA4DE\"]","[\"2D4B761B7D72\", \"2D4B761B7D72\"]"]},"keysExamined" : 42793,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}}}},"feedback" : {},"filterSet" : false},{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf { xxxCodeXxx: 1.0, xxxUcid: 1.0 }, pos: 0\n---Leaf \n---Leaf \n---Leaf \n---Leaf \n)"},"reason" : {"score" : 1.6860023367761837,"stats" : {"stage" : "PROJECTION","nReturned" : 0,"executionTimeMillisEstimate" : 1590,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT","nReturned" : 0,"executionTimeMillisEstimate" : 1580,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"sortPattern" : {"pushXtime" : -1},"memUsage" : 0,"memLimit" : 33554432,"limitAmount" : 10,"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 1580,"works" : 42794,"advanced" : 0,"needTime" : 42794,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"basicXxxResult.basicXxxCode" : {"$eq" : "6733"}},{"pushXtime" : {"$lt" : ISODate("2020-11-06T15:59:00Z")}},{"pushXtime" : {"$gte" : ISODate("2020-10-31T16:00:00Z")}},{"xxxCodeXxxBelong" : {"$in" : ["2D4B761B7D72","6F2DE0CDA4DE","EC1321FF6AF7","9148E85DD884","1FB8011CEC78","36471362D702","013BDA4B799A","DFA0AA339CB3","1FFB9F0C0AF9","78EFFB2F9D81","368D14A6A470","1AD3433D726B"]}},{"pushXstatus" : {"$in" : [0,3,4]}}]},"nReturned" : 0,"executionTimeMillisEstimate" : 1580,"works" : 42793,"advanced" : 0,"needTime" : 42793,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 42793,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 42793,"executionTimeMillisEstimate" : 60,"works" : 42793,"advanced" : 42793,"needTime" : 0,"needYield" : 0,"saveState" : 1671,"restoreState" : 1671,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxUcid" : 1},"indexName" : "idx_company_car","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxUcid" : ["[MinKey, MaxKey]"]},"keysExamined" : 42793,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}}}},"feedback" : {},"filterSet" : false}]
里面有每次候选索引的分数，我们可以看出索引idx_ccode_cbelong的分数是最高的，所以优化器会优先选择分数高的索引为最佳索引，我们在看看查询二缓存的查询计划信息，如下：
mongos> db.test_list_info.getPlanCache().getPlansByQuery({... "query" : {... "xxxCodeXxx" : "DB2C8D44F726",... "pushXtime" : {... "$gte" : ISODate("2020-10-31T16:00:00Z"),... "$lt" : ISODate("2020-11-06T15:59:00Z")... },... "xxxCodeXxxBelong" : {... "$in" : [... "2D4B761B7D72",... "EC1321FF6AF7",... "6F2DE0CDA4DE",... "9148E85DD884",... "1FB8011CEC78",... "36471362D702",... "013BDA4B799A",... "DFA0AA339CB3",... "1FFB9F0C0AF9",... "78EFFB2F9D81",... "368D14A6A470",... "1AD3433D726B"... ]... },... "pushXstatus" : {... "$in" : [... 0,... 3,... 4... ]... },... "basicXxxResult.basicXxxCode" : {... "$in" : [... "ADBA",... "D5EC",... "0C95",... "79D5",... "F7EC",... "6733",... "3D3D",... "7350",... "F31D",... "2DF2",... "DB8B",... "159D"... ]... }... },... "sort" : {... "pushXtime" : -1... },... "projection" : {... "xxxId" : true,... "insureArea" : true,... "xxxCodeXxx" : true,... "xxXNameXxx" : true,... "xxxCodeXxxBelong" : true,... "xxXNameXxxBelong" : true,... "businessType" : true,... "nameType" : true,... "sysSource" : true,... "listType" : true,... "xxxUcid" : true,... "licenceXno" : true,... "userid" : true,... "pushXtime" : true,... "pushXstatus" : true,... "noSendReason" : true,... "xxxResult" : true,... "basicXxxResult" : true,... "combeXxxResult" : true,... "quoteXxxGroup" : true... }... })[{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { xxxCodeXxx: 1.0, xxxCodeXxxBelong: 1.0, pushXtime: 1.0 }, pos: 0\n---Leaf { xxxCodeXxx: 1.0, xxxCodeXxxBelong: 1.0, pushXtime: 1.0 }, pos: 2\n---Leaf { xxxCodeXxx: 1.0, xxxCodeXxxBelong: 1.0, pushXtime: 1.0 }, pos: 2\n---Leaf \n---Leaf { xxxCodeXxx: 1.0, xxxCodeXxxBelong: 1.0, pushXtime: 1.0 }, pos: 1\n---Leaf \n)"},"reason" : {"score" : 1.1614903225806454,"stats" : {"stage" : "LIMIT","nReturned" : 10,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 10,"needTime" : 52,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 1,"invalidates" : 0,"limitAmount" : 10,"inputStage" : {"stage" : "PROJECTION","nReturned" : 10,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 10,"needTime" : 52,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 52,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"basicXxxResult.basicXxxCode" : {"$in" : ["ADBA","D5EC","0C95","79D5","F7EC","6733","3D3D","7350","F31D","2DF2","DB8B","159D"]}},{"pushXstatus" : {"$in" : [0,3,4]}}]},"nReturned" : 10,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 10,"needTime" : 51,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 25,"alreadyHasObj" : 0,"inputStage" : {"stage" : "SORT_MERGE","nReturned" : 25,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 25,"needTime" : 36,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"sortPattern" : {"pushXtime" : -1},"dupsTested" : 34,"dupsDropped" : 0,"inputStages" : [{"stage" : "IXSCAN","nReturned" : 2,"executionTimeMillisEstimate" : 0,"works" : 2,"advanced" : 2,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"1AD3433D726B\", \"1AD3433D726B\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 2,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 2,"executionTimeMillisEstimate" : 0,"works" : 2,"advanced" : 2,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"368D14A6A470\", \"368D14A6A470\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 2,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 1,"executionTimeMillisEstimate" : 0,"works" : 1,"advanced" : 1,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"78EFFB2F9D81\", \"78EFFB2F9D81\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 1,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 1,"executionTimeMillisEstimate" : 0,"works" : 1,"advanced" : 1,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"1FFB9F0C0AF9\", \"1FFB9F0C0AF9\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 1,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 6,"executionTimeMillisEstimate" : 0,"works" : 6,"advanced" : 6,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"DFA0AA339CB3\", \"DFA0AA339CB3\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 6,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 5,"executionTimeMillisEstimate" : 0,"works" : 5,"advanced" : 5,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"013BDA4B799A\", \"013BDA4B799A\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 5,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 2,"executionTimeMillisEstimate" : 0,"works" : 2,"advanced" : 2,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"36471362D702\", \"36471362D702\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 2,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 5,"executionTimeMillisEstimate" : 0,"works" : 5,"advanced" : 5,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"1FB8011CEC78\", \"1FB8011CEC78\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 5,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 4,"executionTimeMillisEstimate" : 0,"works" : 4,"advanced" : 4,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"9148E85DD884\", \"9148E85DD884\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 4,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 1,"advanced" : 0,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 1,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"EC1321FF6AF7\", \"EC1321FF6AF7\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 0,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 6,"executionTimeMillisEstimate" : 0,"works" : 6,"advanced" : 6,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"6F2DE0CDA4DE\", \"6F2DE0CDA4DE\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 6,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0},{"stage" : "IXSCAN","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 1,"advanced" : 0,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 1,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxCodeXxxBelong" : 1,"pushXtime" : 1},"indexName" : "idx_ccode_cbelong","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "backward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxCodeXxxBelong" : ["[\"2D4B761B7D72\", \"2D4B761B7D72\"]"],"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 0,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}]}}}}}},"feedback" : {"nfeedback" : 0,"scores" : [ ]},"filterSet" : false},{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf { pushXtime: -1.0 }, pos: 0\n---Leaf { pushXtime: -1.0 }, pos: 0\n---Leaf \n---Leaf \n---Leaf \n)"},"reason" : {"score" : 1.f9cd,"stats" : {"stage" : "LIMIT","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"limitAmount" : 10,"inputStage" : {"stage" : "PROJECTION","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"xxxCodeXxx" : {"$eq" : "DB2C8D44F726"}},{"basicXxxResult.basicXxxCode" : {"$in" : ["ADBA","D5EC","0C95","79D5","F7EC","6733","3D3D","7350","F31D","2DF2","DB8B","159D"]}},{"xxxCodeXxxBelong" : {"$in" : ["2D4B761B7D72","6F2DE0CDA4DE","EC1321FF6AF7","9148E85DD884","1FB8011CEC78","36471362D702","013BDA4B799A","DFA0AA339CB3","1FFB9F0C0AF9","78EFFB2F9D81","368D14A6A470","1AD3433D726B"]}},{"pushXstatus" : {"$in" : [0,3,4]}}]},"nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 0,"needTime" : 61,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 61,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 61,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 61,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"pushXtime" : -1},"indexName" : "pushXtime_-1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"pushXtime" : ["(new Date(1604678346860), new Date(1604166860000)]"]},"keysExamined" : 61,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}}}},"feedback" : {},"filterSet" : false},{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf \n---Leaf \n---Leaf \n---Leaf { pushXstatus: 1.0 }, pos: 0\n)"},"reason" : {"score" : 1.5584,"stats" : {"stage" : "PROJECTION","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"sortPattern" : {"pushXtime" : -1},"memUsage" : 0,"memLimit" : 33554432,"limitAmount" : 10,"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"xxxCodeXxx" : {"$eq" : "DB2C8D44F726"}},{"pushXtime" : {"$lt" : ISODate("2020-11-06T15:59:00Z")}},{"pushXtime" : {"$gte" : ISODate("2020-10-31T16:00:00Z")}},{"basicXxxResult.basicXxxCode" : {"$in" : ["ADBA","D5EC","0C95","79D5","F7EC","6733","3D3D","7350","F31D","2DF2","DB8B","159D"]}},{"xxxCodeXxxBelong" : {"$in" : ["2D4B761B7D72","6F2DE0CDA4DE","EC1321FF6AF7","9148E85DD884","1FB8011CEC78","36471362D702","013BDA4B799A","DFA0AA339CB3","1FFB9F0C0AF9","78EFFB2F9D81","368D14A6A470","1AD3433D726B"]}}]},"nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 0,"needTime" : 61,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 61,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 61,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 61,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"pushXstatus" : 1},"indexName" : "pushXstatus_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"pushXstatus" : ["[0.0, 0.0]","[3.0, 3.0]","[4.0, 4.0]"]},"keysExamined" : 61,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}}}},"feedback" : {},"filterSet" : false},{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf \n---Leaf \n---Leaf \n---Leaf { xxxCodeXxxBelong: -1.0 }, pos: 0\n---Leaf \n)"},"reason" : {"score" : 1.5584,"stats" : {"stage" : "PROJECTION","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"sortPattern" : {"pushXtime" : -1},"memUsage" : 0,"memLimit" : 33554432,"limitAmount" : 10,"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"xxxCodeXxx" : {"$eq" : "DB2C8D44F726"}},{"pushXtime" : {"$lt" : ISODate("2020-11-06T15:59:00Z")}},{"pushXtime" : {"$gte" : ISODate("2020-10-31T16:00:00Z")}},{"basicXxxResult.basicXxxCode" : {"$in" : ["ADBA","D5EC","0C95","79D5","F7EC","6733","3D3D","7350","F31D","2DF2","DB8B","159D"]}},{"pushXstatus" : {"$in" : [0,3,4]}}]},"nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 0,"needTime" : 61,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 61,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 61,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 61,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxxBelong" : -1},"indexName" : "xxxCodeXxxBelong_-1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"xxxCodeXxxBelong" : ["[\"1AD3433D726B\", \"1AD3433D726B\"]","[\"368D14A6A470\", \"368D14A6A470\"]","[\"78EFFB2F9D81\", \"78EFFB2F9D81\"]","[\"1FFB9F0C0AF9\", \"1FFB9F0C0AF9\"]","[\"DFA0AA339CB3\", \"DFA0AA339CB3\"]","[\"013BDA4B799A\", \"013BDA4B799A\"]","[\"36471362D702\", \"36471362D702\"]","[\"1FB8011CEC78\", \"1FB8011CEC78\"]","[\"9148E85DD884\", \"9148E85DD884\"]","[\"EC1321FF6AF7\", \"EC1321FF6AF7\"]","[\"6F2DE0CDA4DE\", \"6F2DE0CDA4DE\"]","[\"2D4B761B7D72\", \"2D4B761B7D72\"]"]},"keysExamined" : 61,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}}}},"feedback" : {},"filterSet" : false},{"details" : {"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { xxxCodeXxx: 1.0, xxxUcid: 1.0 }, pos: 0\n---Leaf \n---Leaf \n---Leaf \n---Leaf \n---Leaf \n)"},"reason" : {"score" : 1.5584,"stats" : {"stage" : "PROJECTION","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"transformBy" : {"xxxId" : true,"insureArea" : true,"xxxCodeXxx" : true,"xxXNameXxx" : true,"xxxCodeXxxBelong" : true,"xxXNameXxxBelong" : true,"businessType" : true,"nameType" : true,"sysSource" : true,"listType" : true,"xxxUcid" : true,"licenceXno" : true,"userid" : true,"pushXtime" : true,"pushXstatus" : true,"noSendReason" : true,"xxxResult" : true,"basicXxxResult" : true,"combeXxxResult" : true,"quoteXxxGroup" : true,"$sortKey" : {"$meta" : "sortKey"}},"inputStage" : {"stage" : "SORT","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"sortPattern" : {"pushXtime" : -1},"memUsage" : 0,"memLimit" : 33554432,"limitAmount" : 10,"inputStage" : {"stage" : "SORT_KEY_GENERATOR","nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 62,"advanced" : 0,"needTime" : 62,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"inputStage" : {"stage" : "FETCH","filter" : {"$and" : [{"pushXtime" : {"$lt" : ISODate("2020-11-06T15:59:00Z")}},{"pushXtime" : {"$gte" : ISODate("2020-10-31T16:00:00Z")}},{"basicXxxResult.basicXxxCode" : {"$in" : ["ADBA","D5EC","0C95","79D5","F7EC","6733","3D3D","7350","F31D","2DF2","DB8B","159D"]}},{"xxxCodeXxxBelong" : {"$in" : ["2D4B761B7D72","6F2DE0CDA4DE","EC1321FF6AF7","9148E85DD884","1FB8011CEC78","36471362D702","013BDA4B799A","DFA0AA339CB3","1FFB9F0C0AF9","78EFFB2F9D81","368D14A6A470","1AD3433D726B"]}},{"pushXstatus" : {"$in" : [0,3,4]}}]},"nReturned" : 0,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 0,"needTime" : 61,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"docsExamined" : 61,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 61,"executionTimeMillisEstimate" : 0,"works" : 61,"advanced" : 61,"needTime" : 0,"needYield" : 0,"saveState" : 2,"restoreState" : 2,"isEOF" : 0,"invalidates" : 0,"keyPattern" : {"xxxCodeXxx" : 1,"xxxUcid" : 1},"indexName" : "idx_company_car","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"xxxCodeXxx" : ["[\"DB2C8D44F726\", \"DB2C8D44F726\"]"],"xxxUcid" : ["[MinKey, MaxKey]"]},"keysExamined" : 61,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}}}}},"feedback" : {},"filterSet" : false}]
从缓存的执行计划信息可以看出，索引idx_ccode_cbelong分数是最高的，为最优索引。那么两个查询之间到底慢在哪里呢？从上面我们知道查询执行时间包括了查询计划选择时间和实际查询执行的时间，既然查询执行选择是一样的，那么我们可以大胆推测出两个查询之间相差比较大的时间有可能花在了查询计划选择上了，我们该怎么证明自己的猜想呢？很简单，我们对查询使用hint(‘idx_ccode_cbelong’)，这样子查询计划直接跳过优化器（"rejectedPlans" : [ ]显示为空），直接使用我们强制的索引了，执行查询如下：
db.test_list_info.find({ xxxCodeXxx: "DB2C8D44F726", pushXtime: { $gte: new Date(1604166860000), $lt: new Date(1604678346860) }, xxxCodeXxxBelong: { $in: [ "2D4B761B7D72", "EC1321FF6AF7", "6F2DE0CDA4DE", "9148E85DD884", "1FB8011CEC78", "36471362D702", "013BDA4B799A", "DFA0AA339CB3", "1FFB9F0C0AF9", "78EFFB2F9D81", "368D14A6A470", "1AD3433D726B" ] }, pushXstatus: { $in: [ 0, 3, 4 ] }, "basicXxxResult.basicXxxCode" : "6733"},{xxxId: true, insureArea: true, xxxCodeXxx: true, xxXNameXxx: true, xxxCodeXxxBelong: true, xxXNameXxxBelong: true, businessType: true, nameType: true, sysSource: true, listType: true, xxxUcid: true, licenceXno: true, userid: true, pushXtime: true, pushXstatus: true, noSendReason: true, xxxResult: true, basicXxxResult: true, combeXxxResult: true, quoteXxxGroup: true, pushXtime: true}).sort({ pushXtime: -1 }).limit(10).hint('idx_ccode_cbelong')
我们可以看出同样的查询，执行变成了199毫秒，相比较之前，至少提升了一个数量级。
从上面我们可以推断出查询计划选择索引是会对执行时间有比较大的影响，为了更好地验证我们的猜想，我们使用查询二进行验证：
db.test_list_info.find({ xxxCodeXxx: "DB2C8D44F726", pushXtime: { $gte: new Date(1604166860000), $lt: new Date(1604678346860) }, xxxCodeXxxBelong: { $in: [ "2D4B761B7D72", "EC1321FF6AF7", "6F2DE0CDA4DE", "9148E85DD884", "1FB8011CEC78", "36471362D702", "013BDA4B799A", "DFA0AA339CB3", "1FFB9F0C0AF9", "78EFFB2F9D81", "368D14A6A470", "1AD3433D726B" ] }, pushXstatus: { $in: [ 0, 3, 4 ] }, "basicXxxResult.basicXxxCode" : "6733"},{xxxId: true, insureArea: true, xxxCodeXxx: true, xxXNameXxx: true, xxxCodeXxxBelong: true, xxXNameXxxBelong: true, businessType: true, nameType: true, sysSource: true, listType: true, xxxUcid: true, licenceXno: true, userid: true, pushXtime: true, pushXstatus: true, noSendReason: true, xxxResult: true, basicXxxResult: true, combeXxxResult: true, quoteXxxGroup: true, pushXtime: true}).sort({ pushXtime: -1 }).limit(10)
索引名 | 估计时间（毫秒） |
idx_ccode_cbelong | 250 |
pushXtime_-1 | 200 |
pushXstatus_1 | 360 |
xxxCodeXxxBelong_-1 | 1140 |
idx_company_car | 500 |
我们对各个索引估算时间求和250 + 200 + 360 + 1140 + 500 = 2450，我们可以看出评估时间和和查询实际执行其实非常接近，下面我们强制查询二走索引idx_ccode_cbelong如下：
db.test_list_info.explain('executionStats').find({ xxxCodeXxx: "DB2C8D44F726", pushXtime: { $gte: new Date(1604166860000), $lt: new Date(1604678346860) }, xxxCodeXxxBelong: { $in: [ "2D4B761B7D72", "EC1321FF6AF7", "6F2DE0CDA4DE", "9148E85DD884", "1FB8011CEC78", "36471362D702", "013BDA4B799A", "DFA0AA339CB3", "1FFB9F0C0AF9", "78EFFB2F9D81", "368D14A6A470", "1AD3433D726B" ] }, pushXstatus: { $in: [ 0, 3, 4 ] }, "basicXxxResult.basicXxxCode" : "6733"},{xxxId: true, insureArea: true, xxxCodeXxx: true, xxXNameXxx: true, xxxCodeXxxBelong: true, xxXNameXxxBelong: true, businessType: true, nameType: true, sysSource: true, listType: true, xxxUcid: true, licenceXno: true, userid: true, pushXtime: true, pushXstatus: true, noSendReason: true, xxxResult: true, basicXxxResult: true, combeXxxResult: true, quoteXxxGroup: true, pushXtime: true}).sort({ pushXtime: -1 }).limit(10).hint('idx_ccode_cbelong')
五、解决方法
六、结论
七、扩展阅读
// 计算每个索引的分数并记录最好的.for (size_t i = 0; i < statTrees.size(); ++i) {if (!candidates[i].failed) {LOGV2_DEBUG(20956,5,"Scoring plan {planIndex}:\n{querySolution}Stats:\n{stats}","Scoring plan","planIndex"_attr = i,"querySolution"_attr = redact(candidates[i].solution->toString()),"stats"_attr = redact(Explain::statsToBSON(*statTrees[i]).jsonString(ExtendedRelaxedV2_0_0, true)));LOGV2_DEBUG(20957,2,"Scoring query plan: {planSummary} planHitEOF={planHitEOF}","Scoring query plan","planSummary"_attr = Explain::getPlanSummary(candidates[i].root),"planHitEOF"_attr = statTrees[i]->common.isEOF);double score = scoreTree(statTrees[i].get());LOGV2_DEBUG(20958, 5, "Basic plan score: {score}", "Basic plan score", "score"_attr = score);if (statTrees[i]->common.isEOF) {LOGV2_DEBUG(20959,5,"Adding +{eofBonus} EOF bonus to score","Adding EOF bonus to score","eofBonus"_attr = eofBonus);score += 1;}scoresAndCandidateindices.push_back(std::make_pair(score, i));} else {failed.push_back(i);LOGV2_DEBUG(20960,2,"Not scoring plan: {planSummary} because the plan failed","Not scoring a plan because the plan failed","planSummary"_attr = Explain::getPlanSummary(candidates[i].root));}}double PlanRanker::scoreTree(const PlanStageStats* stats) {所有的分数以1开始double baseScore = 1;“工作单元”的数量，查询执行阶段将其工作分为小单元size_t workUnits = stats->common.works;invariant(workUnits != 0);common.advanced是每个索引扫描的时候是否能在collection拿到符合条件的记录，如果能拿到记录那么common.advanced就加1，workUnits则是总共扫描的次数范围: [0, 1]double productivity =static_cast<double>(stats->common.advanced) static_cast<double>(workUnits);workUnits就是每个索引的扫描次数，那么这里的意思就是取1.0 static_cast(10 * workUnits)和1e-4中最小的值const double epsilon = std::min(1.0 static_cast<double>(10 * workUnits), 1e-4);查询不需执行fetch阶段.double noFetchBonus = epsilon;if (hasStage(STAGE_FETCH, stats)) {noFetchBonus = 0;}解决方案更倾向于没有sort阶段double noSortBonus = epsilon;if (hasStage(STAGE_SORT_DEFAULT, stats) || hasStage(STAGE_SORT_SIMPLE, stats)) {noSortBonus = 0;}索引交叉通常比单字段索引解决方案更慢，需要检查索引键的超集，索引交叉解决方案检查相同数量或更少的文档在这种情况下索引交叉允许我们去检查更少的文档，使用交叉索引会扣分double noIxisectBonus = epsilon;if (hasStage(STAGE_AND_HASH, stats) || hasStage(STAGE_AND_SORTED, stats)) {noIxisectBonus = 0;}double tieBreakers = noFetchBonus + noSortBonus + noIxisectBonus;double score = baseScore + productivity + tieBreakers;if (shouldLog(logv2::LogSeverity::Debug(2))) {StringBuilder sb;sb << "baseScore(" << str::convertDoubleToString(baseScore) << ")"<< " + productivity((" << stats->common.advanced << " advanced)/(" << stats->common.works<< " works) = " << str::convertDoubleToString(productivity) << ")"<< " + tieBreakers(" << str::convertDoubleToString(noFetchBonus) << " noFetchBonus + "<< str::convertDoubleToString(noSortBonus) << " noSortBonus + "<< str::convertDoubleToString(noIxisectBonus)<< " noIxisectBonus = " << str::convertDoubleToString(tieBreakers) << ")";LOGV2_DEBUG(20961,2,"score({score}) = {calculation}","Plan score calculation","score"_attr = score,"calculation"_attr = sb.str());}if (internalQueryForceIntersectionPlans.load()) {if (hasStage(STAGE_AND_HASH, stats) || hasStage(STAGE_AND_SORTED, stats)) {强制交叉索引计划score += 3;LOGV2_DEBUG(20962,5,"Score boosted to {newScore} due to intersection forcing","Score boosted due to intersection forcing","newScore"_attr = score);}}return score;}
2、为什么针对每个候选索引估算时间算入查询总时间?
MongoDB会将最优的执行计划缓存到cache中，等待下次同样的查询执行的时候会采用cache中的执行计划，MongoDB优化器计算选择索引时，实际上会touch数据（会实际扫描数据做判断），这个跟MySQL有点不一样，MySQL是通过采集一定数量的page来生成索引统计信息的，但是MySQL和MongoDB都存在由于数据分布不均可能导致优化器选择错误的问题，MySQL 8.0的直方图功能实际在生产上也很鸡肋用处不大。
这个问题很明显是YES，笔者之前所在公司把MongoDB用在最核心的系统上面，MongoDB天然支持分布式的特点可以解决很多性能和扩展问题，但是DBA对MongoDB必须要有一定的把控能力，比如核心核心系统用了分布式MongoDB那么要对所有集合分片防止出现数据和性能倾斜，可以自定义用户角色控制用户权限，核心数据要使用写关注等，另外针对MongoDB定期对开发做一下相关培训。
八、参考资料
https://docs.mongodb.com/manual/reference/explain-results/
https://docs.mongodb.com/manual/reference/method/PlanCache.list/#PlanCache.list
扫描二维码获取
更多知识
DBA入坑指南
点个在看 你最好看
文章转载自DBA入坑指南，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
