MongoDB Basics (iii) index usage in MongoDB

Indexes in MongoDB are similar to other database indexes and are also used with the b-tree structure. The index of MongoDB is at the collection level and supports the creation of indexes in the sub-columns of any column or document within the collection.

Here is an official diagram of a structure that uses index queries and sorting.

All MongoDB collections By default have a unique index on the field "_id", and if the application does not define a value for the "_id" column, MongoDB will create a column with Objectid values. (Objectid is generated based on time, machine ID, process ID, local process counter)

MongoDB also supports the creation of ascending or descending indexes on one or more columns.

MongoDB can also create a multi-key index, an array index, a spatial index, a text index, and a hash index whose properties can be a uniqueness index, a sparse index, and a TTL (time tolive) index.

Limitations of the Index:

Index name cannot exceed 128 characters

No more than 64 indexes per collection

Composite index cannot exceed 31 columns

MongoDB Index Syntax
Db.collection.createIndex ({<field>: < 1 or-1 >}) Db.collection.ensureIndex ({<field>: < 1 or-1 >})   Db.collection.createIndex ({ "Filed": Sort}) Db.collection.createIndex ({"Filed": Sort, "filed2": Sort})   Db.tab.ensureI Ndex ({"id": 1}) Db.tab.ensureIndex ({"id": 1}, {name: "Id_ind"}) Db.tab.ensureIndex ({"id": 1, "name": 1},{ Background:1,unique:1}) Db.tab.ensureIndex ({"id": "hashed"})	Create an index (two methods)     Filed: For the key column Sort: is sorted. 1 is ascending;-1 is descending.   Create single-column index Create index and given index name Background create unique composite index Create hash index   (see the bottom of the article for more parameters)
Db.tab.indexStats ({index: "Id_ind"}) Db.runcommand ({indexstats: "tab", Index: "Id_ind"}) Db.tab.getIndexes () Db.system.indexes.find ()	(top 2 doesn't seem to work, official documentation explains) (not intended for production deployments) View Index
Db.tab.totalIndexSize ();	View index Size
Db.tab.reIndex () Db.runcommand ({reIndex: "tab"})	Rebuilding indexes
Db.tab.dropIndex (<indexname>) Db.tab.dropIndex ("Id_1") Db.tab.dropIndexes ()	Delete Index <indexname> index names seen for getindexes Delete all indexes (note!) ）

Index Performance test:

See if the index is in effect and the performance of the analysis query improves. Insert 100,000 data into the Collection tab first

for (Var i=0;1<=100000;i++) {

var value=parseint (I*math.random ());

Db.tab.insert ({"id": I, "name": "KK" +i, "value": value});

}

Do not know is not the reason for the virtual machine, inserted 10 minutes are not completed! ~

You open the folder to view, and never go into the folder. Result the client connection is broken! ~ View Service has stopped!

Restart the service, go in to see the number of rows: 960,000! (Check it again later!) Just use this data test! ）

Db.tab.find (). Count ()

analyzequery performance:http://docs.mongodb.org/manual/tutorial/analyze-query-plan/

Analytic functions
Db.tab.find ({"Name": "kk50000"}). Explain ()	Query Name= execution analysis of "kk50000"
Db.tab.find ({"Name": "kk50000"}). Explain ("Queryplanner") Db.tab.find ({"Name": "kk50000"}). Explain ("verbosity") Db.tab.find ({"Name": "kk50000"}). Explain ("Executionstats") Db.tab.find ({"Name": "kk50000"}). Explain ("Allplansexecution")	The results of these 3 methods of execution fully include the results above
Db.tab.find ({"Name": "kk50000"}). Explain () result analysis:
"Cursor": "Basiccursor", "Ismultikey": false, "N": 1, "Nscannedobjects": 966423, "nscanned": 966423, "Nscannedobjectsallplans": 966423, "Nscannedallplans": 966423, "Scanandorder": false, "IndexOnly": false, "Nyields": 7555, "Nchunkskips": 0, "Millis": 4677, "Server": "kk-ad:27017", "Filterset": false	The cursor type. Basiccurso (Scan), Btreecursor (index) Whether the multi-key (combined) index Number of rows returned Number of rows scanned Number of rows scanned Number of scheduled scans Number of scheduled scans Whether to sort in memory Time-consuming (milliseconds) Server

Now create the index:

Db.tab.createIndex ({"Name": 1})

Db.tab.find ({"Name": "kk50000"}). Explain () using the results of the index
"Cursor": "Btreecursor name_1", "Ismultikey": false, "N": 1, "Nscannedobjects": 1, "Nscanned": 1, "Nscannedobjectsallplans": 1, "Nscannedallplans": 1, "Scanandorder": false, "IndexOnly": false, "Nyields": 0, "Nchunkskips": 0, "Millis": 1, "Indexbounds": { "Name": [ [ "kk50000", "kk50000" ] ] }, "Server": "kk-ad:27017", "Filterset": false	Cursor Use index btreecursor = name_1 Time: 1 ms

As you can see, when the index is not used, it takes 4677 milliseconds to use the index, 1 milliseconds! ~ and no full document scanning is necessary.

Index hint (hint), the index created by the current collection:

Db.tab.ensureIndex ({"id": 1}, {name: "Id_ind"})

Db.tab.ensureIndex ({"id": 1, "name": 1},{background:1,unique:1})

Db.tab.ensureIndex ({"Name": "Hashed"})

Now query id=5000 rows (result set is 1 rows)

Db.tab.find ({"id": ()). Explain ()

The query uses a composite index of ID and name.

"Nscannedobjectsallplans": 2,

"Nscannedallplans": 2,

Now add the index hint and force the index to be used:

Db.tab.find ({"id": ()). Hint ({"id": 1}). Explain ()

The index of the single key column ID is used.

"Nscannedobjectsallplans": 1,

"Nscannedallplans": 1,

You can also see that the index has a boundary value of "Indexbounds"

This boundary value, when queried by a composite index, causes more data to be scanned. This is a Bug:wrong index ranges when using the compound index on a list

Of course we can also limit the boundary value ourselves.

Db.tab.find (). Min ({"id": +}). Max ({"id": 5005})

From the above, only the values within this boundary are actually queried. Then view the execution plan:

Db.tab.find (). Min ({"id": +}). Max ({"id": 5005}). Explain ()

Just 5 rows of data. If the query is id=5000, but there is a problem with the index boundary, you can restrict the boundary, such as:

Db.tab.find ({"id": +}). Min ({"id": +}). Max ({"id": 5005})

In the index method, there is also a method of Cursor.snapshot (), which ensures that the query does not return the same document more than once, even if the write operation is moved in a document because the document size grows. However, snapshot () cannot guarantee the isolation of insertions or deletions. Snapshot () is the index used on the _ID key column, so snapshot () cannot use sort () or hint ().

Split-Snapshot function Analysis of query results for snapshot ():

Db.tab.find ({"id": ()). Snapshot (). Explain ()

Although the index "_id" was used, the entire collection was searched! ~

Add index hint to see, should be an error:

Db.tab.find ({"id": ()). Snapshot (). Hint ({"id": 1})

This is an error: Snapshot cannot use hints.

Here are some ways to summarize index queries:

Indexing Query Modifiers
Db.tab.find ({"id": +}). Hint ({"id": 1}) Db.tab.find ({"id": "_addspecial"). ("$hint", {"id": 1}) Db.tab.find ({$query: {"id":-), $hint: {"id": 1})	Query the results of id=5000 using index of key column ID
Db.tab.find ({"id": ()). Snapshot () Db.tab.find ({"id": _addspecial}). ("$snapshot", True) Db.tab.find ({$query: {"id": (), $snapshot: true})	Results of the query id=5000 using the snapshot
Db.tab.find ({"id": ()). Hint ({"id": 1}). Explain () Db.tab.find ({"id": "_addspecial"). ("$explain", 1) Db.tab.find ({$query: {"id": (), $hint: {"id": 1}, $explain: 1})	View Execution Plan Information
Index boundary settings
Db.tab.find ({"id": +}). Max ({"id": 5005}) Db.tab.find ({$query: {"id":-), $max: {"id": 5005}) Db.tab.find ({"id": "_addspecial"). ("$max", {"id": 5005}) Db.tab.find ({"id": +}). Min ({"id": (). Max ({"id": 5005}). Explain () Db.tab.find ({$query: {"id": "5005}, $max: {" id ":" {"}, $min: {" id ": 5000}}) Db.tab.find ({"id": "_addspecial"). ("$min", {"id": (). _addspecial ("$max", {"id": 5005})

A summary of this has been extracted: http://www.w3cschool.cc/mongodb/mongodb-indexing.html

Parameter	Type	Description
Background	Boolean	The indexing process blocks other database operations, and background can specify a later way to create an index, which is to add "background" optional parameters. The default value for "Background" is false.
Unique	Boolean	Whether the established index is unique. Specifies true to create a unique index. The default value is false.
Name	String	The name of the index. If not specified, MongoDB generates an index name by the field name and sort order of the connection index.
Dropdups	Boolean	Whether to delete duplicate records when establishing a unique index, specifying true to create a unique index. The default value is false.
Sparse	Boolean	Do not enable indexing for field data that does not exist in the document; This parameter requires special attention, and if set to true, documents that do not contain corresponding fields are not queried in the index field ... The default value is false.
Expireafterseconds	Integer	Specify a value in seconds, complete the TTL setting, and set the lifetime of the collection.
V	Index version	The version number of the index. The default index version depends on the version that is run when the index is mongod created.
Weights	Document	The index weight value, which is between 1 and 99,999, that represents the score weight of the index relative to the other indexed fields.
Default_language	String	For a text index, this parameter determines the list of rules for the stop word and the stemming and the word changer. Default to English
Language_override	String	For a text index, this parameter specifies the name of the field contained in the document, the language overrides the default language, and the default value is language.

More reference official documents: Indexes

MongoDB Basics (iii) index usage in MongoDB