mongodb:如何将索引用于不同的命令和查询? [英] mongodb: How to use an index for distinct command and query?

问题描述

使用查询的速度非常慢的独特命令存在一些问题. 根据我的观察，如果您未指定查询，则distinct命令仅使用索引:

I have some problems with very slow distinct commands that use a query. From what I have observed the distinct command only makes use of an index if you do not specify a query:

我在具有1Mio对象的MongoDB 3.0.10服务器上创建了一个测试数据库.每个对象如下所示:

I have created a test database on my MongoDB 3.0.10 server with 1Mio objects. Each object looks as follows:

{
    "_id" : ObjectId("56e7fb5303858265f53c0ea1"),
    "field1" : "field1_6",
    "field2" : "field2_10",
    "field3" : "field3_29",
    "field4" : "field4_64"
}

字段值末尾的数字为0-99.

The numbers at the end of the field values are random 0-99.

在集合上创建了两个简单索引和一个复合索引:

On the collections two simple indexes and one compound-index has been created:

{ "field1" : 1 } # simple index on "field1"
{ "field2" : 1 } # simple index on "field2"
{                # compound index on all fields
    "field2" : 1,
    "field1" : 1,
    "field3" : 1,
    "field4" : 1
}

现在，我对该数据库执行不同的查询:

Now I execute distinct queries on that database:

db.runCommand({ distinct: 'dbtest',key:'field1'})

结果包含100个值，nscanned = 100，并且已在"field1"上使用索引.

The result contains 100 values, nscanned=100 and it has used index on "field1".

现在，相同的不同查询受到查询的限制:

Now the same distinct query is limited by a query:

db.runCommand({ distinct: 'dbtest',key:'field1',query:{field2:"field2_10"}})

它再次包含100个值，但是nscanned = 9991，并且使用的索引在所有字段中都是第三个.

It contains again 100 values, however nscanned=9991 and the used index is the third one on all fields.

现在，删除上一个查询中使用的第三个索引.再次执行最后一个查询:

Now the third index that was used in the last query is dropped. Again the last query is executed:

db.runCommand({ distinct: 'dbtest',key:'field1',query:{field2:"field2_10"}})

它再次包含100个值，nscanned = 9991，使用的索引是"field2".

It contains again 100 values, nscanned=9991 and the used index is the "field2" one.

结论:如果我执行一个不带查询的独特命令，则结果直接取自索引.但是，当我将独特命令与查询组合时，仅查询使用索引，在这种情况下，独特命令本身不使用索引.

Conclusion: If I execute a distinct command without query the result is taken directly from an index. However when I combine a distinct command with a query only the query uses an index, the distinct command itself does not use an index in such a case.

我的问题是我需要在非常大的数据库上执行带有查询的独特命令.结果集非常大，但仅包含约100个不同的值.因此，完整的独特命令要花很多时间(> 5分钟)，因为它必须循环遍历所有值.

My problem is that I need to perform a distinct command with query on a very large database. The result set is very large but only contains ~100 distinct values. Therefore the complete distinct command takes ages (> 5 minutes) as it has to cycle through all values.

需要执行什么操作才能执行上面显示的，可以由数据库直接从索引中回答的独特命令?

推荐答案

在不同查询中使用索引的可能性要求Mongo 3.4或更高版本-它同时适用于两个存储引擎MMAPv1/WiredTiger.

The possibility to use an index in a distinct query requires Mongo version 3.4 or higher - it works for both storage engines MMAPv1/WiredTiger.

另请参阅故障单 https://jira.mongodb.org/browse/SERVER- 19507

这篇关于mongodb:如何将索引用于不同的命令和查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！