MongoDB: 性能优化与监控：生产级调优

// === mongoose 连接池配置 ===
mongoose.connect(uri, {
  maxPoolSize: 50,          // 最大连接数（默认 100）
  minPoolSize: 5,           // 最小连接数（默认 0）
  maxIdleTimeMS: 30000,     // 连接空闲超时（默认无限）
  waitQueueTimeoutMS: 10000 // 等待连接超时
});

// === 监控连接池 ===
const db = mongoose.connection.db;
const stats = await db.admin().command({ serverStatus: 1 });
console.log('Connections:', stats.connections);

// === 强制使用索引 ===
const products = await Product.find({ category: 'Electronics' })
  .hint({ category: 1 });

// === mongoose 等价 ===
const products = await Product.find({ category: 'Electronics' })
  .hint('category_1');

// === 只查询必要字段 ===
const products = await Product.find()
  .select('sku title price')  // 不查 description, images 等
  .lean();

// === 减少网络传输 90%+ ===

// === 普通查询：构造 mongoose Document（慢）===
const products = await Product.find();

// === lean()：直接返回 plain object（快）===
const products = await Product.find().lean();

// ❌ 慢：$where 执行 JavaScript
db.products.find({ $where: 'this.price > 100' });

// ✅ 快：使用 $gt
db.products.find({ price: { $gt: 100 } });

// === 启用慢查询日志 ===
mongoose.connection.db.admin().command({
  setParameter: 1,
  slowms: 100  // 记录 > 100ms 的查询
});

// === mongoose 中启用 debug ===
mongoose.set('debug', true);
// 输出所有查询到 console

// === mongoose-debug 慢查询日志 ===
mongoose.set('debug', (collectionName, method, query, doc) => {
  const start = Date.now();
  console.log(`${collectionName}.${method}(${JSON.stringify(query)})`);
});

// === 索引使用分析 ===
db.products.aggregate([{ $indexStats: {} }]);
// 找出 unused indexes

// === 慢查询中的索引使用 ===
db.system.profile.find({ millis: { $gt: 100 } })
  .sort({ ts: -1 })
  .limit(20)
  .forEach(profile => {
    console.log('Query:', profile.command.find);
    console.log('Plan:', profile.planSummary);
    console.log('Time:', profile.millis, 'ms');
  });

## 10. 性能优化 Checklist
---

### (1) 数据库
- [ ] 副本集 3 节点（高可用）
- [ ] Write Concern majority（数据不丢）
- [ ] Read Preference primaryPreferred
- [ ] 慢查询监控启用
- [ ] 索引使用率监控
- [ ] 连接池 maxPoolSize 设置

### (2) 应用层
- [ ] mongoose lean() 用于纯查询
- [ ] projection 只查必要字段
- [ ] bulkWrite 批量操作
- [ ] 避免 $where / $regex 无锚定
- [ ] 错误处理中间件
- [ ] 健康检查端点 /healthz

### (3) 运维
- [ ] 每日 mongodump 备份
- [ ] 备份保留 7-30 天
- [ ] 监控告警（连接数 / 慢查询 / 磁盘）
- [ ] TLS/SSL 加密传输
- [ ] SCRAM + RBAC 权限控制
- [ ] Atlas PITR 或 oplog 持续备份

// === 优化前的慢查询 ===
app.get('/api/products', async (req, res) => {
  const products = await Product.find({ isActive: true });
  // 100 万文档全表扫描，~3 秒
  res.json(products);
});

// === 优化后 ===
app.get('/api/products', async (req, res) => {
  const { page = 1, limit = 20, category, search } = req.query;

  // 1. 构建索引优化查询
  const query = { isActive: true };
  if (category) query.category = category;
  if (search) query.title = new RegExp(search, 'i');

  // 2. 投影 + lean + limit
  const products = await Product.find(query)
    .select('sku title price thumbnail')  // 投影
    .hint({ isActive: 1, category: 1 })  // 强制索引
    .limit(Math.min(+limit, 100))  // 限制最大
    .skip((+page - 1) * +limit)
    .lean();  // 性能优化

  // 3. 并行 count
  const total = await Product.countDocuments(query);

  res.json({ data: products, meta: { page: +page, limit: +limit, total } });
});
// 优化后：~50ms（性能 ↑60x）

// === 场景：ShopHub 商品列表查询慢，Alice 用 explain 诊断 ===

// 1. 诊断当前查询
const explain = await Product.find({ category: 'Electronics', isActive: true })
  .sort({ createdAt: -1 })
  .limit(20)
  .explain('executionStats');

console.log('Stage:', explain.queryPlanner.winningPlan.stage);
// 输出：COLLSCAN ❌ 全表扫描！

console.log('Docs examined:', explain.executionStats.totalDocsExamined);
// 输出：1000000（扫描了全部 100 万文档）

console.log('Docs returned:', explain.executionStats.nReturned);
// 输出：20（只返回 20 条）

console.log('Time:', explain.executionStats.executionTimeMillis, 'ms');
// 输出：3200ms（太慢！）

// 2. 创建复合索引
db.products.createIndex({ category: 1, isActive: 1, createdAt: -1 });

// 3. 再次 explain 验证
const explain2 = await Product.find({ category: 'Electronics', isActive: true })
  .sort({ createdAt: -1 })
  .limit(20)
  .hint({ category: 1, isActive: 1, createdAt: -1 })
  .explain('executionStats');

console.log('Stage:', explain2.queryPlanner.winningPlan.stage);
// 输出：IXSCAN ✅ 使用索引！

console.log('Docs examined:', explain2.executionStats.totalDocsExamined);
// 输出：20（精确扫描）

console.log('Time:', explain2.executionStats.executionTimeMillis, 'ms');
// 输出：5ms（↑640x 性能提升！）

// === 场景：商品列表 API 性能优化（3s → 50ms）===

// === 优化前（慢）===
app.get('/api/products', async (req, res) => {
  const products = await Product.find();  // 全表扫描 + 返回所有字段
  res.json(products);
});
// 100 万文档，~3000ms，~50MB 数据

// === 优化后（快）===

// 1. 启用 mongoose debug（开发时观察查询）
mongoose.set('debug', (coll, method, query) => {
  console.log(`${coll}.${method}(${JSON.stringify(query)})`);
});

// 2. 优化连接池
mongoose.connect(uri, {
  maxPoolSize: 50,         // 根据并发调整
  minPoolSize: 5,
  maxIdleTimeMS: 30000,
  waitQueueTimeoutMS: 10000
});

// 3. 启用慢查询日志（>100ms）
mongoose.connection.db.admin().command({
  setParameter: 1,
  slowms: 100
});

// 4. 创建合适的索引
db.products.createIndex({ category: 1, isActive: 1, createdAt: -1 });
db.products.createIndex({ sku: 1 }, { unique: true });
db.products.createIndex({ title: 'text', description: 'text' });

// 5. 优化查询：projection + lean + hint + limit
app.get('/api/products', async (req, res) => {
  const { page = 1, limit = 20, category, search } = req.query;

  const query = { isActive: true };
  if (category) query.category = category;
  if (search) query.title = new RegExp(search, 'i');

  const [products, total] = await Promise.all([
    Product.find(query)
      .select('sku title price thumbnail rating')  // 投影：仅返回 5 个字段
      .hint({ category: 1, isActive: 1, createdAt: -1 })  // 强制索引
      .sort({ createdAt: -1 })
      .skip((page - 1) * limit)
      .limit(Math.min(+limit, 100))
      .lean(),  // 跳过 mongoose hydrate
    Product.countDocuments(query)
  ]);

  res.json({
    success: true,
    data: products,
    meta: { page: +page, limit: +limit, total, pages: Math.ceil(total / limit) }
  });
});
// 100 万文档，~50ms（性能 ↑60x），~200KB 数据（减少 99.6%）

// === 6. explain() 验证索引生效 ===
const explain = await Product.find({ category: 'Electronics', isActive: true })
  .sort({ createdAt: -1 })
  .limit(20)
  .explain('executionStats');

console.log('Stage:', explain.queryPlanner.winningPlan.stage);
// 输出：IXSCAN（使用了索引）

console.log('Docs examined:', explain.executionStats.totalDocsExamined);
console.log('Keys examined:', explain.executionStats.totalKeysExamined);
console.log('Returned:', explain.executionStats.nReturned);
console.log('Time:', explain.executionStats.executionTimeMillis, 'ms');

// === 7. 监控告警 ===
// 7.1 监控连接数
const connStatus = await mongoose.connection.db.admin().command({ serverStatus: 1 });
if (connStatus.connections.current > 1000) {
  console.warn(`⚠️  连接数过高: ${connStatus.connections.current}`);
  // 发送告警（邮件/钉钉/Slack）
}

// 7.2 监控慢查询
const slowQueries = await mongoose.connection.db.collection('system.profile')
  .find({ millis: { $gt: 100 } })
  .sort({ ts: -1 })
  .limit(10)
  .toArray();

slowQueries.forEach(q => {
  console.log(`[${q.ts}] ${q.command.find}: ${q.millis}ms`);
  console.log(`  Plan: ${q.planSummary}`);
});

// 7.3 索引使用率
const indexStats = await mongoose.connection.db.collection('products')
  .aggregate([{ $indexStats: {} }])
  .toArray();

const unused = indexStats.filter(s => s.accesses.ops === 0);
unused.forEach(i => {
  console.log(`⚠️  未使用索引: ${i.name}`);
  // 自动删除（生产环境谨慎）
  // await mongoose.connection.db.collection('products').dropIndex(i.name);
});

// === 8. APM 集成（Datadog/New Relic）===
const tracer = require('dd-trace').init();
tracer.use('mongoose', { service: 'shopdb' });
// 所有 mongoose 查询自动追踪，性能数据上报 Datadog

const mongoose = require('mongoose');

// === 1. 开启 Profiler（三种模式）===
// 模式 0：关闭（默认）；模式 1：只记录慢查询；模式 2：记录所有操作
await mongoose.connection.db.adminCommand({
  profile: 1,                    // 模式 1：只记录慢查询
  slowms: 50,                    // 慢查询阈值 50ms
  sampleRate: 1                  // 采样率 100%
});

// === 2. 查询 Profiler 数据（system.profile 集合）===
async function getSlowQueries(minutes = 30) {
  const since = new Date(Date.now() - minutes * 60 * 1000);
  const slowQueries = await mongoose.connection.db
    .collection('system.profile')
    .find({
      ts: { $gte: since },
      op: { $in: ['query', 'update', 'remove', 'getmore'] },
      'command.aggregate': { $exists: false }  // 排除聚合（另分析）
    })
    .sort({ millis: -1 })
    .limit(20)
    .toArray();

  return slowQueries.map(q => ({
    timestamp: q.ts,
    operation: q.op,
    collection: q.ns,
    query: JSON.stringify(q.query || q.command),
    durationMs: q.millis,
    docsExamined: q.docsExamined,
    docsReturned: q.nreturned,
    indexUsed: q.planSummary,
    efficiency: q.docsExamined > 0
      ? (q.nreturned / q.docsExamined * 100).toFixed(1) + '%'
      : 'N/A'
  }));
}

// === 3. 索引使用统计 ===
async function analyzeIndexUsage(collectionName) {
  const stats = await mongoose.connection.db
    .collection(collectionName)
    .aggregate([{ $indexStats: {} }])
    .toArray();

  return stats.map(s => ({
    name: s.name,
    accesses: s.accesses.ops,
    since: s.accesses.since,
    isUnused: s.accesses.ops === 0,
    keys: Object.keys(s.key),
    size: s.size || 'N/A'
  }));
}

// === 4. explain() 诊断工具函数 ===
async function diagnoseQuery(collection, query, sort = {}) {
  const explanation = await mongoose.connection.db
    .collection(collection)
    .find(query)
    .sort(sort)
    .explain('executionStats');

  const stage = explanation.queryPlanner.winningPlan;
  const stats = explanation.executionStats;

  return {
    totalDocsExamined: stats.totalDocsExamined,
    totalDocsReturned: stats.totalDocsReturned,
    executionTimeMs: stats.executionTimeMillis,
    indexUsed: stage.inputStage?.indexName || 'COLLSCAN',
    isCollectionScan: stage.stage === 'COLLSCAN',
    efficiency: stats.totalDocsExamined > 0
      ? (stats.totalDocsReturned / stats.totalDocsExamined * 100).toFixed(1) + '%'
      : 'N/A',
    recommendation: getRecommendation(stage, stats)
  };
}

function getRecommendation(stage, stats) {
  const ratio = stats.totalDocsReturned / Math.max(stats.totalDocsExamined, 1);
  if (stage.stage === 'COLLSCAN') return '缺少索引！添加匹配查询条件的索引';
  if (ratio < 0.01) return '索引效率低！查询扫描了大量文档但返回很少，考虑更精确的索引';
  if (stats.executionTimeMillis > 100) return '查询仍较慢，考虑添加覆盖索引或优化排序';
  return '查询性能良好';
}

// === 5. 自动索引建议 ===
async function suggestIndexes(collectionName) {
  const slowQueries = await mongoose.connection.db
    .collection('system.profile')
    .find({ ns: `${mongoose.connection.name}.${collectionName}` })
    .sort({ millis: -1 })
    .limit(10)
    .toArray();

  const fieldFrequency = {};
  slowQueries.forEach(q => {
    const fields = q.query ? Object.keys(q.query) : [];
    fields.forEach(f => { fieldFrequency[f] = (fieldFrequency[f] || 0) + 1; });
  });

  const sortedFields = Object.entries(fieldFrequency)
    .sort((a, b) => b[1] - a[1])
    .map(([field, count]) => ({ field, queryCount: count }));

  return {
    collection: collectionName,
    topQueryFields: sortedFields.slice(0, 5),
    suggestedIndex: sortedFields.slice(0, 3).map(f => f.field).reduce((idx, f) => {
      idx[f] = 1; return idx;
    }, {})
  };
}

// === 6. 定时报告 ===
setInterval(async () => {
  const slowQueries = await getSlowQueries(5);
  if (slowQueries.length > 0) {
    console.log(`⚠️ 最近 5 分钟慢查询 (${slowQueries.length} 条):`);
    slowQueries.forEach(q => {
      console.log(`  ${q.collection} | ${q.durationMs}ms | 效率: ${q.efficiency} | 索引: ${q.indexUsed}`);
    });
  }
}, 5 * 60 * 1000);