findAndModify不使用索引搜索

Question

我正在使用python（pymongo）和Mogno4.2

我的数据库包含大约100万个文档 ，数据库中的一个样本：

{
    "_id" : ObjectId("5f41983da09c453f96cebf02"),
    "my_id" : "1",
    "data": {
        "status": "new"
    }
}

我创建了两个索引，一个用于“我的id”，另一个用于“data.status”字段

在我的代码中，我只使用索引字段执行一个简单的查找和更新：

document = collection.find_one_and_update(
            filter={
                "$and": [
                    {"data.status": "new"},
                    {"_id": ObjectId("5f41983da09c453f96cebf02")},
                ]
            },
            update={
                "$set": {
                    "data": {
                        "status": "in_progress",
                        "last_update": datetime.datetime.utcnow(),
                        "other_data": data
                     }
                }
            }
        )

在满量程生产环境下运行此操作会导致COLLSCAN而不是IXSCAN。 但是在robo3t上手动运行它的操作是IXSCAN

我的问题是，为什么mogno不使用我的索引

编辑：

以下是生产过程中发生的操作的系统配置文件输出-

{
    "op" : "command",
    "ns" : "db.collection",
    "command" : {
        "findAndModify" : "collection",
        "query" : {
            "$and" : [ 
                {
                    "data.status" : "new"
                }, 
                {
                    "_id" : ObjectId("5f41983da09c453f96cebf02")
                }
            ]
        },
        "new" : false,
        "update" : {
            "$set" : {
                "data" : {
                    "status" : "in_progress",
                    "last_update" : ISODate("2020-07-30T14:16:23.290Z")
                }
            }
        },
        "sort" : {
            "$natural" : 1
        },
        "upsert" : false,
        "lsid" : {
            "id" : UUID("opkrstuv-abcd-1234-efgh-5678ijkl90mn")
        },
        "$db" : "db",
        "$readPreference" : {
            "mode" : "primary"
        }
    },
    "keysExamined" : 0,
    "docsExamined" : 902501,
    "nMatched" : 1,
    "nModified" : 1,
    "keysInserted" : 1,
    "keysDeleted" : 1,
    "numYield" : 7090,
    "queryHash" : "AAAAAAAA",
    "planCacheKey" : "BBBBBBBB",
    "locks" : {
        "ParallelBatchWriterMode" : {
            "acquireCount" : {
                "r" : NumberLong(7092)
            }
        },
        "ReplicationStateTransition" : {
            "acquireCount" : {
                "w" : NumberLong(7092)
            }
        },
        "Global" : {
            "acquireCount" : {
                "r" : NumberLong(1),
                "w" : NumberLong(7091)
            }
        },
        "Database" : {
            "acquireCount" : {
                "w" : NumberLong(7091)
            }
        },
        "Collection" : {
            "acquireCount" : {
                "w" : NumberLong(7091)
            }
        },
        "Mutex" : {
            "acquireCount" : {
                "r" : NumberLong(2)
            }
        }
    },
    "flowControl" : {
        "acquireCount" : NumberLong(7091),
        "timeAcquiringMicros" : NumberLong(1799)
    },
    "storage" : {},
    "responseLength" : 168,
    "protocol" : "op_msg",
    "millis" : 24746,
    "planSummary" : "COLLSCAN",
    "execStats" : {
        "stage" : "UPDATE",
        "nReturned" : 1,
        "executionTimeMillisEstimate" : 4009,
        "works" : 902502,
        "advanced" : 1,
        "needTime" : 902501,
        "needYield" : 0,
        "saveState" : 7090,
        "restoreState" : 7090,
        "isEOF" : 1,
        "nMatched" : 1,
        "nWouldModify" : 1,
        "wouldInsert" : false,
        "inputStage" : {
            "stage" : "LIMIT",
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 3766,
            "works" : 902502,
            "advanced" : 1,
            "needTime" : 902501,
            "needYield" : 0,
            "saveState" : 7091,
            "restoreState" : 7091,
            "isEOF" : 1,
            "limitAmount" : 1,
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "$and" : [ 
                        {
                            "_id" : {
                                "$eq" : ObjectId("5f41983da09c453f96cebf02")
                            }
                        }, 
                        {
                            "data.status" : {
                                "$eq" : "new"
                            }
                        }
                    ]
                },
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 3726,
                "works" : 902502,
                "advanced" : 1,
                "needTime" : 902501,
                "needYield" : 0,
                "saveState" : 7091,
                "restoreState" : 7091,
                "isEOF" : 0,
                "direction" : "forward",
                "docsExamined" : 902501
            }
        }
    },
    "ts" : ISODate("2020-07-30T14:16:48.069Z"),
    "client" : "256.256.256.256",
    "allUsers" : [],
    "user" : ""
}

这是我从3T手动运行的.explain()的输出

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "db.collection",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "_id" : {
                        "$eq" : ObjectId("5f41983da09c453f96cebf02")
                    }
                }, 
                {
                    "data.status" : {
                        "$eq" : "new"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "UPDATE",
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "data.status" : {
                        "$eq" : "new"
                    }
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "_id" : 1
                    },
                    "indexName" : "_id_",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "_id" : []
                    },
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [ 
                            "[ObjectId('5f41983da09c453f96cebf02'), ObjectId('5f41983da09c453f96cebf02')]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 1,
        "executionStages" : {
            "stage" : "UPDATE",
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 1,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "nMatched" : 1,
            "nWouldModify" : 1,
            "wouldInsert" : false,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "data.status" : {
                        "$eq" : "new"
                    }
                },
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 0,
                "works" : 1,
                "advanced" : 1,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 1,
                "restoreState" : 1,
                "isEOF" : 0,
                "docsExamined" : 1,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 1,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 1,
                    "advanced" : 1,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 1,
                    "restoreState" : 1,
                    "isEOF" : 0,
                    "keyPattern" : {
                        "_id" : 1
                    },
                    "indexName" : "_id_",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "_id" : []
                    },
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [ 
                            "[ObjectId('5f41983da09c453f96cebf02'), ObjectId('5f41983da09c453f96cebf02')]"
                        ]
                    },
                    "keysExamined" : 1,
                    "seeks" : 1,
                    "dupsTested" : 0,
                    "dupsDropped" : 0
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "host",
        "port" : 00000,
        "version" : "4.2.8",
        "gitVersion" : "1"
    },
    "ok" : 1.0
}

编辑2：

以下是getIndexes()输出-

[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "db.collection"
    },
    {
        "v" : 2,
        "unique" : true,
        "key" : {
            "my_id" : 1.0
        },
        "name" : "my_id_1",
        "ns" : "db.collection"
    },
    {
        "v" : 2,
        "key" : {
            "data.status" : 1.0
        },
        "name" : "data.status_1",
        "ns" : "db.collection"
    }
]

谢谢

Answer 1

很明显，我忽略了这样一个事实，即我的python代码在默认情况下按$neutral添加了一个排序，因此排序实际上得到了winningPlan，因此忽略了索引

感谢所有试图帮助你的人