MongoDB的索引(三)
七、Compound Indexes
{
"item": "Banana",
"category": ["food", "produce", "grocery"],
"location": "4th Street Store",
"stock": 4,
"type": "cases"
}1、语法
db.collection.createIndex( { <field1>: <type>, <field2>: <type2>, ... } )2、复合索引中包含的一些隐式索引
{a:1,b:1,c:1} <=> {a:1}
<=> {a:1,b:1}
<=> {a:1,b:1,c:1}3、利用索引进行排序
示例：db.data.createIndex( { a:1, b: 1, c: 1, d: 1 } )
语句 | 使用索引 |
db.data.find().sort( { a: 1 } ) | { a: 1 } |
db.data.find().sort( { a: -1 } ) | { a: 1 } |
db.data.find().sort( { a: 1, b: 1 } ) | { a: 1, b: 1 } |
db.data.find().sort( { a: -1, b: -1 } ) | { a: 1, b: 1 } |
db.data.find().sort( { a: 1, b: 1, c: 1 } ) | { a: 1, b: 1, c: 1 } |
db.data.find( { a: { $gt: 4 } } ).sort( { a: 1, b: 1 } ) | { a: 1, b: 1 } |
db.data.find( { a: 5 } ).sort( { b: 1, c: 1 } ) | { a: 1 , b: 1, c: 1 } |
db.data.find( { b: 3, a: 4 } ).sort( { c: 1 } ) | { a: 1, b: 1, c: 1 } |
db.data.find( { a: 5, b: { $lt: 3} } ).sort( { b: 1 } ) | { a: 1, b: 1 } |
八、Multikey indexes
1、在数组上创建索引时，MongoDB会自动为该集合创建multikey索引。
2、由于multikey indexes会对数组中每个值做索引，所以如果该字段设置为unique multikey indexes，那需要保证该集合中index数组不能重复
1）示例集合
> db.cc.find()
{ "_id" : 6, "type" : "food", "item" : "bbb", "ratings" : [ 5, 9 ] }
> db.cc.createIndex({ratings:1},{unique:true})2）查询结果
> db.cc.insert({"_id" : 5, "type" : "food", "item" : "aaa", "ratings" : [ 1,2 ]})
WriteResult({ "nInserted" : 1 })
>
> db.cc.insert({"type" : "food", "item" : "aaa", "ratings" : [ 7,3 ]})
WriteResult({ "nInserted" : 1 })
>
> db.cc.insert({"type" : "food", "item" : "aaa", "ratings" : [ 7,4 ]})
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.cc index: ratings_1 dup key: { : 7.0 }"
}
})
> db.cc.insert({"type" : "food", "item" : "aaa", "ratings" : [ 6,9 ]})
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.cc index: ratings_1 dup key: { : 9.0 }"
}
})
> db.cc.insert({"type" : "food", "item" : "aaa", "ratings" : [ 6,8 ]})
WriteResult({ "nInserted" : 1 })
>
>
> db.cc.find()
{ "_id" : 6, "type" : "food", "item" : "bbb", "ratings" : [ 5, 9 ] }
{ "_id" : 5, "type" : "food", "item" : "aaa", "ratings" : [ 1, 2 ] }
{ "_id" : ObjectId("5d2e7c6dc5002cd792e912a9"), "type" : "food", "item" : "aaa", "ratings" : [ 7, 3 ] }
{ "_id" : ObjectId("5d2e7c8ac5002cd792e912ae"), "type" : "food", "item" : "aaa", "ratings" : [ 6, 8 ] }2、multikey indexes的一些限制
{ _id: 5, type: "food", item: "aaa", ratings: [ 5, 8, 9 ] }
{ _id: 6, type: "food", item: "bbb", ratings: [ 5, 9 ] }
{ _id: 7, type: "food", item: "ccc", ratings: [ 9, 5, 8 ] }
{ _id: 8, type: "food", item: "ddd", ratings: [ 9, 5 ] }
{ _id: 9, type: "food", item: "eee", ratings: [ 5, 9, 5 ] }1）不能同时在两个数组字段建立复合multikey indexes
2）由于MongoDB3.6版本对排序行为上做了一些改变，导致现在对multikey index进行排序时，查询计划包括一个阻塞排序的阶段，从而对性能产生影响。在排序阻塞阶段，必须等待所有输入完成才能进行排序然后输出结果；对于一个非阻塞排序或者索引排序，sort操作只需要扫描index产生一个有序的请求。
3）multikey indexes不能做分片键，但是，如果一个分片是复合索引的前缀，这个复合索引支持nultikey index。
4）hash索引不支持multikey index
5）multikey index不支持覆盖索引查询
6）multikey index无法使用$expr
3、db.inventory.find( { ratings: [ 5, 9 ] } )，对于该multikey index的查询，MongoDB通过索引查找出所有包含5的记录，然后过滤出[5,9]的记录
4、Multikey Index Bounds
db.survey.insertMany([
{ _id: 1, item: "ABC", ratings: [ 2, 9 ] },
{ _id: 2, item: "XYZ", ratings: [ 4, 3 ] }])
db.survey.createIndex( { ratings: 1 } )1）Intersect Bounds
db.survey.find( { ratings : { $elemMatch: { $gte: 3, $lte: 6 } } } )
<=> ratings: [ [ 3, 6 ] ]
执行计划：
"indexBounds" : {
"ratings" : [
"[3.0, 6.0]"
]
}2）不使用 $elemMatch的情况下MongoDB不会使用multikey inedex的交集
db.survey.find( { ratings : { $gte: 3, $lte: 6 } } )
<=> ratings: [ [ 3, Infinity ] ] or [ [ -Infinity, 6 ] ]
执行计划：
"indexBounds" : {
"ratings" : [
"[-inf.0, 6.0]"
]
}3）Compound Bounds - 等值查询
db.survey.find( { item: "XYZ", ratings: { $gte: 3 } } )
db.survey.createIndex( { item: 1, ratings: 1 } )
<=> ratings: { item: [ [ "XYZ", "XYZ" ] ], ratings: [ [ 3, Infinity ] ] }
执行计划：
"indexBounds" : {
"item" : [
"[\"XYZ\", \"XYZ\"]"
],
"ratings" : [
"[3.0, inf.0]"
]
}4）Compound Bounds - 范围查询
db.survey.find( {item: { $gte: "L", $lte: "Z"}, ratings : { $elemMatch: { $gte: 3, $lte: 6 } }} )
<=> ratings: "item" : [ [ "L", "Z" ] ], "ratings" : [ [3.0, 6.0] ]
执行计划：
"indexBounds" : {
"item" : [
"[\"L\", \"Z\"]"
],
"ratings" : [
"[3.0, 6.0]"
]
}5）Compound Bounds -
1.示例集合
> db.survey.insertMany([{ _id: 1, item: { name: "ABC", manufactured: 2016 }, ratings: [ 2, 9 ] },
{ _id: 2, item: { name: "XYZ", manufactured: 2013 }, ratings: [ 4, 3 ] }])
>
> db.survey.createIndex( { "item.name": 1, "item.manufactured": 1, ratings: 1 } )2.查询结果
> db.survey.find( { "item.name": "L" , "item.manufactured": 2012 } )
<=> "item.name" : [ ["L", "L"] ], "item.manufactured" : [ [2012.0, 2012.0] ]
执行计划：
"indexBounds" : {
"item.name" : [
"[\"L\", \"L\"]"
],
"item.manufactured" : [
"[2012.0, 2012.0]"
],
"ratings" : [
"[MinKey, MaxKey]"
]
}
