Abstract

In storage optimization (2)- Slow query optimization caused by sorting we mentioned the effect of sorting on query selection indexes. But the solution is to add an index. Be careful adding an index to mongo’s large table online. Some problems were also encountered in the process of adding indexes. Relevant records and analysis were carried out here.

Problem description

Table structure

_id,
biz_Id,
version,
name
Copy the code

The index

Primary key index 2. Biz_id,version Joint indexCopy the code

The query

"query": {"find":"historyRecord"."filter": {"bizId": 1234567}."sort": {"_id":-1},"limit": 1}}Copy the code

Add an index

bizId,_id
Copy the code

Add index procedure

For large tables (500 million records in the table), the process of index establishment involves locking the table, and a large number of read and write operations and data synchronization will definitely affect online operations. Therefore, we choose to establish a background index in the business trough, so that the table will not be locked. Note:

Mongo4.2 after optimized the indexing process, do not need background parameters of https://docs.mongodb.com/manual/reference/command/createIndexes/#dbcmd.createIndexes

After the index is created, the execution plan is viewed through the client connection, always scanning a row. Perfect, go to the new index.

"executionStats" : {
       "executionSuccess" : true."nReturned" : 1,
       "executionTimeMillis": 0."totalKeysExamined" : 1,
       "totalDocsExamined": 1.Copy the code

Then observe a few days of slow SQL, surprised to find that there is still a slow query, but the same statement, when put to the client query, is the execution of the new index. View the slow logs in System.Profiles

At the time, the slow query was going cached_plan.

Because the index was added later, plan-cache has not been updated yet. Clear the execution plan cache and perform the operation

db.historyRecord.getPlanCache().clear()
Copy the code

I kept looking, and it didn’t help. In the MongoDB Plan Cache to find some ideas, MongoDB execution Plan

I took a look at the cache plan

db.getCollection('historyRecord').getPlanCache().listQueryShapes()
    {
        "query" : {
            "bizId" : "xxxxx"
        },
        "sort" : 0
            "_id": 1.0},"projection": {}},Copy the code

This query uses the “bizId,version” index, and the index value under bizId=” XXXX “is around 100. In our data distribution, bizId and version within 100 May be 95%, and only 5% above 100, which will cause misjudgment in index judgment.

conclusion

The final solution is to avoid index misjudgment by forcing the index, or by changing the sort to

sort({bizId:-1,_id:-1})
Copy the code

There will be no miscalculation

To sum up:

  1. Add indexes to large tables. Ensure that no other operations are performed on the block table
  2. After the index is added, check whether the index plays a role only by explaining the possibility of misjudgment or by combining the slowlog of the database
  3. The same query database does not always use the same index and will be adjusted according to the query. It needs to be analyzed in combination with plan cache and other situations
  4. Fixing database index errors you can force indexes or adjust statements to guide the database to correct errors.

reference

Mongoing.com/archives/56…