python - mongodb 按照距离排序查询很慢

Question

类似于lbs的服务，需要按照用户位置的距离排序。

集合的结构:

{
    "_id" : ObjectId("574bbae4d009b5364abaebe5"),
    "cityid" : 406,
    "location" : {
        "type" : "Point",
        "coordinates" : [
            118.602355,
            24.89083
        ]
    },
    "shopid" : "a"
}

差不多5万条数据。

索引：

[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "pingan-test.shop_actinfo_collection_0530"
    },
    {
        "v" : 1,
        "key" : {
            "location" : "2dsphere"
        },
        "name" : "location_2dsphere",
        "ns" : "pingan-test.shop_actinfo_collection_0530",
        "2dsphereIndexVersion" : 3
    },
    {
        "v" : 1,
        "key" : {
            "shopid" : 1,
            "cityid" : 1
        },
        "name" : "shopid_1_cityid_1",
        "ns" : "pingan-test.shop_actinfo_collection_0530"
    }
]

查询的条件是：

{'cityid': 2, 'location': {'$near': {'$geometry': {'type': 'Point', 'coordinates': [122.0, 31.0]}}}, 'shopid': {'$in': ['a','b']}}

当使用pymongo查询的时候，迭代会消耗大概300ms的时间，这个难以接受。

results = collection.find(body, {'shopid': 1, '_id':0},).batch_size(20).limit(20)
shops = list(results)

第一步获取一个游标，几乎没有消耗时间；
第二部对这个有标进行迭代消耗300～400ms时间。

应该如何优化？

Answer 1

Finally, I just created an index cityid: 1, shopid: 1, "location" : "2dsphere"
After that, the world was at peace again.

Answer 2

Building an index in this way can certainly solve the problem, but here I would like to emphasize a point that is often overlooked: Put strong filtering conditions first
Because we don’t know how your data is distributed, we can’t judge whether your index meets this minimum requirement. best practices. But it seems that unless shopid在不同城市有重复（我怀疑通常不会这么设计），否则cityid has no effect at all, there is no need to put it in the index, which will only increase the writing pressure.

Answer 3

Use explain in mongo shell to parse sql and build an index based on the parsing. At the same time, you need to consider the principle of leftmost prefix matching;