MySQL database query optimization technology (1) (2)

Database systems are the core of management information systems. Database-based online transaction processing (OLTP) and online analytical processing (OLAP) are one of the most important computer applications of banks, enterprises, governments, and other departments.
From the perspective of most system application instances, query operations account for the largest proportion in various database operations, and the SELECT statement based on query operations is the most costly statement in SQL statements. For example, if the amount of data is accumulated to a certain extent, for example, if the database table information of a bank account is accumulated to millions or even tens of millions of records, it usually takes tens of minutes to scan a full table, even several hours. If you use a better query policy than full table scan, you can usually reduce the query time to several minutes. This shows the importance of the query optimization technology.

During the implementation of the application project, I found that many programmers only pay attention to the gorgeous user interface when developing database applications using some front-end database development tools (such as PowerBuilder and Delphi, the efficiency of query statements is not valued, resulting in low efficiency of the developed application system and serious waste of resources. Therefore, it is very important to design efficient and reasonable query statements. Based on the application example and the database theory, this article introduces the application of the query optimization technology in the real system.

Analyze problems

Many programmers think that query optimization is a task of DBMS (database management system). It has little to do with the SQL statements compiled by the programmers. This is wrong. A good query plan can often increase the program performance by dozens of times. A query plan is a set of SQL statements submitted by the user. A query plan is a set of statements generated after optimization. The process of the DBMS processing the query plan is as follows: after the lexical and syntax check of the query statement is completed, the statement is submitted to the query optimizer of the DBMS, after the optimizer completes algebra optimization and access path optimization, the pre-compilation module processes the statements and generates query plans, and then submits the statements to the system for processing and execution at the appropriate time, finally, return the execution result to the user. The actual database products (such as Oracle and Sybase) use a cost-based optimization method in later versions, this optimization can estimate the cost of different query plans based on the information obtained from the system dictionary table, and then select a better plan. Although the database products have been doing better and better in query optimization, the SQL statements submitted by users are the basis for system optimization, it is hard to imagine that a bad query plan will become efficient after the system is optimized. Therefore, the merits and demerits of the statements written by users are crucial. We will not discuss the system's query optimization. The following describes the solutions to improve the user's query plan.

Solve the problem

The following uses Informix as an example to describe how to improve the user query plan.