The index of MongoDB

1. Introduction

It is like a directory of a book, if not, we need to find the entire book to obtain the desired results, that is, the overall scan;

And with the directory (index), it can help us to locate the target location, quickly get the results we want.

2. Demo

The first step is to insert 100W data into users ' collections.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" border:none;margin:0px;padding:0px; "/>

 1 var insertusers = function ()  { 2     var  Start = new date (). GetTime (); 3     for  (var i =  1; i <= 1000000; i++)  { 4          db.users.insert ({ 5               "userid": i, 6              " Username ": " WJG " + i, 7               "Age":  math.floor (Math.random ()  * 100),  //0~99 random integer  8               "CreateDate":  new date ()  9          }) 10     }11      var end = new date (). GetTime () 12     print ("Insert 100W data time consuming"  +  (end -  start)  / 1000 +  "seconds"); 13 }

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" border:none;margin:0px;padding:0px; "/>

The LZ slag I3 and 4G memory took 484.623 seconds, about 8 minutes. In the Task Manager it is clear that CPU, memory, and disk usage are generally increasing. 650) this.width=650; "src=" Http://images0.cnblogs.com/blog2015/646372/201507/192222590164549.png "style=" border:0 px;height:auto;margin:0px;padding:0px; "/>

Step two: Query the Document object with the user name "wjg465413"

650) this.width=650; "id=" code_img_closed_22000a98-7b34-4341-a55b-8410ee5ecb46 "class=" code_img_closed "src="/img/ Jia.gif "style=" border:0px;margin:0px;padding:0px 5px 0px 0px;vertical-align:middle; "/> View Code

Description: The Explain method here is equivalent to the query plan, which returns you with detailed information about the query process. Its parameters are in three modes: "Queryplanner" (query plan [default]), "executionstats" (execution state) and "allplansexecution" (All execution plans), here we only focus on the following information it returns to us.

1 "executiontimemillis": 865//execution of milliseconds Note: If you are executing for the first time, it may take longer 2 3 "totaldocsexamined": 1000000//Total number of documents checked

Step three: Add an index to the user name "username" field

1 Db.users.createIndex ({"username": 1})

Re-executing the last query operation

650) this.width=650; "id=" code_img_closed_a3047bfe-0a1c-45b6-abcd-f82476ae32b1 "class=" code_img_closed "src="/img/ Jia.gif "style=" border:0px;margin:0px;padding:0px 5px 0px 0px;vertical-align:middle; "/> View Code

You can see that two times the query plan is very different, we still focus on the two property values.

1 "executiontimemillis": 53//Number of milliseconds executed 2 3 "totaldocsexamined": 1//Total number of documents checked

It takes only 53 milliseconds to query this document after it has been indexed, and it is 16 times times faster to scan for direct positioning. The importance of rational use of indexes is visible!

Note: the "_id" field is the index that MONGO for us to add by default, and is a unique index that guarantees the uniqueness of the data and cannot be removed. In addition, limiting the number of query results using limit (1) can also improve query speed

3. Type of index

A), single index: You can index on any field on the dataset, including normal property keys, inline documents, and attribute keys in the inline document.

Db.users.createIndex ({"username": 1})//index of normal attribute key//Assuming class is an inline document Db.users.createIndex ({"Class": 1})//index of the inline document Db.users.createIndex ({"Class.classname": 1})//attribute key index in embedded document

Index direction: 1 for Ascending, 1 for descending

b), composite index: indexed based on multiple attribute keys

1 Db.users.createIndex ({"username": 1, "Age":-1, "userid": 1})//Set up a composite index on "username", "age", and "userid"

Index prefix: By creating a composite index above, MONGO is equivalent to having three indexes at the same time, respectively {"username": 1},{"username": 1, "Age":-1} and {"username": 1, "Age":-1, "use Rid ": 1}, but like {" Age ": -1},{" userid ": 1} or {" Age ":-1," userid ": 1} These three indexes do not work. So it uses the index that contains the prefix (the first) as the composite Index

c), multi-key index: Index multiple values in an array for efficient querying.

Note: Ⅰ, do not allow composite indexes on multiple arrays

Ⅱ, cannot specify a key as a multi-key index

Ⅲ, hash index cannot be a multi-key

Ⅳ, multi-key indexes do not support overwrite queries

d), geospatial indexes and Queries: MONGO provides indexes for two surface types: 2dsphere index and 2d index. Query types include: inclusive (inclusion), crossover (intersection), and proximity (proximity)

e), text index: Used to support querying a document containing a string or array of strings

1 Db.users.createIndex ({"username": "Text"})

Note: The text index does not support sorting and a composite text index can no longer contain any other indexes.

f), hash index: it can be indexed on a dataset that uses a hash key for sharding, supports equality queries, but does not support range queries

1 Db.users.createIndex ({"username": "Hashed"})

4. Index characteristics

A), TTL (time-to-live) Index: is a life-cycle index that allows you to set a time-out for each document

1 Db.users.createIndex ({"CreateDate": 1},{"Expireaftersecs": 60*60*24})

Note: A TTL index is established on the "CreateDate" field, and when the self-segment exists and is a date type, the document is deleted when the server time is 60*60*24 seconds, or 24 hours, after the time of the "CreateDate" field.

b), unique index: Ensure that the specified key for each document in the collection has a unique value

1 Db.users.createIndex ({"username": 1}, {"Unique": true})

c), sparse index: null in MONGO is considered a value, and if there is a field that may or may not exist, we can use the sparse index

1 Db.users.createIndex ({"Age": 1},{"sparse": true})

4. Index operation

A), view all indexes

1 db.users.getIndexes ()

b), remove index

1 Db.users.dropIndex ({"Createdate1": 1})

c), remove all indexes

1 db.users.dropIndexes ()

D), rebuilding the index

1 Db.users.reIndex ()

Description: The operation removes all indexes first, including "_id", and then re-creates all indexes

Service, thinking, security

The index of MongoDB