关于设计mongodb索引,涉及到内存排序问题解决
发布日期:2021-06-29 13:15:31
浏览次数:2
分类:技术文章
本文共 7629 字,大约阅读时间需要 25 分钟。
关于设计mongodb索引,涉及到内存排序问题解决
查询脚本如下:
db.QuickReplyTemplate.find({ "sceneKey": "responseMsg", "termId": 1, "isDelete": false}).sort({ "updatedOn": 1}).explain("executionStats");
很简单一个查询,然后我们需要设计两个索引,看看mongodb执行器对这两个索引如何选择优化?
db.getCollection("QuickReplyTemplate").createIndex({ sceneKey: NumberInt("1"), termId: NumberInt("1"), isDelete: NumberInt("1")}, { name: "idx_QuickReplyTemplate__sceneKey_1_termId_1_isDelete_1"});
db.getCollection("QuickReplyTemplate").createIndex({ sceneKey: NumberInt("1"), termId: NumberInt("1"), isDelete: NumberInt("1"), updatedOn: NumberInt("1")}, { name: "idx_QuickReplyTemplate__sceneKey_1_termId_1_isDelete_1_updatedOn_1"});
两个很简单是索引,并且区别很明显,后者多了一个updateOn参数,然后这个参数就是排序参数,我们看看执行计划
// 1{ "queryPlanner": { "plannerVersion": NumberInt("1"), "namespace": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxx.QuickReplyTemplate", "indexFilterSet": false, "parsedQuery": { "$and": [ { "isDelete": { "$eq": false } }, { "sceneKey": { "$eq": "responseMsg" } }, { "termId": { "$eq": 1 } } ] }, "winningPlan": { "stage": "FETCH", "inputStage": { "stage": "IXSCAN", "keyPattern": { "sceneKey": NumberInt("1"), "termId": NumberInt("1"), "isDelete": NumberInt("1"), "updatedOn": NumberInt("1") }, "indexName": "idx_QuickReplyTemplate__sceneKey_1_termId_1_isDelete_1_updatedOn_1", "isMultiKey": false, "multiKeyPaths": { "sceneKey": [ ], "termId": [ ], "isDelete": [ ], "updatedOn": [ ] }, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": NumberInt("2"), "direction": "forward", "indexBounds": { "sceneKey": [ "[\"responseMsg\", \"responseMsg\"]" ], "termId": [ "[1.0, 1.0]" ], "isDelete": [ "[false, false]" ], "updatedOn": [ "[MinKey, MaxKey]" ] } } }, "rejectedPlans": [ { "stage": "SORT", "sortPattern": { "updatedOn": 1 }, "inputStage": { "stage": "SORT_KEY_GENERATOR", "inputStage": { "stage": "FETCH", "inputStage": { "stage": "IXSCAN", "keyPattern": { "sceneKey": NumberInt("1"), "termId": NumberInt("1"), "isDelete": NumberInt("1") }, "indexName": "idx_QuickReplyTemplate__sceneKey_1_termId_1_isDelete_1", "isMultiKey": false, "multiKeyPaths": { "sceneKey": [ ], "termId": [ ], "isDelete": [ ] }, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": NumberInt("2"), "direction": "forward", "indexBounds": { "sceneKey": [ "[\"responseMsg\", \"responseMsg\"]" ], "termId": [ "[1.0, 1.0]" ], "isDelete": [ "[false, false]" ] } } } } } ] }, "executionStats": { "executionSuccess": true, "nReturned": NumberInt("0"), "executionTimeMillis": NumberInt("12"), "totalKeysExamined": NumberInt("0"), "totalDocsExamined": NumberInt("0"), "executionStages": { "stage": "FETCH", "nReturned": NumberInt("0"), "executionTimeMillisEstimate": NumberInt("0"), "works": NumberInt("2"), "advanced": NumberInt("0"), "needTime": NumberInt("0"), "needYield": NumberInt("0"), "saveState": NumberInt("0"), "restoreState": NumberInt("0"), "isEOF": NumberInt("1"), "invalidates": NumberInt("0"), "docsExamined": NumberInt("0"), "alreadyHasObj": NumberInt("0"), "inputStage": { "stage": "IXSCAN", "nReturned": NumberInt("0"), "executionTimeMillisEstimate": NumberInt("0"), "works": NumberInt("1"), "advanced": NumberInt("0"), "needTime": NumberInt("0"), "needYield": NumberInt("0"), "saveState": NumberInt("0"), "restoreState": NumberInt("0"), "isEOF": NumberInt("1"), "invalidates": NumberInt("0"), "keyPattern": { "sceneKey": NumberInt("1"), "termId": NumberInt("1"), "isDelete": NumberInt("1"), "updatedOn": NumberInt("1") }, "indexName": "idx_QuickReplyTemplate__sceneKey_1_termId_1_isDelete_1_updatedOn_1", "isMultiKey": false, "multiKeyPaths": { "sceneKey": [ ], "termId": [ ], "isDelete": [ ], "updatedOn": [ ] }, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": NumberInt("2"), "direction": "forward", "indexBounds": { "sceneKey": [ "[\"responseMsg\", \"responseMsg\"]" ], "termId": [ "[1.0, 1.0]" ], "isDelete": [ "[false, false]" ], "updatedOn": [ "[MinKey, MaxKey]" ] }, "keysExamined": NumberInt("0"), "seeks": NumberInt("1"), "dupsTested": NumberInt("0"), "dupsDropped": NumberInt("0"), "seenInvalidated": NumberInt("0") } } }, "serverInfo": { "host": "localhost.localdomain", "port": NumberInt("50000"), "version": "4.0.1", "gitVersion": "54f1582fc6eb01de4d4c42f26fc133e623f065fb" }, "ok": 1}
开始分析:
可以看到,它选择了后者,并且:检出文档、然后有进行了索引扫描,基本上可以理解为最优了。
然后我们看看它排除了哪些索引?
第一步可以看出,进行了内存排序(SORT);虽然,我们可以看出,他走了索引,但是索引命中的数据,又进行了内存排序。那么产生内存排序的危害,我们就不细聊了。可以度娘;
转载地址:https://changemax.blog.csdn.net/article/details/111193165 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
第一次来,支持一个
[***.219.124.196]2024年04月30日 18时30分10秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
单片机里面的CPU使用率是什么鬼?
2019-04-29
推荐一个优质Linux技术公众号-作者都是一线Linux代码贡献者们哦
2019-04-29
RT-Thread 编程风格指南
2019-04-29
95后高校电子教师,软硬兼修有趣有料!
2019-04-29
使用 STM32 通用 Bootloader ,让 OTA 更加 Easy
2019-04-29
Cache 的基本概念与工作原理
2019-04-29
Android程序员必备!面试一路绿灯Offer拿到手软,Android面试题及解析
2019-04-29
Android程序员的春天!12个View绘制流程高频面试题,分享PDF高清版
2019-04-29
深入浅出Android开发!你会的还只有初级工程师的技术吗?一线互联网公司面经总结
2019-04-29
深度剖析原理!超全Android中高级面试复习大纲,含BATJM大厂
2019-04-29
温故而知新!Android开发者该学习哪些东西提高竞争力?成功入职阿里
2019-04-29
火爆知乎的Android面试题-Android-App的设计架构经验谈,大厂内部资料
2019-04-29
看完直接怼产品经理!Android多进程从头讲到尾,跳槽薪资翻倍
2019-04-29
快速从入门到精通!面试的时候突然遇到答不上的问题怎么办?已拿到offer
2019-04-29
Android开发知识体系!腾讯+字节+阿里面经真题汇总,成功入职阿里
2019-04-29
android开发语言!大厂经典高频面试题体系化集合,移动架构师成长路线
2019-04-29
typescript学习(进阶)
2019-04-29
三天敲一个前后端分离的员工管理系统
2019-04-29