MongoDB干货系列2-MongoDB执行计划分析详解（3）

朴灿烈づ我的快乐病毒、 2022-06-05 22:07 142阅读 0赞

原文链接： [http://www.mongoing.com/eshu\_explain3][http_www.mongoing.com_eshu_explain3]

## 写在之前的话 ##

作为近年最为火热的文档型数据库，MongoDB受到了越来越多人的关注，但是由于国内的MongoDB相关技术分享屈指可数，不少朋友向我抱怨无从下手。

《MongoDB干货系列》将从实际应用的角度来进行MongoDB的一些列干货的分享，将覆盖调优，troubleshooting等方面，希望能对大家带来帮助。

如果希望了解更多MongoDB基础的信息，还请大家Google下。

要保证数据库处于高效、稳定的状态，除了良好的硬件基础、高效高可用的数据库架构、贴合业务的数据模型之外，高效的查询语句也是不可少的。那么，如何查看并判断我们的执行计划呢？我们今天就来谈论下MongoDB的执行计划分析。

## 引子 ##

MongoDB 3.0之后，explain的返回与使用方法与之前版本有了不少变化，介于3.0之后的优秀特色，本文仅针对MongoDB 3.0+的explain进行讨论。

现版本explain有三种模式，分别如下：

*  queryPlanner
 *  executionStats
 *  allPlansExecution

由于文章字数原因，本系列将分为三个部分。  
[第一部分][Link 1]  
[第二部分][Link 2]  
[第三部分][http_www.mongoing.com_eshu_explain3]

本文是MongoDB执行计划分析详解的最后一个部分，我们将对该如何分析exlain信息进行详细解读，并将针对实例进行explain分析详解。

## 正文 ##

### 对Explain返回逐层分析 ###

#### 第一层，executionTimeMillis。 ####

首先，最为直观explain返回值是executionTimeMillis值，指的是我们这条语句的执行时间，这个值当然是希望越少越好。

且executionTimeMillis 与stage有同样的层数，即：

"executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 29861,
                    "executionTimeMillis" : 66948,
                    "totalKeysExamined" : 29861,
                    "totalDocsExamined" : 29861,
                    "executionStages" : {
                            "stage" : "FETCH",
                            "nReturned" : 29861,
                            "executionTimeMillisEstimate" : 66244,
                            "works" : 29862,
                            "advanced" : 29861,
                            "needTime" : 0,
                            "needFetch" : 0,
                            "saveState" : 2934,
                            "restoreState" : 2934,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "docsExamined" : 29861,
                            "alreadyHasObj" : 0,
                            "inputStage" : {
                                    "stage" : "IXSCAN",
                                    "nReturned" : 29861,
                                    "executionTimeMillisEstimate" : 290,
                                    "works" : 29862,
                                    "advanced" : 29861,
                                    "needTime" : 0,
                                    "needFetch" : 0,
                                    "saveState" : 2934,
                                    "restoreState" : 2934,
                                    ...

其中有3个executionTimeMillis，分别是

executionStats.executionTimeMillis

该query的整体查询时间

executionStats.executionStages.executionTimeMillis

该查询根据index去检索document获取29861条具体数据的时间

executionStats.executionStages.inputStage.executionTimeMillis

该查询扫描29861行index所用时间

这三个值我们都希望越少越好，那么是什么影响这这三个返回值呢？

抛开硬件因素等不谈，我们来进行下一层的剥离。

#### 第二层，index与document扫描数与查询返回条目数 ####

这里主要谈3个返回项，nReturned，totalKeysExamined与totalDocsExamined，分别代表该条查询返回的条目、索引扫描条目和文档扫描条目。

很好理解，这些都直观的影响到executionTimeMillis，我们需要扫描的越少速度越快。

对于一个查询， 我们最理想的状态是

nReturned=totalKeysExamined & totalDocsExamined=0

（cover index，仅仅使用到了index，无需文档扫描，这是最理想状态。）

或者

nReturned=totalKeysExamined=totalDocsExamined(需要具体情况具体分析)

（正常index利用，无多余index扫描与文档扫描。）

如果有sort的时候，为了使得sort不在内存中进行，我们可以在保证nReturned=totalDocsExamined

的基础上，totalKeysExamined可以大于totalDocsExamined与nReturned，因为量级较大的时候内存排序非常消耗性能。

后面我们会针对例子来进行分析。

#### 第三层，Stage状态分析 ####

那么又是什么影响到了totalKeysExamined与totalDocsExamined呢？就是Stage的类型，Stage的具体含义在上文中有提及，如果认真看的同学就不难理解为何Stage会影响到totalKeysExamined 和totalDocsExamined从而影响executionTimeMillis了。

此前有讲解过stage的类型，这里再简单列举下（具体意义请看上文）

COLLSCAN

IXSCAN

FETCH

SHARD\_MERGE

SORT

LIMIT

SKIP

IDHACK

SHARDING\_FILTER

COUNT

COUNTSCAN

COUNT\_SCAN

SUBPLA

TEXT

PROJECTION

#### 对于普通查询，我们最希望看到的组合有这些： ####

Fetch+IDHACK

Fetch+ixscan

Limit+（Fetch+ixscan）

PROJECTION+ixscan

SHARDING\_FILTER+ixscan

等

#### 不希望看到包含如下的stage： ####

COLLSCAN（全表扫），SORT（使用sort但是无index），不合理的SKIP，SUBPLA（未用到index的$or）

#### 对于count查询，希望看到的有： ####

COUNT\_SCAN

#### 不希望看到的有: ####

COUNTSCAN

### Explain分析实例 ###

表中数据如下(简单测试用例，仅10条数据，主要是对explain分析的逻辑进行解析)：

{ "_id" : ObjectId("55b86d6bd7e3f4ccaaf20d70"), "a" : 1, "b" : 1, "c" : 1 }
    { "_id" : ObjectId("55b86d6fd7e3f4ccaaf20d71"), "a" : 1, "b" : 2, "c" : 2 }
    { "_id" : ObjectId("55b86d72d7e3f4ccaaf20d72"), "a" : 1, "b" : 3, "c" : 3 }
    { "_id" : ObjectId("55b86d74d7e3f4ccaaf20d73"), "a" : 4, "b" : 2, "c" : 3 }
    { "_id" : ObjectId("55b86d75d7e3f4ccaaf20d74"), "a" : 4, "b" : 2, "c" : 5 }
    { "_id" : ObjectId("55b86d77d7e3f4ccaaf20d75"), "a" : 4, "b" : 2, "c" : 5 }
    { "_id" : ObjectId("55b879b442bfd1a462bd8990"), "a" : 2, "b" : 1, "c" : 1 }
    { "_id" : ObjectId("55b87fe842bfd1a462bd8991"), "a" : 1, "b" : 9, "c" : 1 }
    { "_id" : ObjectId("55b87fe942bfd1a462bd8992"), "a" : 1, "b" : 9, "c" : 1 }
    { "_id" : ObjectId("55b87fe942bfd1a462bd8993"), "a" : 1, "b" : 9, "c" : 1 }

查询语句：

db.d.find({a:1,b:{$lt:3}}).sort({c:-1})

##### 首先，我们看看没有index时候的查询计划 #####

"executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 2,
                    "executionTimeMillis" : 0,
                    "totalKeysExamined" : 0,
                    "totalDocsExamined" : 10,
                    "executionStages" : {
                            "stage" : "SORT",
                            "nReturned" : 2,
                            ...
                            "sortPattern" : {
                                    "c" : -1
                            },
                            "memUsage" : 126,
                            "memLimit" : 33554432,
                            "inputStage" : {
                                    "stage" : "COLLSCAN",
                                    "filter" : {
                                            "$and" : [
                                                    {
                                                            "a" : {
                                                                    "$eq" : 1
                                                            }
                                                    },
                                                    {
                                                            "b" : {
                                                                    "$lt" : 3
                                                            }
                                                    }
                                            ]
                                    },
                                    "nReturned" : 2,
                                   ...
                                    "direction" : "forward",
                                    "docsExamined" : 10
                            }

nReturned为2，符合的条件的返回为2条。

totalKeysExamined为0，没有使用index。

totalDocsExamined为10，扫描了所有记录。

executionStages.stage为SORT,未使用index的sort，占用的内存与内存限制为”memUsage” : 126, “memLimit” : 33554432。

executionStages.inputStage.stage为COLLSCAN，全表扫描，扫描条件为

"filter" : {
        "$and" : [
            {
                "a" : {
                      "$eq" : 1
                     }
            },
            {
                "b" : {
                    "$lt" : 3
                    }
            }
                ]
            },

很明显，没有index的时候，进行了全表扫描，没有使用到index，在内存中sort，很显然，和都是不可取的。

##### 下面，我们来对sort项c加一个索引 #####

db.d.ensureIndex({c:1})

再来看看执行计划

"executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 2,
                    "executionTimeMillis" : 1,
                    "totalKeysExamined" : 10,
                    "totalDocsExamined" : 10,
                    "executionStages" : {
                            "stage" : "FETCH",
                            "filter" : {
                                    "$and" : [
                                            {
                                                    "a" : {
                                                            "$eq" : 1
                                                    }
                                            },
                                            {
                                                    "b" : {
                                                            "$lt" : 3
                                                    }
                                            }
                                    ]
                            },
                            "nReturned" : 2,
                            ...
                            "inputStage" : {
                                    "stage" : "IXSCAN",
                                    "nReturned" : 10,
                                   ...
                                    "keyPattern" : {
                                            "c" : 1
                                    },
                                    "indexName" : "c_1",
                                    "isMultiKey" : false,
                                    "direction" : "backward",
                                    "indexBounds" : {
                                            "c" : [
                                                    "[MaxKey, MinKey]"
                                            ]
                                    },

我们发现，Stage没有了SORT，因为我们sort字段有了index，但是由于查询还是没有index，故totalDocsExamined还是10，但是由于sort用了index，totalKeysExamined也是10，但是仅对sort排序做了优化，查询性能还是一样的低效。

##### 接下来， 我们对查询条件做index（做多种index方案寻找最优） #####

我们的查询语句依然是:

db.d.find({a:1,b:{$lt:3}}).sort({c:-1})

使用`db.d.ensureIndex({b:1,a:1,c:1})`索引的执行计划：

"executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 2,
                    "executionTimeMillis" : 0,
                    "totalKeysExamined" : 4,
                    "totalDocsExamined" : 2,
                    "executionStages" : {
                            "stage" : "SORT",
                            "nReturned" : 2,
                            ...
                            "sortPattern" : {
                                    "c" : -1
                            },
                            "memUsage" : 126,
                            "memLimit" : 33554432,
                            "inputStage" : {
                                    "stage" : "FETCH",
                                    "nReturned" : 2,
                                    ...
                                    "inputStage" : {
                                            "stage" : "IXSCAN",
                                            "nReturned" : 2,
                                           ...
                                            "keyPattern" : {
                                                    "b" : 1,
                                                    "a" : 1,
                                                    "c" : 1
                                            },
                                            "indexName" : "b_1_a_1_c_1",
                                            "isMultiKey" : false,
                                            "direction" : "forward",
                                            "indexBounds" : {
                                                    "b" : [
                                                            "[-inf.0, 3.0)"
                                                    ],
                                                    "a" : [
                                                            "[1.0, 1.0]"
                                                    ],
                                                    "c" : [
                                                            "[MinKey, MaxKey]"
                                                    ]
                                            },

我们可以看到

nReturned为2，返回2条记录

totalKeysExamined为4，扫描了4个index

totalDocsExamined为2，扫描了2个docs

此时nReturned=totalDocsExamined<totalKeysExamined，不符合我们的期望。

且executionStages.Stage为Sort，在内存中进行排序了，也不符合我们的期望

使用`db.d.ensureIndex({a:1,b:1,c:1})`索引的执行计划：

"executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 2,
                    "executionTimeMillis" : 0,
                    "totalKeysExamined" : 2,
                    "totalDocsExamined" : 2,
                    "executionStages" : {
                            "stage" : "SORT",
                            "nReturned" : 2,
                            ...
                            "sortPattern" : {
                                    "c" : -1
                            },
                            "memUsage" : 126,
                            "memLimit" : 33554432,
                            "inputStage" : {
                                    "stage" : "FETCH",
                                    "nReturned" : 2,
                                    ...
                                    "inputStage" : {
                                            "stage" : "IXSCAN",
                                            "nReturned" : 2,
                                            ...
                                            "keyPattern" : {
                                                    "a" : 1,
                                                    "b" : 1,
                                                    "c" : 1
                                            },
                                            "indexName" : "a_1_b_1_c_1",
                                            "isMultiKey" : false,
                                            "direction" : "forward",
                                            "indexBounds" : {
                                                    "a" : [
                                                            "[1.0, 1.0]"
                                                    ],
                                                    "b" : [
                                                            "[-inf.0, 3.0)"
                                                    ],
                                                    "c" : [
                                                            "[MinKey, MaxKey]"
                                                    ]
                                            },

我们可以看到

nReturned为2，返回2条记录

totalKeysExamined为2，扫描了2个index

totalDocsExamined为2，扫描了2个docs

此时nReturned=totalDocsExamined=totalKeysExamined，符合我们的期望。看起来很美吧？

但是，但是，但是！重要的事情说三遍！executionStages.Stage为Sort，在内存中进行排序了，这个在生产环境中尤其是在数据量较大的时候，是非常消耗性能的，这个千万不能忽视了，我们需要改进这个点。

最后，我们要在nReturned=totalDocsExamined的基础上，让排序也使用index，我们使用`db.d.ensureIndex({a:1,c:1,b:1})`索引,执行计划如下：

"executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 2,
                    "executionTimeMillis" : 0,
                    "totalKeysExamined" : 4,
                    "totalDocsExamined" : 2,
                    "executionStages" : {
                            "stage" : "FETCH",
                            "nReturned" : 2,
                             ...
                            "inputStage" : {
                                    "stage" : "IXSCAN",
                                    "nReturned" : 2,
                                     ...
                                    "keyPattern" : {
                                            "a" : 1,
                                            "c" : 1,
                                            "b" : 1
                                    },
                                    "indexName" : "a_1_c_1_b_1",
                                    "isMultiKey" : false,
                                    "direction" : "backward",
                                    "indexBounds" : {
                                            "a" : [
                                                    "[1.0, 1.0]"
                                            ],
                                            "c" : [
                                                    "[MaxKey, MinKey]"
                                            ],
                                            "b" : [
                                                    "(3.0, -inf.0]"
                                            ]
                                    },
                                    "keysExamined" : 4,
                                    "dupsTested" : 0,
                                    "dupsDropped" : 0,
                                    "seenInvalidated" : 0,
                                    "matchTested" : 0

我们可以看到

nReturned为2，返回2条记录

totalKeysExamined为4，扫描了4个index

totalDocsExamined为2，扫描了2个docs

虽然不是nReturned=totalKeysExamined=totalDocsExamined，但是Stage无Sort，即利用了index进行排序，而非内存，这个性能的提升高于多扫几个index的代价。

综上可以有一个小结论，当查询覆盖精确匹配，范围查询与排序的时候，

`{精确匹配字段,排序字段,范围查询字段}`这样的索引排序会更为高效。

## 后文 ##

执行计划分析一文，到此便告一段落了，希望大家能够对于MongoDB的执行计划有所了解。

[http_www.mongoing.com_eshu_explain3]: http://www.mongoing.com/eshu_explain3
[Link 1]: http://www.mongoing.com/eshu_explain1
[Link 2]: http://www.mongoing.com/eshu_explain2