Tutorials for using indexes in MongoDB database and explain
- 2020-06-19 11:59:26
- OfStack
preface
This paper mainly introduces the relevant content of MongoDB index and explain usage, and shares it for your reference and study. The following words are not enough, let's have a look at the detailed introduction:
mongodb index used
role
An index can often greatly improve a query. An index is a data structure that collects the values of specific fields in a document in a collection. B-Tree index to implement.Create indexes
db.collection.createIndex(keys, options)
keys
keys consists of document fields and index types. {" name ": 1} key represents field value 1,-1 1 represents ascending,-1 descendingoptions
Option for options to create indexes.
参数 | 类型 | 描述 |
---|---|---|
background | boolean | 创建索引在后台运行,不会阻止其他对数据库操作 |
unique | boolean | 创建唯1索引,文档的值不会重复 |
name | string | 索引名称,默认是:字段名_排序类型 开始排序 |
sparse | boolean | 过滤掉null,不存在的字段 |
View index
db.collection.getIndexes()
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1 // The index field
},
"name" : "name_1", // The index name
"ns" : "leyue.userdatas"
}
Remove the index
db.collection.dropIndex(index)
Deletes the specified index.
db.collection.dropIndexes()
Delete all indexes except _id.
Create/view/delete examples
View the data
db.userdatas.find()
{ "_id" : ObjectId("597f357a09c84cf58880e412"), "name" : "u3", "age" : 32 }
{ "_id" : ObjectId("597f357a09c84cf58880e411"), "name" : "u4", "age" : 30, "score" : [ 7, 4, 2, 0 ] }
{ "_id" : ObjectId("597fcc0f411f2b2fd30d0b3f"), "age" : 20, "score" : [ 7, 4, 2, 0, 10, 9, 8, 7 ], "name" : "lihao" }
{ "_id" : ObjectId("597f357a09c84cf58880e413"), "name" : "u2", "age" : 33, "wendang" : { "yw" : 80, "xw" : 90 } }
{ "_id" : ObjectId("5983f5c88eec53fbcd56a7ca"), "date" : ISODate("2017-08-04T04:19:20.693Z") }
{ "_id" : ObjectId("597f357a09c84cf58880e40e"), "name" : "u1", "age" : 26, "address" : " China DangShan " }
{ "_id" : ObjectId("597f357a09c84cf58880e40f"), "name" : "u1", "age" : 37, "score" : [ 10, 203, 12, 43, 56, 22 ] }
{ "_id" : ObjectId("597f357a09c84cf58880e410"), "name" : "u5", "age" : 78, "address" : "china beijing chaoyang" }
Index the field name
// Create indexes
db.userdatas.createIndex({"name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
// View index
db.userdatas.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1
},
"name" : "name_1",
"ns" : "leyue.userdatas"
}
]
Index the field name and name it myindex
db.userdatas.createIndex({"name":1})
db.userdatas.createIndex({"name":1},{"name":"myindex"})
db.userdatas.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1
},
"name" : "myindex",
"ns" : "leyue.userdatas"
}
]
Creating an index for the field name is done in the background
It can be run in the background when the mongodb collection is too large to create an index.
db.userdatas.dropIndex("myindex")
db.userdatas.createIndex({"name":1},{"name":"myindex","background":true})
Create a unique index for the age field
db.userdatas.createIndex({"age":-1},{"name":"ageIndex","unique":true,"sparse":true})
db.userdatas.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1
},
"name" : "myindex",
"ns" : "leyue.userdatas",
"background" : true
},
{
"v" : 1,
"unique" : true,
"key" : {
"age" : -1
},
"name" : "ageIndex",
"ns" : "leyue.userdatas",
"sparse" : true
}
]
// insert 1 One that already exists age
db.userdatas.insert({ "name" : "u8", "age" : 32})
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error index: leyue.userdatas.$ageIndex dup key: { : 32.0 }"
}
})
Create a composite index
db.userdatas.createIndex({"name":1,"age":-1})
db.userdatas.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1,
"age" : -1
},
"name" : "name_1_age_-1",
"ns" : "leyue.userdatas"
}
]
All fields are stored in the collection system.indexes
db.system.indexes.find()
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "leyue.userdatas" }
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "leyue.scores" }
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "leyue.test" }
{ "v" : 1, "key" : { "user" : 1, "name" : 1 }, "name" : "myindex", "ns" : "leyue.test" }
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "leyue.mycapped" }
{ "v" : 1, "key" : { "user" : 1 }, "name" : "user_1", "ns" : "leyue.test" }
{ "v" : 1, "key" : { "name" : 1 }, "name" : "myindex", "ns" : "leyue.userdatas" }
Index to summarize
1: When an index is created,1 represents ascending storage and -1 represents descending storage.
2: You can create a composite index. If you want to use a composite index, you must include the first N index columns in the composite index in the query condition
3: If the order of key values in the query condition and the order of creation in the composite index are not 1,
MongoDB is smart enough to help us adjust this order so that composite indexes can be used by queries.
4: You can create indexes for embedded documents with the same rules as normal document creation.
5: You can only use one index per query. $or is special and you can use one index per branch condition.
6: $where,$exists cannot use indexes, and there are 1 inefficient operator, such as :$ne,$not,$nin, etc.
7: When designing indexes for multiple fields, try to place the fields that are used for exact matches before the index.
explain use
grammar
db.collection.getIndexes()
0
explain() can set parameters:
queryPlanner. executionStats. allPlansExecution.The sample
db.collection.getIndexes()
1
Not using indexes
db.collection.getIndexes()
2
executionStats.executionTimeMillis: query
Total query time.
executionStats.nReturned
: Query the returned entry.
executionStats.totalKeysExamined
: Index scan entries.
executionStats.totalDocsExamined
: Document scan entry.
executionTimeMillis = 326
query execution time
nReturned=2
Return two pieces of data
totalKeysExamined=0
No index is used
totalDocsExamined full document scan
Ideal condition:
nReturned=totalKeysExamined & totalDocsExamined=0
Stage state analysis
stage | 描述 |
---|---|
COLLSCAN | 全表扫描 |
IXSCAN | 扫描索引 |
FETCH | 根据索引去检索指定document |
SHARD_MERGE | 将各个分片返回数据进行merge |
SORT | 表明在内存中进行了排序 |
LIMIT | 使用limit限制返回数 |
SKIP | 使用skip进行跳过 |
IDHACK | 针对_id进行查询 |
SHARDING_FILTER | 通过mongos对分片数据进行查询 |
COUNT | 利用db.coll.explain().count()之类进行count运算 |
COUNTSCAN | count不使用Index进行count时的stage返回 |
COUNT_SCAN | count使用了Index进行count时的stage返回 |
SUBPLA | 未使用到索引的$or查询的stage返回 |
TEXT | 使用全文索引进行查询时候的stage返回 |
PROJECTION | 限定返回字段时候stage的返回 |
For normal queries, I would like to see a combination of stage (query using the index as much as possible) :
Fetch+IDHACK
Fetch+ixscan
Limit + (Fetch + ixscan)
PROJECTION+ixscan
SHARDING_FITER+ixscan
COUNT_SCAN
stage containing the following is not desirable:
COLLSCAN(full table scan),SORT(sort but no index), unreasonable SKIP,SUBPLA($or without index),COUNTSCAN(index without count)
Using the index
db.test.createIndex({"user":1},{"name":"myindex","background":true})
db.test.explain("executionStats").find({"user":"user200000"})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "leyue.test",
"indexFilterSet" : false,
"parsedQuery" : {
"user" : {
"$eq" : "user200000"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"user" : 1
},
"indexName" : "myindex",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"user" : [
"[\"user200000\", \"user200000\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 2,
"executionTimeMillis" : 0,
"totalKeysExamined" : 2,
"totalDocsExamined" : 2,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 2,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"user" : 1
},
"indexName" : "myindex",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"user" : [
"[\"user200000\", \"user200000\"]"
]
},
"keysExamined" : 2,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "lihaodeMacBook-Pro.local",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
executionTimeMillis: 0
totalKeysExamined: 2
totalDocsExamined:2
nReturned:2
stage:IXSCAN
There is a big difference between using indexes and not using them. If indexes are used reasonably, a collection is suitable for 4-5 indexes.
conclusion
Related articles
http://www.mongoing.com/eshu_explain3
https://docs.mongodb.com/v3.2/reference/explain-results/#queryplanner