MongoDB 인덱스

MongoDB index

백만 개 도큐먼트가 있는 컬렉션에서 조회할 때 index 유무 차이

• index는 모든 값을 정렬된 순서로 보관하므로 인덱스 키로 도큐먼트를 정렬하는 작업이 훨씬 빨라지게 합니다.
• index (x)
  • totalDocsExamined가 1000000이고, executionTimeMillis가 682인 걸 알 수 있습니다.(0.682초)

{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'study.User',
    indexFilterSet: false,
    parsedQuery: {
      username: {
        '$eq': 'user101'
      }
    },
    queryHash: '7D9BB680',
    planCacheKey: '7D9BB680',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'COLLSCAN',
      filter: {
        username: {
          '$eq': 'user101'
        }
      },
      direction: 'forward'
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 1,
    executionTimeMillis: 682,
    totalKeysExamined: 0,
    totalDocsExamined: 1000000,
    executionStages: {
      stage: 'COLLSCAN',
      filter: {
        username: {
          '$eq': 'user101'
        }
      },
      nReturned: 1,
      executionTimeMillisEstimate: 129,
      works: 1000002,
      advanced: 1,
      needTime: 1000000,
      needYield: 0,
      saveState: 1000,
      restoreState: 1000,
      isEOF: 1,
      direction: 'forward',
      docsExamined: 1000000
    }
  },
  command: {
    find: 'User',
    filter: {
      username: 'user101'
    },
    '$db': 'study'
  },
  serverInfo: {
    host: 'dd32fd44e4c6',
    port: 27017,
    version: '6.0.4',
    gitVersion: '44ff59461c1353638a71e710f385a566bcd2f547'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1
}

• index (o)
  • totalDocsExamined가 1이고, executionTimeMillis가 12인 걸 알 수 있습니다.(0.012초)
  • index가 없을 때보다 약 60배 빠르게 쿼리를 수행했습니다.

{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'study.User',
    indexFilterSet: false,
    parsedQuery: {
      username: {
        '$eq': 'user101'
      }
    },
    queryHash: '7D9BB680',
    planCacheKey: '24069050',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: {
          username: 1
        },
        indexName: 'username_1',
        isMultiKey: false,
        multiKeyPaths: {
          username: []
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          username: [
            '["user101", "user101"]'
          ]
        }
      }
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 1,
    executionTimeMillis: 12,
    totalKeysExamined: 1,
    totalDocsExamined: 1,
    executionStages: {
      stage: 'FETCH',
      nReturned: 1,
      executionTimeMillisEstimate: 9,
      works: 2,
      advanced: 1,
      needTime: 0,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      docsExamined: 1,
      alreadyHasObj: 0,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 1,
        executionTimeMillisEstimate: 9,
        works: 2,
        advanced: 1,
        needTime: 0,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        keyPattern: {
          username: 1
        },
        indexName: 'username_1',
        isMultiKey: false,
        multiKeyPaths: {
          username: []
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          username: [
            '["user101", "user101"]'
          ]
        },
        keysExamined: 1,
        seeks: 1,
        dupsTested: 0,
        dupsDropped: 0
      }
    }
  },
  command: {
    find: 'User',
    filter: {
      username: 'user101'
    },
    '$db': 'study'
  },
  serverInfo: {
    host: 'dd32fd44e4c6',
    port: 27017,
    version: '6.0.4',
    gitVersion: '44ff59461c1353638a71e710f385a566bcd2f547'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1
}

결론

• index를 사용하면 읽기는 월등히 빠르지만, 인덱싱된 필드를 변경하는 쓰기(write)작업은 더 오래 걸립니다. 데이터가 변경될 때마다 도큐먼트뿐 아니라
  모든 인덱스를 갱신해야 하기 때문입니다.
• 따라서 자주 쓰이는 쿼리에 공통적인 키 셋을 찾아 인덱싱하면 좋습니다.

---

참고자료

몽고DB 완벽 가이드 3판 - 한빛미디어