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, MySql
Index types can basically be distinguished as:
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 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 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'
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 }
users collection will be added to some array fields below.
db.users.updateOne({username:"user1"},{$set:{hobby:["唱歌","篮球","rap"]}}) ......
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 }
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 timesusers 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] } } }); }
db.users.createIndex({location:"2dsphere"}) 'location_2dsphere' //查询500米内的人 db.users.find({ location:{ $near:{ $geometry:{type:"Point",coordinates:[102,41.5]}, $maxDistance:500 } } })
type of the geographical spatial index has many containing
Ponit(point) |
LineString(line) |
Polygon (Polygon)etc
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 completed
MongoDBThe data in the collection will be checked regularly. When it appears: