39. MongoDB 慢查询剖析与索引顾问

// 查看当前级别
db.getProfilingStatus()
// { was: 0, slowms: 100, sampleRate: 1, ok: 1 }

// 级别 0：关闭（默认）
db.setProfilingLevel(0)

// 级别 1：只记录超过 slowms 的操作
db.setProfilingLevel(1, { slowms: 100 })

// 级别 2：记录所有操作（仅调试用，开销大）
db.setProfilingLevel(2)

// 阈值 50ms，采样 50%
db.setProfilingLevel(1, { slowms: 50, sampleRate: 0.5 })

// 只对特定集合提高阈值
db.setProfilingLevel(1, { slowms: 200, sampleRate: 1 })

// 也可以在启动参数中设置默认阈值
// mongod --slowms 100 --profile 1

db.system.profile.find().sort({ ts: -1 }).limit(1).pretty()

{
  op: "query",
  ns: "shop.orders",
  command: { find: "orders", filter: { customerId: "C1001" }, $db: "shop" },
  keysExamined: 0, docsExamined: 128400,   // 全表扫描的信号
  nreturned: 3, planSummary: "COLLSCAN",
  ts: ISODate("2026-10-06T07:00:00.000Z"),
  client: "10.0.0.21:52344", appName: "order-service",
  millis: 412
}

// 耗时最长的十条
db.system.profile.find().sort({ millis: -1 }).limit(10).forEach(p => {
  print(p.millis, p.ns, JSON.stringify(p.command.filter || p.command))
})

// 按命名空间聚合，找出问题集合
db.system.profile.aggregate([
  { $match: { millis: { $gte: 100 } } },
  { $group: { _id: "$ns", count: { $sum: 1 },
              avgMillis: { $avg: "$millis" },
              maxMillis: { $max: "$millis" } } },
  { $sort: { avgMillis: -1 } }
])

// 检查文档数远超返回数的查询
db.system.profile.find({
  planSummary: "COLLSCAN",
  docsExamined: { $gte: 1000 }
}).sort({ docsExamined: -1 }).limit(10)

// 关闭分析器后重建更大的 profile 集合
db.setProfilingLevel(0)
db.system.profile.drop()
db.createCollection("system.profile", { capped: true, size: 104857600 })  // 100MB
db.setProfilingLevel(1, { slowms: 100 })

// queryPlanner 只看计划不执行
db.orders.find({ customerId: "C1001" }).explain("queryPlanner")
// executionStats 执行并统计（推荐）
db.orders.find({ customerId: "C1001" }).explain("executionStats")
// allPlansExecution 执行所有候选计划（最详细，开销大）
db.orders.find({ customerId: "C1001" }).explain("allPlansExecution")

db.orders.find({ customerId: "C1001", status: "paid" })
  .explain("executionStats")

{
  queryPlanner: {
    winningPlan: {
      stage: "FETCH",
      inputStage: {
        stage: "IXSCAN",
        indexName: "customerId_1_status_1",
        indexBounds: { customerId: ["[\"C1001\", \"C1001\"]"],
                       status: ["[\"paid\", \"paid\"]"] },
        keysExamined: 3, docsExamined: 3
      }
    },
    rejectedPlans: [ { stage: "COLLSCAN" } ]
  },
  executionStats: {
    nReturned: 3, executionTimeMillis: 2,
    totalKeysExamined: 3, totalDocsExamined: 3
  }
}

COLLSCAN       全表扫描，最差
IXSCAN         索引扫描，期望
FETCH          按索引回表取文档
SORT           内存排序（无索引可用）
SORT_KEY_GENERATOR  排序键生成
PROJECTION_SIMPLE   投影裁剪
LIMIT / SKIP   分页

// 出现 SORT 说明排序未走索引
db.orders.find({ status: "paid" }).sort({ createdAt: -1 })
  .explain("executionStats").queryPlanner.winningPlan
// { stage: "SORT", inputStage: { stage: "COLLSCAN" } }

const es = db.orders.find({ customerId: "C1001" }).explain("executionStats").executionStats
print(`efficiency: ${(es.totalDocsExamined / es.nReturned).toFixed(2)}`)

db.orders.aggregate([{ $indexStats: {} }])

{
  name: "customerId_1_status_1",
  key: { customerId: 1, status: 1 },
  accesses: { ops: Long("184230"), since: ISODate("2026-09-01T00:00:00Z") }
}
{
  name: "legacyFlag_1",
  key: { legacyFlag: 1 },
  accesses: { ops: Long("0"), since: ISODate("2026-09-01T00:00:00Z") }
}

// 列出所有 ops 为 0 的索引
db.orders.aggregate([{ $indexStats: {} }]).forEach(s => {
  if (s.accesses.ops == 0) print("unused:", s.name)
})

// 删除未使用索引（先确认不是唯一索引或特殊用途）
db.orders.dropIndex("legacyFlag_1")

// 列出所有索引键模式，人工比对前缀
db.orders.getIndexes().forEach(i => print(JSON.stringify(i.key)))

db.orders.stats(1024)   // 单位 KB

{
  ns: "shop.orders",
  count: 13780000,
  size: 4294967296, avgObjSize: 311,
  storageSize: 3865470566, totalIndexSize: 1288490188,
  indexSizes: {
    "_id_": 456340275,
    "customerId_1_status_1": 512000000,
    "createdAt_-1": 320150913
  },
  nindexes: 3
}

// 用聚合形式获取（分片友好），latencyStats 给出读写延迟直方图
db.orders.aggregate([{ $collStats: { storageStats: {}, latencyStats: { histograms: true } } }])

// 找出运行超过 3 秒的操作
db.currentOp({ "secs_running": { $gte: 3 }, "active": true })

{
  inprog: [
    {
      opid: 84213, op: "query", ns: "shop.orders",
      secs_running: 12, numYields: 421, planSummary: "COLLSCAN",
      client: "10.0.0.21:52344", appName: "report-service",
      command: { find: "orders", filter: { note: { $regex: "urgent" } } }
    }
  ]
}

// 终止问题操作
db.killOp(84213)

// 现状：走 customerId 索引
db.orders.find({ customerId: "C1001", status: "paid" })
  .hint({ customerId: 1 }).explain("executionStats").executionStats
// totalDocsExamined: 8420, nReturned: 3

// 候选：新建复合索引后对比
db.orders.createIndex({ customerId: 1, status: 1 })

db.orders.find({ customerId: "C1001", status: "paid" })
  .hint({ customerId: 1, status: 1 }).explain("executionStats").executionStats
// totalDocsExamined: 3, nReturned: 3   ← 显著改善

// 反例：前置通配正则无法用索引
db.orders.find({ note: { $regex: ".*urgent.*" } })   // COLLSCAN

// 正例：前缀匹配可用索引
db.orders.find({ note: { $regex: "^urgent" } })      // IXSCAN