Yi88
  • 1
  • 新人请关照

mongodb使用索引后仍然很慢,执行总时间为什么远大于文档和索引检索时间?

mongodb使用索引后仍然很慢
查询语句:db.events.find({'start_ts': {'$lte': 1574917687.184}, 'end_ts': {'$gte': 1574914087.184}})
问题如下:
1.使用了索引但是仍然超级慢,并且索引扫描后返回很多无用数据,是什么导致查询这么慢呢?
2.explain中可见,总执行时间(executionStats。executionTimeMillis):27738远大于文档搜索(590)以及索引搜索时间(580),这是为什么呢?

db.col.find({'start_ts': {'$lte': 1574917687.184}, 'end_ts': {'$gte': 1574914087.184}}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "admin.col",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "start_ts" : {
                        "$lte" : 1574917687.184
                    }
                },
                {
                    "end_ts" : {
                        "$gte" : 1574914087.184
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "start_ts" : -1,
                    "end_ts" : 1
                },
                "indexName" : "start_ts_-1_end_ts_1",
                "isMultiKey" : false,
                "direction" : "forward",
                "indexBounds" : {
                    "start_ts" : [
                        "[1574917687.184, -inf.0]"
                    ],
                    "end_ts" : [
                        "[1574914087.184, inf.0]"
                    ]
                }
            }
        }
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 5350,
        "executionTimeMillis" : 27738,
        "totalKeysExamined" : 274957,
        "totalDocsExamined" : 5350,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 5350,
            "executionTimeMillisEstimate" : 590,
            "works" : 274957,
            "advanced" : 5350,
            "needTime" : 269606,
            "needFetch" : 0,
            "saveState" : 2150,
            "restoreState" : 2150,
            "isEOF" : 1,
            "invalidates" : 3,
            "docsExamined" : 5350,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 5350,
                "executionTimeMillisEstimate" : 580,
                "works" : 274957,
                "advanced" : 5350,
                "needTime" : 269606,
                "needFetch" : 0,
                "saveState" : 2150,
                "restoreState" : 2150,
                "isEOF" : 1,
                "invalidates" : 3,
                "keyPattern" : {
                    "start_ts" : -1,
                    "end_ts" : 1
                },
                "indexName" : "start_ts_-1_end_ts_1",
                "isMultiKey" : false,
                "direction" : "forward",
                "indexBounds" : {
                    "start_ts" : [
                        "[1574917687.184, -inf.0]"
                    ],
                    "end_ts" : [
                        "[1574914087.184, inf.0]"
                    ]
                },
                "keysExamined" : 274957,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0,
                "matchTested" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "xxxxx",
        "port" : 1111,
        "version" : "3.0.6",
        "gitVersion" : "xxxxx"
    },
    "ok" : 1
}
阅读 185
评论 更新于 2019-12-03
    撰写回答

    登录后参与交流、获取后续更新提醒

    MongoDB 技术问答
    合作问答

    MongoDB 官方中文社区 和 SegmentFault 联合推出的 MongoDB 技术交流平台。 这是一个以社区力量为主,但...