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

判断是否使用索引搜索

索引在数据库中是一个不可或缺的存在，想让你的查询结果快准狠，还是需要索引的来帮忙，那么在mongo中如何判断搜索是不是走索引呢？通常使用执行计划（解释计划、Explain Plan）来查看查询的情况，如查询耗费的时间、是否基于索引查询等。

索引语法

db.collection.find(query,options).explain(options)
1

创建索引前

查看根据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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

关键点看： "stage" : "COLLSCAN", 表示全集合扫描

创建索引后

下面对name建立索引

db.user.createIndex({name:1})
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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

关键点看： "stage" : "IXSCAN" ，基于索引的扫描

compass查看：

建立的索引是否有效，效果如何，都需要通过执行计划查看，以此来判断你的SQL是否需要优化，是否需要创建索引，耗时多久等等，用处可不少呢。