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

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

MongoDB's index andMySql'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 inMongoDB, 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 theusernamefield in the collection to create a single key index.MongoDBwill automatically name this indexusername_1

db.users.createIndex({username:1}) 'username_1'

Copy after login

After creating the index, check the query plan using theusernamefield.stageisIXSCAN, 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 }

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 createusersIf the age value that appears during collection is0-99, then theagefield will have 100 unique values, that is, the base of theagefield is 100. Thesexfield will only have the two values0 | 1, that is, the base of thesexfield 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 asexfield 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 }

Copy after login

Joint index

Joint index means there will be multiple fields on the index. Useage## below. # andsexcreate an index with two fields

db.users.createIndex({age:1,sex:1}) 'age_1_sex_1'

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

userscollection 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: truemeans 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 }

Copy after login

Array index is compared to other indexes Generally speaking, the index entries and volume must increase exponentially. For example, the average

sizeof thehobbyarray of each document is 10, then thehobbyarray 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

userscollection

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"}) 'location_2dsphere' //查询500米内的人 db.users.find({ location:{ $near:{ $geometry:{type:"Point",coordinates:[102,41.5]}, $maxDistance:500 } } })

Copy after login

The

typeof the geographical spatial index has many containingPonit(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 anexpireAfterSecondsThe expiration time unit is seconds, after the creation is completedMongoDBThe data in the collection will be checked regularly. When it appears: