catalogue
Different types of indexes in MongoDB
4. Get all indexes from the collection
introduce
Welcome to day 3 of the MongoDB tutorial. This is the third part of the MongoDB tutorial series. In this section, we'll look at different aspects of performance. Performance is always an important part of any database. In any database, whether RDBMS or no SQL database, we always consider how to increase the query response time, because the performance of the database is always an important part of the overall performance of the application. Whenever we talk about performance, indexes are prioritized. In this article, we will introduce different types of indexes in MongoDB.
background
Before reading this article, it's best to introduce the first two parts of this article (days 1 and 2).
So far, we have introduced:
- Introduction to no SQL (different types of databases belong to no SQL)
- How to install and set up MongoDB on your machine.
- Introduction to Robomongo (MongoDB open source management tool)
- MongoDb terminology
- How to insert a document in MongoDB
- How to select a document in MongoDB
- Where clause, greater than and less than
- Like, logical AND, logical OR
- In operator in MongoDB, Count and Sort records in MongoDB
- Update, Delete,Remove and Drop in MongoDB
- Top, Distinct and Backup in MongoDB
- Schema less behavior
- $exists, $in, $all,$nin
- Data types in MongoDB
- Embedded document and point representation
Indexes
We can think of an index as a Book Index. Suppose we are searching for a topic in a book and we don't have an index, then we need to scan every page until we reach that page. If your book has 100 pages, you can manage (if you have enough free time) but if your book has 1 million pages, it will be a very boring job to search for topics by flipping through each page. We have the same concept in MongoDB.
If we don't have an index, mongodb will do a complete collection scan to select the documents matching the query statement. If the number of documents in the collection is high, it will lead to performance death.
Suppose we have the following documents in the Names collection:
db.Names.insert({"Name":"Ajay"})
db.Names.insert({"Name":"Manoj"})
db.Names.insert({"Name":"Preeti"})
db.Names.insert({"Name":"Anuj"})
db.Names.insert({"Name":"Tony"})
db.Names.insert({"Name":"Steve"})
db.Names.insert({"Name":"Smith"})
db.Names.insert({"Name":"David"})
db.Names.insert({"Name":"William"})
The documents in the Names collection are stored in any order. If there is no Index and we find a document like the following, it will be a complete collection scan and will reduce performance:
db.Names.find({"Name":"Smith"})If we want to know what MongoDB does to the above query, we just need to use explain() as follows:
Here are a few points to note:
- Cursor: basiccursor: indicates that MongoDB will perform a complete collection scan.
- nscannedObjects: MongoDB scans nine objects to match this query.
So here's the question:
What is an index: an index is a sequential set of items. Index stores values in a specific order.
Default index
When we create a collection in MongoDB, MongoDB will automatically create a collection in MongoDB_ Create a unique index on the id field. Because it is a unique index, it can prevent us from_ Enter a duplicate value in the id field. We cannot delete index in MongoDB.
How to create an index
To create an index in MongoDB, we have two methods:
1. createIndex(): the syntax of the createIndex() method is:
db.Names.createIndex({"Name":1})1 is ascending and - 1 is descending. Therefore, if we want to create an Indexo on the Nam key in the Names collection, we will create an index as follows:
db.Names.createIndex({"Name":1})2. ensureIndex(): the syntax of the ensureIndex() method is:
db.CollectionName.ensureIndex({"key": 1 or -1})ensureIndex() method from 3.0 Deprecated as of version 0. This method is an alias for createIndex().
Different types of indexes in MongoDB
1. Single field index
In addition to the default created by MongoDB_ In addition to the id field index, users can also create ascending or descending indexes on a single} field.
We create a single key index as follows:
db.Names.createIndex({"Name":1})Now, just run explain():
wow Now we define an index (BtreeCursor Name_1) instead of a basic cursor. The most important thing is to look at nscannedObjects, which is 1, which means that MongoDB only scans 1 document, which we mentioned in the query.
Suppose we have another collection named users, which contains the following documents:
db.Users.insert({"Name":"Ajay","Age":30})
db.Users.insert({"Name":"Manoj","Age":60})
db.Users.insert({"Name":"Preeti","Age":20})
db.Users.insert({"Name":"Anuj","Age":70})
db.Users.insert({"Name":"Tony","Age":25})
db.Users.insert({"Name":"Steve","Age":18})
db.Users.insert({"Name":"Smith","Age":33})
db.Users.insert({"Name":"David","Age":53})
db.Users.insert({"Name":"William","Age":65})Now suppose we want to find all documents with an Age greater than 30 and less than 60.
So we have basic cursor, so it will perform a complete table scan and query, and the total number of files scanned is 9. Now I'm defining an index on Age:
db.Users.createIndex({"Age":1})Now run the query again and it will find all documents with an Age greater than 30 and less than 60.
After Index, MongoDB will not scan the whole table, but only scan 4 rows.
2. Composite index
Sometimes we want to search based on both Name and Age. In this case, we will have to apply for two indexes of Name and Age, which will be called composite index.
Syntax: dB CollectionName({"Key1":1 or -1,"Key2": 1 or -1,"KeyN":1 or -1})
We will create an Index about Name and Age in the Users collection, as shown below:
db.Users.createIndex({"Name":1,"Age":-1})Note: composite indexes only work when we search for Name or Name and Age. If we only search for Age, the composite index will not work.
Assuming we are searching for Name, we can see that the composite index is in use.
If we search for Name and Age, we can see that the composite index is in use again.
However, if we only search for Age, we can see that the composite index is not used:
So no Index is used in this case.
3. Multi key index
Delete existing documents from the Users collection and insert some documents into the Users collection using interest, as follows:
db.Users.remove({})
db.Users.insert({"Name":"Ajay","Age":30,Interest : ["cricket","music"] })
db.Users.insert({"Name":"Manoj","Age":60,Interest : ["cricket","driving"]})
db.Users.insert({"Name":"Preeti","Age":20,Interest : ["music","driving"]})
db.Users.insert({"Name":"Anuj","Age":70,Interest : ["cooking","music"]})
db.Users.insert({"Name":"Tony","Age":25,Interest : ["swimming","cooking"]})
db.Users.insert({"Name":"Steve","Age":18,Interest : ["dancing","music"]})
db.Users.insert({"Name":"Smith","Age":33,Interest : ["tennis","tv"]})
db.Users.insert({"Name":"David","Age":53,Interest : ["music","swimming"]})
db.Users.insert({"Name":"William","Age":65,Interest : ["dancing","swimming"]})Now, if we want to Index the contents of an array (Interest in my example), we will use the Multikey Index
Syntax: dB CollectionName. createIndex({"Array": 1 or -1})
We will create a MultiKey Index with the following interests:
db.Users.createIndex({Interest : 1})4. Text index
If we are performing a text search, we can apply Text Index to the string content for better performance.
We can only create text indexes on string fields.
Syntax: dB CollectionName. createIndex({Field Name:"text"})
Suppose we want to create an IndexonName field, we will create a text index as follows:
db.Users.createIndex({Name : "text"})Note: a collection can have at most one text index.
In addition to these indexes, MongoDB supports more indexes, including Geospatial and Hashed indexes.
Geospatial index is used to better query geospatial coordinate data, and Hashed Index is the hash value of index field value.
MongoDB index properties
1. Unique
The unique attribute on the index field allows MongoDB not to accept duplicate values of index fields. In other words, the unique attribute will limit duplicate insert values for the index field.
I add another column named "SSN" in the Users collection and a Unique attribute on the "SSN" field, as follows:
db.Users.drop()
db.Users.createIndex({SSN:1},{unique:true})
db.Users.insert({"Name":"Ajay","Age":30,
Interest : ["cricket","music"] ,"SSN" : "12345"})
db.Users.insert({"Name":"Manoj","Age":60,
Interest : ["cricket","driving"],"SSN" : "54321"})I deleted all records from the Users collection and created an index with unique attributes on the SSN field. Therefore, if I try to insert duplicate values in the SSN, an error occurs. Let's try:
db.Users.insert({"Name":"Preeti","Age":20,
Interest : ["music","driving"],"SSN" : "54321"})The error is:
insertDocument :: caused by :: 11000 E11000 duplicate key error index:
Test.Users.$SSN_1 dup key: { : "54321" }Therefore, we cannot insert duplicate values in the index field with the unique attribute.
2. Sparse
I am deleting the Users collection and will insert some documents as follows:
db.Users.drop()
db.Users.insert({"Name":"Ajay","Age":30,
Interest : ["cricket","music"] ,"SSN" : "12345"})
db.Users.insert({"Name":"Manoj","Age":60,
Interest : ["cricket","driving"],"SSN" : "54321"})
db.Users.insert({"Name":"Preeti","Age":20,
Interest : ["music","driving"]})
db.Users.insert({"Name":"Anuj","Age":70,
Interest : ["cooking","music"]})Now, what happens if I try to create an index on the SSN field using the unique attribute. If I try to create an index with unique attribute, as shown below, I will receive an error because the SSN contains null and the last two documents, so the SSN is not unique.
db.Users.createIndex({SSN:1},{unique:true})The error is:
E11000 duplicate key error index: Test.Users.$SSN_1 dup key: { : null }So what is the solution? Can't I create a unique index on such records?
Wait... We have a solution... We have the spark attribute for this situation.
Spark will tell the database that these files should not be included in the index without SSN.
That's cool. It's time to create an index with sparse and unique attributes.
db.Users.createIndex({SSN:1},{unique:true,sparse:true})This time, there will be no error when the index is created using the unique attribute, because spark is there.
3. Partial index
This is a new concept attached to MongoDB 3.2. Sometimes we want to create some specific conditions. If we create an Index with some conditions, it is a partial Index
Suppose I just want to create an Index in the Name field when Age is greater than 30. We need to specify a condition when creating an Index, as shown below:
db.Users.createIndex(
{ Name: 1},
{ partialFilterExpression: { Age: { $gt: 30 } } }
)To apply a condition, we use partialFilterExpression.
4. TTL index
MongoDB has a special type of single field named TTL Index. MongoDB uses this type of Index to automatically delete documents after a period of time. We use the expire after seconds option to provide the expiration time.
I am creating a TTL index on Age with an expiration time of 60 seconds, as shown below:
db.Users.createIndex( { "Age": 1 }, { expireAfterSeconds: 60 } ) It will automatically delete this document after 60 seconds. The background task runs every 60 seconds to delete all expired documents. Therefore, deleting this document from the collection may take some additional time. It also depends on the workload of the mongod instance, so expired documents can be collected after a specified time.
Some key points
1. getIndexes()
If we want to see all the indexes created on a collection, we use the getIndexes() method.
Syntax: dB CollectionName. getIndexes()
2. dropIndex()
To delete the Index, we use the dropIndex() method.
Syntax: dB CollectionName. dropIndex({"Key":1 or -1})
Pass the key with 1 or - 1, which we passed when we created the index.
3. dropIndexes()
To remove all indexes from the collection, we use the dropIndexes() method.
db.CollectionName.dropIndexes()
4. Get all indexes from the collection
db.getCollectionNames().forEach(function(collection) {
index = db[collection].getIndexes();
print("Indexes for " + collection + ":");
printjson(index);
});5. Re index
To rebuild an Index, we use reIndex() as follows:
db.CollectioName.reIndex()
limit
- A single collection cannot have more than 64 indexes.
- Composite index cannot exceed 31 fields.
reference resources
https://www.codeproject.com/Articles/1091645/MongoDB-Tutorial-Day-Performance-Indexing