mongodb index rule

Mongodb's indexing rules are basically the same as those of traditional relational databases. Most techniques for optimizing MySQL/Oracle/SQLite indexes are also applicable to mongodb.

Why index

• When some conditions are used in the query, the key can be indexed to improve the query speed.
• When the amount of data is large and the query is redundant, the index can be used to improve the query speed.

I won't talk much nonsense. I'll talk about it later

• Total data of table 20 million+
  

• Query table index
  

• The query is executed without indexing
  

• Index the fields to be filtered (at this time, the database has 20 million + data, and the execution time is 89s)
  

• Query table index (established)
  

• Queries are executed when indexed
  
  (▽) the index is established to query 20 million data, and the speed is reduced from 30 seconds to 1 millisecond, which is extremely efficient!
  
  Note: when creating an index, try to create it when creating a table. Otherwise, when the table data is too large, creating an index will consume a lot of time and cpu
  

A little, but not much

Of course, there are costs to using indexes: for each index added, each write operation (insert, update, delete) will take more time. This is because when the data changes, not only the document, but also all indexes on the level collection should be updated. Therefore, mongodb limits each collection to a maximum of 64 indexes.

Common operation

unique index

  db.launcher4k20170628.ensureIndex({"name":1},{"unique":true})

single column

  db.launcher4k20170628.ensureIndex({positionCode:1},{name:'positionCode_index'})

Composite index

The values of the index are arranged in a certain order, so using the index key to sort documents is very fast.

  db.launcher4k20170628.ensureIndex({positionCode:1,launcherType:1},{name:'position_index'})

View the index we built

  db.launcher4k20170628.getIndexes()

Delete index

  //Delete single
  db.launcher4k20170628.dropIndex('name')

  //Delete all
  db.launcher4k20170628.dropIndex('*')

Use explain

explain is a very useful tool that will help you get a lot of useful information about queries. As long as you call this method on the cursor, you can get the query details. explain returns a document, not the cursor itself. For example:

explain will return statistics on the index used by the query, time consumption and the number of documents scanned.

Project practice

Statistics function, from mongo statistics to mysql at regular intervals every morning. However, as the amount of data increases, the maximum data reaches 90 million + data. To make statistics on such a huge data, you can't avoid using indexes and mongo aggregation functions.

There are many problems because the index was not added before. For example, as mentioned in the previous article [MongoDB query timeout exception SocketTimeoutException]
It often causes the connection pool to fail, and the query time is too long, resulting in the connection failure of monggo.

org.springframework.dao.DataAccessResourceFailureException: Read operation to server 172.23.5.7:9343 failed on database launcher4k;
 nested exception is com.mongodb.MongoException$Network: Read operation to server 172.23.5.7:9343 failed on database launcher4k
	at org.springframework.data.mongodb.core.MongoExceptionTranslator.translateExceptionIfPossible(MongoExceptionTranslator.java:59)
	at org.springframework.data.mongodb.core.MongoTemplate.potentiallyConvertRuntimeException(MongoTemplate.java:1926)
	at org.springframework.data.mongodb.core.MongoTemplate.execute(MongoTemplate.java:396)
	at org.springframework.data.mongodb.core.MongoTemplate.executeCommand(MongoTemplate.java:336)
	at org.springframework.data.mongodb.core.MongoTemplate.aggregate(MongoTemplate.java:1425)
	at org.springframework.data.mongodb.core.MongoTemplate.aggregate(MongoTemplate.java:1360)
	at com.amt.modules.dao.OprationDaoImpl.getCountsTime(OprationDaoImpl.java:123)
	at com.amt.modules.service.OprationServiceImpl.saveCountsTime(OprationServiceImpl.java:379)
	at com.amt.modules.common.ContentTaskMethod.moveDateMongoToMysql(ContentTaskMethod.java:56)
	at com.amt.modules.common.SystemTask.createStatisticData(SystemTask.java:45)
	at sun.reflect.GeneratedMethodAccessor2305.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

You can optimize by configuring the mongo file, but this is not a long-term solution.

      mongo.connectionsPerHost=8
      mongo.threadsAllowedToBlockForConnectionMultiplier=4
      mongo.connectTimeout=1000
      mongo.maxWaitTime=2000 //Maximum connection duration in seconds
      mongo.autoConnectRetry=true
      mongo.socketKeepAlive=true
      mongo.socketTimeout=0
      mongo.slaveOk=true

This requires indexing to greatly increase the query speed

• Create federated index

    //The method is out of date
    private void createIndex(String name){
     IndexOperations  io=mongoTemplate.indexOps(name);
     Index index =new Index();
       index.on("positionCode",Order.ASCENDING);
       index.on("positionName",Order.ASCENDING);
       index.on("launcherType",Order.ASCENDING);
       index.on("terminalId",Order.ASCENDING);
       index.on("labelId",Order.ASCENDING);
       io.ensureIndex(index);
    }
    //normal
    private void createIndex(String name){
       DBObject indexOptions = new BasicDBObject();
       indexOptions.put("positionCode", 1);
       indexOptions.put("positionName", 1);
       indexOptions.put("launcherType.d", 1);
       indexOptions.put("labelId", 1);
       CompoundIndexDefinition indexDefinition =new CompoundIndexDefinition(indexOptions);
       mongoTemplate.indexOps(name).ensureIndex(indexDefinition);
	  }

    public void create(OprationEntity opration) throws Exception {
    mongoTemplate.insert(opration, getTableName(opration.getUpdateTime()));
    this.createIndex(getTableName(opration.getUpdateTime()));
    }

• Create a single column index

  /**
   * Add index
   * @param string
   */
  @SuppressWarnings("deprecation")
  private void createIndex(String name){
     IndexOperations  io=mongoTemplate.indexOps(name);
       io.ensureIndex(new Index().on("positionCode", Order.ASCENDING));
       io.ensureIndex(new Index().on("positionName", Order.ASCENDING));
       io.ensureIndex(new Index().on("launcherType", Order.ASCENDING));
       io.ensureIndex(new Index().on("labelId", Order.ASCENDING));
  }

Note: when testing whether the index creation is successful, I manually delete the library, and then execute the index creation, which is invalid. However, if the newly created table is indexed, the index value is generated.

[mongoTemplate reference document address]

Please indicate the source for Reprint: [www.updatecg.xin]