MongoDB Learning Notes (vi) MONGODB index usage and efficiency analysis

The indexes in MongoDB are similar to relational databases, all in order to improve the efficiency of query and sorting, and the principle of implementation is basically consistent. Because the key (field) in the collection can be a normal data type, it can also be a subdocument. MongoDB can create indexes on various types of keys. The following sections explain the creation of various types of indexes, queries, and the maintenance of indexes.

First, create an index

1. Default Index

MongoDB has a default "_id" key, which corresponds to the role of "primary key". When a collection is created, the system automatically creates an index on the "_id" key, which is the default index, and the index named "_id_", cannot be deleted. We can view it in the following ways:

Copy Code code as follows:


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 on the "UserInfo" collection for the "UserName" key, the syntax is as follows: (1 for positive sequence, -1 in reverse order)

Copy Code code as follows: MongoCollection.Metadata.CreateIndex (new Document {{"UserName", 1}}, False);

Next, we use the same method to find the index named "_username_"

Copy Code code as follows:


var _username_index = mongoCollection.Metadata.Indexes.Single (x => x.key = "_username_");


Console.WriteLine (_username_index);

3. Combined Index

In addition, we can also create a combined index on multiple keys at the same time. The following code creates a combined index in reverse order of "UserId", "UserName":

Copy Code code as follows: 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, which creates a single-column index of the user details "Detail" as follows:

Copy Code code as follows: MongoCollection.Metadata.CreateIndex (new Document {{"Detail", 1}}, False);

To create a composite index on the keys of a subdocument: for example, to create a composite index on "detail.address" and "Detail.age":

Copy Code code as follows: MongoCollection.Metadata.CreateIndex (new Document {{"detail.address", 1}, {"Detail.age" ,-1}}, False);

5. Unique index

A unique index restricts the addition of duplicate information when adding a value to the current key. It is important to note that when the specified key is not present in the document, the key value is considered "null", so "null" is also considered to be duplicated, so it is generally preferable to have a key value pair for the key that is typically a unique index.

Create a unique index for "UserId" (this is the last argument is "true"):

Copy Code code as follows: 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 it?

The following provides a way to traverse the entire index, printing all index information:

Copy Code code as follows:


foreach (var index in mongoCollection.Metadata.Indexes)


 {


Console.WriteLine (Index. Value);


}

Examples of output results:

Copy Code code as follows:


{"name": "_id_", "ns": "Mydatabase.userinfo", "key": {"_id": 1}}


{"name": "_userid_unique_", "ns": "Mydatabase.userinfo", "key": {"UserId": 1}, "unique": true, "_id": "4d8f406ab8 A4730b78000005 "}


{"name": "_username_", "ns": "Mydatabase.userinfo", "key": {"UserName": 1}, "unique": false, "_id": "4d8f406ab8a4 730b78000006 "}


{"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": "4d8f406ab8a4730b 78000009 "}

Visible, the index of the collection is also maintained through a collection. Name indicates the index name, NS indicates which library the index belongs to, which is the key of the index, positive sequence or reverse order, unique representation is a unique index, etc. ...

2. Delete Index

Beginners often fall into the misconception that the collection is deleted, the index does not exist. In a relational database, the table is deleted and the index does not exist. There is no way to delete a set in the MongoDB, even if the collection data is clear, the index is still in, to remove the index also need to delete manually.

For example, delete an index named "_username_":

Copy Code code as follows:


MongoCollection.Metadata.DropIndex ("_username_");

The following methods are available for removing all other indexes except the default index:

Copy Code code as follows:


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);


        }


    }


}

Iii. efficiency of the Index

Can the index of MongoDB improve query efficiency? We are here to test it by an example. Compare the query speed of the same data without indexing and indexing.

First, we insert a 10W piece of data in such a way:

Copy Code code as follows:


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 () + "strip Data");


}

Then, implement a method to create the index:

Copy Code code as follows:


public void Createindexfordata ()


{


MongoCollection.Metadata.CreateIndex (new Document {{"Data", 1}}, False);


}

There are also methods of sorting:

Copy Code code as follows: public void Sortfordata ()


{


Mongocollection.findall (). Sort (new Document {{"Data", 1}});


}

Run the test code as follows:

Copy Code 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 ("No 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 ("Indexed Sort Execution Time:" + WATCH2.) Elapsed);





}

Final execution Program View results:



Multiple tests have shown that, in the case of an index, the query is more efficient than without indexing.

Author: Lee (Lipan)
Source: [Lipan] (http://www.cnblogs.com/lipan/)