MongoDB-6: MongoDB Index

First, Introduction

Indexing in MongoDB can improve query efficiency, only need to scan the index only a small portion of this collection, and only to load this small part into memory, the efficiency greatly improved, if not indexed, in the query, MongoDB must perform a full table scan, when the data volume is large, the efficiency difference is obvious, For queries that include a sort operation that does not have an index, the server must load all the documents into memory before returning any results for sorting.

Indexes are special data structures that are stored in an easy-to-traverse collection of data that is a structure for sorting the values of one or more columns in a database table. The ordering of indexed items supports efficient equality matching and range-based query operations.

Starting with MongoDB 3.0, Ensureindex is deprecated and the index is created using CreateIndex.

Syntax for creating indexes:

                

parameter	type	description
keys	document	mongodb supports several different index types, including text, space, and hash indexes. View the index type for more information.
options	document

Second, the basic operation of the index

Let's insert the 10w record first.

 for(varI=0; I<1000000; I++){    . Db. Orders.Insert({"Onumber": I, "date": " -- -- Geneva"," CNAME ":" Zcy "+I, "items":[{"Ino": I, "quantity": I, "Price": 4.0               },{"ino": i+1, "Quantity": I+1, "Price": 6.0 }            ]      })  }  

1. Default Index

Each document stored in the MongoDB collection has a default primary key "_id", and if we do not specify a "_id" value when adding a new document, MongoDB creates a Objectid value and creates an index on the "_id" key automatically. , the name of the default index is "_id_" and cannot be deleted, as seen in the following command:

>

2. View index Information

Returns an array that holds a list of documents that identify and describe an existing index on the collection, and can see if we have an index created on a collection and which indexes are created to facilitate our management.

Grammar:

>db.collection.getIndexes ()  

3. Create a single-column index

We create an index on a single field of a document or an individual field of an embedded document

Grammar:

Boolean: Specifies a value of 1 for a field's ascending index, and a value of 1 for the descending index.

(1) Create

Example:

Db.orders.createIndex ({cname:1})

We created a CNAME index on the Orders collection, and the default index name is "Cname_1"

(2) query the document according to the criteria and see how efficient the query is.

Example:

We query the orders collection based on conditional CNAME for zcy100000 documents

we test the efficiency of having indexed and no indexes in 1 million documents to execute the query. We'll use the explain () function first, and we'll introduce

Let's start with a few parameters here.

1) N: The number of documents returned by the current query.

2) Millis: The time required for the current query, the number of milliseconds.

3) Indexbounds: Index of the current query specific use

Example:

Db.orders.find ({"CNAME": "zcy100000"}). Explain ()  

1) When index is not built, the query condition CNAME is zcy100000 document

Returns a record that takes 1006 milliseconds and is not used to index

2) A document with indexed, query criteria cname zcy100000

Returns a record that takes 82 milliseconds to be used to the CNAME index

Our results are very different, there are built index fields, query efficiency is high, in big data, the difference is more obvious.

(3) query and sort combination use

We query the collection of documents with a CNAME greater than zcy100 and sort the onumber in descending order

Example:

Db.orders.find ({"CNAME": {$gt: "zcy1000"}}). Sort ({"Onumber":1

Execution error occurred:

"$err": "Runner error:overflow sort Stage buffered data usage of 33554456 bytes exceeds internal limit of 33554432 bytes" ,

our memory is only 33554432 bytes, and for a query that includes a sort operation without an index, the server must load all the documents into memory before returning any results to sort.

We create an index on Onumber

Db.orders.createIndex ({onumber:-1

This time we are executing, we can perform normally, we have reduced the cached data of the memory

4. Create a composite index

We can create composite indexes on multiple keys at the same time

Grammar:

Stating that :

Db.collection.createIndex ({a:1,b:1,c:1}})

We create indexes on combinations A, B, and C, and several indexes are used to support queries:

1) A

2) A, b

3) A,b,c

The query criteria in these three are used in the index

(1) Creating a composite index

We also combine indexes on onumber and CNAME

Example:

>db.orders.createIndex ({cname:1, Onumber:-1})  

The index is stored in a data collection that is easy to traverse and read, and the stored data

{_id:, "Onumber": 2, "date": "2015-07-02", "CNAME": "Zcy1"})

{_id:, "Onumber": 1, "date": "2015-07-02", "CNAME": "Zcy1"})

{_id:, "Onumber": 1, "date": "2015-07-02", "CNAME": "Zcy2"})

(2) Enquiry

1) When we use CNAME and onumber as query conditions

Example:

>db.orders.find ({"CNAME": {$gt: "zcy1000"}, "Onumber":

We query conditional CNAME is greater than zcy1000 and Onumber equals 2000 data, we use explain () to query index usage

2) We use only two indexes on one of them as a query

First case: We only use "CNAME" for the condition: {$gt: "zcy1000"} as a query condition

Example:

>db.orders.find ({"CNAME": {$gt: "zcy1000"}}). Explain ()

will use the index, in line with our previous introduction to the A, B, c into the combination of creating an index, support query will use the first of the index.

second case: Our condition only uses "Onumber": 2000 as the query condition

Example:

> db.orders.find ({"Onumber":

Does not use to the index, does not conform to we described earlier we to the A, B, c into the combination to create the index, supports the query to use several.

(3) query and sort combination use

We query the collection of documents with a CNAME greater than zcy100 and sort the onumber in descending order

Example:

    >db.orders.find ({"CNAME": {$gt: "zcy1000"}}). Sort ({"Onumber":1}). Explain ()  

Execution error occurred:

"$err": "Runner error:overflow sort Stage buffered data usage of 33554456 bytes exceeds internal limit of 33554432 bytes" ,

Sort, will not be used to index, do not conform to our previous introduction of a, B, c into the combination of creating indexes, support queries will use several.

Summary: When using a composite index, we use several combinations of the front end of the composite index when querying.

We create indexes on combinations A, B, and C, and several indexes are used to support queries:

1) A

2) A, b

3) A,b,c

5. Indexing of embedded documents

When we create an index on an inline document, we create an index on the base document

Grammar:

Db.collection.createIndex ({Field:boolean}})  

Field Description: With "." To indicate the path to the embedded document

(1) index creation of inline documents in a single column

Example:

    >db.orders.createIndex ({"Items.info":1})  

The info field of the inline items collection under our Orders collection creates an index

We use the Items.info field as the query condition, and using the index case

Example:

Db.orders.find ({"Items.info": {$lt:

We query items.info less than 100 of the data

(2) creation of an indexed combination of inline documents

When we create a composite index on a nested document, we create a composite index with the basic document

Grammar:

>db.collection.createIndex ({Field1:boolean, Field2:boolean}})  

Example:

>db.orders.createIndex ({"Items.info":1, "items. Quantity ":-1

6. Deleting an index

We delete an index that has already been created, can be deleted for an index in a specific collection, or it can be removed from all indexes in a collection

(1) specific index name Delete index

Grammar:

Db.collection.dropIndex (index

Delete the specific index, delete according to the index name, if you do not know the name of the index, you can view the index name by Db.collection.getIndexes ()

Example:

    > db.orders.dropIndex ("Cname_1")  

We delete the index of the CNAME field and now only the Onumber field index is left

(2) Delete all indexes in the collection

Grammar:

Db.collection.dropIndexes ()  

Example:

>

We delete the indexes in the collection, we delete the index of the CNAME field and the Onumber field index, and now we only have the default _id field index, we should be cautious when we use the index, so as not to delete the indexes in the collection.

(3) for the Dropindexes method, we also have a usage that can specify the deletion of the specific index of the collection

Example:

    > db.runcommand ({"dropindexes": "Orders", "index": "Cname_1"})  

We delete the index of the CNAME field and now only the Onumber field index is left

Summarize:

indexing in MongoDB can improve query efficiency, but it is slower in MongoDB new and modified efficiency.

MongoDB-6: MongoDB Index