Mongodb索引如何运作？ [英] how does Mongodb index works?

问题描述

I have a collection such as:
{u'_id': ObjectId('5094cc44e3f0f827b3618918'),
  u'xxx': 0},
 {u'_id': ObjectId('5094cc44e3f0f827b3618919'),
  u'xxx': 1},
 {u'_id': ObjectId('5094cc44e3f0f827b361891a'),
  u'xxx': 2},
 {u'_id': ObjectId('5094cc44e3f0f827b361891b'),
  u'xxx': 3},
 {u'_id': ObjectId('5094cc44e3f0f827b361891c'),
  u'xxx': 4}
...

我创建索引，如：

db.test.ensure_index([("_id",-1),("xxx",1)])
db.test.ensure_index([("xxx",1)])

然后，我使用解释如下：

then,I use the explain such as:

db.test.find({"xxx":1}).sort("_id",-1).skip(5).limit(5).explain()

result is:
{u'allPlans': [{u'cursor': u'BtreeCursor _id_ reverse',
                u'indexBounds': {u'_id': [[{u'$maxElement': 1},
                                           {u'$minElement': 1}]]},
                u'n': 9,
                u'nscanned': 34,
               u'nscannedObjects': 34},
               {u'cursor': u'BtreeCursor xxx_1',
                u'indexBounds': {u'xxx': [[1, 1]]},
                u'n': 34,
                u'nscanned': 34,
                u'nscannedObjects': 34},
               {u'cursor': u'BtreeCursor _id_-1_xxx_1',
                u'indexBounds': {u'_id': [[{u'$maxElement': 1},
                                           {u'$minElement': 1}]],
                                 u'xxx': [[1, 1]]},
                u'n': 10,
                u'nscanned': 38,
                u'nscannedObjects': 10},
               {u'cursor': u'BasicCursor',
                u'indexBounds': {},
                u'n': 16,
                u'nscanned': 34,
                u'nscannedObjects': 34}],
 u'cursor': u'BtreeCursor xxx_1',
 u'indexBounds': {u'xxx': [[1, 1]]},
 u'indexOnly': False,
 u'isMultiKey': False,
 u'millis': 1,
 u'n': 5,
 u'nChunkSkips': 0,
 u'nYields': 0,
 u'nscanned': 34,
 u'nscannedAllPlans': 140,
 u'nscannedObjects': 34,
 u'nscannedObjectsAllPlans': 112,
 u'scanAndOrder': True,
 u'server': u'ubuntu:27017'}

来自n，nscanned和nscnnedObjects的num，我认为它应该使用u'BtreeCursor id -1_xxx_1'作为光标，但为什么它使用u'cursor'：u'BtreeCursor xxx_1'，？
任何人都可以给我一些建议吗？我对索引优化有一点了解。

from n,nscanned and nscnnedObjects 's num,I think It should use u'BtreeCursor id-1_xxx_1' as cursor,but Why It use u'cursor': u'BtreeCursor xxx_1',? Can anyone give me some suggestion ?I have a little knowledge about the index optimize.

推荐答案

字段的顺序在索引事项;查找和排序示例的最佳复合索引实际上是：

The order of fields in the index matters; the best compound index for your find and sort example would actually be:

db.test.ensure_index([("xxx",1),("_id",-1)])

由于您的搜索条件位于字段' xxx'，将此字段放在索引中将比通过 _id 搜索更多结果，然后过滤到与 xxx 条件。

Since your search criteria is on field 'xxx', putting this field first in the index will find more results than searching by _id and then filtering to documents matching your xxx criteria.

如果查看查询优化器在<$中考虑的每个计划的 n 数字c $ c> allPlans ， BtreeCursor xxx_1 索引实际上返回的结果最多（34）。其他索引返回9,10和16个结果..因此对于给定的搜索条件效率会降低。

If you look at the n number for each plan considered by the query optimizer in allPlans, the BtreeCursor xxx_1 index actually returns the most results (34). The other indexes return 9, 10, and 16 results .. so would be less efficient for the given search criteria.

有关索引优化的更多信息，本文非常帮助：优化MongoDB复合索引。

For more information on index optimization, this article is very helpful: Optimizing MongoDB Compound Indexes.

这篇关于Mongodb索引如何运作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！