MongoDB study notes (6) MongoDB index usage and efficiency analysis

1. Create an index

1. Default index

MongoDB has a default “_id” key, which is equivalent to the role of “primary key”. After the collection is created, the system will automatically create an index on the “_id” key, which is the default index, and the index name is “_id_”, which cannot be deleted. We can check it with:

var _idIndex = mongoCollection.Metadata.Indexes.Single(x => x.Key == “_id_”);
Console.WriteLine(_idIndex);
2. Single column index

The index created on a single key is a single-column index. For example, we want to create a single-column index for the “UserName” key on the “UserInfo” collection. The syntax is as follows: (1 indicates positive order, -1 reverse order)

mongoCollection.Metadata.CreateIndex(new Document { { “UserName”, 1 } }, false);
Next, we use the same method to find the index named “_UserName_”

var _UserName_Index = mongoCollection.Metadata.Indexes.Single(x => x.Key == “_UserName_”);
Console.WriteLine(_UserName_Index);
3. Composite index

In addition, we can also create composite indexes for multiple keys at the same time. The following code creates a combined index according to the positive order of “UserId” and the reverse order of “UserName”:

mongoCollection.Metadata.CreateIndex(new Document { { “UserId”, 1 }, { “UserName”, -1 } }, false);
4. Sub-document index

We can create various indexes on the key of the document type, such as a single-column index, as follows to create a single-column index for user details “Detail”:

mongoCollection.Metadata.CreateIndex(new Document { { “Detail”, 1 } }, false);
Create a composite index on the keys of the subdocuments: For example, create a composite index on “Detail.Address” and “Detail.Age”:

mongoCollection.Metadata.CreateIndex(new Document { { “Detail.Address”, 1 }, { “Detail.Age”, -1 } }, false);
5. Unique index

The unique index restricts that when adding a value to the current key, duplicate information cannot be added. It is worth noting that when the specified key does not exist in the document, the key value will be considered as “null”, so “null” will also be considered as a duplicate, so it is generally used as the key of the unique index, and it is best to have a key value pairs.

Create a unique index on “UserId” (at this time the last parameter is “true”):

mongoCollection.Metadata.CreateIndex(new Document { { “UserId”, 1 } }, true);
Second, maintain the index

1. Query index

The method of querying by index name has been introduced. But sometimes, we may forget the index name, how to query?

The following provides a method to traverse all indexes and print all index information:

foreach (var index in mongoCollection. Metadata. Indexes)
{
Console.WriteLine(index.Value);
}
Example of the output result:

{ “name”: “_id_”, “ns”: “myDatabase.UserInfo”, “key”: { “_id”: 1 } }
{ “name”: “_UserId_unique_”, “ns”: “myDatabase.UserInfo”, “key”: { “UserId”: 1 }, “unique”: true, “_id”: “4d8f406ab8a4730b78000005” }
{ “name”: “_UserName_”, “ns”: “myDatabase.UserInfo”, “key”: { “UserName”: 1 }, “unique”: false, “_id”: “4d8f406ab8a4730b78000006” }
{ “name”: “_Detail.Address_Detail.Age_”, “ns”: “myDatabase.UserInfo”, “key”: { “Detail.Address”: 1, “Detail.Age”: -1 }, “unique”: false, “_id”: “4d8f406ab8a4730b78000007” }
{ “name”: “_UserId_UserName_”, “ns”: “myDatabase.UserInfo”, “key”: { “UserId”: 1, “UserName”: -1 }, “unique”: false, “_id”: “4d8f406ab8a4730b78000008 ” }
{ “name”: “_Detail_”, “ns”: “myDatabase.UserInfo”, “key”: { “Detail”: 1 }, “unique”: false, “_id”: “4d8f406ab8a4730b78000009” }
It can be seen that the index of the collection is also maintained through a collection. name indicates the index name, ns indicates which library and collection the index belongs to, key indicates which key the index is on, whether it is in positive or reverse order, unique indicates whether it is a unique index, etc…

2. Delete the index

The misunderstanding that novices often fall into is that the index does not exist when the collection is deleted. In a relational database, if the table is deleted, the index will not exist. There is no such thing as deleting a collection in MongoDB. Even if the collection data is cleared, the index is still there. To remove the index, it needs to be deleted manually.

For example, to drop the index named “_UserName_”:

mongoCollection.Metadata.DropIndex(“_UserName_”);
The following provides methods to delete all indexes except the default index:

public void DropAllIndex()
{
var listIndexes = mongoCollection.Metadata.Indexes.ToList();
for (int i = 0; i <listIndexes.Count; i++)
{
If (listIndexes[i].Key != “_id_”)
{
    mongoCollection.Metadata.DropIndex(listIndexes[i].Key);
  }
}
}
3. Index efficiency

Can MongoDB’s index improve query efficiency? Let’s test it with an example here. Compare the query speed of the same data without and with indexes.

First, we insert 10W pieces of data through such a method:

public void InsertBigData()
{
var random = new Random();
for (int i = 1; i <100000; i++)
{
Document doc = new Document();

doc[“ID”] = i;
Doc[“Data”] = “data” + random.Next(100000);

mongoCollection.Save(doc);
}

Console.WriteLine(“Currently has” + mongoCollection.FindAll().Documents.Count() + “pieces of data”);
}
Then, implement a method to create an index:

public void CreateIndexForData()
{
mongoCollection.Metadata.CreateIndex(new Document { { “Data”, 1 } }, false);
}
There is also a sorting method:

public void SortForData()
{
mongoCollection.FindAll().Sort(new Document { { “Data”, 1 } });
}
Run the test code as follows:

static void Main(string[] args)
{
IndexBLL indexBll = new IndexBLL();
indexBll.DropAllIndex();
indexBll. DeleteAll();
indexBll.InsertBigData();

Stopwatch watch1 = new Stopwatch();
watch1.Start();
for (int i = 0; i <1; i++) indexBll.SortForData();
Console.WriteLine(“Non-index sort execution time: ” + watch1.Elapsed);

indexBll.CreateIndexForData();

Stopwatch watch2 = new Stopwatch();
watch2.Start();
for (int i = 0; i <1; i++) indexBll.SortForData();
Console.WriteLine(“Sorting execution time with index: ” + watch2.Elapsed);

}
Finally, execute the program to view the results:

the

Multiple tests have shown that with indexes, the query efficiency is higher than without indexes.