mongoDB的index和我们常见的RDBMS的index差不多,目的都是为了提高查询性能。
在mongoDB中,除了常见的B-Tree索引,还有Geospatial索引(一般用于定位搜索)。
B-Tree索引支持在key,内嵌key,内嵌文档,array等上面建索引,支持复合索引(compound index),唯一索引;在1.7.4以后还引入了稀疏索引的概念.
范例 :
# 新建测试collection
> for (i=0;i<10000;i++) {
... db.userinfo.insert({"firstname" : "zhou","lastname" : "digoal" + "." + i,"age" : i})
... }
> db.userinfo.find().limit(10)
{ "_id" : ObjectId("4d23fdc69ae655253f811078"), "firstname" : "zhou", "lastname" : "digoal.0", "age" : 0 }
{ "_id" : ObjectId("4d23fdc69ae655253f811079"), "firstname" : "zhou", "lastname" : "digoal.1", "age" : 1 }
{ "_id" : ObjectId("4d23fdc69ae655253f81107a"), "firstname" : "zhou", "lastname" : "digoal.2", "age" : 2 }
{ "_id" : ObjectId("4d23fdc69ae655253f81107b"), "firstname" : "zhou", "lastname" : "digoal.3", "age" : 3 }
{ "_id" : ObjectId("4d23fdc69ae655253f81107c"), "firstname" : "zhou", "lastname" : "digoal.4", "age" : 4 }
{ "_id" : ObjectId("4d23fdc69ae655253f81107d"), "firstname" : "zhou", "lastname" : "digoal.5", "age" : 5 }
{ "_id" : ObjectId("4d23fdc69ae655253f81107e"), "firstname" : "zhou", "lastname" : "digoal.6", "age" : 6 }
{ "_id" : ObjectId("4d23fdc69ae655253f81107f"), "firstname" : "zhou", "lastname" : "digoal.7", "age" : 7 }
{ "_id" : ObjectId("4d23fdc69ae655253f811080"), "firstname" : "zhou", "lastname" : "digoal.8", "age" : 8 }
{ "_id" : ObjectId("4d23fdc69ae655253f811081"), "firstname" : "zhou", "lastname" : "digoal.9", "age" : 9 }
# 当前只有_id的默认索引
> db.system.indexes.find({"ns" : "test.userinfo"})
{ "name" : "_id_", "ns" : "test.userinfo", "key" : { "_id" : 1 } }
1. basic usage
# 全表扫描
> db.userinfo.find({"lastname" : "digoal.0"})
{ "_id" : ObjectId("4d23fdc69ae655253f811078"), "firstname" : "zhou", "lastname" : "digoal.0", "age" : 0 }
> db.userinfo.find({"lastname" : "digoal.0"}).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 10000,
"nscannedObjects" : 10000,
"n" : 1,
"millis" : 5,
"indexBounds" : {
}
}
# nscanned 扫描了多少条记录,n返回多少条记录,cursor扫描方法.
# create index on lastname key
# 正向索引
> db.userinfo.ensureIndex({"lastname" : 1})
> db.system.indexes.find({"ns" : "test.userinfo"})
{ "name" : "_id_", "ns" : "test.userinfo", "key" : { "_id" : 1 } }
{ "_id" : ObjectId("4d2402a39ae655253f813789"), "ns" : "test.userinfo", "key" : { "lastname" : 1 }, "name" : "lastname_1" }
# 反向索引
> db.userinfo.ensureIndex({"lastname" : -1})
> db.system.indexes.find({"ns" : "test.userinfo"})
{ "name" : "_id_", "ns" : "test.userinfo", "key" : { "_id" : 1 } }
{ "_id" : ObjectId("4d2402a39ae655253f813789"), "ns" : "test.userinfo", "key" : { "lastname" : 1 }, "name" : "lastname_1" }
{ "_id" : ObjectId("4d2402ad9ae655253f81378a"), "ns" : "test.userinfo", "key" : { "lastname" : -1 }, "name" : "lastname_-1" }
# 重复建同样的索引,mongoDB不会干任何事情
> db.userinfo.ensureIndex({"lastname" : -1})
> db.system.indexes.find({"ns" : "test.userinfo"})
{ "name" : "_id_", "ns" : "test.userinfo", "key" : { "_id" : 1 } }
{ "_id" : ObjectId("4d2402a39ae655253f813789"), "ns" : "test.userinfo", "key" : { "lastname" : 1 }, "name" : "lastname_1" }
{ "_id" : ObjectId("4d2402ad9ae655253f81378a"), "ns" : "test.userinfo", "key" : { "lastname" : -1 }, "name" : "lastname_-1" }
> db.getLastError()
null
# but that's ok(驱动列一样的两个索引)
> db.system.indexes.find({"ns" : "test.userinfo"})
{ "name" : "_id_", "ns" : "test.userinfo", "key" : { "_id" : 1 } }
{ "_id" : ObjectId("4d24050e9ae655253f813790"), "ns" : "test.userinfo", "key" : { "lastname" : 1 }, "name" : "lastname_1" }
{ "_id" : ObjectId("4d24051a9ae655253f813791"), "ns" : "test.userinfo", "key" : { "lastname" : 1, "age" : 1 }, "name" : "lastname_1_age_1" }
# 执行计划测试
# 单个lastname条件时走lastname_1索引
> db.userinfo.find({"lastname" : "digoal.0"}).explain()
{
"cursor" : "BtreeCursor lastname_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"indexBounds" : {
"lastname" : [
[
"digoal.0",
"digoal.0"
]
]
}
}
# 复合条件"lastname" : "digoal.0","age" : 0走lastname_1_age_1索引
> db.userinfo.find({"lastname" : "digoal.0","age" : 0}).explain()
{
"cursor" : "BtreeCursor lastname_1_age_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"indexBounds" : {
"lastname" : [
[
"digoal.0",
"digoal.0"
]
],
"age" : [
[
0,
0
]
]
}
}
# 非驱动列查询条件不走索引
> db.userinfo.find({"age" : 0}).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 10000,
"nscannedObjects" : 10000,
"n" : 1,
"millis" : 5,
"indexBounds" : {
}
}
> db.userinfo.find({"firstname" : "zhou"}).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 10000,
"nscannedObjects" : 10000,
"n" : 10000,
"millis" : 5,
"indexBounds" : {
}
}
# 排序与索引,单KEY索引正向反向排序输出都可以走索引,复合索引需要注意,索引KEY必须同时被正或被反,否则不可以走索引.
> db.userinfo.find().sort({"lastname" : 1}).explain()
{
"cursor" : "BtreeCursor lastname_1",
"nscanned" : 10000,
"nscannedObjects" : 10000,
"n" : 10000,
"millis" : 6,
"indexBounds" : {
"lastname" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}
> db.userinfo.find().sort({"lastname" : -1}).explain()
{
"cursor" : "BtreeCursor lastname_1 reverse",
"nscanned" : 10000,
"nscannedObjects" : 10000,
"n" : 10000,
"millis" : 6,
"indexBounds" : {
"lastname" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
> db.userinfo.find().sort({"lastname" : 1,"age" : 1}).explain()
{
"cursor" : "BtreeCursor lastname_1_age_1",
"nscanned" : 10000,
"nscannedObjects" : 10000,
"n" : 10000,
"millis" : 8,
"indexBounds" : {
"lastname" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"age" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}
> db.userinfo.find().sort({"lastname" : -1,"age" : -1}).explain()
{
"cursor" : "BtreeCursor lastname_1_age_1 reverse",
"nscanned" : 10000,
"nscannedObjects" : 10000,
"n" : 10000,
"millis" : 8,
"indexBounds" : {
"lastname" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"age" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
}
}
> db.userinfo.find().sort({"lastname" : -1,"age" : 1}).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 10000,
"nscannedObjects" : 10000,
"n" : 10000,
"scanAndOrder" : true,
"millis" : 51,
"indexBounds" : {
}
}
> db.userinfo.find().sort({"lastname" : 1,"age" : -1}).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 10000,
"nscannedObjects" : 10000,
"n" : 10000,
"scanAndOrder" : true,
"millis" : 51,
"indexBounds" : {
}
}
2. 内嵌KEY索引
# 新建测试数据
for (i=0;i<1000;i++) {
db.userinfo1.insert({"firstname" : "zhou","lastname" : "digoal","age" : 27,"other" : {"corp" : "sky-mobi","city" : "hangzhou","phone" : i}})
}
# 内嵌KEY索引
> db.userinfo1.ensureIndex({"other.phone" : 1})
> db.userinfo1.getIndexes()
[
{
"name" : "_id_",
"ns" : "test.userinfo1",
"key" : {
"_id" : 1
}
},
{
"_id" : ObjectId("4d2408e59ae655253f813b7c"),
"ns" : "test.userinfo1",
"key" : {
"other.phone" : 1
},
"name" : "other.phone_1"
}
]
# 执行计划,走索引other.phone_1,注意到索引的长度是内嵌文档+.$key_正或反,所以索引的名字
> db.userinfo1.find({"other.phone" : 0}).explain()
{
"cursor" : "BtreeCursor other.phone_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"indexBounds" : {
"other.phone" : [
[
0,
0
]
]
}
}
# 使用自定义索引名字:phone,由于mongoDB中namespace的长度是受限制的127,所以选择适当的名字是非常有必要的
> db.userinfo1.dropIndex({"other.phone" : 1})
{ "nIndexesWas" : 2, "ok" : 1 }
> db.userinfo1.ensureIndex({"other.phone" : 1},{"name" : "phone"})
> db.userinfo1.find({"other.phone" : 0}).explain()
{
"cursor" : "BtreeCursor phone",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"indexBounds" : {
"other.phone" : [
[
0,
0
]
]
}
}
# 内嵌KEY索引也可以做复合索引
3. 内嵌文档索引
# 拿上面的表继续测试
> db.userinfo1.ensureIndex({"other" : 1},{"name" : "ebd"})
> db.userinfo1.getIndexes()
[
{
"name" : "_id_",
"ns" : "test.userinfo1",
"key" : {
"_id" : 1
}
},
{
"_id" : ObjectId("4d2409989ae655253f813b7d"),
"ns" : "test.userinfo1",
"key" : {
"other.phone" : 1
},
"name" : "phone"
},
{
"_id" : ObjectId("4d240a0f9ae655253f813b7e"),
"ns" : "test.userinfo1",
"key" : {
"other" : 1
},
"name" : "ebd"
}
]
# 测试文档索引的使用,只有当使用文档类型时才走索引,直接引用内嵌KEY是无法走内嵌文档索引的.
# 这点和array的索引不太一样,array的索引是建立在array的每个值上面的.
> db.userinfo1.find({"other.corp" : "sky-mobi"}).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 1001,
"nscannedObjects" : 1001,
"n" : 1001,
"millis" : 1,
"indexBounds" : {
}
}
> db.userinfo1.find({"other" : {"corp" : "sky-mobi"}}).explain()
{
"cursor" : "BtreeCursor ebd",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"indexBounds" : {
"other" : [
[
{
"corp" : "sky-mobi"
},
{
"corp" : "sky-mobi"
}
]
]
}
}
# 内嵌文档索引也可以被拆成复合内嵌KEY索引,这样的话引导KEY可以被执行计划使用。
3. array索引
# 参考我之前写的《mongoDB's Multikeys feature》
4. 复合索引
# 如在a,b,c上建索引
可以被条件
a
a,b
a,c
a,b,c使用
# 测试
> db.userinfo.drop()
true
> for (i=0;i<1000;i++) {
... db.userinfo.insert({"firstname" : "zhou","lastname" : "digoal" + "." + i,"age" : i})
... }
> db.userinfo.ensureIndex({"firstname" :1,"lastname" :1,"age" :1})
> db.userinfo.getIndexes()
[
{
"name" : "_id_",
"ns" : "test.userinfo",
"key" : {
"_id" : 1
}
},
{
"_id" : ObjectId("4d240cbf9ae655253f813f67"),
"ns" : "test.userinfo",
"key" : {
"firstname" : 1,
"lastname" : 1,
"age" : 1
},
"name" : "firstname_1_lastname_1_age_1"
}
]
> db.userinfo.find({"firstname" : "zhou"}).explain()
{
"cursor" : "BtreeCursor firstname_1_lastname_1_age_1",
"nscanned" : 1000,
"nscannedObjects" : 1000,
"n" : 1000,
"millis" : 1,
"indexBounds" : {
"firstname" : [
[
"zhou",
"zhou"
]
],
"lastname" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"age" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}
> db.userinfo.find({"firstname" : "zhou","lastname" : "digoal.0"}).explain()
{
"cursor" : "BtreeCursor firstname_1_lastname_1_age_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"indexBounds" : {
"firstname" : [
[
"zhou",
"zhou"
]
],
"lastname" : [
[
"digoal.0",
"digoal.0"
]
],
"age" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}
> db.userinfo.find({"firstname" : "zhou","lastname" : "digoal.0","age" : 0}).explain()
{
"cursor" : "BtreeCursor firstname_1_lastname_1_age_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"indexBounds" : {
"firstname" : [
[
"zhou",
"zhou"
]
],
"lastname" : [
[
"digoal.0",
"digoal.0"
]
],
"age" : [
[
0,
0
]
]
}
}
> db.userinfo.find({"firstname" : "zhou","age" : 0}).explain()
{
"cursor" : "BtreeCursor firstname_1_lastname_1_age_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 2,
"indexBounds" : {
"firstname" : [
[
"zhou",
"zhou"
]
],
"lastname" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"age" : [
[
0,
0
]
]
}
}
> db.userinfo.find({"lastname" : "digoal.0","age" : 0}).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 1000,
"nscannedObjects" : 1000,
"n" : 1,
"millis" : 0,
"indexBounds" : {
}
}
# 当然,如果你愿意的话使用hint强制索引也行
> db.userinfo.find({"lastname" : "digoal.0","age" : 0}).hint({"firstname" :1,"lastname" :1,"age" :1}).explain()
{
"cursor" : "BtreeCursor firstname_1_lastname_1_age_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"indexBounds" : {
"firstname" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"lastname" : [
[
"digoal.0",
"digoal.0"
]
],
"age" : [
[
0,
0
]
]
}
}
5. 松散索引,New in 1.7.4,以下来自官方网站介绍
A "sparse index" is an index that only includes documents with the indexed field.
Any document that is missing the sparsely indexed field will not be stored in the index; the index will therefor be sparse because of the missing documents when values are missing.
Sparse indexes, by definition, are not complete (for the collection) and behave differently than complete indexes. When using a "sparse index" for sorting (or possibly just filtering) some documents in the collection may not be returned. This is because only documents in the index will be returned
# 范例
db.people.ensureIndex({title : 1}, {sparse : true})
db.people.save({name:"Jim"})
db.people.save({name:"Sarah", title:"Princess"})
db.people.find({title:{$ne:null}}).sort({title:1}) // returns only Sarah
You can combine sparse with unique to produce a unique constraint that ignores documents with missing fields