1.索引介绍
> db.t1.find({"username":"user101"}).explain(){"queryPlanner" : {"plannerVersion" : 1,"namespace" : "suq.t1","indexFilterSet" : false,"parsedQuery" : {"username" : {"$eq" : "user101"}},"winningPlan" : {"stage" : "COLLSCAN","filter" : {"username" : {"$eq" : "user101"}},"direction" : "forward"},"rejectedPlans" : [ ]},"serverInfo" : {"host" : "mongodb1","port" : 27017,"version" : "3.2.6","gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"},"ok" : 1}
> db.t1.find({"username":"user101"}).explain("allPlansExecution")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "suq.t1",
"indexFilterSet" : false,
"parsedQuery" : {
"username" : {
"$eq" : "user101"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"username" : 1
},
"indexName" : "username_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"username" : [
"[\"user101\", \"user101\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"username" : 1
},
"indexName" : "username_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"username" : [
"[\"user101\", \"user101\"]"
]
},
"keysExamined" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
"allPlansExecution" : [ ]
},
"serverInfo" : {
"host" : "mongodb1",
"port" : 27017,
"version" : "3.2.6",
"gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"
},
"ok" : 1
}
> db.t1.ensureIndex({"username":1}){"createdCollectionAutomatically" : false,"numIndexesBefore" : 1,"numIndexesAfter" : 2,"ok" : 1}
"winningPlan" : {"stage" : "FETCH","inputStage" : {"stage" : "IXSCAN","keyPattern" : {"username" : 1},"indexName" : "username_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"username" : ["[\"user101\", \"user101\"]"]}}
2.复合索引
> db.t1.ensureIndex({"age":1,"username":1}){"createdCollectionAutomatically" : false,"numIndexesBefore" : 2,"numIndexesAfter" : 3,"ok" : 1}
"inputStage" : {"stage" : "IXSCAN","keyPattern" : {"age" : 1,"username" : 1},"indexName" : "age_1_username_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"age" : ["[MinKey, MaxKey]"],"username" : ["[MinKey, MaxKey]"]}
$操作符如何使用索引
索引和数组
> db.test4.findOne(){"_id" : ObjectId("573f390c9e178b5475b29d92"),"name" : "brent","comment" : [{"name" : "bob","content" : "good"},{"name" : "jack","content" : "repost"}]}
> db.test4.ensureIndex({"comment.name":1}){"createdCollectionAutomatically" : false,"numIndexesBefore" : 1,"numIndexesAfter" : 2,"ok" : 1}
> db.test3.find(){ "_id" : ObjectId("573e874c9e178b5475b29d8e"), "name" : "brent", "fav" : [ "game", "film", "read" ] }{ "_id" : ObjectId("573e87849e178b5475b29d8f"), "name" : "bob", "fav" : [ "drink", "football", "runing" ] }{ "_id" : ObjectId("573e87cc9e178b5475b29d90"), "name" : "jack", "fav" : [ "read", "basketball", "drink" ] }{ "_id" : ObjectId("573e8b719e178b5475b29d91"), "name" : "tom", "fav" : [ "chess", "cooking" ] }
> db.test3.ensureIndex({"fav":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
"isMultiKey" : true,
索引基数
3.使用explain()和hint()
db.products.explain().count( { quantity: { $gt: 50 } } )
db.products.explain("executionStats").find(
{ quantity: { $gt: 50 }, category: "apparel" }
)
db.products.explain("allPlansExecution").update({ quantity: { $lt: 1000}, category: "apparel" },{ $set: { reorder: true } })
> db.t1.explain("allPlansExecution").find({"username":"user101"}){"queryPlanner" : {"plannerVersion" : 1,"namespace" : "suq.t1","indexFilterSet" : false,"parsedQuery" : {"username" : {"$eq" : "user101"}},"winningPlan" : {"stage" : "FETCH","inputStage" : {"stage" : "IXSCAN","keyPattern" : {"username" : 1},"indexName" : "username_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"username" : ["[\"user101\", \"user101\"]"]}}},"rejectedPlans" : [ ]},"executionStats" : {"executionSuccess" : true,"nReturned" : 1,"executionTimeMillis" : 0,"totalKeysExamined" : 1,"totalDocsExamined" : 1,"executionStages" : {"stage" : "FETCH","nReturned" : 1,"executionTimeMillisEstimate" : 0,"works" : 2,"advanced" : 1,"needTime" : 0,"needYield" : 0,"saveState" : 0,"restoreState" : 0,"isEOF" : 1,"invalidates" : 0,"docsExamined" : 1,"alreadyHasObj" : 0,"inputStage" : {"stage" : "IXSCAN","nReturned" : 1,"executionTimeMillisEstimate" : 0,"works" : 2,"advanced" : 1,"needTime" : 0,"needYield" : 0,"saveState" : 0,"restoreState" : 0,"isEOF" : 1,"invalidates" : 0,"keyPattern" : {"username" : 1},"indexName" : "username_1","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"direction" : "forward","indexBounds" : {"username" : ["[\"user101\", \"user101\"]"]},"keysExamined" : 1,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}},"allPlansExecution" : [ ]},"serverInfo" : {"host" : "mongodb1","port" : 27017,"version" : "3.2.6","gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"},"ok" : 1}
> db.t1.find({"username":"user11"}).hint({"age":1,"username":1}).explain()
4.索引类型
| Parameter | Type | Description |
|---|---|---|
| background | boolean | Optional. Builds the index in the background so that building an index does not block other database activities. Specify true to build in the background. The default value is false. |
| unique | boolean | Optional. Creates a unique index so that the collection will not accept insertion of documents where the index key or keys match an existing value in the index. Specify true to create a unique index. The default value is false. The option is unavailable for hashed indexes. |
| name | string | Optional. The name of the index. If unspecified, MongoDB generates an index name by concatenating the names of the indexed fields and the sort order. Whether user specified or MongoDB generated, index names including their full namespace (i.e. database.collection) cannot be longer than the Index Name Limit. |
| partialFilterExpression | document | Optional. If specified, the index only references documents that match the filter expression. See Partial Indexes for more information. A filter expression can include:
You can specify a partialFilterExpression option for all MongoDB index types. New in version 3.2. |
| sparse | boolean | Optional. If true, the index only references documents with the specified field. These indexes use less space but behave differently in some situations (particularly sorts). The default value is false. SeeSparse Indexes for more information. Changed in version 3.2: Starting in MongoDB 3.2, MongoDB provides the option to create partial indexes. Partial indexes offer a superset of the functionality of sparse indexes. If you are using MongoDB 3.2 or later, partial indexes should be preferred over sparse indexes. Changed in version 2.6: 2dsphere indexes are sparse by default and ignore this option. For a compound index that includes 2dsphere index key(s) along with keys of other types, only the 2dsphere index fields determine whether the index references a document. 2d, geoHaystack, and text indexes behave similarly to the 2dsphereindexes. |
| expireAfterSeconds | integer | Optional. Specifies a value, in seconds, as a TTL to control how long MongoDB retains documents in this collection. See Expire Data from Collections by Setting TTL for more information on this functionality. This applies only to TTL indexes. |
| storageEngine | document | Optional. Allows users to specify configuration to the storage engine on a per-index basis when creating an index. The value of thestorageEngine option should take the following form: { <storage-engine-name>: <options> }
Storage engine configuration specified when creating indexes are validated and logged to the oplog during replication to support replica sets with members that use different storage engines. New in version 3.0. |
5.索引管理
| Name | Description |
|---|---|
| db.collection.createIndex() | Builds an index on a collection. |
| db.collection.dropIndex() | Removes a specified index on a collection. |
| db.collection.dropIndexes() | Removes all indexes on a collection. |
| db.collection.getIndexes() | Returns an array of documents that describe the existing indexes on a collection. |
| db.collection.reIndex() | Rebuilds all existing indexes on a collection. |
| db.collection.totalIndexSize() | Reports the total size used by the indexes on a collection. Provides a wrapper around the totalIndexSize field of the collStats output. |
| cursor.explain() | Reports on the query execution plan for a cursor. |
| cursor.hint() | Forces MongoDB to use a specific index for a query. |
| cursor.max() | Specifies an exclusive upper index bound for a cursor. For use withcursor.hint() |
| cursor.min() | Specifies an inclusive lower index bound for a cursor. For use withcursor.hint() |
| cursor.snapshot() | Forces the cursor to use the index on the _id field. Ensures that the cursor returns each document, with regards to the value of the _id field, only once. |
Indexing Database Commands
| Name | Description |
|---|---|
| createIndexes | Builds one or more indexes for a collection. |
| dropIndexes | Removes indexes from a collection. |
| compact | Defragments a collection and rebuilds the indexes. |
| reIndex | Rebuilds all indexes on a collection. |
| validate | Internal command that scans for a collection’s data and indexes for correctness. |
| geoNear | Performs a geospatial query that returns the documents closest to a given point. |
| geoSearch | Performs a geospatial query that uses MongoDB’s haystack index functionality. |
| checkShardingIndex | Internal command that validates index on shard key. |
Geospatial Query Selectors
| Name | Description |
|---|---|
| $geoWithin | Selects geometries within a bounding GeoJSON geometry. The 2dsphere and 2dindexes support $geoWithin. |
| $geoIntersects | Selects geometries that intersect with a GeoJSON geometry. The 2dsphere index supports $geoIntersects. |
| $near | Returns geospatial objects in proximity to a point. Requires a geospatial index. The2dsphere and 2d indexes support $near. |
| $nearSphere | Returns geospatial objects in proximity to a point on a sphere. Requires a geospatial index. The 2dsphere and 2d indexes support $nearSphere. |
Indexing Query Modifiers
| Name | Description |
|---|---|
| $explain | Forces MongoDB to report on query execution plans. See explain(). |
| $hint | Forces MongoDB to use a specific index. See hint() |
| $max | Specifies an exclusive upper limit for the index to use in a query. See max(). |
| $min | Specifies an inclusive lower limit for the index to use in a query. See min(). |
| $returnKey | Forces the cursor to only return fields included in the index. |
| $snapshot | Guarantees that a query returns each document no more than once. Seesnapshot(). |
| Parameter | Type | Description |
|---|---|---|
| background | boolean | Optional. Builds the index in the background so that building an index does not block other database activities. Specify true to build in the background. The default value is false. |
| unique | boolean | Optional. Creates a unique index so that the collection will not accept insertion of documents where the index key or keys match an existing value in the index. Specify true to create a unique index. The default value is false. The option is unavailable for hashed indexes. |
| name | string | Optional. The name of the index. If unspecified, MongoDB generates an index name by concatenating the names of the indexed fields and the sort order. Whether user specified or MongoDB generated, index names including their full namespace (i.e. database.collection) cannot be longer than the Index Name Limit. |
| partialFilterExpression | document | Optional. If specified, the index only references documents that match the filter expression. See Partial Indexes for more information. A filter expression can include:
You can specify a partialFilterExpression option for all MongoDB index types. New in version 3.2. |
| sparse | boolean | Optional. If true, the index only references documents with the specified field. These indexes use less space but behave differently in some situations (particularly sorts). The default value is false. SeeSparse Indexes for more information. Changed in version 3.2: Starting in MongoDB 3.2, MongoDB provides the option to create partial indexes. Partial indexes offer a superset of the functionality of sparse indexes. If you are using MongoDB 3.2 or later, partial indexes should be preferred over sparse indexes. Changed in version 2.6: 2dsphere indexes are sparse by default and ignore this option. For a compound index that includes 2dsphere index key(s) along with keys of other types, only the 2dsphere index fields determine whether the index references a document. 2d, geoHaystack, and text indexes behave similarly to the 2dsphereindexes. |
| expireAfterSeconds | integer | Optional. Specifies a value, in seconds, as a TTL to control how long MongoDB retains documents in this collection. See Expire Data from Collections by Setting TTL for more information on this functionality. This applies only to TTL indexes. |
| storageEngine | document | Optional. Allows users to specify configuration to the storage engine on a per-index basis when creating an index. The value of thestorageEngine option should take the following form: { <storage-engine-name>: <options> }
Storage engine configuration specified when creating indexes are validated and logged to the oplog during replication to support replica sets with members that use different storage engines. New in version 3.0. |
> db.test2.createIndex({"name":1},{"background":true,"unique":true})
db.test2.createIndex({"name":1},{"name":"myindex1","background":true,"unique":true})
> db.test2.getIndexes()
db.test2.dropIndex("myindex1")
> db.test2.dropIndexes()
> db.test2.reIndex({"name":"name_1"})
> db.test2.totalIndexSize()32768
> db.test2.totalIndexSize({"name":"name_1"})
_id_ 16384
name_1 16384
32768
> db.runCommand({"dropIndexes":"test2",index:"myindex1"})
db.runCommand({createIndexes: <collection>,indexes: [{key: {<key-value_pair>,<key-value_pair>,...},name: <index_name>,<option1>,<option2>,...},{ ... },{ ... }]})
db.getSiblingDB("products").runCommand({createIndexes: "inventory",indexes: [{key: {item: 1,manufacturer: 1,model: 1},name: "item_manufacturer_model",unique: true},{key: {item: 1,supplier: 1,model: 1},name: "item_supplier_model",unique: true}]})
1564
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
