mongodb - MongoDB查询很慢（使用索引）

Question

这是我的查询

db.getCollection("images").find({
    "$and": [
        { "text": { "$ne": null } },
        { "text": { "$ne": "" } },
    ],
    "width":"3120"

})

结果explain：

{ 
    "queryPlanner" : {
        "plannerVersion" : 1.0, 
        "namespace" : "mhxy.images", 
        "indexFilterSet" : false, 
        "parsedQuery" : {
            "$and" : [
                {
                    "width" : {
                        "$eq" : "3120"
                    }
                }, 
                {
                    "text" : {
                        "$not" : {
                            "$eq" : null
                        }
                    }
                }, 
                {
                    "text" : {
                        "$not" : {
                            "$eq" : ""
                        }
                    }
                }
            ]
        }, 
        "queryHash" : "7DCCCA85", 
        "planCacheKey" : "5D3F5173", 
        "winningPlan" : {
            "stage" : "FETCH", 
            "filter" : {
                "$and" : [
                    {
                        "text" : {
                            "$not" : {
                                "$eq" : null
                            }
                        }
                    }, 
                    {
                        "text" : {
                            "$not" : {
                                "$eq" : ""
                            }
                        }
                    }
                ]
            }, 
            "inputStage" : {
                "stage" : "IXSCAN", 
                "keyPattern" : {
                    "width" : 1.0
                }, 
                "indexName" : "idx_width", 
                "isMultiKey" : false, 
                "multiKeyPaths" : {
                    "width" : [

                    ]
                }, 
                "isUnique" : false, 
                "isSparse" : false, 
                "isPartial" : false, 
                "indexVersion" : 2.0, 
                "direction" : "forward", 
                "indexBounds" : {
                    "width" : [
                        "[\"3120\", \"3120\"]"
                    ]
                }
            }
        }, 
        "rejectedPlans" : [
            {
                "stage" : "FETCH", 
                "filter" : {
                    "width" : {
                        "$eq" : "3120"
                    }
                }, 
                "inputStage" : {
                    "stage" : "IXSCAN", 
                    "keyPattern" : {
                        "text" : 1.0
                    }, 
                    "indexName" : "idx_text", 
                    "isMultiKey" : false, 
                    "multiKeyPaths" : {
                        "text" : [

                        ]
                    }, 
                    "isUnique" : false, 
                    "isSparse" : false, 
                    "isPartial" : false, 
                    "indexVersion" : 2.0, 
                    "direction" : "forward", 
                    "indexBounds" : {
                        "text" : [
                            "[MinKey, undefined)", 
                            "(null, \"\")", 
                            "(\"\", MaxKey]"
                        ]
                    }
                }
            }, 
            {
                "stage" : "FETCH", 
                "inputStage" : {
                    "stage" : "IXSCAN", 
                    "keyPattern" : {
                        "text" : 1.0, 
                        "width" : 1.0
                    }, 
                    "indexName" : "idx_text_width", 
                    "isMultiKey" : false, 
                    "multiKeyPaths" : {
                        "text" : [

                        ], 
                        "width" : [

                        ]
                    }, 
                    "isUnique" : false, 
                    "isSparse" : false, 
                    "isPartial" : false, 
                    "indexVersion" : 2.0, 
                    "direction" : "forward", 
                    "indexBounds" : {
                        "text" : [
                            "[MinKey, undefined)", 
                            "(null, \"\")", 
                            "(\"\", MaxKey]"
                        ], 
                        "width" : [
                            "[\"3120\", \"3120\"]"
                        ]
                    }
                }
            }
        ]
    }, 
    "serverInfo" : {
        "host" : "...", 
        "port" : 27017.0, 
        "version" : "4.2.1", 
        "gitVersion" : "edf6d45851c0b9ee15548f0f847df141764a317e"
    }, 
    "ok" : 1.0
}

此集合大小超过 13Gb。和这样的文件：

{ 
    "_id" : ObjectId("5dbac8665ef144e92017e190"), 
    "o_img" : BinData(0, "/9j/4AAQSkZJRgABAQAAAQABAAD/4gIo.. 1103798 more bytes"), 
    "c_img" : BinData(0, "/9j/4AAQSkZJRgABAQAAAQABAAD/4gIo.. 81058 more bytes"), 
    "date" : ISODate("2019-10-31T11:41:26.238+0000"), 
    "text" : "....", 
    "correct_text" : "....", 
    "bit" : "119667", 
    "width" : "3120", 
    "height" : "1440"
}

似乎查询过程的每一步都命中了索引。

但查询至少需要 20 秒。

更重要的是，我发现mongodb占用了我电脑90%以上的内存。

我的电脑内存太小了吗？我可以让 mongodb 不将二进制数据加载到内存中吗？

标签： mongodb