小贝子编程

如何检查索引交叉点是否可以支持MongoDB中的查询



tl; dr

  1. 我已经设置了可以用作我查询的索引交叉的索引
  2. 查询策划者不喜欢由于数据不足而产生的交叉点
  3. 现在，我需要一种稳定的方法来验证此的正确性电位交叉计划，就像使用explain() hint()用于单个索引设置

我完全理解为什么索引交点不是首选的ATM，而在大多数情况下实际上可能不是首选。但是我只是在寻找一种验证索引相交的方法。

=========

我有一个 notifications集合，该集合保存了

之类的文档 
{
    "_id": ObjectId("5cdd1819c1136c394a052aa2"),
    "notifiable": DBRef("users", ObjectId("5cdd172ac1136c1bdc06bdf2")),
    "read_at": ISODate("2019-05-16T07:59:17.985Z")
}

...并且有以下索引：

[
    {
        "v" : 2,
        "key" : {
            "notifiable.$id" : 1,
            "notifiable.$ref" : 1,
            "created_at" : -1,
            "updated_at" : -1
        },
        "name" : "notifiable.$id_1_notifiable.$ref_1_created_at_-1_updated_at_-1",
        "ns" : "example.notifications"
},
{
        "v" : 2,
        "key" : {
            "read_at" : 1
        },
        "name" : "read_at_1",
        "ns" : "example.notifications"
    }
]

当我运行查询时，例如

db.notifications.find({ read_at: { $gt: ISODate("2019-05-16T07:55:57.799Z") }, "notifiable.$id": ObjectId("5cdd172ac1136c1bdc06bdf2") })

...我希望MongoDB在需要时使用这两个指数的交集。但是，由于MongoDB考虑了许多因素来确定是否应该使用索引相交，因此我的查询仅使用1个索引(也许是因为该集合中只有几个文档(，甚至explain(true)的结果也不是't有任何AND_SORTED AND_HASH阶段：

{
    "queryPlanner": {
        "plannerVersion": 1,
        "namespace": "example.notifications",
        "indexFilterSet": false,
        "parsedQuery": {
            "$and": [{
                    "notifiable.$id": {
                        "$eq": ObjectId("5cdd172ac1136c1bdc06bdf2")
                    }
                },
                {
                    "read_at": {
                        "$gt": ISODate("2019-05-16T07:55:57.799Z")
                    }
                }
            ]
        },
        "winningPlan": {
            "stage": "FETCH",
            "filter": {
                "notifiable.$id": {
                    "$eq": ObjectId("5cdd172ac1136c1bdc06bdf2")
                }
            },
            "inputStage": {
                "stage": "IXSCAN",
                "keyPattern": {
                    "read_at": 1
                },
                "indexName": "read_at_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                    "read_at": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                    "read_at": [
                        "(new Date(1557993357799), new Date(9223372036854775807)]"
                    ]
                }
            }
        },
        "rejectedPlans": [{
            "stage": "FETCH",
            "filter": {
                "read_at": {
                    "$gt": ISODate("2019-05-16T07:55:57.799Z")
                }
            },
            "inputStage": {
                "stage": "IXSCAN",
                "keyPattern": {
                    "notifiable.$id": 1,
                    "notifiable.$ref": 1,
                    "created_at": -1,
                    "updated_at": -1
                },
                "indexName": "notifiable.$id_1_notifiable.$ref_1_created_at_-1_updated_at_-1",
                "isMultiKey": false,
                "multiKeyPaths": {
                    "notifiable.$id": [],
                    "notifiable.$ref": [],
                    "created_at": [],
                    "updated_at": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                    "notifiable.$id": [
                        "[ObjectId('5cdd172ac1136c1bdc06bdf2'), ObjectId('5cdd172ac1136c1bdc06bdf2')]"
                    ],
                    "notifiable.$ref": [
                        "[MinKey, MaxKey]"
                    ],
                    "created_at": [
                        "[MaxKey, MinKey]"
                    ],
                    "updated_at": [
                        "[MaxKey, MinKey]"
                    ]
                }
            }
        }]
    },
    "executionStats": {
        "executionSuccess": true,
        "nReturned": 1,
        "executionTimeMillis": 0,
        "totalKeysExamined": 2,
        "totalDocsExamined": 2,
        "executionStages": {
            "stage": "FETCH",
            "filter": {
                "notifiable.$id": {
                    "$eq": ObjectId("5cdd172ac1136c1bdc06bdf2")
                }
            },
            "nReturned": 1,
            "executionTimeMillisEstimate": 0,
            "works": 4,
            "advanced": 1,
            "needTime": 1,
            "needYield": 0,
            "saveState": 0,
            "restoreState": 0,
            "isEOF": 1,
            "invalidates": 0,
            "docsExamined": 2,
            "alreadyHasObj": 0,
            "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 2,
                "executionTimeMillisEstimate": 0,
                "works": 3,
                "advanced": 2,
                "needTime": 0,
                "needYield": 0,
                "saveState": 0,
                "restoreState": 0,
                "isEOF": 1,
                "invalidates": 0,
                "keyPattern": {
                    "read_at": 1
                },
                "indexName": "read_at_1",
                "isMultiKey": false,
                "multiKeyPaths": {
                    "read_at": []
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2,
                "direction": "forward",
                "indexBounds": {
                    "read_at": [
                        "(new Date(1557993357799), new Date(9223372036854775807)]"
                    ]
                },
                "keysExamined": 2,
                "seeks": 1,
                "dupsTested": 0,
                "dupsDropped": 0,
                "seenInvalidated": 0
            }
        },
        "allPlansExecution": [{
                "nReturned": 1,
                "executionTimeMillisEstimate": 0,
                "totalKeysExamined": 2,
                "totalDocsExamined": 2,
                "executionStages": {
                    "stage": "FETCH",
                    "filter": {
                        "notifiable.$id": {
                            "$eq": ObjectId("5cdd172ac1136c1bdc06bdf2")
                        }
                    },
                    "nReturned": 1,
                    "executionTimeMillisEstimate": 0,
                    "works": 3,
                    "advanced": 1,
                    "needTime": 1,
                    "needYield": 0,
                    "saveState": 0,
                    "restoreState": 0,
                    "isEOF": 1,
                    "invalidates": 0,
                    "docsExamined": 2,
                    "alreadyHasObj": 0,
                    "inputStage": {
                        "stage": "IXSCAN",
                        "nReturned": 2,
                        "executionTimeMillisEstimate": 0,
                        "works": 3,
                        "advanced": 2,
                        "needTime": 0,
                        "needYield": 0,
                        "saveState": 0,
                        "restoreState": 0,
                        "isEOF": 1,
                        "invalidates": 0,
                        "keyPattern": {
                            "read_at": 1
                        },
                        "indexName": "read_at_1",
                        "isMultiKey": false,
                        "multiKeyPaths": {
                            "read_at": []
                        },
                        "isUnique": false,
                        "isSparse": false,
                        "isPartial": false,
                        "indexVersion": 2,
                        "direction": "forward",
                        "indexBounds": {
                            "read_at": [
                                "(new Date(1557993357799), new Date(9223372036854775807)]"
                            ]
                        },
                        "keysExamined": 2,
                        "seeks": 1,
                        "dupsTested": 0,
                        "dupsDropped": 0,
                        "seenInvalidated": 0
                    }
                }
            },
            {
                "nReturned": 1,
                "executionTimeMillisEstimate": 0,
                "totalKeysExamined": 2,
                "totalDocsExamined": 2,
                "executionStages": {
                    "stage": "FETCH",
                    "filter": {
                        "read_at": {
                            "$gt": ISODate("2019-05-16T07:55:57.799Z")
                        }
                    },
                    "nReturned": 1,
                    "executionTimeMillisEstimate": 0,
                    "works": 3,
                    "advanced": 1,
                    "needTime": 1,
                    "needYield": 0,
                    "saveState": 0,
                    "restoreState": 0,
                    "isEOF": 1,
                    "invalidates": 0,
                    "docsExamined": 2,
                    "alreadyHasObj": 0,
                    "inputStage": {
                        "stage": "IXSCAN",
                        "nReturned": 2,
                        "executionTimeMillisEstimate": 0,
                        "works": 3,
                        "advanced": 2,
                        "needTime": 0,
                        "needYield": 0,
                        "saveState": 0,
                        "restoreState": 0,
                        "isEOF": 1,
                        "invalidates": 0,
                        "keyPattern": {
                            "notifiable.$id": 1,
                            "notifiable.$ref": 1,
                            "created_at": -1,
                            "updated_at": -1
                        },
                        "indexName": "notifiable.$id_1_notifiable.$ref_1_created_at_-1_updated_at_-1",
                        "isMultiKey": false,
                        "multiKeyPaths": {
                            "notifiable.$id": [],
                            "notifiable.$ref": [],
                            "created_at": [],
                            "updated_at": []
                        },
                        "isUnique": false,
                        "isSparse": false,
                        "isPartial": false,
                        "indexVersion": 2,
                        "direction": "forward",
                        "indexBounds": {
                            "notifiable.$id": [
                                "[ObjectId('5cdd172ac1136c1bdc06bdf2'), ObjectId('5cdd172ac1136c1bdc06bdf2')]"
                            ],
                            "notifiable.$ref": [
                                "[MinKey, MaxKey]"
                            ],
                            "created_at": [
                                "[MaxKey, MinKey]"
                            ],
                            "updated_at": [
                                "[MaxKey, MinKey]"
                            ]
                        },
                        "keysExamined": 2,
                        "seeks": 1,
                        "dupsTested": 0,
                        "dupsDropped": 0,
                        "seenInvalidated": 0
                    }
                }
            }
        ]
    },
    "ok": 1,
    "operationTime": Timestamp(1557996666, 2),
    "$clusterTime": {
        "clusterTime": Timestamp(1557996666, 2),
        "signature": {
            "hash": BinData(0, "hDKqoIo9DL71/n8vfgSDS3czZ9c="),
            "keyId": NumberLong("6685056801689305089")
        }
    }
}

我尝试了https://docs.mongodb.com/v4.0/core/index-intersection/的orders示例，并获得了相同的结果。而且，由于MongoDB当前不允许将多个索引用作提示，因此我无法强制使用MongoDB来查询这两个索引。更糟糕的是，当有可用的索引交点计划时，以前explain(true)似乎总是包含"cursor" : "Complex Plan"(为什么MongoDB不使用索引交叉点？(，但是现在此信息已被删除。

那么，当有足够的数据时，我怎么知道是否会使用索引交集？

ps：我正在使用正在运行mongodb 4.0.9企业atm的mongodb地图集。

如果您查看说明执行统计统计输出，您可以看到它使用的索引确实很好 - 在此处使用复合索引而不是索引交点似乎是一个查询计划者的好决定：

    "executionStats": {
        "executionSuccess": true,
        "nReturned": 1,
        "executionTimeMillis": 0,
        "totalKeysExamined": 2,
        "totalDocsExamined": 2,
...
    }

索引交叉点在绝对没有更好的选项时会使用，大多数数据都在磁盘上，并且您没有任何远程选择性的索引。

如果您想在本地查看此内容，则可以创建{a: 1}和{b: 1}不是很有选择性的集合，并且您可以看到Mongo考虑并拒绝使用这些集合作为索引相交。

const toInsert = [];
for (let i = 0; i < 10000; i++) { toInsert.push({a: i % 10, b: i % 9 }); }
db.test_coll.createIndex({a: 1});
db.test_coll.createIndex({b: 1});
db.test_coll.find({a: 100, b: 100}).explain()

查看被拒绝的计划：

...
            {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [
                        {
                            "b" : {
                                "$eq" : 100
                            }
                        },
                        {
                            "a" : {
                                "$eq" : 100
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "AND_SORTED",
                    "inputStages" : [
                        {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "b" : 1
                            },
                            "indexName" : "b_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "b" : [ ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "b" : [
                                    "[100.0, 100.0]"
                                ]
                            }
                        },
                        {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "a" : 1
                            },
                            "indexName" : "a_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "a" : [ ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "a" : [
                                    "[100.0, 100.0]"
                                ]
                            }
                        }
                    ]
                }
            }
        ]

我正在寻找一种验证索引相交的方法。

我不确定您所说的"验证"是什么意思，但是由于MongoDB使用实际时间与不同的索引选项并行运行查询，因此您需要现实世界数据来正确评估索引。您的问题似乎包括少量的虚拟数据，但是您应该使用MgenerateJs之类的东西来创建大量逼真的数据，以便索引选择机制可以使用。然后，您可以运行"所有计划执行"模式，即.explain("allPlansExecution")以查看所有索引在同一查询和数据上进行比较。

但是，在您的用例中，索引相交的性能很大。我会说，您只需创建另一个索引，就会更好：

{"notifiable.$id": 1, "read_at": 1}

是的，另一个索引需要更多的磁盘空间，并且每个写操作都需要更多的工作。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium