代码干货 | MongoDB查询优化：从 10s 到 10ms

本文来源于阿里云-云栖社区，原文点击这里。

苏先生反馈线上某条查询很慢（10+ seconds），语句相当于

db.myColl.find({app:"my_app",requestTime:{$gte:1492502247000,$lt:1492588800000}}).sort({_id:-1}).limit(1)

myColl这个collection中的记录内容类似于：

{ "_id" : ObjectId("58fd895359cb8757d493ce60"), "app" : "my_app", "eventId" : 141761066, "requestTime" : NumberLong("1493010771753"), "scene" : "scene01" }
{ "_id" : ObjectId("58fd895359cb8757d493ce52"), "app" : "my_app", "eventId" : 141761052, "requestTime" : NumberLong("1493010771528"), "scene" : "scene02" }
{ "_id" : ObjectId("58fd895359cb8757d493ce36"), "app" : "my_app", "eventId" : 141761024, "requestTime" : NumberLong("1493010771348"), "scene" : "scene03" }
{ "_id" : ObjectId("58fd895359cb8757d493ce31"), "app" : "my_app", "eventId" : 141761019, "requestTime" : NumberLong("1493010771303"), "scene" : "scene01" }
{ "_id" : ObjectId("58fd895359cb8757d493ce2d"), "app" : "my_app", "eventId" : 141761015, "requestTime" : NumberLong("1493010771257"), "scene" : "scene01" }
{ "_id" : ObjectId("58fd895259cb8757d493ce10"), "app" : "my_app", "eventId" : 141760986, "requestTime" : NumberLong("1493010770866"), "scene" : "scene01" }
{ "_id" : ObjectId("58fd895259cb8757d493ce09"), "app" : "my_app", "eventId" : 141760979, "requestTime" : NumberLong("1493010770757"), "scene" : "scene01" }
{ "_id" : ObjectId("58fd895259cb8757d493ce02"), "app" : "my_app", "eventId" : 141760972, "requestTime" : NumberLong("1493010770614"), "scene" : "scene03" }
{ "_id" : ObjectId("58fd895259cb8757d493cdf1"), "app" : "my_app", "eventId" : 141760957, "requestTime" : NumberLong("1493010770342"), "scene" : "scene02" }
{ "_id" : ObjectId("58fd895259cb8757d493cde6"), "app" : "my_app", "eventId" : 141760946, "requestTime" : NumberLong("1493010770258"), "scene" : "scene01" }

相关的索引有：

[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "myDatabase.myColl"
    },
    {
        "v" : 1,
        "key" : {
            "responseTime" : -1
        },
        "name" : "idx_responseTime_-1",
        "ns" : "myDatabase.myColl"
    },
    {
        "v" : 1,
        "key" : {
            "app" : 1,
            "scene" : 1,
            "eventId" : -1,
            "requestTime" : -1
        },
        "name" : "idx_app_1_scene_1_eventId_-1_requestTime_-1",
        "ns" : "myDatabase.myColl"
    }
]

慢查询就是在myColl中查找符合[1492502247000, 1492588800000)这个时间范围的所有记录，**以下描述中称这条查询为bad query**。

如果去掉$lt:1492588800000这个约束条件，查找[1492502247000, +∞)这个时间范围，就会很快（milliseconds）。

>>>展开全文