MongoDB入门到进阶笔记：05-MongoDB索引查询与建立 createIndex getIndexes dropIndex 性能优化
mongodb数据准备
use shijiange
for(i=1; i<=500000;i++){
db.myuser.insert( {name:’mytest’+i, age:i} )
}
for(i=1; i<=500000;i++){
… db.myuser.insert( {name:’mytest’+i, age:i} )
… }
WriteResult({ “nInserted” : 1 })
db.myuser.count()
500000
mongodb有慢查询的概念，默认是超过100ms会记录慢日志mongodb.log
db.getProfilingStatus()
db.getProfilingStatus()
{ “was” : 0, “slowms” : 100, “sampleRate” : 1 }
查询age为9999的，查看扫描的行数
db.myuser.find( {age:9999} )
db.myuser.find({age:19999})
{ “_id” : ObjectId(“6362396795502fbd5683b9ba”), “name” : “mytest19999”, “age” : 19999 }
db.myuser.find( {age:9999} ).explain(true)#使用explain可以查看是否全表扫描
db.myuser.find({age:113399}).explain()
{
“explainVersion” : “1”,
“queryPlanner” : {
“namespace” : “wygzs.myuser”,
“indexFilterSet” : false,
“parsedQuery” : {
“age” : {
“$eq” : 113399
}
},
“queryHash” : “3838C5F3”,
“planCacheKey” : “BB98D80C”,
“maxIndexedOrSolutionsReached” : false,
“maxIndexedAndSolutionsReached” : false,
“maxScansToExplodeReached” : false,
“winningPlan” : {
“stage” : “COLLSCAN”, # 表示全表扫描
“filter” : {
“age” : {
“$eq” : 113399
}
},
“direction” : “forward”
},
“rejectedPlans” : [ ]
},
“command” : {
“find” : “myuser”,
“filter” : {
“age” : 113399
},
“$db” : “wygzs”
},
“serverInfo” : {
“host” : “5e7b4039fa6f”,
“port” : 27017,
“version” : “5.0.13”,
“gitVersion” : “cfb7690563a3144d3d1175b3a20c2ec81b662a8f”
},
“serverParameters” : {
“internalQueryFacetBufferSizeBytes” : 104857600,
“internalQueryFacetMaxOutputDocSizeBytes” : 104857600,
“internalLookupStageIntermediateDocumentMaxSizeBytes” : 104857600,
“internalDocumentSourceGroupMaxMemoryBytes” : 104857600,
“internalQueryMaxBlockingSortMemoryUsageBytes” : 104857600,
“internalQueryProhibitBlockingMergeOnMongoS” : 0,
“internalQueryMaxAddToSetBytes” : 104857600,
“internalDocumentSourceSetWindowFieldsMaxMemoryBytes” : 104857600
},
“ok” : 1
}
日志会出现慢日志
{“t”:{“$date”:”2022-11-02T09:41:00.284+00:00″},”s”:”I”, “c”:”COMMAND”, “id”:51803, “ctx”:”conn5″,”msg”:”Slow query”,”attr”:{“type”:”command”,”ns”:”wygzs.myuser”,”appName”:”MongoDB Shell”,”command”:{“find”:”myuser”,”filter”:{“age”:113399},”lsid”:{“id”:{“$uuid”:”ac26a8d3-1132-4d39-b35c-b770b70eb2b1″}},”$db”:”wygzs”},”planSummary”:”COLLSCAN”,”keysExamined”:0,”docsExamined”:500000,”cursorExhausted”:true,”numYields”:500,”nreturned”:1,”queryHash”:”3838C5F3″,”planCacheKey”:”BB98D80C”,”reslen”:162,”locks”:{“FeatureCompatibilityVersion”:{“acquireCount”:{“r”:501}},”Global”:{“acquireCount”:{“r”:501}},”Mutex”:{“acquireCount”:{“r”:1}}},”storage”:{},”remote”:”127.0.0.1:49802″,”protocol”:”op_msg”,”durationMillis”:157}}
添加索引，加快查询
db.myuser.getIndexes() #获取当前索引，默认有_id的索引，所以用_id查是比较快
db.myuser.getIndexes()
[ { “v” : 2, “key” : { “_id” : 1 }, “name” : “id” } ]
db.myuser.createIndex( {age:1} ) #增加age的升序索引，默认名称
db.myuser.createIndex({age:1})
{
“numIndexesBefore” : 1,
“numIndexesAfter” : 2,
“createdCollectionAutomatically” : false,
“ok” : 1
}
db.myuser.createIndex({age:1}, {name:”myuser_age”}) ##指定索引名称
db.myuser.createIndex({age:1}, {name:”myuser_age”})
{
“numIndexesBefore” : 2,
“numIndexesAfter” : 3,
“createdCollectionAutomatically” : false,
“commitQuorum” : “votingMembers”,
“ok” : 1,
“$clusterTime” : {
“clusterTime” : Timestamp(1667394028, 7),
“signature” : {
“hash” : BinData(0,”AAAAAAAAAAAAAAAAAAAAAAAAAAA=”),
“keyId” : NumberLong(0)
}
},
“operationTime” : Timestamp(1667394028, 7)
}
db.myuser.getIndexes()
db.myuser.getIndexes()
[
{
“v” : 2,
“key” : {
“_id” : 1
},
“name” : “id”
},
{
“v” : 2,
“key” : {
“age” : 1
},
“name” : “age_1”
}
]
db.myuser.find( {age:9999} )
db.myuser.find( {age:9999} ).explain(true)
db.myuser.find({age:19999}).explain(true)
{
“explainVersion” : “1”,
“queryPlanner” : {
“namespace” : “wygzs.myuser”,
“indexFilterSet” : false,
“parsedQuery” : {
“age” : {
“$eq” : 19999
}
},
“maxIndexedOrSolutionsReached” : false,
“maxIndexedAndSolutionsReached” : false,
“maxScansToExplodeReached” : false,
“winningPlan” : {
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“age” : 1
},
“indexName” : “age_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“age” : [ ]
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“age” : [
“[19999.0, 19999.0]”
]
}
}
},
“rejectedPlans” : [ ]
},
“executionStats” : {
“executionSuccess” : true,
“nReturned” : 1,
“executionTimeMillis” : 1,
“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,
“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,
“keyPattern” : {
“age” : 1
},
“indexName” : “age_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“age” : [ ]
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“age” : [
“[19999.0, 19999.0]”
]
},
“keysExamined” : 1,
“seeks” : 1,
“dupsTested” : 0,
“dupsDropped” : 0
}
},
“allPlansExecution” : [ ]
},
“command” : {
“find” : “myuser”,
“filter” : {
“age” : 19999
},
“$db” : “wygzs”
},
“serverInfo” : {
“host” : “5e7b4039fa6f”,
“port” : 27017,
“version” : “5.0.13”,
“gitVersion” : “cfb7690563a3144d3d1175b3a20c2ec81b662a8f”
},
“serverParameters” : {
“internalQueryFacetBufferSizeBytes” : 104857600,
“internalQueryFacetMaxOutputDocSizeBytes” : 104857600,
“internalLookupStageIntermediateDocumentMaxSizeBytes” : 104857600,
“internalDocumentSourceGroupMaxMemoryBytes” : 104857600,
“internalQueryMaxBlockingSortMemoryUsageBytes” : 104857600,
“internalQueryProhibitBlockingMergeOnMongoS” : 0,
“internalQueryMaxAddToSetBytes” : 104857600,
“internalDocumentSourceSetWindowFieldsMaxMemoryBytes” : 104857600
},
“ok” : 1
}
db.myuser.dropIndex( {age:1} ) #删除索引
db.myuser.dropIndex({age:1})
{ “nIndexesWas” : 2, “ok” : 1 }
db.myuser.dropIndex(“myuser_age”) ## 根据名称删除素银
db.myuser.dropIndex(“myuser_age”)
{
“nIndexesWas” : 3,
“ok” : 1,
“$clusterTime” : {
“clusterTime” : Timestamp(1667394121, 1),
“signature” : {
“hash” : BinData(0,”AAAAAAAAAAAAAAAAAAAAAAAAAAA=”),
“keyId” : NumberLong(0)
}
},
“operationTime” : Timestamp(1667394121, 1)
}
使用正则的话，索引无效果
db.myuser.find( {“name”:”mytest1″} )
db.myuser.createIndex( {name:1} )#添加索引
db.myuser.createIndex({name:1})
{
“numIndexesBefore” : 1,
“numIndexesAfter” : 2,
“createdCollectionAutomatically” : false,
“ok” : 1
}
db.myuser.getIndexes()
[
{
“v” : 2,
“key” : {
“_id” : 1
},
“name” : “id”
},
{
“v” : 2,
“key” : {
“name” : 1
},
“name” : “name_1”
}
]
db.myuser.find( {“name”:”mytest6″} )
db.myuser.find( {“name”:/99999/} )
db.myuser.find( {“name”:/99999/} ).explain(true)#使用正则，全表扫描，也是慢
mongodb建立唯一索引，唯一索引对应的值不能重复
use shijiange
db.myuser.insert( {userid:1} )
db.myuser.insert( {userid:1} )
db.myuser.drop()
true
db.myuser.insert( {userid:1} )
WriteResult({ “nInserted” : 1 })
db.myuser.insert( {userid:1} )
WriteResult({ “nInserted” : 1 })
db.myuser.createIndex({userid:1},{unique:true})
有重复数据，不能创建唯一索引
db.myuser.createIndex({userid:1},{unique:true})
{
“ok” : 0,
“errmsg” : “Index build failed: 26986ed4-2f8a-4bf8-84b9-c8349cf3a85d: Collection wygzs.myuser ( 3bff5780-8e8d-43f2-afe4-300b112f4ef5 ) :: caused by :: E11000 duplicate key error collection: wygzs.myuser index: userid_1 dup key: { userid: 1.0 }”,
“code” : 11000,
“codeName” : “DuplicateKey”,
“keyPattern” : {
“userid” : 1
},
“keyValue” : {
“userid” : 1
}
db.myuser.remove({}) #清空数据
db.myuser.ensureIndex( {userid:1},{unique:true} ) #创建唯一索引
db.myuser.insert( {userid:1} )
db.myuser.insert( {userid:2} )
db.myuser.insert( {userid:1} ) #因为是唯一索引，所以会报错
db.myuser.insert( {userid:1} )
WriteResult({ “nInserted” : 1 })
db.myuser.insert( {userid:2} )
WriteResult({ “nInserted” : 1 })
db.myuser.insert( {userid:1} )
WriteResult({
“nInserted” : 0,
“writeError” : {
“code” : 11000,
“errmsg” : “E11000 duplicate key error collection: wygzs.myuser index: userid_1 dup key: { userid: 1.0 }”
}
})
