MongoDB即使在使用索引时速度也很慢

我有一个名为的索引 type_1_reimbursement.reimbursement_vendor_1_reimbursement.status_1_reimbursement.employee_note_1_company_id_1_created_1 我用它创建的:

db.transaction.createIndex({ type: 1, 'reimbursement.reimbursement_vendor': 1, 'reimbursement.status': 1, 'reimbursement.employee_note': 1, company_id: 1,  created: 1 })

我的收藏有大约600万张唱片。

我正在尝试进行此查询:

db.transaction.aggregate([
  {
    $match: {
      type: 'card',
      company_id: 'google',
      'reimbursement.reimbursement_vendor': { $ne: null },
      'reimbursement.status': { $in: ['approved', 'completed'] },
      'reimbursement.employee_note': {
        $nin: [null, ''],
      },
      created: { $gte: new ISODate('2022-08-12') },
    },
  },
  {
    $sort: { created: 1 },
  },
  {
    $limit: 1,
  },
]);

然而,它真的很慢。跑步需要30-60秒。以下是解释结果

rs [direct: primary] application> db.transaction.aggregate([ { $match: { type: 'card', company_id: 'google', 'reimbursement.reimbursement_vendor': { $ne: null }, 'reimbursement.status': { $in: ['approved', 'completed'] }, 'reimbursement.employee_note': { $nin: [null, ''] }, created: { $gte: new ISODate('2022-08-12') } } }, { $sort: { created: 1 } }, { $limit: 1 }] ).explain();
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'application.transaction',
    indexFilterSet: false,
    parsedQuery: {
      '$and': [
        { company_id: { '$eq': 'google' } },
        { type: { '$eq': 'card' } },
        { created: { '$gte': ISODate("2022-08-12T00:00:00.000Z") } },
        {
          'reimbursement.status': { '$in': [ 'approved', 'completed' ] }
        },
        {
          'reimbursement.reimbursement_vendor': { '$not': { '$eq': null } }
        },
        {
          'reimbursement.employee_note': { '$not': { '$in': [ null, '' ] } }
        }
      ]
    },
    queryHash: '812E0D0B',
    planCacheKey: 'D506B9D0',
    optimizedPipeline: true,
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'SORT',
        sortPattern: { created: 1 },
        memLimit: 104857600,
        limitAmount: 1,
        type: 'default',
        inputStage: {
          stage: 'IXSCAN',
          keyPattern: {
            type: 1,
            'reimbursement.reimbursement_vendor': 1,
            'reimbursement.status': 1,
            'reimbursement.employee_note': 1,
            company_id: 1,
            created: 1
          },
          indexName: 'type_1_reimbursement.reimbursement_vendor_1_reimbursement.status_1_reimbursement.employee_note_1_company_id_1_created_1',
          isMultiKey: false,
          multiKeyPaths: {
            type: [],
            'reimbursement.reimbursement_vendor': [],
            'reimbursement.status': [],
            'reimbursement.employee_note': [],
            company_id: [],
            created: []
          },
          isUnique: false,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: {
            type: [ '["card", "card"]' ],
            'reimbursement.reimbursement_vendor': [ '[MinKey, undefined)', '(null, MaxKey]' ],
            'reimbursement.status': [
              '["approved", "approved"]',
              '["completed", "completed"]'
            ],
            'reimbursement.employee_note': [ '[MinKey, undefined)', '(null, "")', '("", MaxKey]' ],
            company_id: [ '["google", "google"]' ],
            created: [
              '[new Date(1660262400000), new Date(9223372036854775807)]'
            ]
          }
        }
      }
    },
    rejectedPlans: [
      {
        stage: 'SORT',
        sortPattern: { created: 1 },
        memLimit: 104857600,
        limitAmount: 1,
        type: 'simple',
        inputStage: {
          stage: 'FETCH',
          filter: { created: { '$gte': ISODate("2022-08-12T00:00:00.000Z") } },
          inputStage: {
            stage: 'IXSCAN',
            keyPattern: {
              type: 1,
              'reimbursement.reimbursement_vendor': 1,
              'reimbursement.status': 1,
              'reimbursement.employee_note': 1,
              company_id: 1
            },
            indexName: 'type_1_reimbursement.reimbursement_vendor_1_reimbursement.status_1_reimbursement.employee_note_1_company_id_1',
            isMultiKey: false,
            multiKeyPaths: {
              type: [],
              'reimbursement.reimbursement_vendor': [],
              'reimbursement.status': [],
              'reimbursement.employee_note': [],
              company_id: []
            },
            isUnique: false,
            isSparse: false,
            isPartial: false,
            indexVersion: 2,
            direction: 'forward',
            indexBounds: {
              type: [ '["card", "card"]' ],
              'reimbursement.reimbursement_vendor': [ '[MinKey, undefined)', '(null, MaxKey]' ],
              'reimbursement.status': [
                '["approved", "approved"]',
                '["completed", "completed"]'
              ],
              'reimbursement.employee_note': [ '[MinKey, undefined)', '(null, "")', '("", MaxKey]' ],
              company_id: [ '["google", "google"]' ]
            }
          }
        }
      }
    ]
  },
  command: {
    aggregate: 'transaction',
    pipeline: [
      {
        '$match': {
          type: 'card',
          company_id: 'google',
          'reimbursement.reimbursement_vendor': { '$ne': null },
          'reimbursement.status': { '$in': [ 'approved', 'completed' ] },
          'reimbursement.employee_note': { '$nin': [ null, '' ] },
          created: { '$gte': ISODate("2022-08-12T00:00:00.000Z") }
        }
      },
      { '$sort': { created: 1 } },
      { '$limit': 1 }
    ],
    cursor: {},
    '$db': 'application'
  },
  serverInfo: {
    host: '0ebbfaaef1f0',
    port: 27027,
    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,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1699662284, i: 1 }),
    signature: {
      hash: Binary(Buffer.from("0000000000000000000000000000000000000000", "hex"), 0),
      keyId: Long("0")
    }
  },
  operationTime: Timestamp({ t: 1699662284, i: 1 })
}

在下面 winningPlans ,它看起来确实在使用索引,因为我在下面看到了它 IXSCAN 。然而,考虑到它的速度有多慢,它似乎不起作用。

我是否创建索引错误?