Let's talk to you about the rich index types in MongoDB-MongoDB-php.cn

Let's talk to you about the rich index types in MongoDB

青灯夜游

Release： 2022-02-17 10:59:16

forward

2727 people have browsed it

This article will take you to understand MongoDB and introduce the rich index types in MongoDB. I hope it will be helpful to everyone! The functions of Let's talk to you about the rich index types in MongoDB

Let's talk to you about the rich index types in MongoDB

MongoDB's index and MySql's index are basically similar in function and optimization principles, MySqlIndex types can basically be distinguished as:

Single key index - joint index
Primary key index (clustered index) -Non-primary key index (non-clustered index)

In addition to these basic classifications in MongoDB, there are also some special index types, such as: array index | sparse index | geospatial index | TTL index, etc. For the convenience of testing below, we use the script to insert the following data

for(var i = 0;i < 100000;i++){
    db.users.insertOne({
        username: "user"+i,
        age: Math.random() * 100,
        sex: i % 2,
        phone: 18468150001+i
    });
}

Copy after login

Single key index

Single key index means that there is only one indexed field, which is the most basic index. Method.Use the username field in the collection to create a single key index. MongoDB will automatically name this index username_1

db.users.createIndex({username:1})
&#39;username_1&#39;

Copy after login

After creating the index, check the query plan using the username field. stage is IXSCAN, which means index scanning is used

db.users.find({username:"user40001"}).explain()
{ 
   queryPlanner: 
   { 
     winningPlan: 
     { 
        ......
        stage: &#39;FETCH&#39;,
        inputStage: 
        { 
           stage: &#39;IXSCAN&#39;,
           keyPattern: { username: 1 },
           indexName: &#39;username_1&#39;,
           ......
        } 
     }
     rejectedPlans: [] ,
   },
   ......
   ok: 1 
}

Copy after login

Among the principles of index optimization, a very important principle is that the index should be built on a field with a high cardinality. The so-called cardinality is the number of non-repeating values in a field, that is, when we create users If the age value that appears during collection is 0-99, then the age field will have 100 unique values, that is, the base of the age field is 100. The sex field will only have the two values 0 | 1, that is, the base of the sex field is 2, which is a fairly low base. In this case, the index efficiency is not high and will lead to index failure. Let's build a sex field index to query the execution plan. You will find that the query is done Full table scan without related index.

db.users.createIndex({sex:1})
&#39;sex_1&#39;

db.users.find({sex:1}).explain()
{ 
  queryPlanner: 
  { 
     ......
     winningPlan: 
     { 
        stage: &#39;COLLSCAN&#39;,
        filter: { sex: { &#39;$eq&#39;: 1 } },
        direction: &#39;forward&#39; 
     },
     rejectedPlans: [] 
  },
  ......
  ok: 1 
}

Copy after login

Joint index

Joint index means there will be multiple fields on the index. Use age## below. # and sex create an index with two fields

db.users.createIndex({age:1,sex:1})
&#39;age_1_sex_1&#39;

Copy after login

Then we use these two fields to conduct a query, check the execution plan, and successfully go through this index

db.users.find({age:23,sex:1}).explain()
{ 
  queryPlanner: 
  { 
     ......
     winningPlan: 
     { 
        stage: &#39;FETCH&#39;,
        inputStage: 
        { 
           stage: &#39;IXSCAN&#39;,
           keyPattern: { age: 1, sex: 1 },
           indexName: &#39;age_1_sex_1&#39;,
           .......
           indexBounds: { age: [ &#39;[23, 23]&#39; ], sex: [ &#39;[1, 1]&#39; ] } 
        } 
     },
     rejectedPlans: [], 
  },
  ......
  ok: 1 
 }

Copy after login

Array index

Array index is to create an index on the array field, also called a multi-valued index. In order to test, the data in the users collection will be added to some array fields below.

db.users.updateOne({username:"user1"},{$set:{hobby:["唱歌","篮球","rap"]}})
......

Copy after login

Create an array index and view its execution plan. Note that isMultiKey: true means that the index used is a multi-valued index.

db.users.createIndex({hobby:1})
&#39;hobby_1&#39;

db.users.find({hobby:{$elemMatch:{$eq:"钓鱼"}}}).explain()
{ 
   queryPlanner: 
   { 
     ......
     winningPlan: 
     { 
        stage: &#39;FETCH&#39;,
        filter: { hobby: { &#39;$elemMatch&#39;: { &#39;$eq&#39;: &#39;钓鱼&#39; } } },
        inputStage: 
        { 
           stage: &#39;IXSCAN&#39;,
           keyPattern: { hobby: 1 },
           indexName: &#39;hobby_1&#39;,
           isMultiKey: true,
           multiKeyPaths: { hobby: [ &#39;hobby&#39; ] },
           ......
           indexBounds: { hobby: [ &#39;["钓鱼", "钓鱼"]&#39; ] } } 
         },
     rejectedPlans: [] 
  },
  ......
  ok: 1 
}

Copy after login

Array index is compared to other indexes Generally speaking, the index entries and volume must increase exponentially. For example, the average size of the hobby array of each document is 10, then the hobby array index of this collection is The number of entries will be 10 times that of the ordinary index. Joint array index A joint array index is a joint index containing array fields. This type of index does not support one index. Contains multiple array fields, that is, there can be at most one array field in an index. This is to avoid the explosive growth of index entries. Suppose there are two array fields in an index, then the number of index entries will be n* of a normal index. m times

Geographic spatial index

Add some geographical information to the original users collection

for(var i = 0;i < 100000;i++){
    db.users.updateOne(
    {username:"user"+i},
    {
        $set:{
            location:{
                type: "Point",
                coordinates: [100+Math.random() * 4,40+Math.random() * 3]
            }
        }
    });
}

Copy after login

Create a second Dimensional spatial index

db.users.createIndex({location:"2dsphere"})
&#39;location_2dsphere&#39;

//查询500米内的人
db.users.find({
  location:{
    $near:{
      $geometry:{type:"Point",coordinates:[102,41.5]},
      $maxDistance:500
    }
  }
})

Copy after login

The type of the geographical spatial index has many containing Ponit(point) | LineString(line) | Polygon (Polygon)etc

TTL index

The full spelling of TTL is time to live

, which is mainly used for automatic deletion of expired data , to use this kind of index, you need to declare a time type field in the document, and then when creating a TTL index for this field, you also need to set an

expireAfterSecondsThe expiration time unit is seconds, after the creation is completedMongoDBThe data in the collection will be checked regularly. When it appears: