Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use the index in MongoDB, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

In any database, index is a means to improve the retrieval performance of the database.

There are still two types of index creation in the MongoDB database: one is created automatically, and the other is created manually.

Example: re-prepare a new simple collection.

Db.emp.drop ()

Db.emp.insert ({"name": "Zhao Yi", "sex": "male", "age": 30, "sal": 1000, "loc": "Beijing"})

Db.emp.insert ({"name": "Qian er", "sex": "female", "age": 22, "sal": 5000, "loc": "Shanghai"})

Db.emp.insert ({"name": "Sun San", "sex": "male", "age": 40, "sal": 2000, "loc": "Shenzhen"})

Db.emp.insert ({"name": "Li Si", "sex": "female", "age": 30, "sal": 7000, "loc": "Beijing"})

Db.emp.insert ({"name": "Friday", "sex": "female", "age": 30, "sal": 6400, "loc": "Beijing"})

Db.emp.insert ({"name": "Wu Liu", "sex": "male", "age": 30, "sal": 2500, "loc": "Chongqing"})

Db.emp.insert ({"name": "Zheng Qi", "sex": "female", "age": 50, "sal": 4700, "loc": "Chengdu"})

Db.emp.insert ({"name": "son of a bitch", "sex": "male", "age": 35, "sal": 8000, "loc": "Beijing"})

At this point, no indexes are set on the emp collection, so you can observe the indexes in the emp collection through the getIndexes () function.

Example: query the indexed contents of the emp collection by default

> db.emp.getIndexes ()

[

{

"v": 2

"key": {

"_ id": 1

}

"name": "_ id_"

"ns": "hr.emp"

}

]

It is now found that there will be indexed content with a "_ id" column. But if you want to create your own index, you can use the following syntax:

Index creation: db. Collection name .createIndex ({column: 1})

A set of 1 indicates that the index is sorted in ascending order, or "- 1" if descending order is used.

Example: create an index and set a descending index on the age field

> db.emp.createIndex ({"age":-1})

{

"createdCollectionAutomatically": false

"numIndexesBefore": 1

"numIndexesAfter": 2

"ok": 1

The name of the index is not set at this time, so the name is named automatically. Naming specification: "sorting mode of field name _ index"

> db.emp.getIndexes ()

[

{

"v": 2

"key": {

"_ id": 1

}

"name": "_ id_"

"ns": "hr.emp"

}

{

"v": 2

"key": {

"age":-1

}

"name": "age_-1"

"ns": "hr.emp"

}

]

Example: do an analysis of the index on the current age field

> db.emp.find ({"age": 30}) .explain ("executionStats")

{

"queryPlanner": {

"plannerVersion": 1

"namespace": "hr.emp"

"indexFilterSet": false

"parsedQuery": {

"age": {

"$eq": 30

}

}

"winningPlan": {

"stage": "FETCH"

"inputStage": {

"stage": "IXSCAN"

"keyPattern": {

"age":-1

}

"indexName": "age_-1"

"isMultiKey": false

"multiKeyPaths": {

"age": []

}

"isUnique": false

"isSparse": false

"isPartial": false

"indexVersion": 2

"direction": "forward"

"indexBounds": {

"age": [

"[30.0,30.0]"

]

}

}

}

"rejectedPlans": []

}

"executionStats": {

"executionSuccess": true

"nReturned": 4

"executionTimeMillis": 0

"totalKeysExamined": 4

"totalDocsExamined": 4

"executionStages": {

"stage": "FETCH"

"nReturned": 4

"executionTimeMillisEstimate": 0

"works": 5

"advanced": 4

"needTime": 0

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"docsExamined": 4

"alreadyHasObj": 0

"inputStage": {

"stage": "IXSCAN"

"nReturned": 4

"executionTimeMillisEstimate": 0

"works": 5

"advanced": 4

"needTime": 0

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"keyPattern": {

"age":-1

}

"indexName": "age_-1"

"isMultiKey": false

"multiKeyPaths": {

"age": []

}

"isUnique": false

"isSparse": false

"isPartial": false

"indexVersion": 2

"direction": "forward"

"indexBounds": {

"age": [

"[30.0,30.0]"

]

}

"keysExamined": 4

"seeks": 1

"dupsTested": 0

"dupsDropped": 0

"seenInvalidated": 0

}

}

}

"serverInfo": {

"host": "D2-LZY245"

"port": 27017

"version": "3.4.7"

"gitVersion": "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"

}

"ok": 1

}

The query at this time uses the indexing technique, but let's take a look at another query without using index fields.

Example: query for sal field

> db.emp.find ({"sal": {"$gt": 5000}}) .explain ("executionStats")

{

"queryPlanner": {

"plannerVersion": 1

"namespace": "hr.emp"

"indexFilterSet": false

"parsedQuery": {

"sal": {

"$gt": 5000

}

}

"winningPlan": {

"stage": "COLLSCAN"

"filter": {

"sal": {

"$gt": 5000

}

}

"direction": "forward"

}

"rejectedPlans": []

}

"executionStats": {

"executionSuccess": true

"nReturned": 3

"executionTimeMillis": 0

"totalKeysExamined": 0

"totalDocsExamined": 8

"executionStages": {

"stage": "COLLSCAN"

"filter": {

"sal": {

"$gt": 5000

}

}

"nReturned": 3

"executionTimeMillisEstimate": 0

"works": 10

"advanced": 3

"needTime": 6

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"direction": "forward"

"docsExamined": 8

}

}

"serverInfo": {

"host": "D2-LZY245"

"port": 27017

"version": "3.4.7"

"gitVersion": "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"

}

"ok": 1

}

No index is set on the sal field at this time, so the current index form becomes a full collection scan mode.

But now, in another way, age and salary perform the query together:

> db.emp.find ({"age": 30, "sal": 7000}) .explain ("executionStats")

{

"queryPlanner": {

"plannerVersion": 1

"namespace": "hr.emp"

"indexFilterSet": false

"parsedQuery": {

"$and": [

{

"age": {

"$eq": 30

}

}

{

"sal": {

"$eq": 7000

}

}

]

}

"winningPlan": {

"stage": "FETCH"

"filter": {

"sal": {

"$eq": 7000

}

}

"inputStage": {

"stage": "IXSCAN"

"keyPattern": {

"age":-1

}

"indexName": "age_-1"

"isMultiKey": false

"multiKeyPaths": {

"age": []

}

"isUnique": false

"isSparse": false

"isPartial": false

"indexVersion": 2

"direction": "forward"

"indexBounds": {

"age": [

"[30.0,30.0]"

]

}

}

}

"rejectedPlans": []

}

"executionStats": {

"executionSuccess": true

"nReturned": 1

"executionTimeMillis": 0

"totalKeysExamined": 4

"totalDocsExamined": 4

"executionStages": {

"stage": "FETCH"

"filter": {

"sal": {

"$eq": 7000

}

}

"nReturned": 1

"executionTimeMillisEstimate": 0

"works": 5

"advanced": 1

"needTime": 3

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"docsExamined": 4

"alreadyHasObj": 0

"inputStage": {

"stage": "IXSCAN"

"nReturned": 4

"executionTimeMillisEstimate": 0

"works": 5

"advanced": 4

"needTime": 0

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"keyPattern": {

"age":-1

}

"indexName": "age_-1"

"isMultiKey": false

"multiKeyPaths": {

"age": []

}

"isUnique": false

"isSparse": false

"isPartial": false

"indexVersion": 2

"direction": "forward"

"indexBounds": {

"age": [

"[30.0,30.0]"

]

}

"keysExamined": 4

"seeks": 1

"dupsTested": 0

"dupsDropped": 0

"seenInvalidated": 0

}

}

}

"serverInfo": {

"host": "D2-LZY245"

"port": 27017

"version": "3.4.7"

"gitVersion": "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"

}

"ok": 1

}

At this time, although there is an index on the age field, the full table scan operation is still used because there is no index on the sal field. To solve the problem at this time, you can use a composite index.

> db.emp.createIndex ({"age":-1, "sal":-1}, {name: "age_-1_sal_-1_index"})

{

"createdCollectionAutomatically": false

"numIndexesBefore": 2

"numIndexesAfter": 3

"ok": 1

}

Example: use index by default

> db.emp.find ({"age": 30, "sal": 7000}) .explain ("executionStats")

{

"queryPlanner": {

"plannerVersion": 1

"namespace": "hr.emp"

"indexFilterSet": false

"parsedQuery": {

"$and": [

{

"age": {

"$eq": 30

}

}

{

"sal": {

"$eq": 7000

}

}

]

}

"winningPlan": {

"stage": "FETCH"

"inputStage": {

"stage": "IXSCAN"

"keyPattern": {

"age":-1

"sal":-1

}

"indexName": "age_-1_sal_-1_index"

"isMultiKey": false

"multiKeyPaths": {

"age": []

"sal": []

}

"isUnique": false

"isSparse": false

"isPartial": false

"indexVersion": 2

"direction": "forward"

"indexBounds": {

"age": [

"[30.0,30.0]"

]

"sal": [

"[7000.0, 7000.0]"

]

}

}

}

"rejectedPlans": [

{

"stage": "FETCH"

"filter": {

"sal": {

"$eq": 7000

}

}

"inputStage": {

"stage": "IXSCAN"

"keyPattern": {

"age":-1

}

"indexName": "age_-1"

"isMultiKey": false

"multiKeyPaths": {

"age": []

}

"isUnique": false

"isSparse": false

"isPartial": false

"indexVersion": 2

"direction": "forward"

"indexBounds": {

"age": [

"[30.0,30.0]"

]

}

}

}

]

}

"executionStats": {

"executionSuccess": true

"nReturned": 1

"executionTimeMillis": 0

"totalKeysExamined": 1

"totalDocsExamined": 1

"executionStages": {

"stage": "FETCH"

"nReturned": 1

"executionTimeMillisEstimate": 0

"works": 3

"advanced": 1

"needTime": 0

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"docsExamined": 1

"alreadyHasObj": 0

"inputStage": {

"stage": "IXSCAN"

"nReturned": 1

"executionTimeMillisEstimate": 0

"works": 2

"advanced": 1

"needTime": 0

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"keyPattern": {

"age":-1

"sal":-1

}

"indexName": "age_-1_sal_-1_index"

"isMultiKey": false

"multiKeyPaths": {

"age": []

"sal": []

}

"isUnique": false

"isSparse": false

"isPartial": false

"indexVersion": 2

"direction": "forward"

"indexBounds": {

"age": [

"[30.0,30.0]"

]

"sal": [

"[7000.0, 7000.0]"

]

}

"keysExamined": 1

"seeks": 1

"dupsTested": 0

"dupsDropped": 0

"seenInvalidated": 0

}

}

}

"serverInfo": {

"host": "D2-LZY245"

"port": 27017

"version": "3.4.7"

"gitVersion": "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"

}

"ok": 1

}

But if the following conditions are changed:

> db.emp.find ({"$or": [{"age": {"$gt": 30}}, {"sal": {"$gt": 5000}}]}) .explain ("executionStats")

{

"queryPlanner": {

"plannerVersion": 1

"namespace": "hr.emp"

"indexFilterSet": false

"parsedQuery": {

"$or": [

{

"age": {

"$gt": 30

}

}

{

"sal": {

"$gt": 5000

}

}

]

}

"winningPlan": {

"stage": "SUBPLAN"

"inputStage": {

"stage": "COLLSCAN"

"filter": {

"$or": [

{

"age": {

"$gt": 30

}

}

{

"sal": {

"$gt": 5000

}

}

]

}

"direction": "forward"

}

}

"rejectedPlans": []

}

"executionStats": {

"executionSuccess": true

"nReturned": 5

"executionTimeMillis": 0

"totalKeysExamined": 0

"totalDocsExamined": 8

"executionStages": {

"stage": "SUBPLAN"

"nReturned": 5

"executionTimeMillisEstimate": 0

"works": 10

"advanced": 5

"needTime": 4

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"inputStage": {

"stage": "COLLSCAN"

"filter": {

"$or": [

{

"age": {

"$gt": 30

}

}

{

"sal": {

"$gt": 5000

}

}

]

}

"nReturned": 5

"executionTimeMillisEstimate": 0

"works": 10

"advanced": 5

"needTime": 4

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"direction": "forward"

"docsExamined": 8

}

}

}

"serverInfo": {

"host": "D2-LZY245"

"port": 27017

"version": "3.4.7"

"gitVersion": "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"

}

"ok": 1

}

Now it turns out that indexes are not being used, so let's see if we can force the use of indexes once at this time: the hint () function forces the use of index operations.

Example: force the use of an index

> db.emp.find ({"$or": [{"age": {"$gt": 50}}, {"sal": {"$gt": 3000}}]}) .hint ("age_-1_sal_-1_index") .explain ("executionStats")

{

"queryPlanner": {

"plannerVersion": 1

"namespace": "hr.emp"

"indexFilterSet": false

"parsedQuery": {

"$or": [

{

"age": {

"$gt": 50

}

}

{

"sal": {

"$gt": 3000

}

}

]

}

"winningPlan": {

"stage": "FETCH"

"filter": {

"$or": [

{

"age": {

"$gt": 50

}

}

{

"sal": {

"$gt": 3000

}

}

]

}

"inputStage": {

"stage": "IXSCAN"

"keyPattern": {

"age":-1

"sal":-1

}

"indexName": "age_-1_sal_-1_index"

"isMultiKey": false

"multiKeyPaths": {

"age": []

"sal": []

}

"isUnique": false

"isSparse": false

"isPartial": false

"indexVersion": 2

"direction": "forward"

"indexBounds": {

"age": [

"[MaxKey, MinKey]"

]

"sal": [

"[MaxKey, MinKey]"

]

}

}

}

"rejectedPlans": []

}

"executionStats": {

"executionSuccess": true

"nReturned": 5

"executionTimeMillis": 0

"totalKeysExamined": 8

"totalDocsExamined": 8

"executionStages": {

"stage": "FETCH"

"filter": {

"$or": [

{

"age": {

"$gt": 50

}

}

{

"sal": {

"$gt": 3000

}

}

]

}

"nReturned": 5

"executionTimeMillisEstimate": 0

"works": 9

"advanced": 5

"needTime": 3

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"docsExamined": 8

"alreadyHasObj": 0

"inputStage": {

"stage": "IXSCAN"

"nReturned": 8

"executionTimeMillisEstimate": 0

"works": 9

"advanced": 8

"needTime": 0

"needYield": 0

"saveState": 0

"restoreState": 0

"isEOF": 1

"invalidates": 0

"keyPattern": {

"age":-1

"sal":-1

}

"indexName": "age_-1_sal_-1_index"

"isMultiKey": false

"multiKeyPaths": {

"age": []

"sal": []

}

"isUnique": false

"isSparse": false

"isPartial": false

"indexVersion": 2

"direction": "forward"

"indexBounds": {

"age": [

"[MaxKey, MinKey]"

]

"sal": [

"[MaxKey, MinKey]"

]

}

"keysExamined": 8

"seeks": 1

"dupsTested": 0

"dupsDropped": 0

"seenInvalidated": 0

}

}

}

"serverInfo": {

"host": "D2-LZY245"

"port": 27017

"version": "3.4.7"

"gitVersion": "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"

}

"ok": 1

}

Normally, it is impossible for this code to call the default index execution at all, and you can use the hint () function to force MongoDB to use the index if necessary.

If you set too many indexes in a collection, it will actually lead to performance degradation. Then you can delete the index.

Example: delete an index

> db.emp.dropIndex ("age_-1_sal_-1_index")

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

> db.emp.getIndexes ()

[

{

"v": 2

"key": {

"_ id": 1

}

"name": "_ id_"

"ns": "hr.emp"

}

{

"v": 2

"key": {

"age":-1

}

"name": "age_-1"

"ns": "hr.emp"

}

]

Example: delete indexes that are not "_ id", that is, custom indexes

> db.emp.dropIndexes ()

{

"nIndexesWas": 2

"msg": "non-_id indexes dropped for collection"

"ok": 1

}

> db.emp.getIndexes ()

[

{

"v": 2

"key": {

"_ id": 1

}

"name": "_ id_"

"ns": "hr.emp"

}

]

The above is all the contents of the article "how to use the Index in MongoDB". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.