MongoDB Index Use detailed _mongodb

Index is like the directory of books, if the search for a content without the help of the directory, can only look through the entire page, which led to the very low efficiency; If you use the directory, you can quickly locate the specific content area, the efficiency will be straight up.

Introduction to Indexes

First open the command line and enter MONGO. The default MongoDB will connect to the database named Test.

➜~ MONGO
MongoDB shell version:2.4.9
connecting To:test
> Show Collections
> 

You can use show Collections/tables to view the database as empty.

The following code is then executed at the MongoDB command line terminal

> for (var i=0;i<100000;i++) {
... db.users.insert ({username: ' user ' +i})
...}
> Show Collections
system.indexes
users
> 

Then look at the database and find more system.indexes and users two tables, the former is called Index, the latter is the new database table.
In this way, there are 100,000 data in the user table.

> Db.users.find ()
{"_id": ObjectId ("5694d5da8fad9e319c5b43e4"), "username": "User0"}
{"_id": ObjectId ("5 694d5da8fad9e319c5b43e5 ")," username ":" user1 "}
{" _id ": ObjectId (" 5694d5da8fad9e319c5b43e6 ")," username ":" User2 "}
{" _id ": ObjectId (" 5694d5da8fad9e319c5b43e7 ")," username ":" User3 "}
{" _id ": ObjectId (" 5694d5da8fad9 E319c5b43e8 ")," username ":" User4 "}
{" _id ": ObjectId (" 5694d5da8fad9e319c5b43e9 ")," username ":" User5 "}

Now you need to find any one of these data, such as

> Db.users.find ({username: ' user1234 '})
{"_id": ObjectId ("5694d5db8fad9e319c5b48b6"), "username": "user1234" }

Found this data successfully found, but need to know more information, need to add explain method

> Db.users.find ({username: ' user1234 '}). Explain ()
{
  "cursor": "Basiccursor",
  "Ismultikey": false,
  "n": 1,
  "nscannedobjects": 100000,
  "nscanned": 100000,
  "Nscannedobjectsallplans": 100000,
  " Nscannedallplans ": 100000,
  " Scanandorder ": false,
  " indexonly ": false,
  " Nyields ": 0,
  " Nchunkskips ": 0,"
  millis ":" Indexbounds "
  : {
    
  },
  " server ":" root:27017 "
}

There are a lot of arguments, and at the moment we are only concerned with "nscanned": 100000 and "Millis": 30 of these two items.

Nscanned represents the total number of documents scanned by MONGODB during the completion of this query. As you can see, each document in the collection is scanned and has a total time of 30 milliseconds.

If there are 10 million of data, if each query document is traversed. Well, the time is pretty impressive.

For this type of query, indexing is a very good solution.

> Db.users.ensureIndex ({"username": 1})

And then look for user1234.

> Db.users.ensureIndex ({"username": 1})
> Db.users.find ({username: ' user1234 '}). Explain ()
{
  " Cursor ":" Btreecursor username_1 ","
  Ismultikey ": false,
  " n ": 1,
  " nscannedobjects ": 1,
  " nscanned ": 1,
  "Nscannedobjectsallplans": 1,
  "Nscannedallplans": 1,
  "Scanandorder": false,
  "indexonly": false,
  "Nyields": 0,
  "nchunkskips": 0,
  "Millis": 0,
  "indexbounds": {
    "username": [
      [
        ] user1234 ",
        " user1234 "
      ]
    ]
  },
  " server ":" root:27017 "
}

It's a little weird, the query is done in an instant because the index only finds one piece of data, not 100,000.

Of course, there is a cost to using the index: each write operation (insert, UPDATE, DELETE) will take more time for each index added. This is because when the data changes, not only the document is updated, but all indexes on the level collection are updated as well. As a result, MongoDB restricts up to 64 indexes per collection. Typically, you should not have more than two indexes on a particular set.

Small Tips

If a very common query, or this query creates performance bottlenecks, it is a good choice to index a field such as username. But just for the administrator to use the query (not too concerned about the query time-consuming), you should not index this field.

Composite Index

The values of the indexes are arranged in a certain order, so it is very quick to sort the documents using the index keys.

Db.users.find (). Sort ({' Age ': 1, ' username ': 1})

This is based on the age sort and then username, so username is not playing a big role here. To optimize this sort, you may need to index the age and username.

Db.users.ensureIndex ({' Age ': 1, ' username ': 1})
This establishes a composite index (an index built on multiple fields), which is useful if the query condition includes multiple keys.

After a composite index is established, each index entry includes an age field and a username field, and points to where the document is stored on disk.
At this point, the age field is in strict ascending order, and if the age is equal, it is sorted in username ascending order.

Query method

Point Query

For querying a single value (although there may be multiple documents that contain this value)

Db.users.find ({' Age ':}). sort ({' username ':-1})

Because we have built a composite index, an age a username, indexed in ascending order (that is, the number 1), when the use of the point query to find {age:21}, the assumption is still 100,000 data, probably a lot of people ages 21, so will find more than one piece of data. The sort ({' username ':-1}) then sorts the data in reverse order, as intended. But let's not forget that when indexing ' username ': 1 is ascending (from small to large), if you want to reverse order as long as the data from the last index, sequentially traversal can get the desired results.

The sort direction is not important, and MongoDB can traverse the index in any direction.
On the other hand, compound index is very efficient in point query, it is not necessary to sort the result and return the result.

Multi-valued query (Multi-value-query)

Db.users.find ({' age ': {"$gte": "$lte": 30}})

Find documents that match multiple values. Multiple-valued queries can also be understood as multiple point queries.
For example, to find the age between 21 and 30. MONOGDB uses the first key in the index, "age", to get a matching result, which is usually arranged in an indexed order.

Db.users.find ({' age ': {"$gte": "$lte":}). sort ({' username ': 1})

Like the previous one, you need to sort the results this time.
In the absence of sort, the result of our query is first based on age equals 21,age equals 22. This sort is done usernamea-z (0-9) when the age equals 21 when there are multiple. So, sort ({' username ': 1}), to sort all the results in ascending order by name, this time it had to be sorted in memory and then returned. Less efficient than the previous high.

Of course, in the case of very few documents, the sort will not take much time.
If the result set is large, for example, more than 32mb,mongodb will refuse to sort out so much data.

There's another solution.

You can also create another index {' username ': 1, ' Age ': 1}, if you first index the username, and then sortusername, quite no sort. But you need to look up the entire document to find the age equals 21 Congome, so the search time is long.

But which is more efficient?

If you set up multiple indexes, how do you choose which to use?
Efficiency is a part of the situation, if there is no limit to the situation, do not need to be sorted but the search for the entire collection time will be far more than the former. But in the return of some data (such as limit (1000)), a new winner is created.

>db.users.find ({' age ': {"$gte": "$lte":}).
Sort ({username ': 1}).
Limit (1000).
Hint ({' Age ': 1, ' username ': 1})
explain () [' Millis ']
2031ms

>db.users.find ({' age ': {"$gte": "$lte" :}).
Sort ({username ': 1}).
Limit (1000).
Hint ({' username ': 1, ' Age ': 1}).
Explain () [' Millis ']
181ms

Where you can use hint to specify the index you want to use.
So there is an advantage in this way. For example, in the general scenario, we will not take all the data out, just to query the nearest, so this efficiency will be higher.

Index type

Unique index

You can ensure that the specified key for each document in the collection has a unique value.

Db.users.ensureIndex ({' username ': 1, unique:true})
For example, using the Mongoose framework, you can specify unique:true when you define a schema.
If you insert 2 data with the same name John, the second time you insert it will fail. _ID is a unique index and cannot be deleted.

Sparse Index

Use sparse to create sparse indexes

>db.users.ensureindex ({' Email ': 1}, {' Unique ': true, ' sparse ': true})

Index management

The System.indexes collection contains detailed information for each index

Db.system.indexes.find ()

1.ensureIndex () CREATE index

Db.users.ensureIndex ({' username ': 1})
The background creates the index so that the database can still process the read and write requests while the databases are indexed, and specify the background option.

Db.test.ensureIndex ({"username": 1},{"Background": true})

2.getIndexes () View index

Db.collectionName.getIndexes ()
db.users.getIndexes ()
[
  {"
    V": 1,
    "key": {
      "_id": 1
    } ,
    "ns": "Test.users",
    "name": "_id_"
  },
  {
    "V": 1,
    "key": {
      "username": 1
    },
    "ns": "Test.users",
    "name": "Username_1"
  }
]

Where the V field is used internally only to identify the indexed version.

3.dropIndex Delete Index

> Db.users.dropIndex ("username_1")
{"Nindexeswas": 2, "OK": 1}

Or

Select all copies into your notes > Db.users.dropIndex ({"username": 1})