Mysql performance optimization case-Covering Index sharing, mysql Performance Optimization

Mysql performance optimization case-Covering Index sharing, mysql Performance Optimization

Scenario

There is an image table in the product, with nearly 1 million data records, and a related query statement. Due to the high execution frequency, I want to optimize this statement.

The table structure is very simple. main fields:

Copy codeThe Code is as follows:
User_id user ID
Picname
Smallimg thumbnail name

One user will have multiple image records

Now there is an index created based on user_id: uid

The query statement is also simple: getting a user's image set

Copy codeThe Code is as follows:
Select picname, smallimg
From pics where user_id = xxx;

Before Optimization

Execute the query statement (to view the actual execution time, force the cache not to be used)

Copy codeThe Code is as follows:
Select SQL _NO_CACHE picname, smallimg
From pics where user_id = 17853;

Executed 10 times, with an average time of about 40 ms

Use explain for analysis
Copy codeThe Code is as follows:
Explain select SQL _NO_CACHE picname, smallimg
From pics where user_id = 17853

The user_id index is used and the const constant is used for searching, which indicates that the performance is good.

After Optimization

Because this statement is too simple and there is no optimization space for the SQL itself, the index is taken into account.

Modify the index structure and create a joint index (user_id, picname, smallimg): uid_pic

Re-run 10 times, with the average time consumption reduced to about 30 ms

Use explain for analysis

We can see that the Index used is changed to the newly created joint Index, and the Extra section shows that the 'using Index' is used'

Summary

'Using Index' indicates "overwriting Index", which is the key to improving the performance of the preceding SQL statements.

An index that contains the fields required for the query is called "Overwrite Index"

MySQL only needs to return the data required for the query through the index, instead of performing Back-to-table operations after the index is found, reducing IO and improving efficiency.

For example, in the preceding SQL statement, the query condition is user_id. You can use the Union index. The field to be queried is picname smallimg. These two fields are also in the Union index, this enables "overwriting Index". You can perform a one-time query based on the combined index, which improves the performance.

Articles you may be interested in:

• Mysql performance optimization case study-Covering Index and SQL _NO_CACHE