为什么Mongo在执行IXSCAN之后在FETCH中查询空过滤器 [英] Why Mongo query for null filters in FETCH after performing IXSCAN

问题描述

根据 Mongo文档，

{ item : null }查询匹配包含以下内容的文档: item字段，其值为null或不包含item 字段.

The { item : null } query matches documents that either contain the item field whose value is null or that do not contain the item field.

我找不到有关此内容的文档，但据我所知，两种情况(值是null或字段缺失)都以null的形式存储在索引中.

I can't find documentation for this, but as far as I can tell, both cases (value is null or field is missing) are stored in the index as null.

因此，如果我先执行db.orders.createIndex({item: 1})，然后再执行db.orders.find({item: null})，则我希望IXSCAN查找包含值null的item字段或不包含item的所有文档.字段，只有这些文档.

So if I do db.orders.createIndex({item: 1}) and then db.orders.find({item: null}), I would expect an IXSCAN to find all documents that either contain the item field whose value is null or that do not contain the item field, and only those documents.

那么为什么db.orders.find({item: null}).explain()在执行IXSCAN之后在FETCH阶段执行filter: {item: {$eq: null}}?可能需要过滤哪些文件?

So then why does db.orders.find({item: null}).explain() perform filter: {item: {$eq: null}} in the FETCH stage after it performs an IXSCAN? What possible documents could need to be filtered out?

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "temp.orders",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "item" : {
                "$eq" : null
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "item" : {
                    "$eq" : null
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "item" : 1
                },
                "indexName" : "item_1",
                "isMultiKey" : false,
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "forward",
                "indexBounds" : {
                    "item" : [
                        "[null, null]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "Andys-MacBook-Pro-2.local",
        "port" : 27017,
        "version" : "3.2.8",
        "gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
    },
    "ok" : 1
}

我想也许undefined的值会被索引为null，但是简单的实验排除了这一点:

I thought maybe undefined values would get indexed as null, but simple experimentation rules this out:

> db.orders.createIndex({item: 1})
{
    "createdCollectionAutomatically" : true,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}
> db.orders.insert({item: undefined})
WriteResult({ "nInserted" : 1 })
> db.orders.find({item: {$type: 6}}).explain()
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "temp.orders",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "item" : {
                "$type" : 6
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "item" : {
                    "$type" : 6
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "item" : 1
                },
                "indexName" : "item_1",
                "isMultiKey" : false,
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "forward",
                "indexBounds" : {
                    "item" : [
                        "[undefined, undefined]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "Andys-MacBook-Pro-2.local",
        "port" : 27017,
        "version" : "3.2.8",
        "gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
    },
    "ok" : 1
}

推荐答案

空相等匹配谓词(例如{"a.b": null})的语义非常复杂，因为字段可能包含仅索引扫描不足以包含的子文档.提供正确的结果.

The semantics for a null equality match predicate (e.g. {"a.b": null}) are complicated enough because a field could contain subdocuments that an index scan alone isn't enough to provide the correct result.

根据

服务器的版本2.6.0更改了空等号的语义 匹配谓词，以使文档{a:[]}不再存在 被视为查询谓词{"a.b":null}的匹配项(之前 版本的服务器，此文档被视为与此版本的匹配项 谓词). 2.6兼容性说明中对此进行了说明. 空比较"部分.

Version 2.6.0 of the server changed the semantics of a null equality match predicate, such that the document {a: []} was no longer considered a match for the query predicate {"a.b": null} (in prior versions of the server, this document was considered a match for this predicate). This is documented in the 2.6 compatibility notes, under the "null comparison" section.

对于键模式为{"a.b":1}的索引，本文档{a:[]} 生成索引键{":null}.其他文档，例如{a:null}和 空文档{}还会生成索引键{":null}.作为一个 结果，如果谓词为{"a.b":null}的查询使用此索引，则 查询系统无法仅从索引键{":null}判断是否 关联的文档与谓词不匹配.因此， 分配了INEXACT_FETCH界限而不是EXACT界限，因此 FETCH阶段已添加到查询执行树中.

For an index with key pattern {"a.b": 1}, this document {a: []} generates the index key {"": null}. Other documents like {a: null} and the empty document {} also generate the index key {"": null}. As a result, if a query with predicate {"a.b": null} uses this index, the query system cannot tell just from the index key {"": null} whether or not the associated document matches the predicate. As a result, INEXACT_FETCH bounds are assigned instead of EXACT bounds, and hence a FETCH stage is added to the query execution tree.

其他说明:

1. 文档{}为具有键模式{"a.b":1}的索引生成索引键{":null}.
2. 文档{a:[]}还为具有键模式{"a.b":1}的索引生成索引键{":null}.
3. 文档{}与查询{"a.b":空}匹配.
4. 文档{a:[]}与查询{"a.b":空}不匹配.

1. The document {} generates the index key {"": null} for the index with key pattern {"a.b": 1}.
2. The document {a: []} also generates the index key {"": null} for the index with key pattern {"a.b": 1}.
3. The document {} matches the query {"a.b": null}.
4. The document {a: []} does not match the query {"a.b": null}.

因此，查询{"a.b":null}由带有键的索引回答 模式{"a.b":1}必须提取文档并重新检查谓词， 为了确保文档{}包含在结果集中 并且文档{a:[]}未包含在结果集中.

Therefore, a query {"a.b": null} that is answered by an index with key pattern {"a.b": 1} must fetch the document and re-check the predicate, in order to ensure that the document {} is included in the result set and that the document {a: []} is not included in the result set.

这篇关于为什么Mongo在执行IXSCAN之后在FETCH中查询空过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！