Usage of MongoDB Index

The index in http://www.cnblogs.com/lipan/archive/2011/03/28/1997202.html MongoDB is similar to relational database, it is to improve the efficiency of query and sorting, and the principle of implementation is basically consistent. Because the key (field) in the collection can be a normal data type, it can also be a subdocument. MongoDB can create indexes on various types of keys. The following sections describe the creation, querying, and maintenance of indexes for various types of indexes.

This article is a reprint of the article, the author of the MongoDB document after careful reading, summed up the use of various indexes MongoDB.

Original link: http://iamcaihuafeng.blog.sohu.com/151638529.html

Indexes can improve the speed of retrieving data, and you can imagine creating an index in MySQL as well as using B-tree as an index.

1. Single-Column indexing
Create an index on field x, 1 (Ascending) or-1 (descending)

> Db.data.ensureIndex ({x:1})

Show all indexes in table data

> db.data.getIndexes () [{"Name": "_id_", "ns": "Recommender.data", "key": {"_id": 1}},{"_id": ObjectId ("4befb146b0e2 9BA1CE20E0BB ")," ns ":" Recommender.data "," key ": {" X ": 1}," name ":" X_1 "}]

Find the value of field X 6, which is now used for index

 > db.data.find ({x:6}) {"_id": ObjectId ("4bee804ba23d558eb6687117"), "X": 6 , "name": "Caihuafeng1"} {"_id": ObjectId ("4bee804ba23d558eb6687118"), "X": 6, "name": "Caihuafeng2"} {"_id": objec  TId ("4bee804ba23d558eb6687119"), "X": 6, "name": "Caihuafeng3"} {"_id": ObjectId ("4bee804ba23d558eb668711a"), "X": 6, "Name": "Caihuafeng4"} {"_id": ObjectId ("4bee804ba23d558eb668711b"), "X": 6, "name": "Caihuafeng5"} {"_id": Object Id ("4bee804ba23d558eb668711c"), "X": 6, "name": "Caihuafeng6"} {"_id": ObjectId ("4bee804ba23d558eb668711d"), "X": 6, "Name": "Caihuafeng7"} {"_id": ObjectId ("4bee804ba23d558eb668711e"), "X": 6, "name": "Caihuafeng8"} {"_id": objecti D ("4bee804ba23d558eb668711f"), "X": 6, "name": "Caihuafeng9"} {"_id": ObjectId ("4bee804ba23d558eb6687120"), "X": 6, " Name ":" Caihuafeng10 "}  

2. Default Index
The above 1 db.data.getIndexes () shows a total of 2 indexes, where _id is the creation of the table when the index is automatically created, this index can not be deleted.

An index is all created on _id. This index is special and cannot are deleted. The _id index enforces uniqueness for its keys.

3. Document as the key value of the index
A. Single-column index
The official documentation for MongoDB says this:
Documents as Keys

Indexed fields could be of any type, including documents:

Insert three records into table data for database Recommender

> Db.data.insert ({name: "1616", Info:{url: "http://www.1616.net/", City: "Beijing"}});> Db.data.insert ({name: " Hao123 ", Info:{url:" http://www.hao123.com/", City:" Beijing "});> Db.data.insert ({name:" Ll4la ", Info:{url:" http ://www.114la.com/", City:" Dongguan "});

To create an index on a field info

> Db.data.ensureIndex ({info:1});

Show all indexes on the table data

> db.data.getIndexes (); [{"Name": "_id_", "ns": "Recommender.data", "key": {"_id": 1}},{"_id": ObjectId ("4BEFB146B0E29BA1CE20E0BB"), "ns": "Rec Ommender.data "," key ": {" X ": 1}," name ":" X_1 "},{" _id ": ObjectId (" 4BEFB76BB0E29BA1CE20E0BF ")," ns ":" Recommender.data " , "key": {"info": 1}, "name": "Info_1"}]

Finds the specified record, at which time the index is used

> db.data.find ({info: {URL: "http://www.1616.net/", City: "Beijing"}); {"_id": ObjectId ("4BEFB711B0E29BA1CE20E0BC"), "name": "1616", "info": {"url": "http://www.1616.net/", "City": "Beij ing "}}

B. Combined index
Set up a composite index

> Db.data.ensureIndex ({"Info.url": 1, "info.city": 1});> db.data.getIndexes (); [{"Name": "_id_", "ns": "Recommender.data", "key": {"_id": 1}},{"_id": ObjectId ("4BEFB146B0E29BA1CE20E0BB"), "ns": "Rec Ommender.data "," key ": {" X ": 1}," name ":" X_1 "},{" _id ": ObjectId (" 4BEFB76BB0E29BA1CE20E0BF ")," ns ":" Recommender.data " , "key": {"info": 1}, "name": "Info_1"},{"_id": ObjectId ("4befb9d1b0e29ba1ce20e0c0"), "ns": "Recommender.data", "key": { "Info.url": 1, "info.city": 1}, "name": "Info.url_1_info.city_1"}]

The following actions are used for indexing

> Db.data.find ({"Info.url": "http://www.1616.net/", "info.city": "Beijing"}); {"_id": ObjectId ("4BEFB711B0E29BA1CE20E0BC"), "name": "1616", "info": {"url": "http://www.1616.net/", "City": "Beij ing "}}> db.data.find ({" Info.url ":" http://www.1616.net/"}); {"_id": ObjectId ("4BEFB711B0E29BA1CE20E0BC"), "name": "1616", "info": {"url": "http://www.1616.net/", "City": "Beij ing "}}

1 indicates ascending (ASC), 1 indicates descending (DESC)

> Db.data.find ({"Info.url":/http:*/i}). Sort ({"Info.url": 1, "info.city": 1}); {"_id": ObjectId ("4befb740b0e29ba1ce20e0be"), "name": "Ll4la", "info": {"url": "http://www.114la.com/", "City": "Do Ngguan "}} {" _id ": ObjectId (" 4BEFB711B0E29BA1CE20E0BC ")," name ":" 1616 "," info ": {" url ":" http://www.1616.net/"," CI Ty ":" Beijing "}} {" _id ": ObjectId (" 4BEFB723B0E29BA1CE20E0BD ")," name ":" hao123 "," info ": {" url ":" Http://www.hao12 3.com/"," City ":" Beijing "}}> db.data.find ({" Info.url ":/http:*/i}). Sort ({" Info.url ": 1}); {"_id": ObjectId ("4befb740b0e29ba1ce20e0be"), "name": "Ll4la", "info": {"url": "http://www.114la.com/", "City": "Do Ngguan "}} {" _id ": ObjectId (" 4BEFB711B0E29BA1CE20E0BC ")," name ":" 1616 "," info ": {" url ":" http://www.1616.net/"," CI Ty ":" Beijing "}} {" _id ": ObjectId (" 4BEFB723B0E29BA1CE20E0BD ")," name ":" hao123 "," info ": {" url ":" Http://www.hao12 3.com/"," City ":" Beijing "}}> db.data.find ({" Info.url ":/http:*/i}). Sort ({" Info.url ": -1}); {"_id": ObjectId ("4BEFB723B0E29BA1CE20E0BD"), "name": "hao123", "info": {"url": "http://www.hao123.com/", "City": " Beijing "}} {" _id ": ObjectId (" 4BEFB711B0E29BA1CE20E0BC ")," name ":" 1616 "," info ": {" url ":" http://www.1616.net/"," C ity ":" Beijing "}} {" _id ": ObjectId (" 4befb740b0e29ba1ce20e0be ")," name ":" Ll4la "," info ": {" url ":" Http://www.114la . com/"," City ":" Dongguan "}}

4. Combined Index
Note that the combined index here is a bit different from the combination index in the 3 above, and 4 is a composite index of the first-level field, and the above 3 is the combination index of the two-level field.

Create a composite index above the field name and info

> Db.data.ensureIndex ({name:1, Info:-1});

When creating a composite index, 1 of the field followed by the ascending, 1 for descending, 1 or 1 is mainly related to the time of sorting or the query in the specified range, see the English text below for details.
When creating an index, the number associated with a key specifies the direction of the index, so it should always be 1 (a scending) or-1 (Descending). Direction doesn ' t matter for single key indexes or for random access retrieval It's important if you're doing sorts or Range queries on compound indexes.

Show All Indexes

> db.data.getIndexes (); [{"Name": "_id_", "ns": "Recommender.data", "key": {"_id": 1}},{"_id": ObjectId ("4BEFB146B0E29BA1CE20E0BB"), "ns": "Rec Ommender.data "," key ": {" X ": 1}," name ":" X_1 "},{" _id ": ObjectId (" 4BEFB76BB0E29BA1CE20E0BF ")," ns ":" Recommender.data " , "key": {"info": 1}, "name": "Info_1"},{"_id": ObjectId ("4befb9d1b0e29ba1ce20e0c0"), "ns": "Recommender.data", "key": { "Info.url": 1, "info.city": 1}, "name": "Info.url_1_info.city_1"},{"_id": ObjectId ("4befbfcfb0e29ba1ce20e0c1"), "ns": "Recommender.data", "key": {"name": 1, "info":-1}, "name": "Name_1_info_-1"}]

The following sort will use the index above
The last line of "name": "Ll4la" is actually "name": "114la" (that is, the number is written in the letter L), but I entered the time to write "name": "Ll4la", I wrote the wrong, but the result of the order is correct.

> Db.data.find ({"Info.url":/http:*/i}). Sort ({name:1, Info:-1}); {"_id": ObjectId ("4BEFB711B0E29BA1CE20E0BC"), "name": "1616", "info": {"url": "http://www.1616.net/", "City": "Beij ing "}} {" _id ": ObjectId (" 4BEFB723B0E29BA1CE20E0BD ")," name ":" hao123 "," info ": {" url ":" http://www.hao123.com/"," C ity ":" Beijing "}} {" _id ": ObjectId (" 4befb740b0e29ba1ce20e0be ")," name ":" Ll4la "," info ": {" url ":" Http://www.114la . com/"," City ":" Dongguan "}}

MongoDB Composite Index Rules
If you have a compound index in multiple fields, you can use it to query on the beginning subset of fields. So if you have a index on

A,b,c

You can use it to query on

A

A, b

A,b,c

If you've used MySQL, it looks like you're not familiar with the same principles as MySQL.

5. Unique index
Inserts a record into the table data.

> Db.data.insert ({firstname: "Cai", LastName: "Huafeng"});

Since only one record in table data has fields FirstName and LastName, none of the other rows have corresponding values, that is, all null, NULL is the same, and the unique index is not allowed to have the same value, so the following creates a unique composite index The times are wrong.

So when you create a unique index, whether you are indexing a single field or multiple fields, it is best to have this field in each row, or you will get an error.

> Db.data.find (); {"_id": ObjectId ("4bee745a0863b1c233b8b7ea"), "name": "Caihuafeng"} {"_id": ObjectId ("4bee745f0863b1c233b8b7eb"), "website": "1616.net"} {"_id": ObjectId ("4bee804ba23d558eb6687117"), "X": 6, "name": "Caihuafeng1"} {"_id": ObjectId ("4bee804ba23d558eb6687118"), "X": 6, "name": "Caihuafeng2"} {"_id": ObjectId ("4bee804ba23d558eb6687119"), "X": 6, "name": "Caihuafeng3"} {"_id": ObjectId ("4bee804ba23d558eb668711a"), "X": 6, "name": "Caihuafeng4"} {"_id": ObjectId ("4bee804ba23d558eb668711b"), "X": 6, "name": "Caihuafeng5"} {"_id": ObjectId ("4bee804ba23d558eb668711c"), "X": 6, "name": "Caihuafeng6"} {"_id": ObjectId ("4bee804ba23d558eb668711d"), "X": 6, "name": "Caihuafeng7"} {"_id": ObjectId ("4bee804ba23d558eb668711e"), "X": 6, "name": "Caihuafeng8"} {"_id": ObjectId ("4bee804ba23d558eb668711f"), "X": 6, "name": "Caihuafeng9"} {"_id": ObjectId ("4bee804ba23d558eb6687120"), "X": 6, "name": "Caihuafeng10"} {"_id": ObjectId ("4befb711B0E29BA1CE20E0BC ")," name ":" 1616 "," info ": {" url ":" http://www.1616.net/"," City ":" Beijing "}} {" _id ": ObjectId ("4BEFB723B0E29BA1CE20E0BD"), "name": "hao123", "info": {"url": "http://www.hao123.com/", "City": "Beijing"}} {"_id ": ObjectId (" 4befb740b0e29ba1ce20e0be ")," name ":" Ll4la "," info ": {" url ":" http://www.114la.com/"," City ":" Dongguan "}} {" _id ": ObjectId (" 4befc51ab0e29ba1ce20e0c2 ")," FirstName ":" Cai "," LastName ":" Huafeng "}> Db.data.ensureIndex ({firstname:1, lastname:1}, {unique:true}); E11000 duplicate key error index:recommender.data. $firstname _1_lastname_1 dup key: {: null,: null}

Now let's test it with another table person.

> Db.person.ensureIndex ({firstname:1, lastname:1},{unique:true});> Db.person.insert ({firstname: ' Cai ', LastName: ' Huafeng '});

The second time to insert the same value when the error, stating that the unique index is in effect, in fact, the same as in MySQL.

> Db.person.insert ({firstname: ' Cai ', LastName: ' Huafeng '}); E11000 duplicate key error index:recommender.person. $firstname _1_lastname_1 dup key: {: "Cai",: "Huafeng"}

6. Duplicate value handling in a unique index
Delete the index in the above 5, insert two rows of records

> db.person.dropIndexes (); {"Nindexeswas": 2, "MSG": "non-_id indexes dropped for collection", "OK": 1}> db.person.find (); {"_id": ObjectId ("4BEFCDA6B0E29BA1CE20E0CF"), "FirstName": "Cai", "LastName": "Huafeng"}> Db.person.insert ({First Name: ' Cai ', LastName: ' Huafeng '});> db.person.find (); {"_id": ObjectId ("4BEFCDA6B0E29BA1CE20E0CF"), "FirstName": "Cai", "LastName": "Huafeng"} {"_id": ObjectId ("4befcef0b0e29ba1ce20e0d1"), "FirstName": "Cai", "LastName": "Huafeng"}

If you now create a unique composite index directly above the fields FirstName and LastName, you will get an error, let's try it out:

> Db.person.ensureIndex ({firstname:1, lastname:1}, {unique:true}); E11000 duplicate key error index:recommender.person. $firstname _1_lastname_1 dup key: {: "Cai",: "Huafeng"}

Looking at the index of the table person, we can see that the newly created index is not generated.

> db.person.getIndexes (); [{"Name": "_id_", "ns": "Recommender.person", "key": {"_id": 1}]

You can add a dropdups:true to the second JSON object so that you do not get an error when creating a unique composite index, leaving the first duplicate value in the document, and all other duplicate values removed.

Test again, add the dropdups option, although the error, but the unique combination index has been established.

> Db.person.ensureIndex ({firstname:1, lastname:1}, {unique:true, dropdups:true}); E11000 duplicate key error index:recommender.person. $firstname _1_lastname_1 dup key: {: "Cai",: "Huafeng"}> db.pers On.getindexes (); [{"Name": "_id_", "ns": "Recommender.person", "key": {"_id": 1}},{"_id": ObjectId ("4befcfd9b0e29ba1ce20e0d3"), "ns": "R Ecommender.person "," key ": {" FirstName ": 1," LastName ": 1}," name ":" Firstname_1_lastname_1 "," unique ": true," dropdups " : true}]

Querying the records in the table person again reveals that duplicate records have been automatically deleted.

> Db.person.find (); {"_id": ObjectId ("4BEFCDA6B0E29BA1CE20E0CF"), "FirstName": "Cai", "LastName": "Huafeng"}

Description of the MONGODB official documentation
A unique index cannot is created on a key the has duplicate values. If you would like to create the index anyway, keeping the first document the database indexes and deleting all subsequent Documents that has duplicate values, add the dropdups option.

Db.things.ensureIndex ({firstname:1}, {unique:true, dropdups:true})

7. Deleting an index
A. Deleting all indexes in a table
To delete all indexes on the specified collection:

Db.collection.dropIndexes ();

B. Deleting a single index in a table
To delete a single index:

Db.collection.dropIndex ({x:1, Y:-1}) > Db.data.dropIndex ({firstname:1, lastname:1}); {"Nindexeswas": 6, "OK": 1}

Running directly as a command without helper:

Note:command was ' deleteindexes ', not ' dropindexes ', before MongoDB v1.3.2//Remove index with key pattern {y:1} from Collection Foodb.runcommand ({dropindexes: ' foo ', index: {y:1}})//Remove all Indexes:db.runCommand ({dropindexes: ' foo ') , index: ' * ') > Db.person.ensureIndex ({firstname:1, lastname:1});> Db.runcommand ({dropindexes: ' person ', index: {firstname:1, lastname:1}}); {"Nindexeswas": 2, "OK": 1}

Extended reading:
Http://www.mongodb.org/display/DOCS/Indexes#Indexes-DocumentsasKeys
Http://www.mongodb.org/display/DOCS/min+and+max+Query+Specifiers
Http://www.mongodb.org/display/DOCS/Advanced+Queries

Usage of MongoDB Index