MongoDB 索引笔记
索引 (Index)
合适的索引可以大大提高数据库搜索性能
集合层面的索引
支持复合键索引可以对多个字段进行排序
复合索引：(A, B, C)
可以支持的索引：{A}, {A, B}, {A, B, C}
不可以支持的索引：{B}, {C}, {B, C}
- db.collection.getIndexes()
- db.collection.createIndex()
- db.collection.dropIndex()
- db.collection.dropIndexes()
- cursor.explain()
索引的类型
- 单键索引
- 复合键索引
- 多键索引 (针对数组字段)
索引的特性
- 唯一性
- 稀疏性
- 生存时间
查询分析
- 检视索引的效果 explain()
索引的选择
如何创建一个合适的索引
索引对数据库写入操作的影响
创建索引 db.collection.createIndex()
语法： db.collection.createIndex(keys, options, commitQuorum)
- keys 文档指定创建索引的字段
> db.accuntsWithIndex.find()
{ "_id" : ObjectId("635f20261b8b77383133f018"), "name" : "alice", "balance" : 50, "currency" : [ "GBP", "USD" ] }
{ "_id" : ObjectId("635f20261b8b77383133f019"), "name" : "bob", "balance" : 20, "currency" : [ "AUD", "USD" ] }
{ "_id" : ObjectId("635f20261b8b77383133f01a"), "name" : "bob", "balance" : 300, "currency" : [ "CNY" ] }
>
// 创建索引
> db.accuntsWithIndex.createIndex({name: 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>
> db.accuntsWithIndex.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"name" : 1
},
"name" : "name_1"
}
]
// 创建复合键索引
> db.accuntsWithIndex.createIndex({name: 1, balance: -1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.accuntsWithIndex.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"name" : 1
},
"name" : "name_1"
},
{
"v" : 2,
"key" : {
"name" : 1,
"balance" : -1
},
"name" : "name_1_balance_-1"
}
]
>
// 创建一个多键索引 针对数组字段
> db.accuntsWithIndex.createIndex({currency: 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 3,
"numIndexesAfter" : 4,
"ok" : 1
}
> db.accuntsWithIndex.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"name" : 1
},
"name" : "name_1"
},
{
"v" : 2,
"key" : {
"name" : 1,
"balance" : -1
},
"name" : "name_1_balance_-1"
},
{
"v" : 2,
"key" : {
"currency" : 1
},
"name" : "currency_1"
}
]
-
options 文档定义了创建索引时可以使用的一些参数，也可以设定索引的特性
- 索引的唯一性（_id 的索引就是唯一索引)
db.accuntsWithIndex.createIndex({balance: 1}, {unique: true})- 如果已有文档的某个字段有重复值，就不可以创建唯一索引，
- 如果新增文档不包含唯一性索引，则只有第一篇缺失该字段的文档可以写入数据库，索引中该文档的键值被默认为null
- 复合键的索引也可以具有唯一性，不同文档之间，所包含的复合键的组合，不可以重复
- 索引的唯一性（_id 的索引就是唯一索引)
-
索引的稀疏性 ``
- 只将包含索引键字段的文档加入到索引中（即使索引键字段为null）
db.accuntsWithIndex.createIndex({balance: 1}, {sparse: true})- 如果同一个索引既具有唯一性，又具有稀疏性，就可以保存 多篇 缺失索引键的文档了
- 复合键索引也可以具有稀疏性，只有在缺失复合键所有包含的字段的情况下，文档才不会被加入到索引中
-
索引的生存时间
- 针对日期字段，或者包含日期元素的数组字段，可以使用设定了生存时间的索引，来自动删除字段值超过生存时间的文档
db.accuntsWithIndex.createIndex({lastAccess: 1}, {expireAfterSeconds: 20}), lastAccess 与当前时间比较，超过20秒就被删除- 复合键索引 不具备 生存时间特性
- 当索引时包含日期元素的数组字段时，数组中 最小 的日期将被用来计算文档是否已经过期
- 数据库使用一个后台线程来监测和删除过期的文档，删除操作可能有一定的延迟
> db.accuntsWithIndex.createIndex({balance: 1}, {unique: true}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } > db.accuntsWithIndex.createIndex({name: 1}, {unique: true}) { "ok" : 0, "errmsg" : "Index build failed: de8b6096-0dd8-406b-a3c3-dc3911812710: Collection test.accuntsWithIndex ( dbe9339e-d1f8-4ed2-abe4-afce6ec06e7a ) :: caused by :: E11000 duplicate key error collection: test.accuntsWithIndex index: name_1 dup key: { name: \"bob\" }", "code" : 11000, "codeName" : "DuplicateKey", "keyPattern" : { "name" : 1 }, "keyValue" : { "name" : "bob" } } // 如果新增文档不包含唯一性索引，则只有**第一篇**缺失该字段的文档可以写入数据库，索引中该文档的键值被默认为null > db.accuntsWithIndex.find() { "_id" : ObjectId("635f20261b8b77383133f018"), "name" : "alice", "balance" : 50, "currency" : [ "GBP", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f019"), "name" : "bob", "balance" : 20, "currency" : [ "AUD", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f01a"), "name" : "bob", "balance" : 300, "currency" : [ "CNY" ] } > > db.accuntsWithIndex.insert({"name": "charlie", lastAccess: new Date()}) WriteResult({ "nInserted" : 1 }) > db.accuntsWithIndex.insert({"name": "david", lastAccess: new Date()}) WriteResult({ "nInserted" : 0, "writeError" : { "code" : 11000, "errmsg" : "E11000 duplicate key error collection: test.accuntsWithIndex index: balance_1 dup key: { balance: null }" } }) > db.accuntsWithIndex.find() { "_id" : ObjectId("635f20261b8b77383133f018"), "name" : "alice", "balance" : 50, "currency" : [ "GBP", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f019"), "name" : "bob", "balance" : 20, "currency" : [ "AUD", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f01a"), "name" : "bob", "balance" : 300, "currency" : [ "CNY" ] } { "_id" : ObjectId("635ff408a5d828642b37ded7"), "name" : "charlie", "lastAccess" : ISODate("2022-10-31T16:12:56.554Z") } // 索引的稀疏性 > db.accuntsWithIndex.createIndex({balance: 1}, {sparse: true}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.accuntsWithIndex.getIndexes() [ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" }, { "v" : 2, "key" : { "balance" : 1 }, "name" : "balance_1", "sparse" : true } ] // 如果同一个索引既具有唯一性，又具有稀疏性，就可以保存 **多篇** 缺失索引键的文档了 > db.accuntsWithIndex.createIndex({balance: 1}, {unique: true, sparse: true}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.accuntsWithIndex.getIndexes() [ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" }, { "v" : 2, "unique" : true, "key" : { "balance" : 1 }, "name" : "balance_1", "sparse" : true } ] > db.accuntsWithIndex.find() { "_id" : ObjectId("635f20261b8b77383133f018"), "name" : "alice", "balance" : 50, "currency" : [ "GBP", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f019"), "name" : "bob", "balance" : 20, "currency" : [ "AUD", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f01a"), "name" : "bob", "balance" : 300, "currency" : [ "CNY" ] } { "_id" : ObjectId("635ff408a5d828642b37ded7"), "name" : "charlie", "lastAccess" : ISODate("2022-10-31T16:12:56.554Z") } > > db.accuntsWithIndex.insert({"name": "david", lastAccess: new Date()}) WriteResult({ "nInserted" : 1 }) > db.accuntsWithIndex.find() { "_id" : ObjectId("635f20261b8b77383133f018"), "name" : "alice", "balance" : 50, "currency" : [ "GBP", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f019"), "name" : "bob", "balance" : 20, "currency" : [ "AUD", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f01a"), "name" : "bob", "balance" : 300, "currency" : [ "CNY" ] } { "_id" : ObjectId("635ff408a5d828642b37ded7"), "name" : "charlie", "lastAccess" : ISODate("2022-10-31T16:12:56.554Z") } { "_id" : ObjectId("635ff7eda5d828642b37deda"), "name" : "david", "lastAccess" : ISODate("2022-10-31T16:29:33.427Z") } > db.accuntsWithIndex.find() { "_id" : ObjectId("635f20261b8b77383133f018"), "name" : "alice", "balance" : 50, "currency" : [ "GBP", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f019"), "name" : "bob", "balance" : 20, "currency" : [ "AUD", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f01a"), "name" : "bob", "balance" : 300, "currency" : [ "CNY" ] } { "_id" : ObjectId("635ff408a5d828642b37ded7"), "name" : "charlie", "lastAccess" : ISODate("2022-10-31T16:12:56.554Z") } { "_id" : ObjectId("635ff7eda5d828642b37deda"), "name" : "david", "lastAccess" : ISODate("2022-10-31T16:29:33.427Z") } > > db.accuntsWithIndex.createIndex({lastAccess: 1}, {expireAfterSeconds: 20}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } > db.accuntsWithIndex.find() { "_id" : ObjectId("635f20261b8b77383133f018"), "name" : "alice", "balance" : 50, "currency" : [ "GBP", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f019"), "name" : "bob", "balance" : 20, "currency" : [ "AUD", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f01a"), "name" : "bob", "balance" : 300, "currency" : [ "CNY" ] } // 插入新的文档 > db.accuntsWithIndex.insert({"name": "david", lastAccess: new Date()}) WriteResult({ "nInserted" : 1 }) > > db.accuntsWithIndex.find() { "_id" : ObjectId("635f20261b8b77383133f018"), "name" : "alice", "balance" : 50, "currency" : [ "GBP", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f019"), "name" : "bob", "balance" : 20, "currency" : [ "AUD", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f01a"), "name" : "bob", "balance" : 300, "currency" : [ "CNY" ] } { "_id" : ObjectId("635ffb7ba5d828642b37dedb"), "name" : "david", "lastAccess" : ISODate("2022-10-31T16:44:43.770Z") } > // 等待20s 后文档被删除 > db.accuntsWithIndex.find() { "_id" : ObjectId("635f20261b8b77383133f018"), "name" : "alice", "balance" : 50, "currency" : [ "GBP", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f019"), "name" : "bob", "balance" : 20, "currency" : [ "AUD", "USD" ] } { "_id" : ObjectId("635f20261b8b77383133f01a"), "name" : "bob", "balance" : 300, "currency" : [ "CNY" ] }
索引的效果 cursor.explain()
索引对查询操作的效果
> db.accuntsWithIndex.find({balance: 50}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.accuntsWithIndex",
"indexFilterSet" : false,
"parsedQuery" : {
"balance" : {
"$eq" : 50
}
},
"queryHash" : "88DDD986",
"planCacheKey" : "9238DC63",
"winningPlan" : {
"stage" : "COLLSCAN", // collection scan 查询效率低
"filter" : {
"balance" : {
"$eq" : 50
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "29ff2844a258",
"port" : 27017,
"version" : "4.4.16",
"gitVersion" : "a7bceadbac919a2c035f2874c61d138fd75d6a6f"
},
"ok" : 1
}
> db.accuntsWithIndex.find({balance: 50}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.accuntsWithIndex",
"indexFilterSet" : false,
"parsedQuery" : {
"balance" : {
"$eq" : 50
}
},
"queryHash" : "88DDD986",
"planCacheKey" : "9238DC63",
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"balance" : {
"$eq" : 50
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "29ff2844a258",
"port" : 27017,
"version" : "4.4.16",
"gitVersion" : "a7bceadbac919a2c035f2874c61d138fd75d6a6f"
},
"ok" : 1
}
>
> db.accuntsWithIndex.find({name: "bob"}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.accuntsWithIndex",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "bob"
}
},
"queryHash" : "01AEE5EC",
"planCacheKey" : "0BE5F32C",
"winningPlan" : {
"stage" : "FETCH", // 通过 index，获取查询结果，如果结果中只包含索引值，则不会进行fetch操作
"inputStage" : {
"stage" : "IXSCAN", // index scan 效率比较高
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"bob\", \"bob\"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1,
"balance" : -1
},
"indexName" : "name_1_balance_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"balance" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"bob\", \"bob\"]"
],
"balance" : [
"[MaxKey, MinKey]"
]
}
}
}
]
},
"serverInfo" : {
"host" : "29ff2844a258",
"port" : 27017,
"version" : "4.4.16",
"gitVersion" : "a7bceadbac919a2c035f2874c61d138fd75d6a6f"
},
"ok" : 1
}
>
// 通过 index，获取查询结果，如果结果中只包含索引值，则不会进行fetch操作
> db.accuntsWithIndex.find({name: "bob"}, {_id: 0, name: 1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.accuntsWithIndex",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "bob"
}
},
"queryHash" : "3066FB64",
"planCacheKey" : "A8F8C110",
"winningPlan" : {
"stage" : "PROJECTION_COVERED",
"transformBy" : {
"_id" : 0,
"name" : 1
},
"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" : [
"[\"bob\", \"bob\"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION_COVERED",
"transformBy" : {
"_id" : 0,
"name" : 1
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1,
"balance" : -1
},
"indexName" : "name_1_balance_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"balance" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"bob\", \"bob\"]"
],
"balance" : [
"[MaxKey, MinKey]"
]
}
}
}
]
},
"serverInfo" : {
"host" : "29ff2844a258",
"port" : 27017,
"version" : "4.4.16",
"gitVersion" : "a7bceadbac919a2c035f2874c61d138fd75d6a6f"
},
"ok" : 1
}
索引对排序操作的效果
- 排序与索引一致
- 排序与索引不一致
// 排序与索引一致
> db.accuntsWithIndex.find().sort({name: 1, balance: -1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.accuntsWithIndex",
"indexFilterSet" : false,
"parsedQuery" : {
},
"queryHash" : "DC9EFEDE",
"planCacheKey" : "DC9EFEDE",
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN", // 使用索引排序，然后fetch 文档
"keyPattern" : {
"name" : 1,
"balance" : -1
},
"indexName" : "name_1_balance_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"balance" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[MinKey, MaxKey]"
],
"balance" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "29ff2844a258",
"port" : 27017,
"version" : "4.4.16",
"gitVersion" : "a7bceadbac919a2c035f2874c61d138fd75d6a6f"
},
"ok" : 1
}
// 排序与索引不一致
> db.accuntsWithIndex.find().sort({name: 1, balance: 1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.accuntsWithIndex",
"indexFilterSet" : false,
"parsedQuery" : {
},
"queryHash" : "797A24CD",
"planCacheKey" : "797A24CD",
"winningPlan" : {
"stage" : "SORT", // 先加载索引文档到内容，让排序，耗费内存，效率不高
"sortPattern" : {
"name" : 1,
"balance" : 1
},
"memLimit" : 104857600,
"type" : "simple",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "29ff2844a258",
"port" : 27017,
"version" : "4.4.16",
"gitVersion" : "a7bceadbac919a2c035f2874c61d138fd75d6a6f"
},
"ok" : 1
}
>
删除索引 db.collection.dropIndex()
- 如果需要更改某些字段上已经创建的索引，必须先删除原索引，再重新创建新索引
- 使用索引名称删除索引
- 使用索引定义删除索引
// 列出集合中索引
> db.accuntsWithIndex.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"name" : 1
},
"name" : "name_1"
},
{
"v" : 2,
"key" : {
"name" : 1,
"balance" : -1
},
"name" : "name_1_balance_-1"
},
{
"v" : 2,
"key" : {
"currency" : 1
},
"name" : "currency_1"
}
]
// 删除 'name_1' 索引
> db.accuntsWithIndex.dropIndex("name_1")
{ "nIndexesWas" : 4, "ok" : 1 }
>
> db.accuntsWithIndex.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"name" : 1,
"balance" : -1
},
"name" : "name_1_balance_-1"
},
{
"v" : 2,
"key" : {
"currency" : 1
},
"name" : "currency_1"
}
]
// 使用索引定义删除索引
> db.accuntsWithIndex.dropIndex({"name": 1, "balance": -1})
{ "nIndexesWas" : 3, "ok" : 1 }
> db.accuntsWithIndex.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"currency" : 1
},
"name" : "currency_1"
}
]
>
