性能分析与优化

db.posts.find({ authorId: "alice" }).explain("queryPlanner")      // 默认，只看计划
db.posts.find({ authorId: "alice" }).explain("executionStats")    // 真的执行，给统计
db.posts.find({ authorId: "alice" }).explain("allPlansExecution") // 所有候选计划的统计

db.posts.find({ status: "published", "stats.views": { $gt: 1000 } })
  .sort({ createdAt: -1 })
  .limit(20)
  .explain("executionStats")

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "LIMIT",
      "limitAmount": 20,
      "inputStage": {
        "stage": "FETCH",
        "filter": { "stats.views": { "$gt": 1000 } },
        "inputStage": {
          "stage": "IXSCAN",
          "indexName": "status_1_createdAt_-1",
          "direction": "forward",
          "indexBounds": {
            "status": [ "[\"published\", \"published\"]" ],
            "createdAt": [ "[MaxKey, MinKey]" ]
          }
        }
      }
    },
    "rejectedPlans": [ ]
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 20,
    "executionTimeMillis": 8,
    "totalKeysExamined": 640,
    "totalDocsExamined": 640
  }
}

db.posts.find({ status: "published", tags: "mongodb" })
  .sort({ "stats.likes": -1 })
  .limit(20)
  .explain("executionStats")

{
  "executionStats": {
    "nReturned": 20,
    "executionTimeMillis": 812,
    "totalKeysExamined": 0,
    "totalDocsExamined": 1204882,
    "executionStages": {
      "stage": "SORT",
      "memUsage": 31459840,
      "inputStage": { "stage": "COLLSCAN" }
    }
  }
}

db.posts.createIndex({ status: 1, tags: 1, "stats.likes": -1 })

{
  "executionStats": {
    "nReturned": 20,
    "executionTimeMillis": 6,
    "totalKeysExamined": 20,
    "totalDocsExamined": 20,
    "executionStages": {
      "stage": "LIMIT",
      "inputStage": { "stage": "FETCH", "inputStage": { "stage": "IXSCAN" } }
    }
  }
}

db.posts.createIndex({ status: 1, tags: 1, "stats.likes": -1, title: 1, "author.username": 1 })
 
db.posts.find(
  { status: "published", tags: "mongodb" },
  { title: 1, "author.username": 1, "stats.likes": 1, _id: 0 }
).sort({ "stats.likes": -1 }).limit(20)

{
  "executionStats": {
    "nReturned": 20,
    "executionTimeMillis": 1,
    "totalKeysExamined": 20,
    "totalDocsExamined": 0,
    "executionStages": { "stage": "PROJECTION_COVERED" }
  }
}

// level: 0 关闭, 1 只记慢查询, 2 记全部（仅用于调试，开销大）
db.setProfilingLevel(1, { slowms: 100, sampleRate: 1.0 })
db.getProfilingStatus()

{ "was": 1, "slowms": 100, "sampleRate": 1, "ok": 1 }

db.setProfilingLevel(0)
db.system.profile.drop()
db.createCollection("system.profile", { capped: true, size: 100 * 1024 * 1024 })
db.setProfilingLevel(1, { slowms: 100 })

// 最慢的 5 条
db.system.profile.find({ ns: "community.posts" })
  .sort({ millis: -1 }).limit(5)
  .projection({ op: 1, millis: 1, command: 1, planSummary: 1, docsExamined: 1, nreturned: 1 })

[
  {
    "op": "query",
    "millis": 3421,
    "planSummary": "COLLSCAN",
    "docsExamined": 1204882,
    "nreturned": 12,
    "command": { "find": "posts", "filter": { "slug": "why-mongodb" } }
  }
]

db.system.profile.aggregate([
  { $match: { millis: { $gte: 100 } } },
  { $group: {
      _id: { ns: "$ns", plan: "$planSummary" },
      count: { $sum: 1 },
      totalMs: { $sum: "$millis" },
      avgMs: { $avg: "$millis" },
      maxMs: { $max: "$millis" }
  } },
  { $sort: { totalMs: -1 } },
  { $limit: 10 }
])

[
  { "_id": { "ns": "community.posts", "plan": "COLLSCAN" },
    "count": 4210, "totalMs": 1852300, "avgMs": 439.9, "maxMs": 3421 },
  { "_id": { "ns": "community.comments", "plan": "IXSCAN { postId: 1 }" },
    "count": 88120, "totalMs": 924100, "avgMs": 10.5, "maxMs": 210 }
]

{"t":{"$date":"2024-06-04T09:31:23.451+08:00"},"s":"I","c":"COMMAND",
 "msg":"Slow query","attr":{"type":"command","ns":"community.posts",
 "command":{"find":"posts","filter":{"slug":"why-mongodb"}},
 "planSummary":"COLLSCAN","keysExamined":0,"docsExamined":1204882,
 "nreturned":1,"durationMillis":3421}}

// 所有运行超过 3 秒的操作
db.currentOp({
  active: true,
  secs_running: { $gte: 3 },
  ns: /^community\./
})

{
  "inprog": [
    {
      "opid": 84213,
      "active": true,
      "secs_running": 128,
      "op": "command",
      "ns": "community.posts",
      "command": { "aggregate": "posts", "pipeline": [ { "$group": { "_id": "$authorId" } } ] },
      "planSummary": "COLLSCAN",
      "numYields": 84021,
      "waitingForLock": false,
      "lockStats": { }
    }
  ]
}

// 杀掉它
db.killOp(84213)

  理想状态：
  ┌────────────────────────────┐
  │   WiredTiger Cache (32GB)  │
  │  ┌──────────────────────┐  │
  │  │  热数据 (18GB)        │  │  ← 命中率 99%+
  │  │  索引  (10GB)         │  │
  │  └──────────────────────┘  │
  └────────────────────────────┘
       磁盘：冷数据 500GB
 
  糟糕状态：
  ┌────────────────────────────┐
  │   WiredTiger Cache (8GB)   │  ← 装不下
  └────────────────────────────┘
       每次查询都要读磁盘，延迟从 0.1ms 变成 5ms

db.serverStatus().wiredTiger.cache

{
  "maximum bytes configured": 34359738368,
  "bytes currently in the cache": 33982221312,
  "pages read into cache": 8421093,
  "pages written from cache": 1204882,
  "tracked dirty bytes in the cache": 421093888,
  "eviction server evicting pages": 84210
}

// 索引总大小 vs 内存
db.posts.stats().indexSizes
db.stats().indexSize

"mongodb://.../community?maxPoolSize=50&minPoolSize=5&maxIdleTimeMS=60000&waitQueueTimeoutMS=5000"

总连接数 = 应用实例数 × maxPoolSize × 副本节点数
 
  20 个 Pod × maxPoolSize 100 × 3 个节点 = 6000 个连接
 
  mongod 每个连接约占 1MB 栈空间 → 6GB 内存被连接吃掉
  而且 MongoDB 默认最大连接数是 65536，但实际上超过几千就会出问题

maxPoolSize ≈ 单实例并发请求数 × 每请求的平均数据库查询数
 
  例：单 Pod 峰值 200 QPS，平均响应 50ms，每请求 2 次查询
     并发查询数 ≈ 200 × 0.05 × 2 = 20
     maxPoolSize 设 30 到 50 足够

db.serverStatus().connections

{ "current": 842, "available": 51386, "totalCreated": 120483, "active": 27 }

// 慢：无法用索引
db.posts.find({ title: /mongodb/i })
 
// 改写方案一：前缀匹配（能用索引）
db.posts.find({ title: /^MongoDB/ })
 
// 改写方案二：冗余一个小写字段 + 文本索引
db.posts.createIndex({ titleLower: 1 })
db.posts.find({ titleLower: { $regex: "^mongodb" } })
 
// 改写方案三：上 Atlas Search / ES

// 慢：需要扫描索引中除了排除值之外的所有条目
db.posts.find({ status: { $ne: "deleted" } })
 
// 改写：正向枚举
db.posts.find({ status: { $in: ["draft", "published"] } })

// 慢
db.posts.find().sort({ _id: -1 }).skip(200000).limit(20)
 
// 改写：游标分页（第 6 章）
db.posts.find({ _id: { $lt: lastId } }).sort({ _id: -1 }).limit(20)

// 慢：内存排序，可能撞 32MB 上限
db.posts.find({ status: "published" }).sort({ "stats.likes": -1 })
 
// 改写：建符合 ESR 的索引
db.posts.createIndex({ status: 1, "stats.likes": -1 })

// 慢：对 10 万条帖子每条都关联一次
db.posts.aggregate([
  { $lookup: { from: "users", localField: "authorId", foreignField: "_id", as: "author" } },
  { $match: { status: "published" } },
  { $limit: 20 }
])
 
// 改写一：先 match 和 limit，再 lookup
db.posts.aggregate([
  { $match: { status: "published" } },
  { $sort: { createdAt: -1 } },
  { $limit: 20 },
  { $lookup: { from: "users", localField: "authorId", foreignField: "_id", as: "author" } }
])
 
// 改写二（更好）：用扩展引用模式冗余作者名，彻底去掉 lookup

// 慢：文档 5MB，每次读取都要传输整个数组
{ _id: 11, title: "...", comments: [ /* 20000 条 */ ] }
 
// 改写：子集模式 + 独立 comments 集合（第 12 章）

// 慢：循环里 N 次查询
for (const post of posts) {
  post.author = db.users.findOne({ _id: post.authorId })
}
 
// 改写：批量查询后在内存里 join
const ids = [...new Set(posts.map(p => p.authorId))]
const users = db.users.find({ _id: { $in: ids } }).toArray()
const map = new Map(users.map(u => [u._id.toString(), u]))
posts.forEach(p => { p.author = map.get(p.authorId.toString()) })

  用户反馈慢 / 监控告警
          │
          ▼
  1. 是全局慢还是某个接口慢？
     └─ 看 CPU / 内存 / 磁盘 IO / 连接数
          │
          ▼
  2. 全局慢 → 检查：
     ├─ WiredTiger cache 命中率（工作集是否超内存）
     ├─ 复制延迟（secondary 是否拖累）
     ├─ 是否有大批量写入 / 索引构建在跑
     └─ 连接数是否异常
          │
          ▼
  3. 局部慢 → 用 profiler 定位具体查询
     └─ 按 totalMs 排序，找出总耗时最高的查询形状
          │
          ▼
  4. 对该查询 explain("executionStats")
     ├─ 有 COLLSCAN？    → 建索引
     ├─ 有 SORT？        → 调整索引顺序（ESR）
     ├─ docsExamined 远大于 nReturned？ → 调整索引或改查询
     └─ 已经很优？       → 考虑改建模 / 加缓存 / 分片
          │
          ▼
  5. 改完后再 explain 验证，并观察线上指标