Last year, the project was reconstructed, and the system was switched to microservice architecture, and we also did some practice of sub-database and sub-table in all aspects of the database. I feel that I wrote the code heartily and pleasantly during that period. However, the old system still needs to be maintained, and according to the current situation, the two systems may be provided to users at the same time in the next few years. We have no opinion on development, all for the operation service (inner OS: product and operation can we push forward the switch as soon as possible, after all, the old still need to write JSP, which is sour cool, hard to understand).

This day, the boss lost a slow query SQL statement, said the old system service SQL statement query exceeds 4s+, see where the specific business is in use and confirm whether there is room for optimization, the specific query statement example is as follows (business security problem, can not be displayed to the public) :

select o.attr1,o.attr2,o.attr3, u.attr4, u.attr5 from order o outer join user u on 
o.u_id=u.id and o.createAt>='2022-03-20 00:00:00' and o.status="success" and o.feeType='TV' and o.payType='ali_pay' ....
Copy the code

The main function of this service is to update the subscription rights and interests of users from three parties regularly every 5s. Okay, so I’m going to do the task, and I’m going to run it on the test environment and I’m going to look at the execution plan of the SQL statement,

id select_type table type possible_key key key_len ref rows Extra 1 SIMPLE this_ index PRIMARY 4 56 Using where 1 SIMPLE  user2_ eq_ref PRIMARY PRIMARY 4 this_.user_id 1 Using whereCopy the code

Test environment is more than 50 test purchase order data, it seems, although is the use of the primary key index, but rows range is the whole table, this basically is the query in descending order according to the id of the order table, remove the sort you can see, is actually a full table scan clearly:

id select_type table type possible_key key key_len ref rows Extra 1 SIMPLE this_ index PRIMARY 4 56 Using where 1 SIMPLE  user2_ eq_ref PRIMARY PRIMARY 4 this_.user_id 1 Using whereCopy the code

Ok, let the operation and maintenance confirm the purchase order table data of the live network, up to 340W+, the query time reaches 4.737s, at this level, the full table scan is unacceptable, and the amount of more than 1W every day continues to increase, in this case, the query must be optimized, the optimization of the first C bit, must use the index. At present, in addition to the default id primary key, the order table has added an index to the user_id field, which is used for business associated query. However, for this requirement, it cannot effectively reduce the scan range, so new indexes need to be considered. Determine what index to build?

  1. There are several conditions in the query conditions: CreateAt (createAt); createAt (createAt); createAt (createAt); createAt (createAt); So createAt is the best field to create an index
  2. Considering the existing business, can the index be created to serve more business needs? Reorganize the whole service application, the order table also has the following applications:
    • Query the successful payment situation of a certain time range in the channel (1 place, with low application frequency, mainly used for operating back-end query)
    • Query the successful subscription record of a user’s TV paid service type (1 place, executed every 30s)
    • Query TV terminal order records based on time range (1 place, executed every 30s)

Therefore, combined with the specific business form, we decided to use composite index to further improve index reuse and query sharing ability. Create index as follows:

alter table order add index time_user_status_idx(createAt,user_id,status)
Copy the code

After determining the processing idea, I discussed with the boss and compared the current business situation of the service. Then I handled it together with the service launch in the evening. At 12 o ‘clock in the morning, the operation and maintenance students sent the good news, and the execution time was 0.001s. Although this optimization is not very in-depth, it does understand the importance of performance optimization when data volume comes up in the business. The road ahead is long and requires a lot of thinking.