Automatic optimization of SQL statements _ MySQL

You can write executable SQL statements in the database application system in multiple ways, but it is difficult to determine which one is the best solution. To solve this problem, it is necessary to optimize SQL. Simply put, the optimization of SQL statements is to convert low-performance SQL statements into SQL statements with better performance for the same purpose. The reason for optimizing SQL statements the lifecycle of the database system can be implemented in multiple ways by writing executable SQL statements in the database application system, but which one is the best solution is hard to determine. To solve this problem, it is necessary to optimize SQL. Simply put, the optimization of SQL statements is to convert low-performance SQL statements into SQL statements with better performance for the same purpose.

Reasons for optimizing SQL statements

The lifecycle of a database system can be divided into three stages: design, development, and product. Optimization at the design stage has the lowest cost and the greatest benefit. Optimization in the finished stage has the highest cost and the lowest benefit. If a database system is compared to a building, correction after the building is built is often costly and has little effect (or even cannot be corrected ), in the building design and production stages, controlling the quality of each brick and tile can achieve the goal of low cost and high effectiveness.

To maximize the benefits, we often need to optimize the database. Database optimization can usually be performed by optimizing the network, hardware, operating system, database parameters, and applications. According to statistics, the performance improvement obtained by optimizing network, hardware, operating system, and database parameters only accounts for about 40% of the database application system performance improvement, the other 60% of system performance improvements come from application optimization. Many optimization experts even believe that application optimization can improve system performance by 80%. Therefore, it is certain that optimization of the database system by optimizing the application can achieve greater benefits.

Application optimization can be divided into two aspects: Source code optimization and SQL statement optimization. Due to changes in program logic, source code optimization is costly in terms of time cost and risk (especially for systems in use ). On the other hand, source code optimization has limited effect on improving the database system performance, because the database operations performed by applications are ultimately performed by SQL statements on the database.

There are some direct reasons for optimizing SQL statements:

1. SQL statements are the only way to operate databases (data). the execution of applications is ultimately attributed to the execution of SQL statements, the efficiency of SQL statements plays a decisive role in the performance of the database system.

2. SQL statements consume 70% ~ 90% of database resources.

3. SQL statements are independent of Program Design logic. Optimization of SQL statements does not affect program logic. compared with optimization of program source code, the cost of optimizing SQL statements is low in both time and risk.

4. SQL statements can be written in different ways. The performance of different statements may vary greatly.

5. SQL statements are easy to learn and difficult to master. The performance of SQL statements is often related to the database structure and number of records of the actual running system. there is no general rule to improve the performance.

Traditional optimization methods

Traditionally, SQL programmers use manual rewriting to optimize SQL statements. This mainly relies on DBA or senior programmers to analyze the SQL statement execution plan, rely on experience, try to rewrite the SQL statement, and then compare the results and performance to try to find the SQL statement with better performance. This practice has the following shortcomings:

1. all possible SQL statements cannot be written. It may take a lot of time to find SQL statements with better performance. Even if an SQL statement with better performance is found, you cannot know whether there is a better performance statement.

2. it is very dependent on human experience. the amount of experience often determines the performance of the optimized SQL statement.

3. very time-consuming. Rewrite --> verify correctness --> compare performance. this cycle takes a lot of time.

According to the functions of traditional SQL optimization tools, optimization tools are generally divided into the following three generations:

The first generation of SQL optimization tools is execution plan analysis tools. These tools extract execution plans from the database for input SQL statements and explain the meaning of keywords in the execution plan.

The second generation of SQL optimization tool can only provide recommendations for adding indexes. it analyzes the execution plan of input SQL statements to generate recommendations for increasing indexes. This type of tool has a fatal disadvantage: only one SQL statement is analyzed and the conclusion of adding an index is obtained, which is ignored (in fact, it cannot be evaluated) the impact of the added Index on the overall database system performance.

The third generation tool uses artificial intelligence to achieve automatic SQL optimization.

Artificial intelligence automatic SQL optimization

With the development of AI technology and its in-depth application in the field of database optimization, the optimization technology achieved a breakthrough in the end of 1990s, and artificial intelligence automatic SQL optimization emerged. The essence of artificial intelligence automatic SQL optimization is to use artificial intelligence technology to automatically rewrite SQL statements to find the best equivalent SQL statement. Lecco SQL Expert uses this artificial intelligence technology. Its SQL Expert supports Oracle, Sybase, ms SQL Server, and IBM DB2 database platforms. Its prominent feature is the automatic optimization of SQL statements. In addition, you can use the AI knowledge base "feedback-based search engine" to rewrite SQL statements and find all equivalent SQL statements and possible execution plans, by testing and running, you can automatically find the best-performing SQL statements for applications and databases to provide microsecond-level timing; it can optimize Web applications and SQL statements that run for a short time in online transaction processing for a large number of users. it can compare the differences between source SQL and the SQL to be selected, it provides developers with "learning-side training" to quickly improve their SQL programming skills.

This tool provides several special modules for the development and maintenance of database applications: SQL syntax Optimizer, PL/SQL integrated development and debugging environment (IDE), scanner, and database monitor. The working principle of "SQL syntax Optimizer", one of its core modules, is roughly as follows: enter a source SQL statement, the "artificial intelligence feedback search engine" overwrites the input SQL statements in combination with the detected database structure and index to generate N equivalent SQL statement outputs, the generated N equivalent SQL statements are sent to the "artificial intelligence feedback-type search engine" for rewriting until new output cannot be generated or the search quota is full. then, the output SQL statements are filtered, select the SQL statements with different execution plans (different execution plans mean different execution efficiency), and finally conduct batch tests on the obtained SQL statements, find the best-performing SQL statement (see ).

Figure artificial intelligence automatic SQL optimization

Lecco SQL Expert not only can find the best SQL statements, but also provides "training while learning" to teach developers and database administrators how to write the best-performing SQL statements. The automatic SQL statement optimization function of lecco SQL Expert makes SQL optimization extremely simple. as long as you can write SQL statements, it can help developers find the best performance.

Knot

SQL statements are a key part of database applications. their execution performance directly affects the running efficiency of applications. Because of this, people have invested a lot of energy in optimizing SQL statements, and many SQL statement optimization tools have emerged. As AI and other related technologies become increasingly mature, more and better tools will certainly emerge, which will provide developers with more help.