我有一个名为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 })
我的Collection 有大约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
.然而,考虑到它的速度如此之慢,它似乎并不起作用.
我是否创建了错误的索引?