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.