Getting to know the index in mongodb

Time:2021-11-30

The index is like a book directory, which allows us to quickly locate the required content. There are indexes in relational databases and NoSQL. In this article, we will briefly introduce the indexes in mongodb.

This is the ninth article in mongodb series. Understanding the previous articles will help you better understand this article:


1.Installing mongodb on Linux
2.Mongodb basic operation
3.Mongodb data type
4.Mongodb document update operation
5.Mongodb document query operation (I)
6.Mongodb document query operation (II)
7.Mongodb document query operation (III)
8.Mongodb view execution plan


Index creation

By default, in the collection_idA field is an index. We can view the index in a collection through the getindexes () method:

db.sang_collect.getIndexes()

The results are as follows:

[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "sang.sang_collect"
    }
]

We see that there is only one index here, that is_id

Now there are 10000 documents in my collection. I want to query the documents with x = 1. My query operations are as follows:

db.sang_collect.find({x:1})

This query will scan the whole table by default. We can use explain() introduced in the previous article to view the query plan, as follows:

db.sang_collect.find({x:1}).explain("executionStats")

The results are as follows:

{
    "queryPlanner" : {
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 15,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 10000,
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "x" : {
                    "$eq" : 1.0
                }
            },
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 29,
            "works" : 10002,
            "advanced" : 1,
            "needTime" : 10000,
            "needYield" : 0,
            "saveState" : 78,
            "restoreState" : 78,
            "isEOF" : 1,
            "invalidates" : 0,
            "direction" : "forward",
            "docsExamined" : 10000
        }
    },
    "serverInfo" : {
    },
    "ok" : 1.0
}

The result was long, and I took the key part. We can see that the query method is full table scanning. A total of 10000 documents were scanned to find the results I want. In fact, the document I want is the second, but the system does not know how many documents with x = 1 are in this collection, so it will scan the whole table. This method is of course inefficient, but if I add limit, it is as follows:

db.sang_collect.find({x:1}).limit(1)

At this time, looking at the query plan again, it is found that only two documents have been scanned, but if I want to query the records with x = 9999, I still have to scan the whole table. At this time, we can create an index for this field. The index creation method is as follows:

db.sang_collect.ensureIndex({x:1})

1 indicates ascending order, – 1 indicates descending order. After we index the X field, we can query according to the X field. The speed is very fast. Let’s see the execution plan of the query operation below:

db.sang_collect.find({x:9999}).explain("executionStats")

I won’t post this query plan if it’s too long. We can focus on a significant reduction in the time it takes to query.

At this point, call getindexes() method to see the index we just created, as follows:

[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "sang.sang_collect"
    },
    {
        "v" : 2,
        "key" : {
            "x" : 1.0
        },
        "name" : "x_1",
        "ns" : "sang.sang_collect"
    }
]

We see that each index has a name. The default index name isField name_ Sort valueOf course, we can also customize the index name when creating the index, as follows:

db.sang_collect.ensureIndex({x:1},{name:"myfirstindex"})

The created index is as follows:

{
    "v" : 2,
    "key" : {
        "x" : 1.0
    },
    "name" : "myfirstindex",
    "ns" : "sang.sang_collect"
}

Of course, there are many other optional parameters during index creation, as follows:

db.sang_collect.ensureIndex({x:1},{name:"myfirstindex",dropDups:true,background:true,unique:true,sparse:true,v:1,weights:99999})

As for the parameters here, let me say:

1. Name indicates the name of the index
2. DropDups means that if there are duplicates when creating a unique index, the duplicates will be deleted and only the first one will be retained
3. Background whether to create an index in the background. Creating an index in the background does not affect the current operation of the database. The default is false
4. Unique whether to create a unique index. The default is false
5. Spark whether to disable the index for fields that do not exist in the document. The default is false
6. V indicates the version number of the index, which is 2 by default
7. Weights indicates the weight of the index

The created index is as follows:

{
    "v" : 1,
    "unique" : true,
    "key" : {
        "x" : 1.0
    },
    "name" : "myfirstindex",
    "ns" : "sang.sang_collect",
    "background" : true,
    "sparse" : true,
    "weights" : 99999.0
}

View index

As described above, getindexes() can be used to view the index. We can also view the size of the index through totalindexsize(), as follows:

db.sang_collect.totalIndexSize()

Delete index

We can delete the index by name as follows:

db.sang_collect.dropIndex("xIndex")

It means to delete an index named xindex. Of course, we can also delete all indexes, as follows:

db.sang_collect.dropIndexes()

summary

Index is a good thing, which can effectively improve the query speed, but the index will reduce the speed of insertion, update and deletion, because these operations not only update the document, but also update the index. Mongodb limits the maximum number of 64 indexes on each set. We should carefully consider the fields of the index when creating the index.

Well, that’s all for the introduction to the index in mongodb. If you have any questions, please leave a message for discussion.

reference material:

1. Authoritative guide to mongodb, 2nd Edition

More information on the official account:
Getting to know the index in mongodb