A brief introduction to index

1.1 Creating An Index

Like most relational databases, MongoDB supports query optimization using indexes, b-tree-like data structures to store index and document location information, as well as prefix and overwrite indexes. In MongoDB 4.0, the syntax for index creation is as follows:

db.collection.createIndex( <key and index type specification>, <options> )
Copy the code
  • <key and index type specification>: used to specify the attributes of the index field and the ascending and descending order.
  • <options>: Optional configuration, usually used to specify the nature of an index.

For the sake of the demonstration, insert some test data and create an index for the name field:

Db.user.insertmany ([{name: "heibai", age: 26, birthday: new Date(1998,08,23), createTime: new Timestamp(), Hobby: ["basketball", "football", "tennis"], {name: "hei", age: 32, birthday: new Date(1989,08,23), createTime: new Timestamp(), Hobby: ["basketball", "tennis"] }, { name: "ying", age: 46, birthday: New Date(1978,08,23), createTime: new Timestamp(), Hobby: ["tennis"]}])

#Create an index. -1 indicates that the index is stored in descending order
db.user.createIndex( { name: -1 } )
Copy the code

1.2 Viewing Indexes

After indexes are created, you can use getIndexes() to view all indexes of the collection, as shown in the following example:

db.user.getIndexes()
Copy the code

You can see from the output that the default index name is: field name + collation. In addition to the index we created for the name field, there is also an index for the _ID field in the collection, which is automatically created by the program to disallow the insertion of documents with the same _ID:

{
    "v" : 2,
    "key" : {
        "_id" : 1
    },
    "name" : "_id_",
    "ns" : "test.user"
},

{
    "v" : 2,
    "key" : {
        "name" : -1
    },
    "name" : "name_-1",
    "ns" : "test.user"
}
Copy the code

Type of index

MongoDB 4.x currently supports the following six types of indexes:

2.1 Single-field index

Index creation for a single field is supported, which is the most basic form of index. The above index created for the name field is a single-field index. In particular, we specify a collation for the name field when we create the index for it. But in practice, in sorted queries involving single-field indexes, the index key collation is irrelevant because MongoDB supports traversing the index in either direction. That is, the following two queries can be sorted using the name_-1 index:

db.user.find({}).sort({name:-1})
db.user.find({}).sort({name:1})
Copy the code

Most current databases support bi-directional index traversal, depending on the storage structure (see figure below). In the leaf node of b-tree structure, the value of the index key and the location information of the corresponding document are stored, and each leaf node is similar to a bidirectional linked list, which can be traversed from front to back:

2.2 Composite Index

Create indexes for multiple fields as shown in the following example:

db.user.createIndex( { name: -1,birthday: 1} )
Copy the code

If you create an index {a:1, b: 1, C :1, D :1}, then there are three implicit indexes on the set. These three implicit indexes can also be used to optimize queries and sort operations:

{ a: 1 }
{ a: 1, b: 1 }
{ a: 1, b: 1, c: 1 }
Copy the code

You should avoid creating redundant indexes, which can lead to additional performance overhead. Birthday: 1 birthday: 1 birthday: 1 birthday: 1 birthday: 1 birthday: 1 birthday: 1

For example, index {A: 1, b: -1} supports sort queries in the form of {A: 1, b: -1} and {A: -1, b: 1}, but does not support sort queries in the form of {A: -1, b: -1} or {A: 1, b: 1}. That is, the collation of the field is either exactly the same or completely opposite to the collation of the index key, in which case the bidirectional traversal lookup can be performed.

2.3 Multi-key Indexes

If the index contains fields of type array, MongoDB automatically creates a separate index entry for each element in the array, which is called a multi-key index. MongoDB uses multi-key indexes to optimize queries for what is stored in an array. The following is an example:

db.user.createIndex( { Hobby: 1 } )
Copy the code

2.4 Hash index

To support hash-based sharding, MongoDB provides hash indexes, which hash the index value and then calculate the sharding position. The syntax is as follows:

db.collection.createIndex( { _id: "hashed" } )
Copy the code

The hash results are scattered, so the hash index cannot be used for range queries, only for equivalence queries.

2.5 Geospatial index

To support efficient queries of geospatial coordinate data, MongoDB provides two special indexes:

  • 2d index using plane geometry, mainly used for plane map data (such as game map data), continuous time data;
  • 2DSPHERE index using spherical geometry, mainly for actual spherical map data.

This data is usually used to solve actual geographical queries, such as nearby food, all the businesses within the query range and so on. Its creation syntax is as follows:

db.<collection>.createIndex( { <location field> : "2d" ,
                               <additional field> : <value> } ,
                             { <index-specification options> } )
db.collection.createIndex( { <location field> : "2dsphere" } )
Copy the code

2.6 Text Index

MongoDB supports full-text indexes for full-text retrieval of the contents of specified fields. Its creation syntax is as follows:

db.<collection>.createIndex( { field: "text" } )
Copy the code

Note that a collection can have at most one text index, but a text index can contain multiple fields. The syntax is as follows:

db.<collection>.createIndex(
   {
     field0: "text",
     field1: "text"
   }
 )
Copy the code

Create a text index is a very expensive operation, because when you create a text index need to semantic analysis and effective resolution of the text, also need to break up after the keywords stored in memory, the computing power and storage of equipment have very high demand, it will also reduce the mongo’s performance, so you need to use caution.

Third, the nature of index

When creating an index, you can pass in the second parameter
to specify the properties of the index.

3.1 Unique Index

Unique indexes ensure that the value of a unique indexed column occurs only once in the same collection. The following is an example:

db.user.createIndex( { name: -1,birthday: 1}, { unique: true })
Copy the code

Name = heibai birthday = new Date(1998,08,23);

Db.user. insertOne({name: "heibai", birthday: new Date(1998,08,23)})Copy the code

The above situation is obvious, but if you perform the following operation twice, you will find that only the first insert succeeds, and the second duplicate key exception will be reported. This is because under the constraint of a unique index, the state in which name does not exist is treated as a unique state:

db.user.insertOne({
        age: 12
})
Copy the code

To solve this problem, you need to use index sparsity.

3.2 the sparse sex

To solve the above problem, we need to add sparsity to the index. Indexes cannot be modified. The index must be deleted and then created with the value set to true.

db.user.dropIndex("name_-1_birthday_1")
db.user.createIndex( { name: -1,birthday: 1}, { unique: true,sparse: true})
Copy the code

At this point, you execute the insert statement above several times to insert successfully. The reason is that for a sparse index, it only contains the index information of documents with index fields, even if the value of the index field is null, but the corresponding index field cannot be missing. If it is missing, the corresponding document is not included in the index information.

3.3 Partial Indexes

A partial index is used to create an index for the partial data that meets the criteria and must be used with the partialFilterExpression option. The partialFilterExpression option can use the following expression to determine the data range:

  • The equation expression (i.eValues of fields:Or use the $eq operator);
  • $exists: trueThe expression;
  • Gte,Lte operator;
  • The $type operator;
  • $and operator at the top level.

The following is an example:

db.user.createIndex(
   { name: -1 },
   { partialFilterExpression: { age: { $gt: 30 } } }
)
Copy the code

3.4 TTL index

The TTL index allows you to set a timeout for each document, and when a document reaches its timeout, it will be deleted. The expiration time of the TTL index is equal to the value of the index field + the specified number of seconds. The value of the index field can only be Date, as shown in the following example:

db.user.createIndex( { "birthday": 1 }, { expireAfterSeconds: 60 } )
Copy the code

Here we create a TTL index on the Birthday field for demonstration purposes only. In fact, THE TTL index is mainly used for data that only needs to be stored for a certain amount of time, such as session state, temporary logs, and so on. There are also the following considerations when using TTL indexes:

  • The TTL attribute can only be used for single-field indexes. Composite indexes are not supported.
  • The type of the field to set up the TTL index can only be Date and not timestamp.
  • If the field is an array and there are multiple date values in the index, MongoDB uses the earliest date value in the array to calculate the expiration time.
  • If the index field in the document is not a date or an array containing date values, the document will not expire.
  • If the document does not contain index fields, the document will not expire.

Delete index

The syntax for dropping an index is simple. You only need to call the dropIndex method and pass in the name and definition of the index as shown in the following example:

db.user.dropIndex("name_-1")
db.user.dropIndex({ name: -1,birthday: 1})
Copy the code

If you want to drop all indexes, you can call the dropIndexes method. Note that the default indexes based on the _ID will not be dropped.

db.collection.dropIndexes()
Copy the code

In addition, this command acquires the write lock of the corresponding database and blocks other operations until the index is deleted.

Fifth, the EXPLAIN

5.1 Output Parameters

MongoDB’s Explain () method, like MySQL’s Explain keyword, is used to display information about the execution plan. The following is an example:

db.user.find({name:"heibai"},{name:1,age:1}).sort({ name:1}).explain()
Copy the code

The partial output of the execution plan is as follows:

"inputStage" : {
    "stage" : "FETCH",
    "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
            "name" : -1,
            "birthday" : 1
        },
        "indexName" : "name_-1_birthday_1",
        "isMultiKey" : false,
        "multiKeyPaths" : {
            "name" : [ ],
            "birthday" : [ ]
        },
        "isUnique" : true,
        "isSparse" : true,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "backward",
        "indexBounds" : {
            "name" : [
                "[\"heibai\", \"heibai\"]"
            ],
            "birthday" : [
                "[MaxKey, MinKey]"
            ]
        }
    }
}
Copy the code

The value of the inner inputstage. stage in the output is IXSCAN, indicating that the index is used for scanning, and the indexName field indicates that the corresponding index is name_-1_birthday_1. The value of the outer inputstage. stage is FETCH, which means that in addition to fetching data from the index, it also needs to FETCH data from the corresponding document, because age information is not stored in the index. This output proves that MongoDB supports prefix indexes, and single-key indexes support bidirectional scanning.

5.2 Overwriting an Index

Here we modify the above query statement slightly, do not return the age field and the default _id field, statement as follows:

db.user.find({name:"heibai"},{_id:0, name:1}).sort({ name:1 }).explain()
Copy the code

The output is as follows. You can see that the query is missing a FETCH phase. Indicates that you only need to scan the index to obtain all the required information. In this case, the name_-1_birthday_1 index is the override index of the query operation.

"inputStage" : {
    "stage" : "IXSCAN",
    "keyPattern" : {
        "name" : -1,
        "birthday" : 1
    },
    "indexName" : "name_-1_birthday_1",
    "isMultiKey" : false,
    "multiKeyPaths" : {
        "name" : [ ],
        "birthday" : [ ]
    },
    "isUnique" : true,
    "isSparse" : true,
    "isPartial" : false,
    "indexVersion" : 2,
    "direction" : "backward",
    "indexBounds" : {
        "name" : [
            "[\"heibai\", \"heibai\"]"
        ],
        "birthday" : [
            "[MaxKey, MinKey]"
        ]
    }
}
Copy the code

The resources

  1. Official documentation: Indexes, sort-on-multiple-Fields
  2. Kristina Chodorow. The Definitive Guide to MongoDB (2nd edition). People’s Mail Publishing House. 2014-01

For more articles, please visit the full stack Engineer manual at GitHub.Github.com/heibaiying/…