MONGODB3.0 index management Learning and finishing __mongodb

One, index type (a), one -touch index

The index created on one key is the one-touch index, and the single key index is the most common index, such as the _id index created by default by MongoDB. (ii), composite index

Indexes built on multiple keys are composite indexes (iii), multiple indexes

If you create an index on a field that is an array of values, MongoDB will decide whether to build the index into a multiple-key index (d), geo-spatial index

MongoDB supports several types of geo-spatial indexes. The most commonly used are 2dsphere indexes (maps for the Earth's surface type) and 2d indexes (for planar maps and time continuous data) (v), full-text indexing

Full-text indexing is used to search for text in documents, and we can use regular expressions to query strings, but regular expression searches can be very slow when text blocks are larger, and cannot handle problems with language comprehension (such as entry and entries should be matched). With full-text indexing, text searches can be done very quickly, just as with the built-in support for multiple language segmentation mechanisms. The cost of creating an index is large, and full-text indexing is more expensive. When creating an index, you need to create (vi), hash index , background or offline

A hash index can support an equality query, but a hash index does not support a range query. You may not be able to create a composite index with a hash key or impose uniqueness restrictions on the hash index. However, you can create a hash index and an ascending/descending (for example, not hash) index on the same key, so that MongoDB indexes are automatically used for range queriesSecond, indexed properties (i), unique indexA unique index can refuse to save documents that have been duplicated by the value of the index key. By default, the unique property of the MongoDB index is false. If you impose uniqueness restrictions on a composite index, then MongoDB enforces the uniqueness of the compound value rather than requiring unique values for each individual value.uniqueness is limited to different documents in a collection. In other words, a unique index prevents the same value from being stored on the indexed keys of different documents, but it does not prevent the same document from being stored in an array of indexed keys or the same value as the embedded document. When duplicate data is stored in the same document, duplicate values are only indexed once. If a document does not contain an indexed key with a unique index, the index stores a null value by default for that document. Because of the uniqueness limit, MongoDB will only allow one article that can not contain the indexed key. If more than one document does not contain an indexed key or no value, then a key duplicate (duplicate key) error is thrown, causing the index creation to fail. You can combine features that use uniqueness and sparse indexes to filter documents that contain null values to avoid this error.(ii), sparse index (Sparse Indexes)A sparse index skips all documents that do not contain the key being indexed. This index is called "sparse" because it does not include all the documents in the collection. By contrast, a non sparse index indexes each document and stores a null value for a document if it does not contain the indexed key. If an index causes the query or the sorted result set to be incomplete, then MongoDB will not use the index unless the user uses the hint () method to display the specified index. For example, the query {x: {$exists: false}} will not use the sparse index on the X key unless the hint is displayed. 2dsphere (version 2), 2d and text These indexes are always sparse. A composite index with at least one index key in one document, sparse and containing only the increment/decrement index key, indexes the document. For composite indexes that are sparse and contain geographical index keys (such as 2dsphere, 2d), and ascending/Descending index keys, only the presence or absence of a geographical index key determines whether a document is indexed. As for composite indexes that are sparse and contain Full-text index keys and other ascending/descending index keys, only the presence or absence of a Full-text index key determines whether the document is indexed. A sparse and unique index that prevents the document in the collection from being duplicated in the index key and also allows multiple documents to not contain the indexed key

Note: 3.2.0 version recommended to use partial Indexes (iii), local index (Partial Indexes) This is the 3.2.0 version of the new index that will only be indexed to documents that collections meet the criteria partialfilterexpression. Reduced storage and indexing and maintenance cost reduction to some extent (iv), TTL index TTL collection supports expiration time settings, when the collection automatically clears the timed out document after a specified amount of time, which is useful for saving information such as session sessions. or store cached data for use. but deleting a field that has a delayed index must be a Bson type of date you cannot create a TTL index if the field to be indexed is already in use in another index. Otherwise, the document will not be automatically purged after timeout. Indexes cannot contain more than one field three, Index property option configuration (i), Options global option (for all indexes)

parameter name	type	description
Background	Boolean	The indexing process blocks other database operations, and background can specify that the index be created later, adding "background" optional parameters. The default value of ' background ' is false.
Unique	Boolean	Is the unique index established. Specifies true to create a unique index. The default value is false.
Name	String	The name of the index. If unspecified, MongoDB generates an index name through the field name and sort order of the connection index. cannot exceed 128 characters
Partialfilterexpression	Document	Setting indexes only works on documents that meet the criteria specifically see official website on partial Indexes
Sparse	Boolean	Do not enable indexing of field data that does not exist in the document; This parameter requires special attention, and if set to true, the document that does not contain the corresponding field is not queried in the indexed field ... The default value is false.
Expireafterseconds	Integer	Specify a value in seconds, complete the TTL setting, and set the lifetime of the collection.
Storageengine	Document	Allows users to specify the configuration of each index when creating an index

(ii), Full-text (text) indexing options

Name of parameter	type	Description
Weights	Document	Set the weight of the text index field, the weight value 1-99,999
Default_language	Set the language of the text participle, default to 中文版, other support languages
Language_override	String	Use the value of one field in the document as the language for setting the text participle, default to language, example
Textindexversion	Integer	Version number, can be 1 or 2

Note: Other indexed properties See official website four, view index

syntax: db.collection.getIndexes ()

Example:
db.index_test.getIndexes ()
output:
[
    {
        "V": 1,
        "key": {
            "_id": 1
        },
        " Name ': ' _id_ ',
        ' ns ': ' Usercenter_test.index_test '
    }
]

you can see that the _id field is indexed by default . v. Establishment of an index

Use Db.collection.createIndex () instead of Db.collection.ensureIndex () after version 3.0

syntax: Db.collection.createIndex (keys, options)

Parameter description:
1. Keys: {field name 1:ascending,... Field name N:ascending}: Ascending set to 1 identity index Ascending,-1 descending
2. Options: Set indexing options, such as setting the name, setting a unique index

Preparing Data

Db.index_test.insert ({"Name": "2", "age":%, "Sex": 1})
Db.index_test.insert ({"Name": "3", "Age":, "Sex": 0})
Db.index_test.insert ({"Name": "4", "Age": M, "Sex": 2})
Db.index_test.insert ({"Name": "5", "Age":), "Sex": 5 })
Db.index_test.insert ({"Name": "6", "Age": [, "Sex": 0})
Db.index_test.insert ({"Name": "7", "Age":), "Sex" : 1})
Db.index_test.insert ({"Name": "8", "Age": Button, "Sex": 1})
Db.index_test.insert ({"Name": "9", "Age": 7, "sex ": 2}"
Db.index_test.insert ({"Name": "L0", "Age": "Sex": 1})
Db.index_test.insert ({"Name": "L", "age": 23, "Sex": 1})

(i), create a normal index on a single key

Syntax: Db.collections.createIndex ({field name: 1 or -1},{options})

Example:
db.index_test.createIndex ({"Age": 1})

can see that the returned Numindexesafter is larger than numindexesbefore after successful execution

1. The effect of single-key index and Query field setting on query performance

Example 1: The query field does not contain an indexed field

Db.index_test.find ({"Sex": 1}). Explain ("Executionstats")

You can see that its winningplan.stage=collscan is a full table scan

Example 2: Query fields contain both index and non-indexed fields

Db.index_test.find ({"Age": {"$gte": Ten}, "Sex": 1}). Explain ("Executionstats")

Although Winningplan.stage=fetch and WinningPlan.inputStage.stage =ixscan, But its totalkeysexamined and totaldocsexamined are larger than nreturned, indicating that some unnecessary scans were carried out at the time of the query.

Example 3: Query fields contain only indexed fields

Db.index_test.find ({"Age": {"$gte":}}). Explain ("Executionstats")

You can see in the returned

Winningplan.stage=fetch (retrieves the specified document according to the index)
WinningPlan.inputStage.stage =ixscan (Index Scan)
Executionstats.nreturned=totalkeysexamined=totaldocsexamined=9 indicates that the query uses a query for the specified document based on the index
(nreturned: The entry returned by the query, totalkeysexamined: Index Scan entry, totaldocsexamined: Document Scan Entry)

Summary: When setting query fields, you should try to set up only the fields that have been indexed 2. The effect of the setting of the sort field on query performance when indexing and querying

Example 1: the sort field does not contain an indexed field

Db.index_test.find ({"Age":}). sort ({"Sex": -1}). Explain ()

Returns the Winningplan.stage=sort in the query that needs to be sorted in memory and then returned

Example 2: Sort fields that contain both indexed and non indexed fields

Db.index_test.find ({"Age":}). Sort ({"Age": 1, "Sex": 1}). Explain ()

The result is the same as above

Example 3: The Sort field contains only one single index field

Db.index_test.find ({"Age":}). Sort ({' Age ': 1}). Explain ()

You can see Winningplan.stage changed to fetch (using the index)

Example 4: The Sort field contains multiple individual index fields

Db.index_test.find ({}). sort ({"Sex": 1, "Age": 1}). Explain (' executionstats ')
db.index_test.find ({}). Sort ({"Age ": 1," Sex ": 1). Explain (" Executionstats ")

You can see that this situation is winningplan.stage to sort, where the index does not work in the sort, which means that a single index has no effect in sorting multiple fields.

Summary: sort operations can ensure the order of results by obtaining documents in indexed order from the index. If the Query Planner (planner) cannot get a sort order from the index, it will need to sort (winningplan.stage=sort) the results in memory. You need to use a composite index for sorting on multiple fields (ii), creating a composite index

Syntax: Db.collections.createIndex ({"Field name 1": 1 or-1,..., "field name N": 1 or -1},{options})

Example:
db.index_test.dropIndexes ()///First delete the previously created index
db.index_test.createIndex ({"Age": 1, "Sex": 1})// Create a composite index on age and sex

After a successful creation, you can see from db.index_test.getIndexes () that the composite index you created is actually just an index, and its key is {"Age": 1, "Sex": 1}; instead of multiple.) 1. The effect of compound index and query field setting on query performance

Example 1: The query field contains only some of the fields in the Create a composite index field

Db.index_test.dropIndexes ()///First delete the previously created index
db.index_test.createIndex ({"Age": 1, "Sex": 1})/////create composite index on age and sex

And then execute each

Db.index_test.find ({"Age": 1}). Explain ()
db.index_test.find ({"Sex": 1}). Explain ()

You can see the first sentence executing the returned winningplan.stage=fetch; and winningplan.inputstage.stage=ixscan;indexname=age_-1_sex_1

And the second sentence executes the returned Wininigplan.stage=collscan

This seems to indicate that when a query condition is just a partial field in a composite index key, the index only works for the first field specified at creation time. Next we remove the original index and then create a composite index on the Age,name,sex three fields to see.

Db.index_test.dropIndexes ()
Db.index_test.createIndex ({"Age":-1, "name": 1, "Sex": 1})
Db.index_test.find ( {"Age": "1"}). Explain ()//1th query
db.index_test.find ({"Sex": "1"}). Explain ()//2nd query
Db.index_test.find ({"Age": "1", "Sex" : 1}). Explain ()//3rd query
db.index_test.find ({"Name": "1", "Sex": 1}). Explain ()//4th query

You can see that the 1th and 3rd queries return Winningplan.stage=fetch, while the 2nd and 4th queries return the Winningplan.stage=collscan

Summary:

When a query condition is only part of a composite index key, if you want the composite index to be optimized, you must include the 1th field that you specified when you created the composite index, such as the field "age" in the above column, "Age" 2. Effect of composite index and Sort field setting on query performance

Example 1: The Sort field contains only some of the fields in the Create a composite index field

Db.index_test.find (). Sort ({"Age": -1}). Explain ()
db.index_test.find (). Sort ({' Age ': 1}). Explain ()
Db.index_test.find (). Sort ({"Name": 1}). Explain ()
db.index_test.find (). Sort ({"Sex": 1}). Explain ()
Db.index_test.find (). Sort ({"Age":-1, "Name": 1}). Explain ()
db.index_test.find (). Sort ({"Name": 1, "Sex": 1}). Explain ()
db.index_test.find (). Sort ({"Age":-1, "Sex": 1}). Explain ()

Above only the winningplan.stage= FETCH returned by the 1,2,5; the other is =sort.
This means that the sort fields only contain the order of the sort keys in the same order as they are in the index field when you create some of the fields in the compound index fields, and they cannot jump (that is, the first field must have and cannot skip the middle field)

Next look at the following query

Db.index_test.find (). Sort ({"Age": 1, ' name ': -1} '). Explain ()
db.index_test.find (). Sort ({"Age":-1, "name":-1}). Explain ()

1th returns the winningplan.stage= FETCH; 2nd winningplan.stage= sort; This indicates that the sort order of all keys specified in sort (for example, increment/decrement) must be identical to the sort order of the corresponding keys in the index, or Quite the opposite.

Summary

You can specify that all keys or partial keys on the index are sorted. However, the order of the sort keys must match the order in which they are arranged in the index

The sort order (for example, increment/decrement) of all keys specified in sort must be identical to the sort order of the corresponding keys in the index, or exactly the opposite (three), creating a unique index 1. A unique index on a single field

Syntax: Db.collections.createIndex ({field name: 1 or -1},{"unique": true})
Example 1:

Db.index_test.createIndex ({"Name": 1},{"unique": true})
Db.index_test.createIndex ({"Sex": 1},{"unique": true})

You see the 1th execution succeeds, and the 2nd fails because the existing data has duplicate data on the sex field.

Example 2:

Db.index_test.insert ({"Name": "2", "Age": 123, "Sex": 9})
Db.index_test.insert ({"Name": "+", "Age": 123, "Sex": 9})

You can see that article 1th failed, and 2nd succeeded. Because a unique index prevents data that is written to be duplicated on a unique index field

Summary 1:

The unique index prevents the application of documents that insert the value on the indexed key as a duplicate value
Cannot create a unique index on a field that already has duplicate data 2. Building a unique index on multiple fields

Syntax: Db.collections.createIndex ({"Field name": 1 or-1,... "field name N": 1 or -1},{"unique": true})

Db.index_test.createIndex ({"Name": 1, "Age": 1},{"unique": true}
Db.index_test.insert ({"Name": "A", "age": 123, " Sex ": 1}"
Db.index_test.insert ({"Name": "?", "Age": 123, "Sex": 1})
Db.index_test.insert ({"name": "=", "Age" : 123, "Sex": 2})

You can see that both 2nd and 3rd are successful, but the last 1 are failures. This means that building a unique index on multiple fields forces the uniqueness of the composite key value, not the uniqueness of each key.

Summary: A unique index prevents documents that have values that are duplicates from being inserted on the indexed key. Establishing a unique index on a field that has duplicate data a unique index on multiple fields enforces uniqueness of composite key values, not uniqueness of each key (iv), To create a sparse index

Syntax: Db.collection.createIndex ({field name: 1},{"sparse": true}) 1. Relationship between sparse index and $exists

Create a sparse index on one field, and the index skips all documents that do not contain the key (field) of the index

The sparse index is not used when executing query {indexed key: {$exists: false}}, unless the specified hint ({indexed key: 1}) is displayed

Example:
db.scores.insert ({"userid": "Lxh"})
Db.scores.insert ({"userid": "Pen", "Score":})
Db.scores.insert ({"userid": "Xiao", "Score": ")
////////Create sparse index on field score
Db.scores.createIndex ({score:1}, { Sparse:true})
Db.scores.find ({score:{$exists: false}}). Explain ()
Db.scores.find ({score:{$exists: false}} ). Hint ({score:1}). Explain ()
Db.scores.find ({score:{$exists: true}}). Explain ()

From the example, we can see that the 5th article returns the Winningplan.stage=collscan (indicating the scan whole table) when executing the $exists:false query;

5th when a exists:false query is executed, the hint is specified and the Winningplan.stage=fetch returned (using an index scan) is displayed;

The last one executes the exists:true query, does not display the specified hint, and the Winningplan.stage=fetch it returns (using an index scan);

Summary:

When you execute a exists:false query on a sparse indexed field, you need to display the specified hint for the index to work, and you do not need to execute the exists:true query.

Create a normal index on a field, and if the document does not contain the field, the index value is set to NULL, and the sparse index skips the document; that is, the sparse index is less than the normal index when you scan the collection with the index. (v) Creation of partial Index

Syntax: Db.collection.createIndex ({"Field name": 1 or-1,... "field name N": 1 or -1},{"partialfilterexpression": {partialfilterexpression}})

partialfilterexpression expressions are as follows

Equality expressions (i.e. field:value or using the $EQ operator)
$exists: True expression,
$gt, $gte, $lt, $lte expressions,
$type expressions,
$and operator at the top-level only

Example:
db.restaurants.createIndex (
   {cuisine:1, name:1},
   {partialfilterexpression: {rating: {$gt: 5}}}
  )

(Note: Geo-spatial index, FULL-TEXT Index, TTL index, hash index I am rarely used in my work, there is no description here)