MongoDB Basics (iii) index usage in MongoDB

Source: Internet
Author: User
Tags createindex

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.