5网站开发,中企动力邮箱登陆入口,wordpress 502,2022八月热点新闻摘抄判断是否使用索引搜索
索引在数据库中是一个不可或缺的存在#xff0c;想让你的查询结果快准狠#xff0c;还是需要索引的来帮忙#xff0c;那么在mongo中如何判断搜索是不是走索引呢#xff1f;通常使用执行计划#xff08;解释计划、Explain Plan#xff09;来查看查询…
判断是否使用索引搜索
索引在数据库中是一个不可或缺的存在想让你的查询结果快准狠还是需要索引的来帮忙那么在mongo中如何判断搜索是不是走索引呢通常使用执行计划解释计划、Explain Plan来查看查询的情况如查询耗费的时间、是否基于索引查询等。
索引语法
db.collection.find(query,options).explain(options)创建索引前
查看根据name查询数据的情况 db.user.find({name:张三}).explain()
{explainVersion: 1,queryPlanner: {namespace: test.user,indexFilterSet: false,parsedQuery: {name: {$eq: 张三}},queryHash: A2F868FD,planCacheKey: A2F868FD,maxIndexedOrSolutionsReached: false,maxIndexedAndSolutionsReached: false,maxScansToExplodeReached: false,winningPlan: {stage: COLLSCAN,filter: {name: {$eq: 张三}},direction: forward},rejectedPlans: []},command: {find: user,filter: {name: 张三},$db: test},serverInfo: {host: ADMIN,port: 27017,version: 7.0.6,gitVersion: 66cdc1f28172cb33ff68263050d73d4ade73b9a4},serverParameters: {internalQueryFacetBufferSizeBytes: 104857600,internalQueryFacetMaxOutputDocSizeBytes: 104857600,internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,internalDocumentSourceGroupMaxMemoryBytes: 104857600,internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,internalQueryProhibitBlockingMergeOnMongoS: 0,internalQueryMaxAddToSetBytes: 104857600,internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600,internalQueryFrameworkControl: trySbeRestricted},ok: 1
}关键点看 stage : COLLSCAN, 表示全集合扫描 创建索引后
下面对name建立索引
db.user.createIndex({name:1})看效果 db.user.find({name:张三}).explain()
{explainVersion: 1,queryPlanner: {namespace: test.user,indexFilterSet: false,parsedQuery: {name: {$eq: 张三}},queryHash: A2F868FD,planCacheKey: A3E454E0,maxIndexedOrSolutionsReached: false,maxIndexedAndSolutionsReached: false,maxScansToExplodeReached: false,winningPlan: {stage: FETCH,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: [[张三, 张三]]}}},rejectedPlans: []},command: {find: user,filter: {name: 张三},$db: test},serverInfo: {host: ADMIN,port: 27017,version: 7.0.6,gitVersion: 66cdc1f28172cb33ff68263050d73d4ade73b9a4},serverParameters: {internalQueryFacetBufferSizeBytes: 104857600,internalQueryFacetMaxOutputDocSizeBytes: 104857600,internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,internalDocumentSourceGroupMaxMemoryBytes: 104857600,internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,internalQueryProhibitBlockingMergeOnMongoS: 0,internalQueryMaxAddToSetBytes: 104857600,internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600,internalQueryFrameworkControl: trySbeRestricted},ok: 1
}关键点看 stage : IXSCAN 基于索引的扫描 compass查看
建立的索引是否有效效果如何都需要通过执行计划查看以此来判断你的SQL是否需要优化是否需要创建索引耗时多久等等用处可不少呢。
