Shulou(Shulou.com)06/01 Report--

Composite index

> delete the previous collection and re-establish it, as shown below:

> db.person.drop ()

True

> for (var itemositani)

> db.person.ensureIndex ({"age": 1}) creates a single index

{"createdCollectionAutomatically": false, "numIndexesBefore": 1, "numIndexesAfter": 2, "ok": 1}

> db.person.ensureIndex ({"name": 1, "age": 1}) create a composite index {"name": 1, "age": 1}

{"createdCollectionAutomatically": false, "numIndexesBefore": 2, "numIndexesAfter": 3, "ok": 1}

> db.person.ensureIndex ({"age": 1, "name": 1}) create a composite index {"age": 1, "name": 1}

{"createdCollectionAutomatically": false, "numIndexesBefore": 3, "numIndexesAfter": 4, "ok": 1}

> specify multiple conditions when searching, and it is slow to use hint to force the use of a single index, as shown below:

> db.person.find ({"age": {"$gte": 20, "$lte": 30}, "name": "meteor1"}) .hint ({"age": 1}) .explain ("executionStats")

{"queryPlanner": {"plannerVersion": 1, "namespace": "test.person", "indexFilterSet": false, "parsedQuery": {"$and": [{"name": {"$eq": "meteor1"}}, {"age": {"$lte": 30}, {"age": {"$gte": 20}}]}, "winningPlan": {"stage": "FETCH" "filter": {"name": {"$eq": "meteor1"}, "inputStage": {"stage": "IXSCAN", "keyPattern": {"age": 1}, "indexName": "age_1", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward" "indexBounds": {"age": ["[20.030.0]]}}," rejectedPlans ": []}," executionStats ": {" executionSuccess ": true," nReturned ": 2000," executionTimeMillis ": 2621," totalKeysExamined ": 2000000," totalDocsExamined ": 2000000," executionStages ": {" stage ":" FETCH "," filter ": {" name ": {" $eq ":" meteor1 "} "nReturned": 2000, "executionTimeMillisEstimate": 2050, "works": 2000001, "advanced": 2000, "needTime": 1998000, "needYield": 0, "saveState": 15625, "restoreState": 15625, "isEOF": 1, "invalidates": 0, "docsExamined": 2000000, "alreadyHasObj": 0, "inputStage": {"stage": "IXSCAN", "nReturned": 2000000, "executionTimeMillisEstimate": 640, "works": 2000001, "advanced": 2000000 NeedTime: 0, "needYield": 0, "saveState": 15625, "restoreState": 15625, "isEOF": 1, "invalidates": 0, "keyPattern": {"age": 1}, "indexName": "age_1", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward" "indexBounds": {"age": ["[[20.0,30.0]]}," keysExamined ": 2000000," dupsTested ": 0," dupsDropped ": 0," seenInvalidated ": 0}," serverInfo ": {" host ":" meteor.yeecall.com "," port ": 27027," version ":" 3.2.8 "," gitVersion ":" ed70e33130c977bda0024c125b56d159573dbaf0 "}," ok ": 1}

> specify multiple conditions when looking up data, and use hint to force the use of {"age": 1, "name": 1} composite index, which is relatively fast, as shown below:

> db.person.find ({"age": {"$gte": 20, "$lte": 30}, "name": "meteor1"}) .hint ({"age": 1, "name": 1}) .explain ("executionStats")

{"queryPlanner": {"plannerVersion": 1, "namespace": "test.person", "indexFilterSet": false, "parsedQuery": {"$and": [{"name": {"$eq": "meteor1"}}, {"age": {"$lte": 30}, {"age": {"$gte": 20}}]}, "winningPlan": {"stage": "FETCH" "inputStage": {"stage": "IXSCAN", "keyPattern": {"age": 1, "name": 1}, "indexName": "age_1_name_1", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"age": ["[20.0,30.0]"] "name": [[\ "meteor1\",\ "meteor1\"]}, "rejectedPlans": []}, "executionStats": {"executionSuccess": true, "nReturned": 2000, "executionTimeMillis": 15, "totalKeysExamined": 2010, "totalDocsExamined": 2000, "executionStages": {"stage": "FETCH", "nReturned": 2000, "executionTimeMillisEstimate": 10, "works": 2011, "advanced": 2000 NeedTime: 10, "needYield": 0, "saveState": 15, "restoreState": 15, "isEOF": 1, "invalidates": 0, "docsExamined": 2000, "alreadyHasObj": 0, "inputStage": {"stage": "IXSCAN", "nReturned": 2000, "executionTimeMillisEstimate": 10, "works": 2011, "advanced": 2000, "needTime": 10, "needYield": 0, "saveState": 15, "restoreState": 15. "isEOF": 1, "invalidates": 0, "keyPattern": {"age": 1, "name": 1}, "indexName": "age_1_name_1", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"age": ["[20.030.0]"] "name": ["[\" meteor1\ ",\" meteor1\ "]}," keysExamined ": 2010," dupsTested ": 0," dupsDropped ": 0," seenInvalidated ": 0}," serverInfo ": {" host ":" meteor.yeecall.com "," port ": 27027," version ":" 3.2.8 "," gitVersion ":" ed70e33130c977bda0024c125b56d159573dbaf0 "}," ok ": 1}

=

Sort again at the end of the query (sort by name), and use limit to intercept some of them, and use hint to force the use of {"age": 1, "name": 1} index is slower, as shown below:

> db.person.find ({"age": {"$gte": 20, "$lte": 30}}) .sort ({"name": 1}) .limit (100.hint ({"age": 1, "name": 1}) .explain ("executionStats")

{"queryPlanner": {"plannerVersion": 1, "namespace": "test.person", "indexFilterSet": false, "parsedQuery": {"$and": [{"age": {"$lte": 30}}, {"age": {"$gte": 20}}]}, "winningPlan": {"stage": "SORT", "sortPattern": {"name": 1}, "limitAmount": 100 "inputStage": {"stage": "SORT_KEY_GENERATOR", "inputStage": {"stage": "FETCH", "inputStage": {"stage": "IXSCAN", "keyPattern": {"age": 1, "name": 1}, "indexName": "age_1_name_1", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1 "direction": "forward", "indexBounds": {"age": ["[20.0,30.0]," name ": [" [MinKey, MaxKey] "]}," rejectedPlans ": []}," executionStats ": {" executionSuccess ": true," nReturned ": 100,100," executionTimeMillis ": 6991," totalKeysExamined ": 2000000," totalDocsExamined ": 2000000," executionStages ": {" stage ":" SORT " "nReturned": 100, "executionTimeMillisEstimate": 5980, "works": 2000103, "advanced": 100, "needTime": 2000002, "needYield": 0, "saveState": 15625, "restoreState": 15625, "isEOF": 1, "invalidates": 0, "sortPattern": {"name": 1}, "memUsage": 6100, "memLimit": 33554432, "limitAmount": 100, "inputStage": {"stage": SORT_KEY_GENERATOR " "nReturned": 0, "executionTimeMillisEstimate": 5680, "works": 2000002, "advanced": 0, "needTime": 1, "needYield": 0, "saveState": 15625, "restoreState": 15625, "isEOF": 1, "invalidates": 0, "inputStage": {"stage": "FETCH", "nReturned": 2000000, "executionTimeMillisEstimate": 4870, "works": 2000001, "advanced": 2000000, "needTime": 0, "needYield": 0 "saveState": 15625, "restoreState": 15625, "isEOF": 1, "invalidates": 0, "docsExamined": 2000000, "alreadyHasObj": 0, "inputStage": {"stage": "IXSCAN", "nReturned": 2000000, "executionTimeMillisEstimate": 2400, "works": 2000001, "advanced": 2000000, "needTime": 0, "needYield": 0, "saveState": 15625, "restoreState": 15625, "isEOF": 1, "invalidates": 0 "keyPattern": {"age": 1, "name": 1}, "indexName": "age_1_name_1", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"age": [[20.0,30.0]], "name": ["[MinKey, MaxKey]"]} "keysExamined": 2000000, "dupsTested": 0, "dupsDropped": 0, "seenInvalidated": 0}, "serverInfo": {"host": "meteor.yeecall.com", "port": 27027, "version": "3.2.8", "gitVersion": "ed70e33130c977bda0024c125b56d159573dbaf0"}, "ok": 1} >

Sort again at the end of the query (sort by name), and use limit to intercept some of them, and use hint to force the use of {"name": 1, "age": 1} index is faster, as shown below:

> db.person.find ({"age": {"$gte": 20, "$lte": 30}}) .sort ({"name": 1}) .limit (100.hint ({"name": 1, "age": 1}) .explain ("executionStats")

{"queryPlanner": {"plannerVersion": 1, "namespace": "test.person", "indexFilterSet": false, "parsedQuery": {"$and": [{"age": {"$lte": 30}}, {"age": {"$gte": 20}}]}, "winningPlan": {"stage": "LIMIT", "limitAmount": 100, "inputStage": {"stage": "FETCH" "filter": {"$and": [{"age": {"$lte": 30}}, {"age": {"$gte": 20}}]}, "inputStage": {"stage": "IXSCAN", "keyPattern": {"name": 1, "age": 1}, "indexName": "name_1_age_1", "isMultiKey": false, "isUnique": false, "isSparse": false "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"name": ["[MinKey, MaxKey]"], "age": ["[MinKey, MaxKey]"]}, "rejectedPlans": []}, "executionStats": {"executionSuccess": true, "nReturned": 100, "executionTimeMillis": 5, "totalKeysExamined": 100, "totalDocsExamined": 100 "executionStages": {"stage": "LIMIT", "nReturned": 100,0,101,101,100,101,101,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,100,100,100,100,100,100,100,100,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,101,100,101,101,101,101,101,101,101,101,100,101,101,101,101,101,101,100,101,101,101,101,101,101,101,101,100,101,101,101,101,10 "filter": {"$and": [{"age": {"$lte": 30}}, {"age": {"$gte": 20}}]}, "nReturned": 100, "executionTimeMillisEstimate": 0, "works": 100,100, "needTime": 0, "needYield": 0, "saveState": 0, "restoreState": 0, "isEOF": 0, "invalidates": 0, "docsExamined": 100 "alreadyHasObj": 0, "inputStage": {"stage": "IXSCAN", "nReturned": 100, "executionTimeMillisEstimate": 0, "works": 100, "advanced": 100, "needTime": 0, "needYield": 0, "saveState": 0, "restoreState": 0, "isEOF": 0, "invalidates": 0, "keyPattern": {"name": 1, "age": 1}, "indexName": "name_1_age_1" "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"name": ["MinKey, MaxKey]"], "age": ["MinKey, MaxKey]"]}, "keysExamined": 100, "dupsTested": 0, "dupsDropped": 0, "seenInvalidated": 0} "serverInfo": {"host": "meteor.yeecall.com", "port": 27027, "version": "3.2.8", "gitVersion": "ed70e33130c977bda0024c125b56d159573dbaf0"}, "ok": 1}

> if you sort by age, the index uses {"name": 1, "age": 1} very slowly; if you sort by age, the index uses {"age": 1, "name": 1} faster

> Analysis: in the first kind of index, you need to find the values of all composite query conditions (according to the index, keys and documents can be found quickly), but after finding them, you need to sort the documents in memory, which takes a lot of time. The second index works very well because there is no need to sort large amounts of data in memory. However, MongoDB has to scan the entire index to find all documents. Therefore, if you limit the scope of the query results, MongoDB can stop scanning the index after several matches, in which case it is a very good strategy to put the sort key first.

View Index

> db.person.getIndexes ()

[{"v": 1, "key": {"_ id": 1}, "name": "_ id_", "ns": "test.person"}, {"v": 1, "key": {"age": 1}, "name": "age_1", "ns": "test.person"}, {"v": 1, "key": {"name": 1, "age": 1} "name": "name_1_age_1", "ns": "test.person"}, {"v": 1, "key": {"age": 1, "name": 1}, "name": "age_1_name_1", "ns": "test.person"}]

> db.person.dropIndex ("name_1_age_1") deletes the index

{"nIndexesWas": 4, "ok": 1}

> db.person.dropIndex ("age_1_name_1")

{"nIndexesWas": 3, "ok": 1}

> db.person.dropIndex ("age_1")

{"nIndexesWas": 2, "ok": 1}

> another way for db.runCommand ({dropIndexes: "person", index: "*"}) to delete an index

{"nIndexesWas": 1, "msg": "non-_id indexes dropped for collection", "ok": 1}

> db.person.ensureIndex ({"name": 1, "age": 1}, {"unique": true}) create a unique index (this example did not succeed because there are duplicates in the collection)

{"ok": 0, "errmsg": "E11000 duplicate key error collection: test.person index: name_1_age_1 dup key: {:\" meteor0\ "," code ": 11000}

MongoDB index limit

Extra expenses

Each index occupies a certain amount of storage space, and it is also necessary to operate on the index during insert, update and delete operations. Therefore, if you rarely read the collection, it is recommended that you do not use the index.

Memory (RAM) usage

Since the index is stored in memory (RAM), you should ensure that the size of the index does not exceed the memory limit. (as indicated above, after sort sorting, the system will prompt an error if there is no limit field, because the index size exceeds the memory limit)

If the size of the index is larger than the memory limit, MongoDB will delete some indexes, which will cause performance degradation.

Query restriction

Indexes cannot be used by the following queries: regular expressions and non-operators, such as $nin, $not, etc.; arithmetic operators, such as $mod, etc.; $where clause

Therefore, it is a good habit to check whether a statement uses an index, which can be viewed with explain.

Index key limit

Starting with version 2.6, if the value of an existing index field exceeds the limit of the index key, no index will be created in MongoDB.

Insert document exceeds index key limit

If the value of the index field of a document exceeds the limit of the index key, MongoDB does not convert any document into a collection of indexes. Similar to mongorestore and mongoimport tools.

Maximum range

The index in the collection cannot exceed 64; the length of the index name cannot exceed 125 characters

A composite index can have up to 31 fields

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.