Database index is similar to the index of books. With the index, there is no need to turn over the whole book. The database can directly search in the index, and after finding an item in the index, it can directly jump to the location of the target document, which can improve the speed of search by several orders of magnitude.
Create index
We create an index on the age key of the person collection to compare the performance of a query before and after the index is created.
Create index db.person.ensureIndex({“age”:1}). Here we use ensureIndex to create an index on age. 1: indicates the ascending order based on age. -1: indicates the descending order based on age.
Query performance without indexes:
Indexed query performance:
Let’s focus on these parameters. (See the previous article for parameter descriptions.)
ExecutionTimeMillis (total query time) : no index 962 ms; Having an index takes 143 milliseconds.
TotalDocsExamined (document scan entries) : no index is 2 million; There are 2000 indexes.
Stage (type of query) : None Index is COLLSCAN (full table scan); The index is FETCH+IXSCAN (index scan + retrieve the specified document according to the index).
ExecutionStages. ExecutionTimeMillisEstimate (time consuming of obtained data retrieval document) : no index of 910 milliseconds; It takes 0 ms to have an index.
After the index is built, the overall speed of the query is increased by an order of magnitude (1 order of magnitude means 10 times). Depending on the query statement, indexes can increase speed by several orders of magnitude.
Compound index
An index built on multiple keys is a compound index. Sometimes our query is not single condition, but multiple conditions, for example, to find a student whose age is 20~30 and name is “Ryan1”, then we can set up a joint index of “age” and “name” to speed up the query.
To demonstrate the effect of indexing, let’s rebuild and insert a collection of 2 million documents.
1 //Delete the original collection
2 db.person.drop();
3
4 //Insert 2 million pieces of data
5 for(vari=0; i<2000000; i++) { 6 db.person.insert({"name":"ryan"+i%1000,"age":20+i%10});
7 }
8
9 //Create three indexes
10 db.person.ensureIndex({"age":1})
11 db.person.ensureIndex({"name":1,"age":1})
12 db.person.ensureIndex({"age":1,"name":1})Copy the codeWe can use the hint() method to enforce which index the query goes to.
Let’s take a look at the power of a composite index over a single key index when the query criteria are multiple.
db.person.find({“age”:{“$gte”:20,”$lte”:30},”name”:”ryan1″}).hint({“age”:1}).explain(“executionStats”);
1 {
2 . 3 "executionStats" : {
4 "executionSuccess" : true. 5 "nReturned" : 2000. 6 "executionTimeMillis" : 2031. 7 "totalKeysExamined" : 2000000. 8 "totalDocsExamined" : 2000000. 9 .10 }Copy the code
db.person.find({“age”:{“$gte”:20,”$lte”:30},”name”:”ryan1″}).hint({“age”:1,”name”:1}).explain(“executionStats”);
1 {
2 . 3 "executionStats" : {
4 "executionSuccess" : true. 5 "nReturned" : 2000. 6 "executionTimeMillis" : 8. 7 "totalKeysExamined" : 2010. 8 "totalDocsExamined" : 2000. 9 .10 }Copy the code
You can tell the difference at a glance from the value of executionTimeMillis. The single-room index took 2031 milliseconds, and the composite index took 8 milliseconds. From this we can see, according to the different query statement, it is very important to establish the correct index, for the query statement is multi-condition, should consider the application of composite index.
Let’s look at one important use of composite indexes. There are scenarios where you sort a key and only want the first 100 results (this is often the case in real projects). In this case, the index should be set up {“sortKey”:1,”queryCriteria”:1}, and the sorted key should be placed first in the composite index.
Db. Person. The find ({” age “: {” $gte” : 21.0, “$lte” : 30.0}}), sort ({” name “: 1}). Limit (100). Hint ({” age” : 1, “name” : 1}). Explain (” executio nStats”);
1 {
2 . 3 "executionStats" : {
4 "executionSuccess" : true. 5 "nReturned" : 100. 6 "executionTimeMillis" : 6882. 7 "totalKeysExamined" : 1800000. 8 "totalDocsExamined" : 1800000. 9 .10 }Copy the codeDb. Person. The find ({” age “: {” $gte” : 21.0, “$lte” : 30.0}}), sort ({” name “: 1}). Limit (100). Hint ({” name” : 1, “age” : 1}). Explain (” executio nStats”);
1 {
2 . 3 "executionStats" : {
4 "executionSuccess" : true. 5 "nReturned" : 100. 6 "executionTimeMillis" : 3. 7 "totalKeysExamined" : 2100. 8 "totalDocsExamined" : 2100. 9 .10 }Copy the codeFrom the above results, it is easy to see that indexing based on sort keys works very well.
Analysis: In the first type of index, you need to find all the values of the compound query criteria (quickly by index, key, and document), but once you find them, you need to sort the documents in memory, which takes a lot of time. The second kind of index works very well because you don’t have to sort a lot of data in memory. However, MongoDB has to scan the entire index to find all the documents. Therefore, if the scope of the query results is limited, MongoDB can stop scanning the index after a few matches. In this case, placing the sort key first is a good strategy.
3. Unique index
A unique index ensures that the specified key of each document in the collection has a unique value. If you want to ensure that the “name” key for different documents has different values, you can create a unique index on the “name” key.
db.person.ensureIndex({“name”:1},{“unique”:true});
Then use db.person.getIndexes() to view all indexes in the current Person collection.
You can also create a composite unique index. When a composite unique index is created, individual keys can have the same value, but all key combinations must be unique.
db.person.ensureIndex({“name”:1,”age”:1},{“unique”:true});
Sparse index
Unique indexes treat NULL as a value, so multiple documents that lack a key in a unique index cannot be inserted into the collection. However, in some cases, you may want a unique index to work only for documents that contain the corresponding key. At this point we can use sparse indexes in MongoDB. This index is a completely different concept from sparse index in relational database. Sparse indexing in MongoDB simply does not require every document to be an index entry.
For example, if there is an optional Mobilephone field, but if it is provided, its value must be unique:
db.person.ensureIndex({“mobilephone”:1}{“unique”:true,”sparse”:true});
Sparse indexes need not be unique. Simply remove the unique option to create a non-unique sparse index.
Index management
As described in section 1, you can create a new index using the ensureIndex method, or you can use the createIndex method.
Once an index is created, you can use the getIndexes() method to view information about all indexes on a given collection.
db.person.getIndexes(); The results are shown below.
1 [
2 {
3 "v" : 1. 4 "key" : {
5 "_id" : 1
6 },
7 "name" : "_id_". 8 "ns" : "personmap.person"
9 },
10 {
11 "v" : 1.12 "unique" : true.13 "key" : {
14 "name" : 1.0
15 },
16 "name" : "name_1".17 "ns" : "personmap.person"
18 },
19 {
20 "v" : 1.21 "unique" : true.22 "key" : {
23 "name" : 1.0.24 "age" : 1.0
25 },
26 "name" : "name_1_age_1".27 "ns" : "personmap.person"
28 },
29 {
30 "v" : 1.31 "unique" : true.32 "key" : {
33"The mobilephone" : 1.034 },
35 "name" : "mobilephone_1".36 "ns" : "personmap.person".37 "sparse" : true
38 }
39 ]Copy the codeResult of the db.Person.getIndexes () method
As the business changes, you may find that the data or query has changed and the original index is no longer as useful. You can use the dropIndex() method to drop unwanted indexes:
db.person.dropIndex(“name_1”); // drop index name_1.