索引类型
- 单字段索引
- 复合索引
- 多key索引
- 文本索引
单字段索引
> db.users.getIndexKeys() // 查询所有单字段索引的列
[ { "_id" : 1 }, { "name" : 1 }, { "age" : -1 } ]
// 查询命中索引
> db.users.find({"age": 20}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"age" : {
"$eq" : 20
}
},
"queryHash" : "3838C5F3",
"planCacheKey" : "041C5DE3",
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN", // 命中索引,然后扫描索引
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[20.0, 20.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "bogon",
"port" : 27017,
"version" : "4.2.12",
"gitVersion" : "5593fd8e33b60c75802edab304e23998fa0ce8a5"
},
"ok" : 1
}
// 没命中索引,然后进行全集合扫描
> db.users.find({"pswd": "n1"}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"pswd" : {
"$eq" : "n1"
}
},
"queryHash" : "39E10397",
"planCacheKey" : "39E10397",
"winningPlan" : {
"stage" : "COLLSCAN", // 全集合扫描
"filter" : {
"pswd" : {
"$eq" : "n1"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "bogon",
"port" : 27017,
"version" : "4.2.12",
"gitVersion" : "5593fd8e33b60c75802edab304e23998fa0ce8a5"
},
"ok" : 1
}
交叉索引
- 不是索引类型
- 为一个集合的多个字段分别建立索引,在查询的时候通过多个字段作为查询条件,这种情况称为交叉索引
- 交叉索引的查询效率较低,在使用时,当查询使用到多个字段的时候,尽量使用复合索引
复合(组合)索引
复合索引时单字段索引的升级版,针对多个字段联合创建索引,先按第一个字段排序,第一个字段相同的文档按照第二个字段排序,依次类推
> db.users.dropIndexes()
> db.users.ensureIndex({"age": 1, "name": 1})
> db.users.find({"age": {"$gt": 20}, "name": "n1"}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"name" : {
"$eq" : "n1"
}
},
{
"age" : {
"$gt" : 20
}
}
]
},
"queryHash" : "CF57D41A",
"planCacheKey" : "3B9D692D",
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1,
"age" : -1
},
"indexName" : "name_1_age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"n1\", \"n1\"]"
],
"age" : [
"[inf.0, 20.0)"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "bogon",
"port" : 27017,
"version" : "4.2.12",
"gitVersion" : "5593fd8e33b60c75802edab304e23998fa0ce8a5"
},
"ok" : 1
}
索引最左前缀,name+age组合索引,只查age时,不会使用复合索引
> db.users.find({"age": {"$gt": 20}}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"age" : {
"$gt" : 20
}
},
"queryHash" : "4BB283C4",
"planCacheKey" : "DF7FEF1F",
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"age" : {
"$gt" : 20
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "bogon",
"port" : 27017,
"version" : "4.2.12",
"gitVersion" : "5593fd8e33b60c75802edab304e23998fa0ce8a5"
},
"ok" : 1
}
多key索引(Multikey Index)
当索引的字段为数组时,创建出的索引称为多key索引,多key索引会为数组的每个元素建立一条索引,比如users集合加入一个courses字段用于描述课程。需要查询有相同课程的人就可以利用courses字段的多key索引
> db.users.update({}, {"$set": {courses: ["Golang", "Gorm", "Gin"]}}, false, true)
WriteResult({ "nMatched" : 5, "nUpserted" : 0, "nModified" : 4 })
> db.users.find()
{ "_id" : ObjectId("601cf85c7cfdb08d65ff4668"), "name" : "n1", "age" : 20, "gender" : "m", "birth" : ISODate("2000-04-10T00:00:00Z"), "pswd" : "password123", "courses" : [ "Golang", "Gorm", "Gin" ] }
{ "_id" : ObjectId("601cf85c7cfdb08d65ff4669"), "name" : "n2", "age" : 21, "gender" : "f", "birth" : ISODate("1999-04-10T00:00:00Z"), "pswd" : "password123", "courses" : [ "Golang", "Gorm", "Gin" ] }
{ "_id" : ObjectId("601cf85c7cfdb08d65ff466a"), "name" : "n3", "age" : 22, "gender" : "m", "birth" : ISODate("1998-04-10T00:00:00Z"), "pswd" : "password123", "courses" : [ "Golang", "Gorm", "Gin" ] }
{ "_id" : ObjectId("601cf85c7cfdb08d65ff466b"), "name" : "n4", "age" : 23, "gender" : "f", "birth" : ISODate("1997-04-10T00:00:00Z"), "pswd" : "password123", "courses" : [ "Golang", "Gorm", "Gin" ] }
{ "_id" : ObjectId("601cf85c7cfdb08d65ff466c"), "name" : "n5", "age" : 24, "gender" : "m", "birth" : ISODate("1996-04-10T00:00:00Z"), "pswd" : "password123", "courses" : [ "Golang", "Gorm", "Gin" ] }
> db.users.ensureIndex({"courses": 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.users.getIndexKeys()
[ { "_id" : 1 }, { "name" : 1, "age" : -1 }, { "courses" : 1 } ]
// 查询多key索引
> db.users.find({"courses": "Gin"}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"courses" : {
"$eq" : "Gin"
}
},
"queryHash" : "D2FFAE50",
"planCacheKey" : "422675F2",
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"courses" : 1
},
"indexName" : "courses_1",
"isMultiKey" : true, // 多key索引
"multiKeyPaths" : {
"courses" : [
"courses"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"courses" : [
"[\"Gin\", \"Gin\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "bogon",
"port" : 27017,
"version" : "4.2.12",
"gitVersion" : "5593fd8e33b60c75802edab304e23998fa0ce8a5"
},
"ok" : 1
}