MongoDB找出冗余和未被使用的索引

2023-03-07 14:31 abce 阅读(73) 评论(0) 编辑收藏举报

找出冗余的索引

var ldb = db.adminCommand( { listDatabases: 1 } );
 
for ( i = 0; i < ldb.databases.length; i++ ) {
 
   if ( ldb.databases[i].name != 'admin' && ldb.databases[i].name != 'config' && ldb.databases[i].name != 'local') {
 
      print('DATABASE ',ldb.databases[i].name);
      print("+++++++++++++++++++++++++++++++++++++++++")
 
      var db = db.getSiblingDB(ldb.databases[i].name); 
      var cpd = db.getCollectionNames();
 
      for ( j = 0; j < cpd.length; j++ ) { 
 
         if ( cpd[j] != 'system.profile' ) {
 
            var indexes = JSON.parse(JSON.stringify(db.runCommand( { listIndexes: cpd[j] } ).cursor.firstBatch));
            print("COLL :"+cpd[j]);
 
            for ( k = 0; k < indexes.length; k++ ) {
 
               indexes[k] = (((JSON.stringify(indexes[k].key)).replace("{","")).replace("}","")).replace(/,/g ,"_");
 
            }
 
            var founddup = false;
 
            for ( k1 = 0; k1 < indexes.length; k1++ ) {
 
               for ( k2 = 0; k2 < indexes.length; k2++ ) {
 
                  if ( k1 != k2 ) {
 
                     if (indexes[k1].startsWith(indexes[k2],0)) {
 
                        print("{ "+indexes[k2]+" } is the left prefix of { "+indexes[k1]+" } and should be dropped");
 
                        founddup = true;
 
                     }
                  }
               } 
            }
 
            if (!founddup) {
 
               print("no duplicate indexes found");
 
            }
 
            print("\n");
 
         } 
      }
 
      print("\n");
   } 
}

找出未被使用的索引

var ldb=db.adminCommand( { listDatabases: 1 } );
 
   for (i=0; i<ldb.databases.length; i++) { 
 
      print('DATABASE ',ldb.databases[i].name);
 
      if ( ldb.databases[i].name != 'admin' && ldb.databases[i].name != 'config' && ldb.databases[i].name != 'local' ) {
 
      var db = db.getSiblingDB(ldb.databases[i].name); 
      var cpd = db.getCollectionNames();
 
      for (j=0; j<cpd.length; j++) {
 
         if ( cpd[j] != 'system.profile' ) {
 
            print(cpd[j]); 
 
            var pui = db.runCommand({ aggregate : cpd[j] ,pipeline : [{$indexStats: {}}],cursor: { batchSize: 100 } }); 
            printjson(pui);
 
         } 
      }
 
      print('\n\n'); 
   }
}