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
}
阅读 2.6k
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进