mongodb常用脚本

那些你一定会用到的 MongoDB 查询脚本，本文脚本基于 MongoDB 3.4.20 - 8.0.19 测试，可用于定期巡检。

可用性

副本集节点状态

• 查看副本集名称、节点地址、角色、主从延迟

var status=rs.status(),replSetName=rs.conf()._id,primaryTime=0;
status.members.forEach(function(m){if(m.stateStr==="PRIMARY")primaryTime=m.optimeDate.getTime();});
if(!primaryTime){print("Error：Not found PRIMARY");quit();}
print("replicaSet,host,role,delaySeconds");

status.members.forEach(function(m) {
  var role = m.stateStr,delaySec = 0;
  if (role !== "PRIMARY") {
    var optimeDate = m.optimeDate || m.lastHeartbeatRecv || new Date(0);
    var delayMs = primaryTime - optimeDate.getTime();
    delaySec = Math.max(0, Math.floor(delayMs / 1000));
  }
  print([replSetName, m.name, role, delaySec].join(','));
});

性能篇

关注 top 20 大库

• 查看每个 db 的库名、storageSizeMB (磁盘实际占用,包含空闲碎片)、dataSizeMB (真实业务数据大小,不包括碎片/索引)、storageSizeMB / dataSizeMB (压缩比/膨胀系数,数值越大碎片越严重)

// 设置优先读从节点，减轻主库压力
db.getMongo().setReadPref('secondaryPreferred');

var dbList = db.adminCommand({listDatabases: 1});
var databases = dbList.databases;
var resultList = [];

databases.forEach(function(d) {
  var dbName = d.name;
  if (['admin','local','config'].includes(dbName)){return;}
  var storageSizeMB = d.sizeOnDisk / 1024 / 1024;
  var stats = db.getSiblingDB(dbName).stats(1024*1024);
  var dataSizeMB = stats.dataSize;
  var ratio = dataSizeMB > 0 ? (storageSizeMB / dataSizeMB) : 0;
  resultList.push({name:dbName,storageSizeMB:storageSizeMB,dataSizeMB:dataSizeMB,ratio:ratio});
});

var dummy = resultList.sort(function(a,b){ return b.storageSizeMB - a.storageSizeMB; });
var topx = resultList.slice(0,20);

// print("dbName,storageSizeMB,dataSizeMB,storage/size");
topx.forEach(function(item){
  print(`${item.name},${item.storageSizeMB.toFixed(2)},${item.dataSizeMB.toFixed(2)},${item.ratio.toFixed(2)}`);
});

关注 top 20 大表

• 查看每个 collection 的库名、表名、行数、storageSizeMB (磁盘实际占用,包含空闲碎片)、dataSizeMB (真实业务数据大小,不包括碎片/索引)、storageSizeMB / dataSizeMB (压缩比/膨胀系数,数值越大碎片越严重)

附：建议对压缩比超过1.5的大表进行压缩优化，语法：db.runCommand({compact: 'xxxxxx', force: true});

// 设置优先读从节点，减轻主库压力
db.getMongo().setReadPref('secondaryPreferred');

var dbs = db.adminCommand('listDatabases').databases;
var collList = [];
dbs.forEach(dbInfo => {
  var dbName = dbInfo.name;
  if (dbName === 'admin' || dbName === 'local' || dbName === 'config') { return; }
  var targetDb = db.getSiblingDB(dbName);
  var collections = targetDb.getCollectionNames();
  collections.forEach(collection => {
    const stats = targetDb[collection].stats();
    if (stats.count >= 1) {
      collList.push({
        db: dbName, 
        coll: collection, 
        count: stats.count, 
        dataSizeMB: stats.size/1024/1024, 
        storageSizeMB: stats.storageSize/1024/1024, 
        ratio: stats.storageSize/stats.size 
      });
    }
  });
});

var dummy = collList.sort((a, b) => b.storageSizeMB - a.storageSizeMB);
var topx = collList.slice(0, 20);

// print("dbName,collection,count,storageSizeMB,dataSizeMB,storage/size");
topx.forEach(item => {
  print(`${item.db},${item.coll},${item.count},${item.storageSizeMB.toFixed(2)},${item.dataSizeMB.toFixed(2)},${item.ratio.toFixed(2)}`);
});

• 当前 db 的 TOP 10 大表：

// 设置优先读从节点，减轻主库压力
db.getMongo().setReadPref('secondaryPreferred');

var targetDb = db;  // 当前数据库
var collList = [];
var collections = targetDb.getCollectionNames();

collections.forEach(collection => {
  const stats = targetDb[collection].stats();
  if (stats.count >= 1) {
    collList.push({
      db: targetDb.getName(), 
      coll: collection, 
      count: stats.count, 
      dataSizeMB: stats.size/1024/1024, 
      storageSizeMB: stats.storageSize/1024/1024, 
      ratio: stats.storageSize/stats.size 
    });
  }
});

var sorted = collList.sort((a, b) => b.storageSizeMB - a.storageSizeMB);
var topx = sorted.slice(0, 10);

// print("dbName,collection,count,storageSizeMB,dataSizeMB,storage/size");
topx.forEach(item => {
  print(`${item.db},${item.coll},${item.count},${item.storageSizeMB.toFixed(2)},${item.dataSizeMB.toFixed(2)},${item.ratio.toFixed(2)}`);
});

行数为 0 的空表

// 设置优先读从节点，减轻主库压力
db.getMongo().setReadPref('secondaryPreferred');

var dbs = db.adminCommand('listDatabases').databases;
dbs.forEach(dbInfo => {
  var dbName = dbInfo.name;
  if (dbName === 'admin' || dbName === 'local' || dbName === 'config') { return; }
  var targetDb = db.getSiblingDB(dbName);
  var collections = targetDb.getCollectionNames();
  collections.forEach(collection => {
    const stats = targetDb[collection].stats();
    if (stats.count === 0) { 
      print(`${dbName},${collection},${stats.count}`);
    }
  });
});

安全篇

检查具有super权限的账号

var users = db.getSiblingDB("admin").runCommand({usersInfo:1});
if (users && users.ok === 1 && users.users) {
  var highPrivUsers = users.users.filter(function(user) {
    return (user.roles || []).some(function(role) {
      return ["root","dbOwner","clusterAdmin","userAdminAnyDatabase","dbAdminAnyDatabase"].includes(role.role) || 
        (role.db === "admin" && ["root","dbOwner","clusterAdmin","userAdminAnyDatabase","dbAdminAnyDatabase"].includes(role.role));
    });
  });

  highPrivUsers.forEach(function(user) {
    var roleInfo = (user.roles || []).map(function(role) { return role.role + "," + (role.db || "admin"); }).join("; ");
    print(user.user + "," + user.db + "," + roleInfo);
  });
};

个人收藏

• 分批删除历史数据，示例 aaa 表、每次删除 10000 行、每次停顿 10 毫秒：

实测：删1万条一开始仅1.5秒，但越删越慢，到后来删1万条需3秒，删1521万总耗时 98分钟

// 前提：insert_time 字段上有索引
var cutoffDate = new Date("2024-09-01T00:00:00");
var batchSize = 10000;
var totalDeleted = 0;

// 示例 lt 删除 < 20240901 以前的数据
while (true) {
  var ids = db.aaa.find({insert_time:{$lt:cutoffDate}},{_id:1}).limit(batchSize).toArray().map(d =>d._id);
  if (ids.length === 0) break;
  var res = db.aaa.deleteMany({ _id: { $in: ids } });
  totalDeleted += res.deletedCount;
  print("已删除: " + totalDeleted + " 条");
  sleep(10);  // 停顿10毫秒
}
print("删除完成，共删除: " + totalDeleted + " 条");