Shulou(Shulou.com)05/31 Report--

This article shows you how to use the index in the MongoDB database, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

What are the uses of various indexes of MongoDB

1. Single column index

Create an index on field x, 1 (ascending) or-1 (descending)

> db.data.ensureIndex ({XRV 1})

Show all indexes in the table data

> db.data.getIndexes ()

[

{

"name": "_ id_"

"ns": "recommender.data"

"key": {

"_ id": 1

}

}

{

"_ id": ObjectId ("4befb146b0e29ba1ce20e0bb")

"ns": "recommender.data"

"key": {

"x": 1

}

"name": "Xero1"

}

]

Find the value of field x 6, where the index is already used

> db.data.find ({XRV 6})

{"_ id": ObjectId ("4bee804ba23d558eb6687117"), "x": 6, "name": "caihuafeng1"}

{"_ id": ObjectId ("4bee804ba23d558eb6687118"), "x": 6, "name": "caihuafeng2"}

{"_ id": ObjectId ("4bee804ba23d558eb6687119"), "x": 6, "name": "caihuafeng3"}

{"_ id": ObjectId ("4bee804ba23d558eb668711a"), "x": 6, "name": "caihuafeng4"}

{"_ id": ObjectId ("4bee804ba23d558eb668711b"), "x": 6, "name": "caihuafeng5"}

{"_ id": ObjectId ("4bee804ba23d558eb668711c"), "x": 6, "name": "caihuafeng6"}

{"_ id": ObjectId ("4bee804ba23d558eb668711d"), "x": 6, "name": "caihuafeng7"}

{"_ id": ObjectId ("4bee804ba23d558eb668711e"), "x": 6, "name": "caihuafeng8"}

{"_ id": ObjectId ("4bee804ba23d558eb668711f"), "x": 6, "name": "caihuafeng9"}

{"_ id": ObjectId ("4bee804ba23d558eb6687120"), "x": 6, "name": "caihuafeng10"}

two。 Default index

There are two indexes shown in db.data.getIndexes () above, of which _ id is the index created automatically when the table is created, and this index cannot be deleted.

An index is always created on _ id. This index is special and cannot be deleted. The _ id index enforces uniqueness for its keys.

3. The key value of the document as the index

a. Single column index

The official document of MongoDB says:

Documents as Keys

Indexed fields may be of any type, including documents:

Insert three records into table data in database recommender

> db.data.insert ({name: "1616", info: {url: "http://www.1616.net/",city:"beijing"}});

> db.data.insert ({name: "hao123", info: {url: "http://www.hao123.com/",city:"beijing"}});"

> db.data.insert ({name: "ll4la", info: {url: "http://www.114la.com/",city:"dongguan"}});"

Create an index on the field info

> db.data.ensureIndex ({info: 1})

Show all indexes on the table data

> db.data.getIndexes ()

[

{

"name": "_ id_"

"ns": "recommender.data"

"key": {

"_ id": 1

}

}

{

"_ id": ObjectId ("4befb146b0e29ba1ce20e0bb")

"ns": "recommender.data"

"key": {

"x": 1

}

"name": "Xero1"

}

{

"_ id": ObjectId ("4befb76bb0e29ba1ce20e0bf")

"ns": "recommender.data"

"key": {

"info": 1

}

"name": "info_1"

}

]

Find the specified record, where the index is used

> db.data.find ({info: {url: "http://www.1616.net/",city:"beijing"}});

{"_ id": ObjectId ("4befb711b0e29ba1ce20e0bc"), "name": "1616", "info": {"url": "http://www.1616.net/"," city ":" beijing "}

b. Combinatorial index

Set up a combined index

> db.data.ensureIndex ({"info.url": 1, "info.city": 1})

> db.data.getIndexes ()

[

{

"name": "_ id_"

"ns": "recommender.data"

"key": {

"_ id": 1

}

}

{

"_ id": ObjectId ("4befb146b0e29ba1ce20e0bb")

"ns": "recommender.data"

"key": {

"x": 1

}

"name": "Xero1"

}

{

"_ id": ObjectId ("4befb76bb0e29ba1ce20e0bf")

"ns": "recommender.data"

"key": {

"info": 1

}

"name": "info_1"

}

{

"_ id": ObjectId ("4befb9d1b0e29ba1ce20e0c0")

"ns": "recommender.data"

"key": {

"info.url": 1

"info.city": 1

}

"name": "info.url_1_info.city_1"

}

]

Indexes are used in the following operations

> db.data.find ({"info.url": "http://www.1616.net/"," info.city ":" beijing "})

{"_ id": ObjectId ("4befb711b0e29ba1ce20e0bc"), "name": "1616", "info": {"url": "http://www.1616.net/"," city ":" beijing "}

> db.data.find ({"info.url": "http://www.1616.net/"});"

{"_ id": ObjectId ("4befb711b0e29ba1ce20e0bc"), "name": "1616", "info": {"url": "http://www.1616.net/"," city ":" beijing "}

1 for ascending order (asc), and-1 for descending order (desc)

> db.data.find ({"info.url": / http:*/i}) .sort ({"info.url": 1, "info.city": 1})

{"_ id": ObjectId ("4befb740b0e29ba1ce20e0be"), "name": "ll4la", "info": {"url": "http://www.114la.com/"," city ":" dongguan "}}

{"_ id": ObjectId ("4befb711b0e29ba1ce20e0bc"), "name": "1616", "info": {"url": "http://www.1616.net/"," city ":" beijing "}

{"_ id": ObjectId ("4befb723b0e29ba1ce20e0bd"), "name": "hao123", "info": {"url": "http://www.hao123.com/"," city ":" beijing "}}

> db.data.find ({"info.url": / http:*/i}) .sort ({"info.url": 1})

{"_ id": ObjectId ("4befb740b0e29ba1ce20e0be"), "name": "ll4la", "info": {"url": "http://www.114la.com/"," city ":" dongguan "}}

{"_ id": ObjectId ("4befb711b0e29ba1ce20e0bc"), "name": "1616", "info": {"url": "http://www.1616.net/"," city ":" beijing "}

{"_ id": ObjectId ("4befb723b0e29ba1ce20e0bd"), "name": "hao123", "info": {"url": "http://www.hao123.com/"," city ":" beijing "}}

> db.data.find ({"info.url": / http:*/i) .sort ({"info.url":-1})

{"_ id": ObjectId ("4befb723b0e29ba1ce20e0bd"), "name": "hao123", "info": {"url": "http://www.hao123.com/"," city ":" beijing "}}

{"_ id": ObjectId ("4befb711b0e29ba1ce20e0bc"), "name": "1616", "info": {"url": "http://www.1616.net/"," city ":" beijing "}

{"_ id": ObjectId ("4befb740b0e29ba1ce20e0be"), "name": "ll4la", "info": {"url": "http://www.114la.com/"," city ":" dongguan "}}

What are the uses of various indexes of MongoDB

4. Combinatorial index

Note that the composite index here is a little different from the composite index in b in the above 3. 4 is a composite index for first-level fields, while above 3 is a combined index for secondary fields.

Create a composite index on top of fields name and info

> db.data.ensureIndex ({name: 1, info:-1})

When creating a combined index, the 1 after the field indicates ascending order and-1 indicates descending order. Whether to use 1 or-1 is mainly related to the time of sorting or when querying within the specified range, as described in the English text below.

When creating an index, the number associated with a key specifies the direction of the index, so it should always be 1 (ascending) or-1 (descending). Direction doesn't matter for single key indexes or for random access retrieval but is important if you are doing sorts or range queries on compound indexes.

Show all indexes

> db.data.getIndexes ()

[

{

"name": "_ id_"

"ns": "recommender.data"

"key": {

"_ id": 1

}

}

{

"_ id": ObjectId ("4befb146b0e29ba1ce20e0bb")

"ns": "recommender.data"

"key": {

"x": 1

}

"name": "Xero1"

}

{

"_ id": ObjectId ("4befb76bb0e29ba1ce20e0bf")

"ns": "recommender.data"

"key": {

"info": 1

}

"name": "info_1"

}

{

"_ id": ObjectId ("4befb9d1b0e29ba1ce20e0c0")

"ns": "recommender.data"

"key": {

"info.url": 1

"info.city": 1

}

"name": "info.url_1_info.city_1"

}

{

"_ id": ObjectId ("4befbfcfb0e29ba1ce20e0c1")

"ns": "recommender.data"

"key": {

"name": 1

"info":-1

}

"name": "name_1_info_-1"

}

]

The following sort will be used for the above index

The "name": "ll4la" in the last line is actually "name": "114la" (that is, the number is written as the letter l), but I wrote "name": "ll4la" when I typed it, but the sorting result is correct.

> db.data.find ({info.url: / http:*/i}) .sort ({name:1, info:-1})

{"_ id": ObjectId ("4befb711b0e29ba1ce20e0bc"), "name": "1616", "info": {"url": "http://www.1616.net/"," city ":" beijing "}

{"_ id": ObjectId ("4befb723b0e29ba1ce20e0bd"), "name": "hao123", "info": {"url": "http://www.hao123.com/"," city ":" beijing "}}

{"_ id": ObjectId ("4befb740b0e29ba1ce20e0be"), "name": "ll4la", "info": {"url": "http://www.114la.com/"," city ":" dongguan "}}

MongoDB composite index rules

If you have a compound index on multiple fields, you can use it to query on the beginning subset of fields. So if you have an index on

A,b,c

You can use it query on

A

A,b

A,b,c

If you have used MySQL, do you look familiar? the principle is the same as MySQL.

5. Unique index

Insert a record into the table data.

> db.data.insert ({firstname: "cai", lastname: "huafeng"})

Since only one record in the table data has fields firstname and lastname, and the other rows do not have corresponding values, that is, all are null, it means that null is the same, and the unique index is not allowed to have the same value, so it is wrong to create a unique combined index.

So when creating a unique index, whether you are indexing a single field or multiple fields, it is best to have this field in each row, otherwise an error will be reported.

> db.data.find ()

{"_ id": ObjectId ("4bee745a0863b1c233b8b7ea"), "name": "caihuafeng"}

{"_ id": ObjectId ("4bee745f0863b1c233b8b7eb"), "website": "1616.net"}

{"_ id": ObjectId ("4bee804ba23d558eb6687117"), "x": 6, "name": "caihuafeng1"}

{"_ id": ObjectId ("4bee804ba23d558eb6687118"), "x": 6, "name": "caihuafeng2"}

{"_ id": ObjectId ("4bee804ba23d558eb6687119"), "x": 6, "name": "caihuafeng3"}

{"_ id": ObjectId ("4bee804ba23d558eb668711a"), "x": 6, "name": "caihuafeng4"}

{"_ id": ObjectId ("4bee804ba23d558eb668711b"), "x": 6, "name": "caihuafeng5"}

{"_ id": ObjectId ("4bee804ba23d558eb668711c"), "x": 6, "name": "caihuafeng6"}

{"_ id": ObjectId ("4bee804ba23d558eb668711d"), "x": 6, "name": "caihuafeng7"}

{"_ id": ObjectId ("4bee804ba23d558eb668711e"), "x": 6, "name": "caihuafeng8"}

{"_ id": ObjectId ("4bee804ba23d558eb668711f"), "x": 6, "name": "caihuafeng9"}

{"_ id": ObjectId ("4bee804ba23d558eb6687120"), "x": 6, "name": "caihuafeng10"}

{"_ id": ObjectId ("4befb711b0e29ba1ce20e0bc"), "name": "1616", "info": {"url": "http://www.1616.net/"," city ":" beijing "}

{"_ id": ObjectId ("4befb723b0e29ba1ce20e0bd"), "name": "hao123", "info": {"url": "http://www.hao123.com/"," city ":" beijing "}}

{"_ id": ObjectId ("4befb740b0e29ba1ce20e0be"), "name": "ll4la", "info": {"url": "http://www.114la.com/"," city ":" dongguan "}}

{"_ id": ObjectId ("4befc51ab0e29ba1ce20e0c2"), "firstname": "cai", "lastname": "huafeng"}

> db.data.ensureIndex ({firstname: 1, lastname: 1}, {unique: true})

E11000 duplicate key error index: recommender.data.$firstname_1_lastname_1 dup key: {: null,: null}

Let's test it with another table, person.

> db.person.ensureIndex ({firstname:1, lastname: 1}, {unique: true})

> db.person.insert ({firstname: 'cai', lastname:' huafeng'})

An error was reported when inserting the same value the second time, indicating that the unique index is valid, which is actually the same as in MySQL.

> db.person.insert ({firstname: 'cai', lastname:' huafeng'})

E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1 dup key: {: "cai",: "huafeng"}

6. Duplicate value handling in a unique index

Delete the index in the above 5 and insert the same record in two rows

> db.person.dropIndexes ()

{

"nIndexesWas": 2

"msg": "non-_id indexes dropped for collection"

"ok": 1

}

> db.person.find ()

{"_ id": ObjectId ("4befcda6b0e29ba1ce20e0cf"), "firstname": "cai", "lastname": "huafeng"}

> db.person.insert ({firstname: 'cai', lastname:' huafeng'})

> db.person.find ()

{"_ id": ObjectId ("4befcda6b0e29ba1ce20e0cf"), "firstname": "cai", "lastname": "huafeng"}

{"_ id": ObjectId ("4befcef0b0e29ba1ce20e0d1"), "firstname": "cai", "lastname": "huafeng"}

If you now create a unique composite index directly on fields firstname and lastname, you will definitely get an error. Let's give it a try:

> db.person.ensureIndex ({firstname: 1, lastname: 1}, {unique: true})

E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1 dup key: {: "cai",: "huafeng"}

Looking at the index of the table person, we can see that the newly created index is not generated.

> db.person.getIndexes ()

[

{

"name": "_ id_"

"ns": "recommender.person"

"key": {

"_ id": 1

}

}

]

You can add a dropDups: true to the second json object so that no errors are reported when creating a unique composite index, the first duplicate value in the document is retained, and all other duplicate values are deleted.

To test it again, add the dropDups option, although an error has been reported, but the only composite index has been established.

> db.person.ensureIndex ({firstname: 1, lastname: 1}, {unique: true, dropDups: true})

E11000 duplicate key error index: recommender.person.$firstname_1_lastname_1 dup key: {: "cai",: "huafeng"}

> db.person.getIndexes ()

[

{

"name": "_ id_"

"ns": "recommender.person"

"key": {

"_ id": 1

}

}

{

"_ id": ObjectId ("4befcfd9b0e29ba1ce20e0d3")

"ns": "recommender.person"

"key": {

"firstname": 1

"lastname": 1

}

"name": "firstname_1_lastname_1"

"unique": true

"dropDups": true

}

]

Query the records in the table person again and find that the duplicate records have been automatically deleted.

> db.person.find ()

{"_ id": ObjectId ("4befcda6b0e29ba1ce20e0cf"), "firstname": "cai", "lastname": "huafeng"}

Description of the MongoDB official documentation

A unique index cannot be created on a key that has duplicate values. If you would like to create the index anyway, keeping the first document the database indexes and deleting all subsequent documents that have duplicate values, add the dropDups option.

Db.things.ensureIndex ({firstname: 1}, {unique: true, dropDups: true})

7. Delete index

a. Delete all indexes in a table

To delete all indexes on the specified collection:

Db.collection.dropIndexes ()

b. Delete a single index in a table

To delete a single index:

Db.collection.dropIndex ({x: 1, y:-1})

> db.data.dropIndex ({firstname: 1, lastname: 1})

{"nIndexesWas": 6, "ok": 1}

Running directly as a command without helper:

/ / note: command was "deleteIndexes", not "dropIndexes", before MongoDB v1.3.2

/ / remove index with key pattern {y:1} from collection foo

Db.runCommand ({dropIndexes:'foo', index: {yvvl}})

/ / remove all indexes:

Db.runCommand ({dropIndexes:'foo', index:'*'})

> db.person.ensureIndex ({firstname: 1, lastname: 1})

> db.runCommand ({dropIndexes:'person', index: {firstname:1, lastname:1}})

{"nIndexesWas": 2, "ok": 1}

The above is how to use indexes in MongoDB database. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.