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

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

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
    });
}

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})
'username_1'

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: 'FETCH',
        inputStage: 
        { 
           stage: 'IXSCAN',
           keyPattern: { username: 1 },
           indexName: 'username_1',
           ......
        } 
     }
     rejectedPlans: [] ,
   },
   ......
   ok: 1 
}

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})
'sex_1'

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

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})
'age_1_sex_1'

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: 'FETCH',
        inputStage: 
        { 
           stage: 'IXSCAN',
           keyPattern: { age: 1, sex: 1 },
           indexName: 'age_1_sex_1',
           .......
           indexBounds: { age: [ '[23, 23]' ], sex: [ '[1, 1]' ] } 
        } 
     },
     rejectedPlans: [], 
  },
  ......
  ok: 1 
 }

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"]}})
......

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})
'hobby_1'

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

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]
            }
        }
    });
}

Create a second Dimensional spatial index

db.users.createIndex({location:"2dsphere"})
'location_2dsphere'

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

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:

$$\#\#Current\; time-TTLIndex\; field\; time>expireAfterSrconds$$

The above is the detailed content of Let's talk to you about the rich index types in MongoDB. For more information, please follow other related articles on the PHP Chinese website!