MongoDB Learning (vi) index

Preparation: Insert 1 million data first

 for (i=0;i<=1000000;i++) {    Db.users.insert ({           "i": I,           "username": "user" +I,           "Age": Math.floor (Math.random () *120),           "created":new  Date ()    })}        

1. Create an index: the larger the amount of data, the longer it takes to create the index

Db.users.ensureIndex ({"username": 1})

Db.users.find ({"username": "user234455"}). Explain ()

Fast query Speed ... O (∩_∩) o~ the cost of using an index: Adding an index, each insert, UPDATE, delete operation takes more time, because the data changes when MongoDB updates the document and index every time. You should not normally have more than two indexes on a collection. Index on a commonly used key. 2. Composite index: The index that is built on multiple keys (the index is very fast when sorting, as long as the index key is used first)

Db.users.ensureIndex ({"username": 1, "Age": 1})//Each index contains the age and Name fields

Three ways to use: (MongoDB can traverse an index in any direction)

(1) Point query, this most efficient

Db.users.find ({"Age": +}). Sort ({"username":-1})

(2) Multipoint query: The result of using an index query is unordered, MongoDB needs to be reordered in memory, and then returned. Problem: The result set document number is large, the query speed is slow; The result set exceeds 32M,MONGODB error

Db.users.find ({"Age": {"$gte": +, "$lte": +}}). Sort ({"username": 1})

(3) Use another index {"username": 1, "Age": 1} You can use hint to force an index

Db.users.find ({"Age": {"$gte": $, "$lte": +}}). Sort ({"Name": 1}). Explain ()
Db.users.find ({"Age": {"$gte": +, "$lte": +}}). Sort ({"Name": 1}). Hint ({"name": 1, "Age": 1}). Explain ()

The index is essentially: the tree, the smallest value on the leftmost, and the largest value at the far right.

(4) Orientation becomes important only when sorting based on multiple keys.

(5) Overlay index: The field that you want to find is already included in the index;

(6) Composite index: The different indexes are represented by different queries: {"Age": 1, "username": 1}, "free" gets all indexes beginning with the key "

(7) $where and check if a key is not available to use the index; Fields that do not exist in the index are stored in the same way as null fields.

(8) Query: The exact matching field is placed in front, the range matching field is placed at the end;

(9) $or: You can use the index for each clause, because actually $OR executes two queries and then merges the result set, which is inefficient and uses as much as possible $in

$or need to check the result set of the query every time and remove the duplicate documents.

(10) Index nested documents:

Db.users.ensureIndex ({"loc.city":1})

(11) Index of an array of resumes: Indexing an array is actually an index entry for each element in the array, not an index on the set itself, and an index with a maximum of one field.
(12) Index cardinality: That is, each field in the collection has a different worth. The index should be indexed on the higher cardinality field.

Explain () can provide a lot of information about the query, if Scanandorder is true, then the index is not used, MongoDB needs to be sorted in memory, which is slow

Hint () forcibly use an index

Db.users.find ({"age":-}). Hint ({"Age":1 })

3. When the index should not be used: the greater the proportion of result sets in the original collection, the slower the index speed. In general: Queries need to return result set 30% to compare the speed of full table scans and index lookups ...
Index Lookup: The index needs to make two lookups, one is to find the index entry, and one time to find the corresponding document according to the index entry; Force full table scan with {"$natural": 1}

Db.users.find ({"Age":-}). Hint ({"$natural": 1})

4. Index Type:
(1) Unique index: (null is considered a value)

Db.users.ensureIndex ({"Usename": 1},{"unique":true})

Composite UNIQUE index: The combined value of all keys must be unique.

To remove a duplicate index:

Db.users.ensureIndex ({"Usename": 1},{"unique":true, "dropdups":True})

(2) Sparse index: A sparse index can make a unique if a field that is willing to exist or may not exist when it exists, it must be unique.

Db.users.ensureIndex ({"Email": 1},{"sparse":true})

5. Index management: All indexes of the database are stored in the System.indexes collection; Index order is important: {' age ': 1, ' username ': 1},{' username ': 1, ' Age ': 1} is a different index

// uses all the indexes can be viewed here

(1) Index ID: The index name is unique. You can customize the index

Db.users.ensureIndex ({"Usename": 1},{"name": "Myindex"})

(2) Delete index: MongoDB blocks all read and write requests to the data when the index is created. All the time until the creation is complete.

Db.users.drop ("Username_1")

6. Fixed set ...