MongoDB Index related

1. introduction of the index :

Monggodb 's index is also a balanced binary tree , so most of the index optimization techniques in traditional databases are also available .

Note : MongoDB can traverse the data in any direction ( This is not the same as the index in the relational database ), But this is limited to single-key ordering , The direction of the multi-key sort index is more important .

Here's a single-key ordering without directionality specific examples are :

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/58/D5/wKioL1S-FKuSfNiNAAL2NwS7sS0273.jpg "title=" 1.png " alt= "Wkiol1s-fkusfninaal2nws7ss0273.jpg"/>

The above is the execution plan for two queries, the red part of The parameter indicates whether the query has a sort operation in memory ,

As can be seen from the above example, the query does not have a sort operation in memory either in descending order of iage or ascending order .

There are more index types in 2.mongodb, and I'm just listing the more common index types, and more index types , See http://www.cnblogs.com/xinghebuluo/archive/2011/12/19/2293043.html

Unique index : it guarantees that documents that are duplicated with indexed keywords for existing documents are not inserted

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/58/D8/wKiom1S-E_Tj_LFHAAF35HMBqPM293.jpg "title=" 2.png " alt= "Wkiom1s-e_tj_lfhaaf35hmbqpm293.jpg"/>

In the example above, a unique index has been created successfully .

Composite Unique index: a unique index of multiple key-value combinations.

Composite unique Index instance:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/58/D5/wKioL1S-FNij4wS5AAJK8IID8R4423.jpg "title=" 3.png " alt= "Wkiol1s-fnij4ws5aajk8iid8r4423.jpg"/>

considerations for creating unique indexes :

(1) unique indexes and non-existent keywords :

When a document saved to the collection has no value in the indexed field, its indexed field is assigned null and then inserted. That is, you cannot insert multiple documents in a unique index that have no value in an indexed field. ( multiple null values are considered to be equal values )

The following instance inserts multiple null values on a unique index field to error .

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/58/D8/wKiom1S-FBrAig-YAAMU74pKWqA513.jpg "title=" 4.png " alt= "Wkiom1s-fbraig-yaamu74pkwqa513.jpg"/>

(2) . Create a unique index on a field that already exists for duplicate values .

If you create a unique index directly, you will definitely get an error .

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/58/D8/wKiom1S-FDzyFAilAAEgTGCNdoY648.jpg "title=" 5.png " alt= "Wkiom1s-fdzyfailaaegtgcndoy648.jpg"/>

(3). If you must create a unique index on such a field , You can specify a keyword when creating a unique index dropdups to force the creation of an index . However, this method of creating an index removes the data from the collection, so it is not recommended.

To force the creation of an index example using dropdups :

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/58/D5/wKioL1S-FVag8eprAAHuXAfnG7Q003.jpg "title=" 6.png " alt= "Wkiol1s-fvag8epraahuxafng7q003.jpg"/>

Sparse Indexes : in the collection of Mongdo , the key-value pairs for each document can be different ( the number of columns in the row is different ), then in an index , the field may be in a document, or it may not be in a document , and when the field is not in a document, the query uses the index to get the data, and by default the document that does not have that field is queried .

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/58/D8/wKiom1S-FJHDFY6cAASlHYLKG0M542.jpg "title=" 7.jpg " alt= "Wkiom1s-fjhdfy6caaslhylkg0m542.jpg"/>

The query in the above example gets the data through the index iage , but the document that does not contain the iage field is also queried.

At this point, you can filter out documents that do not contain indexed fields through a sparse index .

The following is an example of a document that is filtered by a sparse index that does not contain an indexed field:

As you can see from the execution plan , the query is obtained from the sparse index, and all of the result sets in the query contain the iage field ( filtered out not containing iage the documentation ).

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/58/D5/wKioL1S-FXiyBOE2AARx-3yu1BU730.jpg "title=" 8.png " alt= "Wkiol1s-fxiyboe2aarx-3yu1bu730.jpg"/>

3. Index management :

View the index information under the Set collection :d b.collection.getindexes ();

View all index information under the current schema :d b.system.indexes.find ({});

Create INDEX :d b.collection.ensureindex ({key:1}},{unique:true,dropdups:true});

Hint ({}): forced use of a key value ( index )

Delete index :d b.collection.dropindexes (), Db.collection.dropIndex (index)

Rebuild Index :d b.test.reindex ()

Identity index : Db.collection.ensureIndex ({key:1}},{name: "index_name"});

(1) : View all indexes of the specified collection :d b.colleciton.getindexes ();

650) this.width=650; "Src=" http://s3.51cto.com/wyfs02/M00/58/D5/wKioL1S-FYfA5MyUAADZcAkoZZQ775.jpg " Title= "9.png" alt= "Wkiol1s-fyfa5myuaadzcakozzq775.jpg"/>

(2) View all index information under the current schema :d b.system.indexes.find ({});

650) this.width=650; "Src=" http://s3.51cto.com/wyfs02/M02/58/D5/wKioL1S-FZHQOjdaAAFcTddqdi8498.jpg " Title= "10.png" alt= "Wkiol1s-fzhqojdaaafctddqdi8498.jpg"/>

(3) CREATE INDEX :d b.collection.ensureindex ({key:1},{unique:true,dropdups:true,sparse:true})

Above is the standard syntax for creating an index, and the second curly brace ({unique:true,dropdups:true,sparse:true}) is the index type parameter.

Unique: represents a unique index.

Sparse: represents a sparse index .

Dropdups: When duplicate values exist in an indexed field, forcing a duplicate document to be deleted, the parameter loses data and is not recommended .

(4) hint ({}): forced use of a key value ( index )

In the following example , it can be seen from the execution plan that the index is enforced.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/58/D8/wKiom1S-FMnyviNCAAOG-medeHc284.jpg "title=" 11.png "alt=" Wkiom1s-fmnyvincaaog-medehc284.jpg "/>

(5). Delete Index :d b.collection.dropindexes (), Db.collection.dropIndex (index)

Db.collection.dropIndexes (): Deletes all indexes below the collection .

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/58/D8/wKiom1S-FNSjrbcvAAEf2nz6nPU529.jpg "title=" 12.png "alt=" Wkiom1s-fnsjrbcvaaef2nz6npu529.jpg "/>

The collection test has no index to delete , _id_ is the system's own index and cannot be deleted manually .

Db.collection.dropIndex (index_name): Deletes the specified index

650) this.width=650; "Src=" http://s3.51cto.com/wyfs02/M01/58/D8/wKiom1S-FOjD4ubCAAHTBKjG_xQ103.jpg " Title= "13.png" alt= "Wkiom1s-fojd4ubcaahtbkjg_xq103.jpg"/>

(6) . when the index is inefficient ( may be more fragments ) , you need to rebuild the index. db.test.reIndex () .

Db.test.reIndex (): Rebuilding the index

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/58/D5/wKioL1S-FcaA_VchAAJvwOH6paU697.jpg "title=" 14.png "alt=" Wkiol1s-fcaa_vchaajvwoh6pau697.jpg "/>

Rebuilds a single index or all indexes under a collection.

4. Interpretation of the implementation plan :

The correct interpretation of a query's execution plan is the first step in optimizing the query , so you must accurately read the query's execution plan:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/58/D8/wKiom1S-FQTx3vS4AAkNXIQuIek588.jpg "title=" 15.png "alt=" Wkiom1s-fqtx3vs4aaknxiquiek588.jpg "/>

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1606208

MongoDB Index related