Learning MongoDB 8: MongoDB index (index restrictions) (2), mongodb Index

Learning MongoDB 8: MongoDB index (index restrictions) (2), mongodb Index
I. Introduction

In the previous article, we introduced basic index operations through db. collection. the createIndex (keys, options) syntax creates an index. We will continue to introduce the restrictions of geospatial indexes and indexes, so that we can improve the query efficiency in MongoDB.

 

Index Syntax:

Db. collection. createIndex (keys, options)

Options parameter description

2. geospatial Indexing

The more geographic locations we use in our daily life, the more latitude and longitude we store, and the more geographic locations we query. To improve the query efficiency in MongoDB, we have created a geospatial index.

 

1. Create a geospatial Index

Syntax:

        db.collection.createIndex({ <location field> : "2d" ,                                    <additionalfield> : <value> } ,                                  {<index-specification options> } )

Index-specification parameter description:

{Min: <lower bound>, max: <upper bound>}

We often create several longitude and latitude formats, such as location: [50, 40], location: {lng: 50, lat: 40}

> db.places.find(){ "_id" :ObjectId("55ad0df063ea39b3057bdeef"), "onumber" : 1,"date" : "2015-07-01", "cname" :"zcy", "location" : [ -10, 100 ] }{ "_id" :ObjectId("55ad0e0463ea39b3057bdef0"), "onumber" : 2,"date" : "2015-07-02", "cname" :"zcy", "location" : [ 10, 60 ] }{ "_id" :ObjectId("55ad0e1663ea39b3057bdef1"), "onumber" : 3,"date" : "2015-07-03", "cname" :"zcy", "location" : [ 100, 150 ] }{ "_id" :ObjectId("55ad0e2463ea39b3057bdef2"), "onumber" : 4,"date" : "2015-07-04", "cname" : "zcy","location" : [ 150, 200 ] }{ "_id" :ObjectId("55ad0e3263ea39b3057bdef3"), "onumber" : 5,"date" : "2015-07-05", "cname" :"zcy", "location" : [ -100, 100 ] }

Example:

    >db.places.createIndex({location:"2d"})

The default value range of a geospatial index is-180 to 180. If the value already exists and exceeds 200, index creation fails:

"Errmsg": "point not in interval of [-180,180]: caused by ::{_ id: ObjectId ('55ad07bc63ea39b3057bdeed'), onumber: 5.0, date: \ "2015-07-05 \", cnam

E: \ "zcy \", location: [100.0, 200.0]} "," code ": 13027

We can create a default value outside the location range of a two-dimensional geospatial index. The minimum and maximum options are used for index creation.

Syntax:

      db.collection.createIndex( {<location field> : "2d" } ,                                 {min : <lower bound> , max : <upper bound> } )

Example:

      >db.places.createIndex({location:"2d"},{min:-200,max:200})

The default value range of a geospatial index is-200 to 200.

 

2. query points on the plane

You can use $ near or geoNear Command to query the logs. You can use the limit () function. If this parameter is not specified, 100 documents are returned by default.

 

(1) Precise Query

Example:

       > db.places.find({location:[60,100]})

The latitude and longitude of the query is [60,100].

 

(2) $ near Query

We need to query the longitude and latitude in the range

Syntax:

    db.collection.find( {<location field> :                         {$near : [ <x> , <y> ]                      } } )

Example:

      > db.places.find({location:{$near:[100,200]}})

We query the target point [100,200] the nearest 100 points, and then sort by the nearest

 

(3) geoNearCommand Query

GeoNear Command query root db. collection. find () query is similar

Syntax:

    db.runCommand( { geoNear:<collection>, near: [ <x> , <y> ] } )

Example:

       > db.runCommand( {geoNear:"places", near: [ -100,100] } ){        "results" :[                {                       "dis" : 0,                       "obj" : {                               "_id" : ObjectId("55ad0e3263ea39b3057bdef3"),                               "onumber" : 5,                               "date" : "2015-07-05",                               "cname" : "zcy",                               "location" : [                                        -100,                                        100                               ]                        }                },                {                       "dis" : 90,                       "obj" : {                               "_id" : ObjectId("55ad0df063ea39b3057bdeef"),                               "onumber" : 1,                               "date" : "2015-07-01",                               "cname" : "zcy",                               "location" : [                                       -10,                                        100                               ]                        }                },                {                       "dis" : 117.04699910719626,                       "obj" : {                               "_id" : ObjectId("55ad0e0463ea39b3057bdef0"),                               "onumber" : 2,                               "date" : "2015-07-02",                               "cname" : "zcy",                                "location" : [                                        10,                                        60                               ]                        }                },                {                       "dis" : 206.15528128088303,                       "obj" : {                               "_id" : ObjectId("55ad0e1663ea39b3057bdef1"),                               "onumber" : 3,                               "date" : "2015-07-03",                               "cname" : "zcy",                               "location" : [                                        100,                                        150                               ]                        }                },                {                        "dis" : 269.2582403567252,                       "obj" : {                               "_id" : ObjectId("55ad0e2463ea39b3057bdef2"),                               "onumber" : 4,                               "date" : "2015-07-04",                               "cname" : "zcy",                               "location" : [                                        150,                                        200                               ]                        }                }        ],        "stats" : {               "nscanned" : NumberLong(5),               "objectsLoaded" : NumberLong(5),               "avgDistance" : 136.4921041489609,               "maxDistance" : 269.2582403567252,               "time" : 52        },        "ok" : 1}

 

4. query the points defined on the surface

(1) $ box

(2) $ polygon

(3) $ center (defines a circle)

Here is not specific introduction, you can go to the official documentation to view: http://docs.mongodb.org/manual/tutorial/query-a-2d-index/

Iii. Index restrictions

The second chapter introduces the basic creation of indexes. We now add restrictions when creating indexes, such as unique indexes.

1. Unique Index

When you set a unique index for a field, you can ensure that the field is unique.

Syntax:

     db.collection.createIndex({field1：boolean, field2：boolean },{unique: true})

(1) create a unique index

Example:

          > db.orders.createIndex({onumber:1},{unique:true})

We have created an onumber as the unique index.

When we insert the same onumber, the addition fails.

Note: When a new field is a unique index and the corresponding field does not exist, the index stores the field as null. If the first field value is not saved when the document is added, it is saved as null, and the second record is the value corresponding to the specified field. When data is added, null already exists, which causes the addition to fail.

 

(2) create a unique index for the same fields in the document

 

When data already exists in our document, we create a unique index.

Example:

       >db.orders.createIndex({onumber:1},{unique:true})

 

The onumber field value in our set already exists, so it will cause the creation of the unique index to fail.

A unique index cannot be created on a key with duplicate values. If you must create a unique index with the dropDups parameter, the system only keeps the first record on the duplicate key, the remaining records will be deleted.

Example:

      >db.orders.createIndex({onumber:1},{unique:true,dropDups:true})

2. Index name

We didn't specify the index name when creating the index. MongoDB will generate a default index name. We can use the name parameter to specify the name of the new index.

Syntax:

          db.collection.createIndex({field1：boolean,field2：boolean },{name: "index_name"}) 

Example:

        >db.orders.createIndex({onumber:1},{name:"index_onumber"})

Create an index named index_onumber in the onumber field.

 

3. Create indexes in the background

When creating an index, the background will block other operations on MongoDB. For example, if the background is trues when querying MongoDB, you can specify a later method to create an index. The default value is false.

 

Syntax:

         db.collection.createIndex({{field1：boolean,field2：boolean }} },{background: true})

Example:

         > db.orders.createIndex({cname:1},{background:true}<span style="font-size:18px;">)</span>

4. sparse Index 

Sparse: sparse indexes only contain documents with index fields. Even if the index field contains null values, index skipping lacks index fields. The index is "sparse" because it does not contain all documents in the set. In contrast, non-sparse indexes contain all documents in a set that do not contain null values of index fields. Similar to $ exists, it is used to determine whether a field exists.

Syntax

        db.collection.createIndex({{field1：boolean,field2：boolean }} },{ sparse: true})

Example:

       >db.orders.createIndex({onumber:1},{sparse:true})

 

When we query, when onumber is null, there are four records. When we use onumber as the query condition, the index is not used.

When we use onumber as 1 as the query condition, indexes are used.

 

Sparse indexes only contain documents with index fields. Even if the index field contains null values, the index skips the missing index fields.

 

 

Iv. index information

 

1. Force Index

When we query MongoDB, we can use hint to force an index.

Syntax:

    db. collection.find().hint(“index_name”)

Example:

    >db.orders.find({onumber:1}).hint("onumber_1")

We force the onumber field index to be named onumber_1.

 

The MongoDB query optimizer is very intelligent. You can choose which index to use. In most cases, you do not need to specify the index.

 

2. Execution Plan

 

MongoDB provides an explain command to let us know how the system processes query requests. Using the explain command, we can observe how the system uses indexes to accelerate the search and optimize the INDEX accordingly.

>db.orders.find({onumber:1}).hint("onumber_1").explain(){       "cursor" : "BtreeCursor onumber_1",       "isMultiKey" : false,       "n" : 1,       "nscannedObjects" : 1,       "nscanned" : 1,       "nscannedObjectsAllPlans" : 1,       "nscannedAllPlans" : 1,       "scanAndOrder" : false,       "indexOnly" : false,       "nYields" : 0,       "nChunkSkips" : 0,       "millis" : 77,       "indexBounds" : {                "onumber" : [                        [                                1,                                1                        ]                ]       },       "server" : "zhengcy-PC:27017",       "filterSet" : false}

 

Important parameter descriptions:

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

2) millis: the time required for the current query, in milliseconds.

3) indexBounds: The index used for the current query.

4) nscanned: number of rows scanned for the document.

5) cursor: The returned cursor type (BasicCursor and BtreeCursor). Here we use the BtreeCursor type.

6) nscannedObjects: Number of scanned documents.

7) scanAndOrder: whether to sort in memory.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.