As the volume and logic complexity of the business increases, WorkCenter puts new requirements on interface performance time, and the most effective way to improve interface performance is to optimize database operation logic and SQL statements. This article shares some experience and advice on database performance tuning

Database structure optimization

MySQL logical architecture diagram:

  • Level 1: The client connects to the service and transmits the SQL instructions to be executed
  • The second level: The server parses and optimizes the SQL, generates the final execution plan, and executes it
  • The third layer: storage engine, responsible for database storage and extraction

The index optimization

An index contains the values of one or more columns. MySQL can only efficiently utilize the leftmost prefix column of an index. The advantages of indexes are:

  • Reduce the amount of data scanned by the query
  • Avoid sorting and zero-time tables
  • Change Random IO to Sequential IO (Sequential IO is more efficient than Random IO)

Optimization Suggestions: (1) If the string is very long, you can use the prefix index, and select the appropriate prefix length according to the index selectivity. (2) When using the multi-column index, you can use the prefix index. (4) Put the range query at the end of the condition query to prevent the right side index failure problem. (5) It is best not to select a long string for the index. Also, the index column should not be NULL

SQL query optimization

There are three important indicators of query quality :(1) response time (service time, queuing time), (2) scanned lines, and (3) returned lines

Optimization recommendations: (1) avoid querying unrelated columns, such as using SELECT * to return all lists, (2) avoid querying unrelated rows, and (3) shard queries. Break a task that puts a lot of pressure on the server into a longer period of time and execute it multiple times. If you want to delete 10,000 pieces of data, you can execute it in 10 times. After each execution, you can pause for a period of time and then continue the execution. During the process, server resources can be released to other tasks (4) to decompose associated queries. Decompose a query from a multi-table associative query into multiple queries from a single table. The lock contention can be reduced, and the query efficiency is also relatively high. COUNT (*) (6) COUNT (*) (6) COUNT (*) (6) COUNT (*) (6) GROUP BY IDENTIFY_COLUMN Group results should not appear outside the group column column (7) association query delay association, according to the query conditions to narrow the scope of each query, and then association (8) Limit paging optimization. By default, the UNION query is de-duplicating. If it is not required by the business, it is recommended to use a more efficient UNION ALL

TypeORM performance optimization

WorkCenter uses TypeORM as a tool for database manipulation, of course