MongoDB BASICS (3) Use of indexes in mongodb and mongodb Indexes

Source: Internet
Author: User
Tags createindex stem words

MongoDB BASICS (3) Use of indexes in mongodb and mongodb Indexes

The indexes in MongoDB are similar to those in other databases and use the B-Tree structure. MongoDB indexes are at the collection level and support creating indexes in any column or the sub-column of the document in the set.

 

The following is an official structure chart that uses index query and sorting.


All MongoDB sets have a unique index on the field "_ id" by default. If the application does not define a value for the "_ id" column, mongoDB creates a column with the ObjectId value. (ObjectId is generated based on the time, computer ID, process ID, and local process counter)


MongoDB also supports creating an index in ascending or descending order on one or more columns.

MongoDB can also create multi-key indexes, array indexes, spatial indexes, text indexes, and hash indexes. The attributes of these indexes can be unique indexes, sparse indexes, and TTL (Time to live) Index.

 

Index restrictions:

The index name cannot exceed 128 characters

Each set cannot contain more than 64 indexes.

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. ensureIndex ({"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: Key Column

Sort: sort. 1 is in ascending order;-1 is in descending order.

 

Create a single column Index

Create an index and specify the index name

Create a unique compound index in the background

Create a hash Index

 (For more parameters, see the bottom of the article)

Db. tab. indexStats ({index: "id_ind "})

Db. runCommand ({indexStats: "tab", index: "id_ind "})

Db. tab. getIndexes ()

Db. system. indexes. find ()

(The first two do not seem to be available, as explained in the official document)

(Not intended for production deployments)

View Indexes

Db. tab. totalIndexSize ();

View index size

Db. tab. reIndex ()

Db. runCommand ({reIndex: "tab "})

Re-Indexing

Db. tab. dropIndex (<indexname>)

Db. tab. dropIndex ("id_1 ")

Db. tab. dropIndexes ()

Delete Index

<Indexname> indicates the index name displayed in getIndexes.

Delete all indexes (note !)

 

 



Index performance test:

 

Check whether the index takes effect and check whether the query performance has improved. First insert 0.1 million data to the Set tab

 

For (var I = 0; 1 <= 100000; I ++ ){

Var value = parseInt (I * Math. random ());

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

}

 

I don't know if it is the reason for the virtual machine. It has not been completed after being inserted for 10 minutes !~

Open the folder again and you will not be able to enter the folder. The client is disconnected !~ Check that the service has stopped!



Restart the service and check the number of lines: 0.96 million! (Check it later! Use this data for testing !)

Db. tab. find (). count ()


AnalyzeQuery Performance:Http://docs.mongodb.org/manual/tutorial/analyze-query-plan/

Analysis functions

Db. tab. find ({"name": "kk50000"}). explain ()

Query the execution Analysis of "name =" 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 execution results of these three methods fully include the results above.

Db. tab. find ({"name": "kk50000"}). explain () results for analysis:

"Cursor": "BasicCursor ",

"IsMultiKey": false,

"N": 1,

"NscannedObjects": 966423,

"Nscanned": 966423,

"NscannedObjectsAllPlans": 966423,

"NscannedAllPlans": 966423,

"ScanAndOrder": false,

"IndexOnly": false,

"NYields": 7555,

"NChunkSkips": 0,

"Millennium": 4677,

"Server": "k-ad: 27017 ",

"FilterSet": false

Cursor type. BasicCurso (SCAN), BtreeCursor (INDEX)

Multi-Key (combination) index?

Number of returned rows

Number of scanned rows

Number of scanned rows

Number of all scheduled scans

Number of all scheduled scans

Sort in memory?

 

 

 

Time elapsed (MS)

Server

 



Create an index now:

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



Db. tab. find ({"name": "kk50000"}). explain () use index results

"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": "k-ad: 27017 ",

"FilterSet": false

The cursor uses the index BtreeCursor = name_1

 

 

 

 

 

 

 

 

 

 

Time consumed: 1 ms

 

 

 

 

 

 

 

 

 

 


As you can see above, it takes 4677 milliseconds to use an index when it is not used. After using an index, it takes 1 millisecond !~ You do not need to scan all documents.



Index prompt (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 "})


Query the rows with id = 5000 (the result set is one row)

Db. tab. find ({"id": 5000}). explain ()



The query uses the compound index of id and name.

"NscannedObjectsAllPlans": 2,

"NscannedAllPlans": 2,

 

Now the index prompt is added to force the index:

Db. tab. find ({"id": 5000}). hint ({"id": 1}). explain ()



The index of the id column is used.

"NscannedObjectsAllPlans": 1,

"NscannedAllPlans": 1,

 

As you can see above, the index has a boundary value "indexBounds"



When this boundary value is queried by a composite index, more data is scanned. This is a bug: wrong index ranges when using compound index on a list


Of course, we can also limit the boundary value.

Db. tab. find (). min ({"id": 5000}). max ({"id": 5005 })



From the above, we can only query the values in the boundary. View the execution plan again:

Db. tab. find (). min ({"id": 5000}). max ({"id": 5005}). explain ()



Only five rows of data. If the query id is 5000, but there is a problem with the index boundary, you can limit the boundary, for example:

Db. tab. find ({"id": 5000}). min ({"id": 5000}). max ({"id": 5005 })



In the index method, another method is cursor. snapshot (), which ensures that the same document is not returned for multiple queries, even if the write operation is performed on a document that is moved due to the increase in the document size. However, snapshot () cannot guarantee the isolation of insertion or deletion. Snapshot () is an index on the _ id key column. Therefore, snapshot () cannot use sort () or hint ().


Analyze the query result of snapshot () by using the snapshot function:

Db. tab. find ({"id": 5000}). snapshot (). explain ()


Although the index "_ id" is used, the entire set is searched !~

 

If you add an index, the following error occurs:

Db. tab. find ({"id": 5000}). snapshot (). hint ({"id": 1 })


Indeed, an error occurs: the snapshot cannot be used.



The following describes the index query methods:

Indexing Query Modifiers

Db. tab. find ({"id": 5000}). hint ({"id": 1 })

Db. tab. find ({"id": 5000}). _ addSpecial ("$ hint", {"id": 1 })

Db. tab. find ({$ query: {"id": 5000}, $ hint: {"id": 1 }})

Use the index of the key column id to query the result with id = 5000

Db. tab. find ({"id": 5000}). snapshot ()

Db. tab. find ({"id": 5000}). _ addSpecial ("$ snapshot", true)

Db. tab. find ({$ query: {"id": 5000}, $ snapshot: true })

Result of querying with snapshot id = 5000

Db. tab. find ({"id": 5000}). hint ({"id": 1}). explain ()

Db. tab. find ({"id": 5000}). _ addSpecial ("$ explain", 1)

Db. tab. find ({$ query: {"id": 5000}, $ hint: {"id": 1}, $ explain: 1 })

View execution plan information

Index boundary settings

Db. tab. find ({"id": 5000}). max ({"id": 5005 })

Db. tab. find ({$ query: {"id": 5000}, $ max: {"id": 5005 }})

Db. tab. find ({"id": 5000}). _ addSpecial ("$ max", {"id": 5005 })

 

Db. tab. find ({"id": 5000}). min ({"id": 5000}). max ({"id": 5005}). explain ()

Db. tab. find ({$ query: {"id": 5000}, $ max: {"id": 5005}, $ min: {"id": 5000 }})

Db. tab. find ({"id": 5000 }). _ addSpecial ("$ min", {"id": 5000 }). _ addSpecial ("$ max", {"id": 5005 })



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

Parameter

Type

Description

Background

Boolean

Other database operations are blocked during index creation. You can specify the method for creating indexes in the future by adding the "background" optional parameter. The default value of "background" isFalse.

Unique

Boolean

Whether the created index is unique. Specify true to create a unique index. The default value isFalse.

Name

String

The name of the index. If this parameter is not specified, an index name is generated by the field name and sorting order of the connected index.

DropDups

Boolean

Whether to delete duplicate records when creating a unique index. Specify true to create a unique index. The default value isFalse.

Sparse

Boolean

If this parameter is set to true, documents that do not contain the corresponding fields are not queried .. The default value isFalse.

ExpireAfterSeconds

Integer

Specify a value in seconds to complete TTL setting and set the survival time of the set.

V

Index version

The version number of the index. The default index version depends on the version that runs when mongod creates an index.

Weights

Document

Index weight. The value ranges from 1 to 99,999, indicating the score weight of the index relative to other index fields.

Default_language

String

For text indexes, this parameter determines the list of rules for deprecated words, stem words, and word servers. English by default

Language_override

String

For text indexes, this parameter specifies the field names included in the document. The language overwrites the default language. The default value is language.



For more information, see the official document Indexes.


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.