代码之家  ›  专栏  ›  技术社区  ›  Zack Newsham

不同主机上同一查询的MongoDB索引使用情况不同

mongodb
  •  0
  • Zack Newsham  · 技术社区  · 5 年前

    我正在运行一个非常简单的查询: db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25) ,其中teamIds是一个数组,上面有一个升序索引。如果我在次要对象上运行此查询, explain 显示了查看25个键和查看25个文档(如预期)。如果我在主服务器上运行完全相同的查询, 解释 显示了50K以上的键和文档被查看。我检查了主索引和次索引是否相同,以及是否都在使用。以下是两种情况下的解释结果。

    什么可能导致这种行为?

    是否有可能在主服务器上,索引未完全加载到内存中?主存储器使用约65%的内存,次存储器使用约55%(均为8GB)。

    编辑:

    我刚刚发现小学的成绩是3.6.8,而有问题的中学(表现得像我想要的那样)的成绩是3.7.7——这可能是原因吗?

    主要的,重要的 

    rs0:PRIMARY> db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25).explain({verbosity: "executionStats"})
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "dbName.users",
        "indexFilterSet" : false,
        "parsedQuery" : {

        },
        "winningPlan" : {
            "stage" : "PROJECTION",
            "transformBy" : {
                "_id" : 1
            },
            "inputStage" : {
                "stage" : "SORT",
                "sortPattern" : {
                    "teamIds" : 1
                },
                "limitAmount" : 25,
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "teamIds" : 1
                            },
                            "indexName" : "teamIds_1",
                            "isMultiKey" : true,
                            "multiKeyPaths" : {
                                "teamIds" : [
                                    "teamIds"
                                ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "teamIds" : [
                                    "[MinKey, MaxKey]"
                                ]
                            }
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 25,
        "executionTimeMillis" : 182,
        "totalKeysExamined" : 52320,
        "totalDocsExamined" : 51412,
        "executionStages" : {
            "stage" : "PROJECTION",
            "nReturned" : 25,
            "executionTimeMillisEstimate" : 180,
            "works" : 52348,
            "advanced" : 25,
            "needTime" : 52322,
            "needYield" : 0,
            "saveState" : 408,
            "restoreState" : 408,
            "isEOF" : 1,
            "invalidates" : 0,
            "transformBy" : {
                "_id" : 1
            },
            "inputStage" : {
                "stage" : "SORT",
                "nReturned" : 25,
                "executionTimeMillisEstimate" : 170,
                "works" : 52348,
                "advanced" : 25,
                "needTime" : 52322,
                "needYield" : 0,
                "saveState" : 408,
                "restoreState" : 408,
                "isEOF" : 1,
                "invalidates" : 0,
                "sortPattern" : {
                    "teamIds" : 1
                },
                "memUsage" : 25242,
                "memLimit" : 33554432,
                "limitAmount" : 25,
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "nReturned" : 51412,
                    "executionTimeMillisEstimate" : 140,
                    "works" : 52322,
                    "advanced" : 51412,
                    "needTime" : 909,
                    "needYield" : 0,
                    "saveState" : 408,
                    "restoreState" : 408,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "inputStage" : {
                        "stage" : "FETCH",
                        "nReturned" : 51412,
                        "executionTimeMillisEstimate" : 100,
                        "works" : 52321,
                        "advanced" : 51412,
                        "needTime" : 908,
                        "needYield" : 0,
                        "saveState" : 408,
                        "restoreState" : 408,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 51412,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 51412,
                            "executionTimeMillisEstimate" : 40,
                            "works" : 52321,
                            "advanced" : 51412,
                            "needTime" : 908,
                            "needYield" : 0,
                            "saveState" : 408,
                            "restoreState" : 408,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "keyPattern" : {
                                "teamIds" : 1
                            },
                            "indexName" : "teamIds_1",
                            "isMultiKey" : true,
                            "multiKeyPaths" : {
                                "teamIds" : [
                                    "teamIds"
                                ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "teamIds" : [
                                    "[MinKey, MaxKey]"
                                ]
                            },
                            "keysExamined" : 52320,
                            "seeks" : 1,
                            "dupsTested" : 52320,
                            "dupsDropped" : 908,
                            "seenInvalidated" : 0
                        }
                    }
                }
            }
        },
        "allPlansExecution" : [ ]
    },
    "serverInfo" : {
        "host" : "mongo1.justplayss.com",
        "port" : 27017,
        "version" : "3.6.8",
        "gitVersion" : "6bc9ed599c3fa164703346a22bad17e33fa913e4"
    },
    "ok" : 1
}

    中学 

    rs0:SECONDARY> db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25).explain({verbosity: "executionStats"})
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "dbName.users",
        "indexFilterSet" : false,
        "parsedQuery" : {

        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 25,
            "inputStage" : {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "_id" : 1
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "teamIds" : 1
                        },
                        "indexName" : "teamIds_1",
                        "isMultiKey" : true,
                        "multiKeyPaths" : {
                            "teamIds" : [
                                "teamIds"
                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 1,
                        "direction" : "forward",
                        "indexBounds" : {
                            "teamIds" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 25,
        "executionTimeMillis" : 6,
        "totalKeysExamined" : 25,
        "totalDocsExamined" : 25,
        "executionStages" : {
            "stage" : "LIMIT",
            "nReturned" : 25,
            "executionTimeMillisEstimate" : 0,
            "works" : 26,
            "advanced" : 25,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "limitAmount" : 25,
            "inputStage" : {
                "stage" : "PROJECTION",
                "nReturned" : 25,
                "executionTimeMillisEstimate" : 0,
                "works" : 25,
                "advanced" : 25,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 0,
                "invalidates" : 0,
                "transformBy" : {
                    "_id" : 1
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "nReturned" : 25,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 25,
                    "advanced" : 25,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 0,
                    "restoreState" : 0,
                    "isEOF" : 0,
                    "invalidates" : 0,
                    "docsExamined" : 25,
                    "alreadyHasObj" : 0,
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "nReturned" : 25,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 25,
                        "advanced" : 25,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 0,
                        "invalidates" : 0,
                        "keyPattern" : {
                            "teamIds" : 1
                        },
                        "indexName" : "teamIds_1",
                        "isMultiKey" : true,
                        "multiKeyPaths" : {
                            "teamIds" : [
                                "teamIds"
                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 1,
                        "direction" : "forward",
                        "indexBounds" : {
                            "teamIds" : [
                                "[MinKey, MaxKey]"
                            ]
                        },
                        "keysExamined" : 25,
                        "seeks" : 1,
                        "dupsTested" : 25,
                        "dupsDropped" : 0,
                        "seenInvalidated" : 0
                    }
                }
            }
        },
        "allPlansExecution" : [ ]
    },
    "serverInfo" : {
        "host" : "ip-172-31-6-96",
        "port" : 27017,
        "version" : "3.4.15",
        "gitVersion" : "52e5b5fbaa3a2a5b1a217f5e647b5061817475f9"
    },
    "ok" : 1
}
    0 回复  |  直到 5 年前
        1
  •  1
  •   Joe    5 年前

    这归结为:“按包含数组的字段对文档进行排序意味着什么?”

    考虑3份文件: 

    {a: [ 3, 4, 5 ]}
{a: [ 2, 1, 4 ]}
{a: [ 1, 3, 2 ]}

    如果我们排序 {a:1} 他们可能会以几种不同的方式回来:

    按数组的二进制表示进行排序,在这种情况下,它给出的结果与比较每个元素的第一个元素相同,给出: 

    {a: [ 1, 3, 2 ]}
{a: [ 2, 1, 4 ]}
{a: [ 3, 4, 5 ]}

    包含数组的字段上的索引为数组的每个元素都有一个单独的键,而不是数组值本身的键。

    如果我们在字段上创建索引 a ,它将包含9个键: 

    1 - record 2
1 - record 3
2 - record 2
2 - record 3
3 - record 1
3 - record 3
4 - record 1
5 - record 1

    因此,如果我们使用用于排序的索引,那么文档将按顺序排列: 

    {a: [ 2, 1, 4 ]}
{a: [ 1, 3, 2 ]}
{a: [ 3, 4, 5 ]}

    这意味着,如果我们使用limit(1)来只查找第一个文档,我们的结果会根据可用索引和规划器选择的索引而变化,而规划器可以根据查询部分而变化。

    排序顺序也因 find 和 aggregate .

    在MongoDB 3.6中,通过限制何时可以使用多键索引进行排序,发现并修复了这个问题。结果是,数组字段上的大多数排序现在都阻塞了内存操作,这意味着性能显著降低,以换取一致的排序和结果。

    您包含的解释输出显示,辅助节点正在使用索引进行排序,而主节点正在加载所有文档并执行内存排序。

    推荐文章
    Tom McLean  ·  如何使用证书管理器制作的证书在AWS上创建到mongodb实例的TLS/SSL连接?健康检查失败
    5 月前
    hasratyan  ·  Next.js 15 API路由-动态路由中的无效GET导出错误[id]
    5 月前
    RoadieRich  ·  MongoDB:无法创建<type>的实例,因为它是一个抽象类
    5 月前
    Plup  ·  连接失败时,PyMongo异步客户端未引发异常
    6 月前
    Yusuf Damista  ·  将mongodb节点代码转换为php
    7 月前
    ian12_  ·  客户端未定义,无法使用mongodb驱动程序连接到数据库
    10 月前
    Sachin  ·  Spring Session响应MongoDB-如何处理属性
    10 月前
    Isus Ramzy  ·  如何在Vercel中使用“pymongo”和“flask”
    10 月前
    weareblahs  ·  由于BSONError,路径(pathName)处的值(数组)转换为ObjectId失败
    10 月前
    sachin p  ·  MongoDB docker的默认用户和密码
    10 月前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号