mongodb查询语句效率分析

准备工作

  1. 安装最新版的mongodb.
  2. 安装免费的图形客户端Robo 3T
  3. 在数据库local下创建一个名为test的collection
  4. 创建索引db.test.createIndex( { a: -1, b:-1 }, {name:"ab"})
  5. 准备一些数据
{_id:1, a:1, b:2}
{_id:2, a:1, b:2}
{_id:3, a:1, b:3}
{_id:4, a:1, b:3}
{_id:5, a:2, b:2}
{_id:6, a:2, b:2}
{_id:7, a:2, b:3}
{_id:8, a:2, b:3}

索引分析

db.test.find({a:1}).explain();

执行结果

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "local.test",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "a" : {
                "$eq" : 1.0
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "a" : -1.0,
                    "b" : -1.0
                },
                "indexName" : "ab",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "a" : [],
                    "b" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "a" : [ 
                        "[1.0, 1.0]"
                    ],
                    "b" : [ 
                        "[MaxKey, MinKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : []
    },
    "serverInfo" : {},
    "ok" : 1.0
}

关键参数说明

字段 说明
queryPlanner.indexFilterSet 是否启用了index filter, mongodb使用index filter来选择索引
queryPlanner.winningPlan 优化器最终选择的plan
queryPlanner.winningPlan.inputStage 查询输入的参数和索引
queryPlanner.winningPlan.inputStage.stage 所处的阶段
COLLSCAN: 扫描collection
IXSCAN扫描索引
FETCH读取文档
queryPlanner.winningPlan.inputStage.keyPattern 索引的格式, 与创建索引时的格式一致
queryPlanner.winningPlan.inputStage.indexBounds 查询条件的约束值.
queryPlanner.rejectedPlans 被拒绝掉的候选plan

执行过程分析

db.test.find({a:1}).explain("executionStats");
{
    "queryPlanner" : { 略 },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 4,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 4,
        "totalDocsExamined" : 4,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 4,
            "executionTimeMillisEstimate" : 0,
            "works" : 5,
            "advanced" : 4,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 4,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 4,
                "executionTimeMillisEstimate" : 0,
                "works" : 5,
                "advanced" : 4,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "a" : -1.0,
                    "b" : -1.0
                },
                "indexName" : "ab",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "a" : [],
                    "b" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "a" : [ 
                        "[1.0, 1.0]"
                    ],
                    "b" : [ 
                        "[MaxKey, MinKey]"
                    ]
                },
                "keysExamined" : 4,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {},
    "ok" : 1.0
}

关键字段说明

字段 说明
executionStats.totalKeysExamined 遍历索引的次数
executionStats.totalDocsExamined 遍历文档的次数
executionTimeMillisEstimate 预测需要执行的时间

遍历的次数越多, 遍历肯定越慢. 本例中使用了索引, 有4条满足条件的文档, 所有遍历次数都是4.

不使用索引

db.test.find({b:2}).explain("executionStats");
{
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 4,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 8,
    },
}

db.test.find({b:2})无法使用索引{a:1, b:1}, 所以没有遍历索引, 然后遍历了整个collection

只返回索引数据

db.test.find({a:2}, {_id:0, a:1, b:1}).explain("executionStats");
{
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 4,
        "executionTimeMillis" : 0,
 	"totalKeysExamined" : 4,
        "totalDocsExamined" : 0,
        }
    },
}

本例要mongodb只返回a, b两次字段, 而这两个字段刚好创建了索引, 不需要再读取document, 所以没有遍历文档.

总结

mongodb中的索引与mysql的索引的行为非常相似

发布了74 篇原创文章 · 获赞 42 · 访问量 7万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览