[MongoDB] 쿼리 실행계획 확인하는 방법 explain

1. MongoDB 실행계획

쿼리가 효율적으로 실행되었는지 알기위해 MongoDB 는 explain 이라는 명령을 제공한다.

실행계획을 분석하는 방법은 내용이 방대하여 여기에서는 다루지 않는다.

 

2. MongoDB 테스트 버전

유형	버전	구성
mongosh	2.2.10
mongodb	7.0.12	Config : 1개, 포트 20000 Route : 1개, 포트 20001 Shard1 : 1개, 포트 30001 Shard2 : 1개, 포트 40001

 

 

3. 실행계획 확인

# 데이터베이스 이동
[direct: mongos] test> use explainDB
switched to db explainDB

# 데이터 적재
[direct: mongos] explainDB> db.explainCollection.insert ( { identity : 1, name : "Park" } )
DeprecationWarning: Collection.insert() is deprecated. Use insertOne, insertMany, or bulkWrite.

{
  acknowledged: true,
  insertedIds: { '0': ObjectId('66a89cc76d332c462a1b157d') }
}
[direct: mongos] explainDB> db.explainCollection.insert ( { identity : 2, name : "Kim" } )
DeprecationWarning: Collection.insert() is deprecated. Use insertOne, insertMany, or bulkWrite.
{
  acknowledged: true,
  insertedIds: { '0': ObjectId('66a89d7d6c7cfe6d2f1b157d') }
}

# 실행계획 조회
[direct: mongos] explainDB> db.explainCollection.insert ( { identity : 2, name : "Kim" } )
DeprecationWarning: Collection.insert() is deprecated. Use insertOne, insertMany, or bulkWrite.
{
  acknowledged: true,
  insertedIds: { '0': ObjectId('66a89d7d6c7cfe6d2f1b157d') }
}
[direct: mongos] explainDB> db.explainCollection.find ( { identity : 2 } ).explain()
{
  queryPlanner: {
    mongosPlannerVersion: 1,
    winningPlan: {
      stage: 'SINGLE_SHARD',
      shards: [
        {
          shardName: 'rs1',
          connectionString: 'rs1/127.0.0.1:30001',
          serverInfo: {
            host: 'ip-999-99-9-999.ap-northeast-2.compute.internal',
            port: 30001,
            version: '7.0.12',
            gitVersion: 'b6513ce0781db6818e24619e8a461eae90bc94fc'
          },
          namespace: 'explainDB.explainCollection',
          indexFilterSet: false,
          parsedQuery: { identity: { '$eq': 2 } },
          queryHash: '5AB21508',
          planCacheKey: '5AB21508',
          maxIndexedOrSolutionsReached: false,
          maxIndexedAndSolutionsReached: false,
          maxScansToExplodeReached: false,
          winningPlan: {
            stage: 'COLLSCAN',
            filter: { identity: { '$eq': 2 } },
            direction: 'forward'
          },
          rejectedPlans: []
        }
      ]
    }
  },
  serverInfo: {
    host: 'ip-999-99-9-999.ap-northeast-2.compute.internal',
    port: 20001,
    version: '7.0.12',
    gitVersion: 'b6513ce0781db6818e24619e8a461eae90bc94fc'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600,
    internalQueryFrameworkControl: 'trySbeRestricted'
  },
  command: {
    find: 'explainCollection',
    filter: { identity: 2 },
    lsid: { id: UUID('563d6010-4000-4a17-a431-9a64b2c44bad') },
    '$clusterTime': {
      clusterTime: Timestamp({ t: 1722326399, i: 1 }),
      signature: {
        hash: Binary.createFromBase64('AAAAAAAAAAAAAAAAAAAAAAAAAAA=', 0),
        keyId: 0
      }
    },
    '$db': 'explainDB'
  },
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1722326443, i: 1 }),
    signature: {
      hash: Binary.createFromBase64('AAAAAAAAAAAAAAAAAAAAAAAAAAA=', 0),
      keyId: Long('0')
    }
  },
  operationTime: Timestamp({ t: 1722326443, i: 1 })
}