如何检查是否正在使用索引 [英] How do I check if an index is being used

问题描述

我有一个mongodb副本集，其中包含许多数据库，集合和&索引.

I have a mongodb replica set with a lot of databases, collections & indexes.

我们做了很多重构和优化，当然，我从消费者那里得到了很多创意查询".

We did a lot of refactor and optimization and, of course, I have a lot of "creative queries" from the consumers.

我想清理未使用的索引.只是想节省一些空间.

I would like to clean up the unused indexes. just wanna save some space.

如何检查索引是否正在使用?我可以按索引检查索引并删除未使用的索引.

How can I check if an index is being used? I can afford to check index by index and drop the unused ones.

在所有可能的查询中运行解释"不是一种选择:)

Running an "explain" in all the possible queries is not an option :)

基于接受的答案的解决方案

该脚本已被窃听.我不是JavaScript专家，但我输入了更正的脚本.我希望对某人有用:

The script was bugged. I am not a javascript expert, but I put the corrected script. I hope will be useful for someone:

DB.prototype.indexStats = function() {
  var queries = [];
  var collections = db.getCollectionNames();

  var findQuery = function(q) {
    for(entryIdx in queries) {
      if(q == queries[entryIdx].query) {
        return entryIdx;
      }
    }
    return -1;
  }

  for(cIdx in collections) {
    var cName = collections[cIdx];
    var nsName = db.getName()+"."+cName;
    if(cName.indexOf("system") == -1) {
      var i = 1;
      var count = db.system.profile.count({ns:nsName});
      print('scanning profile {ns:"'+nsName+'"} with '+count+' records... this could take a while...');
      db.system.profile.find({ns:nsName}).addOption(16).batchSize(10000).forEach(function(profileDoc) {           
        if(profileDoc.query && !profileDoc.query["$explain"]) { 
          var qIdx = findQuery(profileDoc.query);
          if(qIdx == -1 && profileDoc.query["query"] ) {
            var size = queries.push({query:profileDoc.query, count:1, index:""});                   
            var explain = db[cName].find(queries[size-1].query).explain();
            if(profileDoc.query && profileDoc.query["query"]) {
              queries[size-1].sort = profileDoc.query["orderby"];
              if(queries[size-1].sort) {
                explain = db[cName].find(queries[size-1].query.query).sort(queries[size-1].sort).explain();
              }
            }
            queries[size-1].cursor = explain.cursor;
            queries[size-1].millis = explain.millis;
            queries[size-1].nscanned = explain.nscanned;
            queries[size-1].n = explain.n;
            queries[size-1].scanAndOrder = explain.scanAndOrder ? true : false;
            if(explain.cursor && explain.cursor != "BasicCursor") {
              queries[size-1].index = explain.cursor.split(" ")[1];             
            } else {
              print('warning, no index for query {ns:"'+nsName+'"}: ');
              printjson(profileDoc.query);
              print('... millis: ' + queries[size-1].millis);
              print('... nscanned/n: ' + queries[size-1].nscanned + '/' + queries[size-1].n);
              print('... scanAndOrder: ' + queries[size-1].scanAndOrder);
            }
          } else if ( qIdx != -1 ) {
            queries[qIdx].count++;
          }
        }
      });
    }
  }

  for(cIdx in collections) {
    var cName = collections[cIdx];
    if(cName.indexOf("system") == -1) {
      print('checking for unused indexes in: ' + cName);
      for(iIdx in db[cName].getIndexes()) {
        var iName = db[cName].getIndexes()[iIdx].name;
        if(iName.indexOf("system") == -1) {
          var stats = db[cName].stats();
          var found = false;
          for(qIdx in queries) {
            if(queries[qIdx].index == iName) {
              found = true;
              break;
            }
          }
          if(!found) {
            print('this index is not being used: ');
            printjson(iName);
          }
        }
      }
    }
  }
}

推荐答案

在Github上有一个很酷的脚本，您应该看一下:

There is a pretty cool script out on Github that you should look at:

https://github.com/wfreeman/indexalizer

基本上，它涉及为数据库打开概要分析，然后它将使用探查器收集的数据来驱动explain()调用.然后，它告诉您哪些索引未使用，哪些查询未使用索引.很漂亮.

Basically it involves turning on profiling for your database and then it will use the data collected by the profiler to drive explain() calls. It then tells you both which indexes are not being used and which queries are not using indexes. Pretty slick.

有关mongoDB数据库分析的更多信息:

More about mongoDB database profiling:

http://docs.mongodb.org/manual/reference/database-profiler/

这篇关于如何检查是否正在使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！