MongoDB的索引(二)
四、Case Insesitive索引
1、语法
db.collection.createIndex(
{ "key" : 1 },
{ collation: {locale : <locale>,strength : <strength>}} )collation：指定语言规则
strength：指定比较规则（是否区分大小写），1-5，数组越大，比较规则越严格，默认等级为3
2、使用case insensitive索引不影响查询结果，但是会增加查询的资源消耗
3、当创建索引时候指定了locale和strength，需要在查询时候指定相同的locale和strength才能确保有效使用索引。
1）示例集合
> db.fruit.find()
{ "_id" : ObjectId("5d2af3815e1bc81c62ef58b6"), "type" : "apple" }
{ "_id" : ObjectId("5d2af3815e1bc81c62ef58b7"), "type" : "Apple" }
{ "_id" : ObjectId("5d2af3815e1bc81c62ef58b8"), "type" : "APPLE" }
db.fruit.createIndex( { type: 1},
{ collation: { locale: 'en', strength: 2 } } )2）查询结果
1.没有指定locale和strength，无法使用索引，查询记录为1
db.fruit.find( { type: "apple" } )
2.指定索引对应的locale和strength，使用索引，查询记录为2
db.fruit.find( { type: "apple" } ).collation( { locale: 'en', strength: 2 } )
3.指定非索引对应的locale和strength，不使用索引，返回记录为1
db.fruit.find( { type: "apple" } ).collation( { locale: 'en', strength: 1 } )4、当创建集合时指定了locale和strength，集合的索引和查询默认使用对应的locale和strength。
1）示例集合
> db.createCollection("names", { collation: { locale: 'en_US', strength: 2 } } )
> db.names.createIndex( { first_name: 1 } ) // inherits the default collation
> db.names.find()
{ "_id" : ObjectId("5d3023b1cd8afaa592e2398d"), "first_name" : "Betsy" }
{ "_id" : ObjectId("5d3023b1cd8afaa592e2398e"), "first_name" : "BETSY" }
{ "_id" : ObjectId("5d3023b1cd8afaa592e2398f"), "first_name" : "betsy" }2）查询结果
1.使用集合默认locale和strength，返回记录为3
db.names.find( { first_name: "betsy" } )
2.无法使用索引且只返回一条记录，因为strength默认为3
db.names.find( { first_name: "betsy" } ).collation( { locale: 'en_US' } )五、Sparse索引
1、语法
db.addresses.createIndex( { "xmpp_id": 1 }, { sparse: true } )2、稀疏索引的索引仅仅会覆盖包含索引字段的文档，所以根据稀疏索引来统计文档数/查询/排序时，可能得到的计算往往不是准确的
1）示例集合
> db.collection.find()
{ "_id" : 1, "y" : 1 }
{ "_id" : 2, "x" : 1, "y" : 2 }
{ "_id" : 3, "x" : 2 }
db.collection.createIndex( { x: 1 }, { sparse: true } );
db.collection.createIndex({ y: 1 });2）查询结果
> db.collection.find().count();
3
> db.collection.find().hint({ y: 1 }).count();
3
> db.collection.find().hint( { x: 1 } ).count(); //仅统计x字段存在的记录
23、2dsphere (version 2)、 2d,、geoHaystack、text indexes都默认为稀疏索引
4、2dsphere和text都可以创建为稀疏复合索引，这些稀疏复合索引是否存在仅仅依赖于geospatial/text索引字段
5、稀疏索引的查询
1）示例文档
> db.scores.find()
{ "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }
{ "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
{ "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }
> db.scores.createIndex( { score: 1 } , { sparse: true } )2）查询结果
#查询结果不会包含无score字段记录
> db.scores.find( { score: { $lt: 90 } } )
{ "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }6、稀疏索引的排序
1）示例文档
> db.scores.find()
{ "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }
{ "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
{ "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }
> db.scores.createIndex( { score: 1 } , { sparse: true } )2）查询结果
#COLLSCAN,未使用索引
> db.scores.find().sort( { score: -1 } )
{ "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }
{ "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
{ "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }
#强制使用稀疏索引后，得到记录只有2条
> db.scores.find().sort( { score: -1 } ).hint( { score: 1 } )
{ "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }
{ "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }7、稀疏索引 + 唯一索引，仅仅限制满足稀疏索引字段唯一，不包含稀疏索引字段的文档可重复
1）示例集合
db.scores.createIndex( { score: 1 } , { sparse: true, unique: true } )
> db.scores.find()
{ "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }
{ "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
{ "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }2）查询结果
> db.scores.insert( { "userid": "AAAAAAA", "score": 43 } )
WriteResult({ "nInserted" : 1 })
> db.scores.insert( { "userid": "BBBBBBB", "score": 34 } )
WriteResult({ "nInserted" : 1 })
> db.scores.insert( { "userid": "CCCCCCC" } )
WriteResult({ "nInserted" : 1 })
> db.scores.insert( { "userid": "DDDDDDD" } )
WriteResult({ "nInserted" : 1 })
>
> db.scores.insert( { "userid": "AAAAAAA", "score": 82 } )
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.scores index: score_1 dup key: { : 82.0 }"
}
})
> db.scores.insert( { "userid": "BBBBBBB", "score": 90 } )
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.scores index: score_1 dup key: { : 90.0 }"
}
})六、Single Field Indexes
{
"_id": ObjectId("570c04a4ad233577f97dc459"),
"score": 1034,
"location": { state: "NY", city: "New York" }
}1、语法：
db.records.createIndex( { score: 1 } )2、在嵌套字段上创建索引
db.records.createIndex( { "location.state": 1 } )可支持查询：
db.records.find( { "location.state": "CA" } )
db.records.find( { "location.city": "Albany", "location.state": "NY" } )3、在嵌套文档上创建索引
db.records.createIndex( { location: 1 } )可支持查询：
db.records.find( { location: { city: "New York", state: "NY" } } )需要注意的是：
对于以上查询，location嵌套字段的顺序与创建索引顺序不一致可以使用索引，但是查询结果记录为0，因为查询顺序
> db.records.find()
{ "_id" : ObjectId("570c04a4ad233577f97dc459"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }
>
> db.records.find( { location: { city: "New York", state: "NY" } } )
>
> db.records.find( { location: { state: "NY",city: "New York" } } )
{ "_id" : ObjectId("570c04a4ad233577f97dc459"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }
