当前位置：首页 > news >正文

6.如何判断数据库搜索是否走索引？

news 来源：原创 2024/5/13 1:26:47

判断是否使用索引搜索

索引在数据库中是一个不可或缺的存在，想让你的查询结果快准狠，还是需要索引的来帮忙，那么在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
}