The database system is the core of the management information system. Database-based online transaction processing (OLTP) and online analytical processing (OLAP) are one of the most important computer applications in banks, enterprises, governments and other departments.
Judging from the application examples of most systems, query operations account for the largest proportion of various database operations, and the SELECT statement on which the query operation is based is the most expensive statement among SQL statements. For example, if the amount of data accumulates to a certain extent, such as a bank's account database table information accumulating to millions or even tens of millions of records, a full table scan often takes dozens of minutes or even hours. If you adopt a better query strategy than a full table scan, you can often reduce the query time to a few minutes, which shows the importance of query optimization technology.
During the implementation of the application project, the author found that many programmers only focus on the gorgeous user interface and do not pay attention to query statements when developing database applications using some front-end database development tools (such as PowerBuilder, Delphi, etc.) efficiency problems, resulting in low efficiency of the developed application system and serious waste of resources. Therefore, how to design efficient and reasonable query statements is very important. Based on application examples and combined with database theory, this article introduces the application of query optimization technology in real systems.
Analysis of problems
Many programmers think that query optimization is the task of the DBMS (database management system) and has little to do with the SQL statements written by programmers. This is Wrong. A good query plan can often improve program performance dozens of times. The query plan is a collection of SQL statements submitted by the user, and the query plan is a collection of statements generated after optimization. The process of DBMS processing query plan is as follows: after completing the lexical and syntax check of the query statement, the statement is submitted to the DBMS query optimizer. After the optimizer completes the algebraic optimization and access path optimization, the precompiled module Process the statement and generate a query plan, then submit it to the system for processing and execution at the appropriate time, and finally return the execution results to the user. In high versions of actual database products (such as Oracle, Sybase, etc.), cost-based optimization methods are used. 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 planning. Although current database products are getting better and better at query optimization, the SQL statements submitted by users are the basis for system optimization. It is difficult to imagine that an originally poor query plan will become efficient after system optimization. Therefore The quality of the statements users write is crucial. We will not discuss the query optimization performed by the system for now. The following focuses on solutions to improve user query plans.
Solving the problem
The following takes the relational database system Informix as an example to introduce methods to improve user query plans.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
