Reasons to improve database SQL statements for optimization

Data | database | optimization | statement

Optimization of an application can usually be divided into two areas: source code optimization and SQL statement optimization. Source code optimization Costs a lot of time and risk; On the other hand, the optimization of source code has a limited effect on improving the performance of database system.

Reasons to optimize

1 SQL statement is the only way to operate the database (data);

2 The SQL statement consumes the 70%~90% database resources;

3 The SQL statement is independent of the program design logic, compared with the optimization of the program source code, the cost of the SQL statement is very low in time cost and risk;

4 The SQL statement can be written differently;

5 SQL statement Easy to learn, difficult to master.

The development of optimization technology

The first generation of SQL optimization tools is the Execution Plan analysis tool. This type of tool extracts the execution plan from the database for the input SQL statements and interprets the meaning of the keywords in the execution plan; the second generation of SQL optimization tools can only provide suggestions for adding indexes, and it generates suggestions for whether to add indexes by analyzing the execution plans of the imported SQL statements. There are fatal drawbacks to this type of tool--parsing only one SQL statement to get the conclusion that an index is added, regardless of the effect of an increased index on the overall database system performance. The damage is:

1, ignoring the addition of index to other increase, delete, change the negative impact of SQL statements;

2, does not consider the addition of the index may lead to database judgment errors;

3. The burden of database system due to the increase of index is negligible.

At the same time, these tools have the following drawbacks due to technical limitations:

1, can not guarantee the correctness of the proposal or rewriting;

2, can not be rewritten, only to provide a recommendation or a limited degree of rewriting, rewrite work or need to manually complete, the optimization of the work of the time and the workload is similar to the artificial optimization;

3, rewrite the rules and hints limited, difficult to deal with complex SQL statements;

4, must be manually tested.

Such tools have been flourished until the advent of artificial intelligence automatic SQL optimization.