我正在尋找一些關于如何提高查詢性能的建議。
我在 mongoose 中有這個用戶模型，我正在索引Interests_cities。
const firebaseToken = new Schema({
type: {
type: String,
default: "android",
required: true,
trim: true,
},
device_id: {
type: String,
required: true,
trim: true,
},
fcm_token: {
type: String,
required: true,
trim: true,
},
});
const userSchema = new Schema({
name: {
type: String,
required: true,
trim: true,
},
interested_cities: {
type: [{
type: String,
trim: true,
lowercase: true,
unique: true
}],
required: false,
default: [],
},
push_notification_tokens_firebase: {
type: [firebaseToken],
required: false,
default: [],
},
});
userSchema.index({
interested_cities: 1
});
我正在尋找的是查詢在他們的interested_cities陣列中具有“A”或“B”的用戶。
我正在查詢這樣的事情。我只需要查詢中的 firebase fcm_token。
const involvedUsers = await User.find(
{
$or: [
{ interested_cities: { $in: ['A', 'B'] } },
{ phone_number: { $in: adminPhoneNumbersList } },
],
},
{
_id: 1,
"push_notification_tokens_firebase.fcm_token": 1,
}
);
目前，查詢 14k 檔案需要 20 秒，這需要改進。任何指標將不勝感激。
解釋：
{
"explainVersion": "1",
"queryPlanner": {
"namespace": "production.users",
"indexFilterSet": false,
"parsedQuery": {
"interested_cities": {
"$in": [
"A",
"B"
]
}
},
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"stage": "PROJECTION_DEFAULT",
"transformBy": {
"_id": 1,
"push_notification_tokens_firebase.fcm_token": 1
},
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"interested_cities": 1
},
"indexName": "interested_cities_1",
"isMultiKey": true,
"multiKeyPaths": {
"interested_cities": [
"interested_cities"
]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"interested_cities": [
"[\"A\", \"A\"]",
"[\"B\", \"B\"]"
]
}
}
}
},
"rejectedPlans": []
}
"executionStats": {
"executionSuccess": true,
"nReturned": 6497,
"executionTimeMillis": 48,
"totalKeysExamined": 0,
"totalDocsExamined": 14827,
"executionStages": {
"stage": "SUBPLAN",
"nReturned": 6497,
"executionTimeMillisEstimate": 46,
"works": 14829,
"advanced": 6497,
"needTime": 8331,
"needYield": 0,
"saveState": 14,
"restoreState": 14,
"isEOF": 1,
"inputStage": {
"stage": "PROJECTION_DEFAULT",
"nReturned": 6497,
"executionTimeMillisEstimate": 46,
"works": 14829,
"advanced": 6497,
"needTime": 8331,
"needYield": 0,
"saveState": 14,
"restoreState": 14,
"isEOF": 1,
"transformBy": {
"_id": 1,
"push_notification_tokens_firebase.fcm_token": 1
},
"inputStage": {
"stage": "COLLSCAN",
"filter": {
"$or": [
{
"interested_cities": {
"$in": [
"A",
"B"
]
}
},
{
"phone_number": {
"$in": [
"phone numbers",
"phone number"
]
}
}
]
},
"nReturned": 6497,
"executionTimeMillisEstimate": 41,
"works": 14829,
"advanced": 6497,
"needTime": 8331,
"needYield": 0,
"saveState": 14,
"restoreState": 14,
"isEOF": 1,
"direction": "forward",
"docsExamined": 14827
}
}
},
"allPlansExecution": []
}
uj5u.com熱心網友回復：
貓鼬優化：
默認情況下，Mongoose 查詢回傳 Mongoose Document 類的一個實體。檔案比普通的 JavaScript 物件重得多，因為它們有很多用于更改跟蹤的內部狀態。啟用精益選項會告訴 Mongoose 跳過實體化完整的 Mongoose 檔案，只給你 POJO。
https://mongoosejs.com/docs/tutorials/lean.html#using-lean
.lean()您可以通過在末尾附加來禁用此行為。如果您的查詢回傳“大量”檔案，這確實可以提高您的速度。您應該從上面的鏈接中了解更多有關 lean() 的資訊。
查詢優化：
在評估 $or 運算式中的子句時，MongoDB 要么執行集合掃描，要么，如果索引支持所有子句，則 MongoDB 執行索引掃描。也就是說，為了讓 MongoDB 使用索引來評估 $or 運算式，$or 運算式中的所有子句都必須由索引支持。否則，MongoDB 將執行集合掃描。
https://www.mongodb.com/docs/manual/reference/operator/query/or/#-or-clauses-and-indexes
您共享的查詢如下所示：
const involvedUsers = await User.find({
$or: [
{ interested_cities: { $in: citiesArr } },
{ phone_number: { $in: phonesArr } },
],
}, { _id: 1, "push_notification_tokens_firebase.fcm_token": 1 });
根據以上資訊，您需要創建以下兩個索引：
userSchema.index({ interested_cities: 1 });
userSchema.index({ phone_number: 1 });
這樣，mongo 將能夠“知道”哪些檔案是相關的，在磁盤上找到它們，提取您的投影（“_id”和“push_notification_tokens_firebase.fcm_token”）并回傳它。
進一步優化的一步是創建以下索引而不是上面的索引：
userSchema.index({ interested_cities: 1, _id: 1, "push_notification_tokens_firebase.fcm_token": 1 });
userSchema.index({ phone_number: 1, _id: 1, "push_notification_tokens_firebase.fcm_token": 1 });
這樣，mongo 將擁有從索引中完成查詢所需的所有資訊，這意味著它甚至永遠不會訪問磁盤來獲取檔案。
您可以通過運行<your-query>.explain('executionStats')并確認totalDocsExaminedis 來確認這一點0。
在此處閱讀有關 executionStats 的更多資訊：https: //www.mongodb.com/docs/manual/reference/explain-results/#mongodb-data-explain.executionStats
我希望這有幫助！
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/476760.html