mongoDB中collections中会有默认索引 _id索引,
单键索引
# 在name属性上添加索引
db.student.createIndex({name:1})
# 查看查询学生名为 学生1 的执行计划
db.student.find({name:"学生1"}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "kaka.student",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "学生1"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"学生1\", \"学生1\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "iz8vb4efgq8tzqwlylia5uz",
"port" : 27017,
"version" : "4.0.5",
"gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412"
},
"ok" : 1
}
多键索引
# 添加在数据类型为数组上的索引
db.student.createIndex({subject: 1})
# 查看执行计划
db.student.find({"subject": "chinese"}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "kaka.student",
"indexFilterSet" : false,
"parsedQuery" : {
"subject" : {
"$eq" : "chinese"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"subject" : 1
},
"indexName" : "subject_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"subject" : [
"subject"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"subject" : [
"[\"chinese\", \"chinese\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "iz8vb4efgq8tzqwlylia5uz",
"port" : 27017,
"version" : "4.0.5",
"gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412"
},
"ok" : 1
}
复合索引
# 多键值
db.student.createIndex({name:1, age: -1})
# 查询
# 查询索引中第一个字段
db.student.find({name:"学生1"}).explain(); # 使用索引
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "kaka.student",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "学生1"
}
},
"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" : [
"[\"学生1\", \"学生1\"]"
],
"age" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "iz8vb4efgq8tzqwlylia5uz",
"port" : 27017,
"version" : "4.0.5",
"gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412"
},
"ok" : 1
}
db.student.find({age:18}).explain() # 不使用索引
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "kaka.student",
"indexFilterSet" : false,
"parsedQuery" : {
"age" : {
"$eq" : 18
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"age" : {
"$eq" : 18
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "iz8vb4efgq8tzqwlylia5uz",
"port" : 27017,
"version" : "4.0.5",
"gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412"
},
"ok" : 1
}
# 按照索引中字段顺序查询 使用索引
db.student.find({name:"学生1", age:{$gt:18}}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "kaka.student",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"name" : {
"$eq" : "学生1"
}
},
{
"age" : {
"$gt" : 18
}
}
]
},
"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" : [
"[\"学生1\", \"学生1\"]"
],
"age" : [
"[inf.0, 18.0)"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "iz8vb4efgq8tzqwlylia5uz",
"port" : 27017,
"version" : "4.0.5",
"gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412"
},
"ok" : 1
}
# 调换查询的字段顺序 使用索引
db.student.find({age:{$gt:18},name:"学生1"}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "kaka.student",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"name" : {
"$eq" : "学生1"
}
},
{
"age" : {
"$gt" : 18
}
}
]
},
"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" : [
"[\"学生1\", \"学生1\"]"
],
"age" : [
"[inf.0, 18.0)"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "iz8vb4efgq8tzqwlylia5uz",
"port" : 27017,
"version" : "4.0.5",
"gitVersion" : "3739429dd92b92d1b0ab120911a23d50bf03c412"
},
"ok" : 1
}
排序场景
"key" : {
"name" : 1,
"age" : -1
},
"name" : "name_1_age_-1",
# 按照名称排序
db.student.find().sort({name:1}).explain() #走索引
db.student.find().sort({name:-1}).explain() #走索引
db.student.find().sort({age:-1}).explain() #不走索引
db.student.find().sort({age:1}).explain() #不走索引
db.student.find().sort({name:1, age: -1}).explain() #走索引
db.student.find().sort({name:1, age: 1}).explain() #不走索引
db.student.find().sort({name:-1, age: 1}).explain() #走索引
db.student.find().sort({name:-1, age: -1}).explain() #不走
db.student.find().sort({age: -1, name:1}).explain() #不走
db.student.find().sort({age: -1, name: -1}).explain() #不走
db.student.find().sort({age: 1, name: -1}).explain() #不走
db.student.find().sort({age: 1, name: 1}).explain() #不走
全文索引
# 加在字段属性为字符串的字段上
# 添加数据
db.project.insert(
[ { _id: 1, name: "Java Script", description: "name is js and jquery" },
{ _id: 2, name: "Git", description: "Git is a free and open source distributed version control system designed to handle everything from small to very large projects with speed and efficiency" },
{ _id: 3, name: "Apache dubbo", description: "Apache Dubbo is a high-performance, java based open source RPC framework.阿里 开源 项目" },
{ _id: 4, name: "Redis", description: "Redis is an open source (BSD licensed), in-memory data structure store, used as a database, cache and message broker. It supports data structures" },
{ _id: 5, name: "Apache ZooKeeper", description: "Apache ZooKeeper is an effort to develop and maintain an open-source server which enables highly reliable distributed coordination" }
]
)
# 多关键词查询
db.project.find({$text:{$search:"java dubbo"}})
{ "_id" : 3, "name" : "Apache dubbo", "description" : "Apache Dubbo is a high-performance, java based open source RPC framework.阿里 开源 项目" }
{ "_id" : 1, "name" : "Java Script", "description" : "name is js and jquery" }
# 由于id为3的是通过java和dubbo匹配查询到的,所以权重更高
#使用-屏蔽关键字
db.project.find({$text:{$search:"java -dubbo"}})
{ "_id" : 1, "name" : "Java Script", "description" : "name is js and jquery" }
# 查询短语,\" 包含即可
db.project.find({$text:{$search:"\"Apache Dubbo\""}})
{ "_id" : 3, "name" : "Apache dubbo", "description" : "Apache Dubbo is a high-performance, java based open source RPC framework.阿里 开源 项目" }
过期索引
# 过期索引设置在时间列上,过期后删除数据
# 创建索引
db.log.createIndex({"createTime":1},{expireAfterSeconds:10})
# 插入数据
db.log.insert({"title":"this is logger info","createTime":new Date()})
# 立即查看数据
db.log.find()
{ "_id" : ObjectId("5dcd657d64782a209176b919"), "title" : "this is logger info", "createTime" : ISODate("2019-11-14T14:32:29.321Z") }
# 10秒后查看数据
> db.log.find()
>
db.log.insert({"title":"this is logger info","createTime":Date()})
# 时间字符串是删除不了的