db.test.insert({price:45,category:'normal',brand:'GE'});db.createIndex({price:-1});db.createIndex({category:1,brand:1}); query1: db.test.find({brand:'GE'}); query2: db.test.find({brand:'GE'}).sort({price:1}); query3: db.test.find({$and:[{price:{$lt:50}}, {price:{$gt:30}}]}).sort({brand:1}); query4: db.test.find({ brand:'GE'}).sort({category:1, brand:-1});
场景还原:假设按照如上操作,插入一条数据,并建立索引,分别执行query,问有哪些使用了索引,为什么使用这样的索引?
提示:可以使用explain查看详细信息,以下是四条语句的executionStats;
query1: "executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 1, "executionStages" : { "stage" : "COLLSCAN", "filter" : { "brand" : { "$eq" : "GE" } }, "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 1, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "direction" : "forward", "docsExamined" : 1 }, "allPlansExecution" : [ ] } query2: "executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 0, "totalKeysExamined" : 1, "totalDocsExamined" : 1, "executionStages" : { "stage" : "FETCH", "filter" : { "brand" : { "$eq" : "GE" } }, "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 1, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "price" : -1 }, "indexName" : "price_-1", "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 1, "direction" : "backward", "indexBounds" : { "price" : [ "[MinKey, MaxKey]" ] }, "keysExamined" : 1, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } }, "allPlansExecution" : [ ] } query3:"executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 8, "totalKeysExamined" : 1, "totalDocsExamined" : 1, "executionStages" : { "stage" : "SORT", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 5, "advanced" : 1, "needTime" : 3, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "sortPattern" : { "brand" : 1 }, "memUsage" : 78, "memLimit" : 33554432, "inputStage" : { "stage" : "KEEP_MUTATIONS", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 1, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "nReturned" : 0, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 0, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "inputStage" : { "stage" : "FETCH", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 1, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "price" : -1 }, "indexName" : "price_-1", "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 1, "direction" : "forward", "indexBounds" : { "price" : [ "(50.0, 30.0)" ] }, "keysExamined" : 1, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } } } } }, "allPlansExecution" : [ ] } query4:"executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 1, "executionStages" : { "stage" : "SORT", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 6, "advanced" : 1, "needTime" : 4, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "sortPattern" : { "category" : 1, "brand" : -1 }, "memUsage" : 78, "memLimit" : 33554432, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "nReturned" : 0, "executionTimeMillisEstimate" : 0, "works" : 4, "advanced" : 0, "needTime" : 2, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "inputStage" : { "stage" : "COLLSCAN", "filter" : { "brand" : { "$eq" : "GE" } }, "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 1, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "direction" : "forward", "docsExamined" : 1 } } }, "allPlansExecution" : [ ] }
分析:query1毋庸置疑,由于brand并没有索引,所以stage为COLLSCAN 全collection扫描
query2 因为查询语句执行优先级为,等值条件>排序条件>范围条件 索引stage为FETCH,进行了取值的查询,问题1,TETCH和COLLSCAN有什么区别,因为当collect中2条数据时executionStats.nReturned = 1, executionStats.totalKeysExamined = 2, executionStats.totalDocsExamined = 2, 可以看到仍然是检索了全表的doc和索引;
query3 对于该查询语句则是一脸懵逼,因为stage为SORT,并非四种常见的stage。 官方文档给出了解释,由于本人英语比较渣,所以个人的理解是:当客户端请求将结果排序返回时,如果mongodb不能使用索引字段进行排序,那么stage将是SORT,如果能则不包含stage为SORT;
这个当然不是重点,重点是stage还有KEEP_MUTATIONS SORT_KEY_GENERATOR 这个不仅官方没有找到资料,Google也无能为了,只能希冀中文社区大神的帮助解释一下了, 问题2为何query3的executionStages有4层inputStage嵌套,而且最底层才使用price索引;
query4 同理query3的查询中出现的stage为SORT,因为mongodb并不能根据已有索引进行排序,所以也出现了stage为SORT,当然对于SORT_KEY_GENERATOR 还是一脸懵逼;问题3 为何query4不能使用category_1_brand_1这个索引,或者换个角度,
category_1_brand_1 和category_1_brand_-1有区别吗?如果有,那么category_1_brand_1 和 brand_1_category_1有区别吗?当然对于该问题,本质上是不太了解mongodb的索引结构(虽然知道是B-Tree,但是仍旧不清楚:()
希望大神不吝赐教
先说明一下基本问题,看explain结果通常只看winningPlan,这才是最终选择的执行计划
看计划从里往外看才是实际的执行顺序。从外往里是MongoDB的分析顺序。
问题1:
首选这个查询是使用了索引的,所以可以看到IXSCAN。因为使用索引来排序,但显然你的price索引并不能帮助过滤出{brand: “GE”},所以返回了2个文档。其次注意索引只提供一个入口,而不是整个文档。要拿到文档的内容还需要FETCH操作。所以mongodb在FETCH的时候做了一次filter,找出满足{brand: “GE”}的文档,最后返回回来。
问题2:
这里索引{price: -1}可以帮助过滤文档,所以可以直接得到满足条件的一系列文档的入口。但是这个索引显然不能帮助你排序。所以在得到文档后得有一个SORT的动作在内存中排序。但是现在你通过索引只拿到一堆入口啊,所以IXSCAN上面一级先有了FETCH抓取所有的文档;然而FETCH到一堆文档之后还需要从中取出你想要排序的KEY,也就是brand。所以有了SORT_KEY_GENERATOR。最后再根据这一级生成的KEY来排序,也就是SORT。
问题3:
(不好意思赶时间,回头再答这个问题)