Share some suggestions for Sql performance optimization

With the increase in business volume and logic complexity, workcenter has new requirements for the performance and energy consumption of interfaces, and the most effective way to improve interface performance is of course optimized for database operation logic and SQL statements. This article shares some experience and suggestions for database performance optimization

Database structure optimization

mysql logical architecture diagram:

• The first layer: The client transmits the SQL command to be executed through the connection service
• The second layer: the server parses and optimizes sql, generates the final execution plan and executes it
• The third layer: storage engine, responsible for the storage and extraction of the database

Index optimization

The index contains one or more column values. MySql can only efficiently use the leftmost prefix column of the index. The advantages of indexing are:

• Reduce the amount of data scanned by queries
• Avoid sorting and zero-time tables
• Change random IO into sequential IO (sequential IO is more efficient than random IO)

Optimization suggestions:
(1) For particularly long strings, prefix index can be used, and the appropriate prefix length can be selected according to the selectivity of the index
(2) When using a multi-column index, you can connect by AND and OR syntax
(3) The index is particularly effective when querying where conditions and group by syntax queries
(4) Put the range query at the end of the conditional query to prevent the problem of index failure on the right caused by the range query
(5) It is best not to choose too long strings for the index, and the index column should not be null

SQL query optimization

Three important indicators of query quality: (1) Response time (service time, queuing time), (2) Scan code lines, (3) Return lines

Optimization suggestions:
(1) Avoid querying irrelevant columns, such as using Select * to return all lists
(2) Avoid querying irrelevant rows
(3) Split query. Divide a task that puts a lot of pressure on the server into a longer period of time and execute it in multiple times. If you want to delete 10,000 pieces of data, you can execute it in 10 times, and pause for a while after each execution, and then continue the execution. During the process, server resources can be released for other tasks
(4) Decompose related queries. Decompose one query of multi-table associated query into multiple queries of single table. It can reduce lock competition, and the query efficiency of the query itself is relatively high. Because the connection and disconnection of MySql are lightweight operations, the query will not be split into multiple times, which will cause efficiency problems
(5) Note that the count operation can only count columns that are not null, so use count (*) to count the total number of rows
(6) The group by grouping efficiency is high according to the identification column, and the grouping result should not appear in columns other than the grouping column
(7) Associated query delayed association, you can narrow down the scope of each query according to the query conditions, and then associate
(8) Limit paging optimization. You can scan the code according to the index coverage, and then query other columns according to the index column association itself
(9) Union query is deduplicated by default. If it is not necessary for business, it is recommended to use Union All which is more efficient

TypeORM performance optimization

WorkCenter uses TypeORM as a tool for database operations. It is true that