在MongoDB中，如果索引在3个字段上，我们可以在查询2个字段时使用该索引吗？ （第3场的通配符） [英] In MongoDB, if an index is on 3 fields, we can use that index when querying on 2 fields? (wildcard on the 3rd field)

问题描述

如果有索引

page_type, our_id, date

当查询时，

db.analytics.find({page_type: 'ingredients', ga_date: 
  {$gte : new Date('Wed Sep 08 2010 12:00:00 GMT-0800')}})

db.analytics.find({page_type: 'ingredients', ga_date: 
  {$gte : new Date('Wed Sep 08 2010 12:00:00 GMT-0800')}}).explain()

如果省略 our_id ，或日期被省略，它仍然可以使用索引，在explain（）输出中使用以下内容：

if our_id is omitted, or date is omitted, it can still use the index, with something like the following in the explain() output:

"our_id" : [
    [
        {
            "$minElement" : 1
        },
        {
            "$maxElement" : 1
        }
    ]
],

即使两者都是 our_id 和 date 被省略，索引仍可使用。

even if both our_id and date are omitted, the index can still be used.

但是，当 page_typ时e 被省略，不能使用索引（如 explain（）所示）。因此，在MongoDB中，当索引的一部分是序列（如数字或日期）时，查询时可以省略它吗？在关系数据库中也是如此，因为我认为如果该索引基于这3个字段，它可能严格地在3个字段上。

However, when page_type is omitted, no index can be used (as shown in explain()). So is it true that in MongoDB, when part of the index is something of a sequence, like number or date, then it can be omitted when querying? Is this true in the relational DB as well, because I think it might be strictly on the 3 fields if that index was based on those 3 fields.

推荐答案

这些是B树索引，因此它们可用于所涉及列的前缀子集。如果您没有前导列，则不再可以进行索引范围扫描（主要用于B树索引的操作）。可能还有其他方法仍然使用索引（例如，Oracle具有快速完全扫描和跳过扫描），但通常不会使用索引。

Those are B-tree indexes, so they can be used for a prefix subset of the columns involved. If you do not have the leading columns, an index range scan (the operation that a B-tree index is primarily used for) is no longer possible. There may be other ways to still use the index (Oracle has fast full scans and skip scans for example), but usually, the index will not be used.

这种推理适用于使用B树索引的所有内容，不管是否为关系数据库。

This reasoning applies to everything that uses B-tree indexes, relational DB or not.

同样，这不依赖于列的类型，而是依赖于列的顺序指数。您需要具有前导列（在您的情况下，您需要page_type）。如果您有许多没有page_type的查询，请考虑使用page_type作为最后一列重新创建索引（这当然也可能对其他查询产生负面影响）。通常，您需要知道在设计索引之前将运行哪种查询。

Again, this does not depend on the type of the column, but on the order of columns in the index. You need to have the leading columns (in your case, you need page_type). If you have many queries without the page_type, consider recreating the index with page_type as the last column (which of course may also have a negative impact on other queries). In general, you need to know what kind of queries you will run before you can design the indexes.

这篇关于在MongoDB中，如果索引在3个字段上，我们可以在查询2个字段时使用该索引吗？ （第3场的通配符）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！