MongoDB index learning Notes

I. Indexing basics:

MongoDB indexes are almost identical to those of traditional relational databases, which also includes some basic optimization techniques. The following command creates an index:
> Db. test. ensureIndex ({"username": 1 })
You can use the following name to check whether the index has been successfully created:
> Db. test. getIndexes ()
The command to delete an index is:
> Db. test. dropIndex ({"username": 1 })
In MongoDB, we can also create a composite index, for example:
-The number 1 indicates that the index of the username key is stored in ascending order, and the value-1 indicates that the index of the age key is stored in descending order.
> Db. test. ensureIndex ({"username": 1, "age":-1 })
After the index is created, the index will be used for queries based on username and age, or for queries based on username, however, this composite index is not used for only age-based queries. Therefore, to use a composite index, you must include the first N index columns in the composite index in the query conditions. However, if the key-value order in the query condition is inconsistent with the order in which the composite index is created, MongoDB can help us adjust the order so that the composite index can be used for query. For example:
> Db. test. find ({"age": 30, "username": "stephen "})
For the query conditions in the preceding example, MongoDB dynamically adjusts the order of the query condition documents before retrieval, so that the query can use the compound index just created.
We can create indexes for embedded documents. There is no difference between the rules and common documents, for example:
> Db. test. ensureIndex ({"comments. date": 1 })
For the index created above, MongoDB will automatically assign an index name based on the index's keyname and index direction. The following command can specify the index name when creating the index, for example:
> Db. test. ensureIndex ({"username": 1 },{ "name": "testindex "})
As the set grows, you need to index a large number of orders in the query. If sort is not called for the index key, MongoDB needs to extract all data to the memory and sort it. Therefore, if the data volume is too large to be sorted in the memory during index-free sorting, an error will be reported in MongoDB.

II. Unique index:

By default, the created indexes are not unique indexes. The following example creates a unique index, for example:
> Db. test. ensureIndex ({"userid": 1}, {"unique": true })
If a duplicate userid document is inserted again, MongoDB reports an error, prompting you to insert a duplicate key, for example:
> Db. test. insert ({"userid": 5 })
> Db. test. insert ({"userid": 5 })
E11000 duplicate key error index: test. test. $ userid_1 dup key: {: 5.0}
If the inserted document does not contain the userid key, the key value in this document is null. If you insert a similar document multiple times, MongoDB will report the same error, for example:
> Db. test. insert ({"userid1 & Prime;: 5 })
> Db. test. insert ({"userid1 & Prime;: 5 })
E11000 duplicate key error index: test. test. $ userid_1 dup key: {: null}
If duplicate items already exist when creating a unique index, we can use the following command to help us eliminate duplicate documents when creating a unique index. Only the first document found is retained, for example:
-Delete the created unique index first.
> Db. test. dropIndex ({"userid": 1 })
-Insert test data to ensure that duplicate keys exist in the set.
> Db. test. remove ()
> Db. test. insert ({"userid": 5 })
> Db. test. insert ({"userid": 5 })
-Create a unique index and eliminate duplicate data.
> Db. test. ensureIndex ({"userid": 1}, {"unique": true, "dropDups": true })
-The query results confirm that duplicate keys are indeed deleted when the index is created.
> Db. test. find ()
{"_ Id": ObjectId ("4fe823c180144abd15acd52e"), "userid": 5}

We can also create a unique compound index, that is, ensure that the compound key value is unique. For example:
> Db. test. ensureIndex ({"userid": 1, "age": 1 },{ "unique": true })

 

3. Loose indexing

If some rows in your data do not contain a field or the field value is null, if you create a common index on this field, the row without this field or null value will also participate in the index structure, occupying the corresponding space. If we do not want empty rows to participate in our index, we can use loose indexes, A loose index will only involve rows with unspecified fields in index creation. To create a loose index, run the following command:

Db. reviews. ensureIndex ({user_id: 1}, {sparse: true })

IV. Multi-value index

MongoDB can create an index for an array type, such as the following structure. MongoDB can create an index on the tags field:

{Name: "Wheelbarrow ",

}

When an index is generated, three index elements are generated for the three values in tags. The values of tools, gardening, and soil in the index all point to the same row of data. Split into three independent index items.

III. Use explain:

Explain is a very useful tool that can help you obtain a lot of useful information about queries. You can call this method on the cursor to obtain the query details. Explain returns a document instead of the cursor itself. Example: www.111cn.net
> Db. test. find (). explain ()
{
"Cursor": "BasicCursor ",
"Nscanned": 1,
"NscannedObjects": 1,
"N": 1,
"Millis": 0,
"NYields": 0,
"NChunkSkips": 0,
"IsMultiKey": false,
"IndexOnly": false,
"IndexBounds ":{

}
}
Explain returns the index used for the query, the time consumed, and the number of scanned documents.
"Cursor": "BasicCursor" indicates that no index is used.
"Nscanned": 1 indicates the number of documents queried.
"N": 1 indicates the number of returned documents.
"Millis": 0 indicates the time consumed by the entire query.

IV. Index management:

The system. indexes set contains detailed information about each index. Therefore, you can use the following command to query existing indexes, for example:
> Db. system. indexes. find ()
If you create an index for an existing document, you can execute the following command to create an index for MongoDB in the background, so that other operations are not blocked during the creation. However, creating indexes in blocking mode will make the entire creation process more efficient, but MongoDB cannot receive other operations during creation.
> Db. test. ensureIndex ({"username": 1 },{ "background": true })

Index creation command

In fact, there is a more convenient command to create an index, that is, ensureIndex. For example, if we create a joint index for the open and close fields, we can use the following command:

Db. values. ensureIndex ({open: 1, close: 1 })

This command will trigger two indexing processes. One is to sort the corresponding fields, because the indexes are organized by the B + tree and the tree needs to be built, sorting data improves the efficiency of inserting B + trees (the efficiency of the second process). In the log, you can see the output similar to the following:

Tue Jan 4 09:58:17 [conn1] building new index on {open: 1.0, close: 1.0} for stocks. values

1000000/4308303 23%

2000000/4308303 46%

3000000/4308303 69%

4000000/4308303 92%

Tue Jan 4 09:59:13 [conn1] external sort used: 5 files in 55 secs

The second process is to insert sorted data into the index structure to form available indexes:

1200300/4308303 27%

2227900/4308303 51%

2837100/4308303 65%

3278100/4308303 76%

3783300/4308303 87%

4075500/4308303 94%

Tue Jan 4 10:00:16 [conn1] done building bottom layer, going to commit

Tue Jan 4 10:00:16 [conn1] done for 4308303 records 118.942 secs

Tue Jan 4 10:00:16 [conn1] insert stocks. system. indexes 118942 ms

In addition to log output, you can also run the currentOp command on the terminal to obtain information about the current operation thread, as shown in the following example:

> Db. currentOp ()

{

"Inprog ":[

        {

"Opid": 58,

"Active": true,

"LockType": "write ",

"WaitingForLock": false,

"Secs_running": 55,

"Op": "insert ",

"Ns": "stocks. system. indexes ",

"Query ":{

},

"Client": "127.0.0.1: 53421 ",

"Desc": "conn ",

"Msg": "index: (1/3) external sort 3999999/4308303 92%"

     }

    ]

}

The last part is an index building process. Currently, the sorting process is being executed, and the process is up to 92%.

Create an index www.111cn.net in the background

Creating an index adds a write lock to the database. If a dataset is large, the online read/write operations on the database are suspended until the index is created. This affects the normal service of the database. We can add the background: true option when creating the index to let the creation work be executed in the background. At this time, the index creation still requires a write lock, however, this write lock is not directly exclusive to index creation, but will be paused to give way to other read/write operations, without causing serious performance impact. Specific method:

Db. values. ensureIndex ({open: 1, close: 1}, {background: true })

Create an index offline

In any case, the creation of indexes puts a certain amount of pressure on the database, thus affecting online services. If you want to create an index without affecting online services at all, you can remove the nodes in replica sets from the cluster and add the corresponding indexes to the node, after the index is added, add it to the replica sets. This requires only one condition, that is, the index creation time cannot be longer than the time that oplog can save logs. Otherwise, after the creation, the node will no longer be able to catch up with primary after it is launched, resync will be performed.

Index Backup

We know that both mongodump and mongoexport are used to back up data and cannot back up indexes. During restoration, we still need to wait for a long process of index creation. Therefore, if you want to include indexes during backup, you 'd better back up data files.

Index compression

After using indexes for a period of time, operations such as adding, deleting, modifying, and so on will become loose. In this way, we can use the reindex command to re-organize indexes, makes the index space less occupied.