推荐工具
RoboMongo
推荐书目
50 Tips and Tricks for MongoDB Developers
Related Blogs
Snail in a Turtleneck
adow
V2EX  ›  MongoDB

最近在测试 mongodb 的 2dsphere 索引,在 900w 数据情况下发现速度慢的可怕,求指导啊?

  •  1
     
  •   adow · Mar 11, 2014 · 8990 views
    This topic created in 4472 days ago, the information mentioned may be changed or developed.
    文档内容很简单,就几个字段当然主要的是坐标ugps字段已经建过2dsphere索引
    db.client_request_real.ensureIndex({"ugps":"2dsphere"},{"name":"_ugps_", "background" : true })
    然后在指定的一个4变形区内搜索
    var wxgd ={
    "type":"Polygon",
    "coordinates":[[
    [120.2673590, 31.5510940],
    [120.2673590, 31.5710940],
    [120.2873590, 31.5710940],
    [120.2873590, 31.5510940],
    [120.2673590, 31.5510940]
    ]]}
    db.client_request_real.find({"ugps":{"$within":{"$geometry":wxgd}}}).sort({"time":-1}).limit(20)

    explain显示
    > db.client_request_real.find({"ugps":{"$within":{"$geometry":wxgd}}}).sort({"time":-1}).limit(20).explain()
    {
    "cursor" : "S2Cursor",
    "isMultiKey" : true,
    "n" : 20,
    "nscannedObjects" : 33446,
    "nscanned" : 447373,
    "nscannedObjectsAllPlans" : 33446,
    "nscannedAllPlans" : 447373,
    "scanAndOrder" : true,
    "indexOnly" : false,
    "nYields" : 7,
    "nChunkSkips" : 0,
    "millis" : 35337,
    "indexBounds" : {

    },
    "nscanned" : 447373,
    "matchTested" : NumberLong(413927),
    "geoTested" : NumberLong(413927),
    "cellsInCover" : NumberLong(2),
    "server" : "syslog1:27017"
    }
    >
    狂汗这个速度好像也太慢了,查询期间用dsata查看了下系统负载
    cpu、磁盘、内存均无压力。这是咋回事啊?

    文档示例数据:
    { "_id" : ObjectId("531ebe0ea4e3a228463350a6"), "v" : "2", "key" : 0, "routeid" : 359, "segmentid" : "34436760", "stationseq" : 3, "ugps" : { "type" : "Point", "coordinates" : [ 120.275593, 31.567584 ] }, "ugps_type" : 0, "time" : ISODate("2014-03-11T07:40:38Z") }
    1 replies    1970-01-01 08:00:00 +08:00
    sheaven
        1
    sheaven  
       Mar 11, 2014   ❤️ 1
    慢就慢在.sort({"time":-1})这个排序上面,就跟mysql的file sort一个概念。就算time做个索引也无用,应该先用其他条件过滤再过滤下,减少nscannedObjects
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Solana   ·   5506 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 33ms · UTC 07:06 · PVG 15:06 · LAX 00:06 · JFK 03:06
    ♥ Do have faith in what you're doing.