Shulou(Shulou.com)06/01 Report--

An example is given to explain the query optimization technology of MySQL database [@ more@] database system is the core of 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. From the application examples of most systems, the query operation accounts for the largest proportion of all kinds of database operations, and the SELECT statement on which the query operation is based is the most expensive statement in the SQL statement. For example, if the amount of data accumulates to a certain extent, for example, the database table information of a bank account accumulates to millions or even tens of millions of records, a full table scan often takes tens of minutes or even hours. If we adopt a better query strategy than full table scanning, the query time can often be reduced to a few minutes, which shows the importance of query optimization technology.

In the implementation of the application project, the author finds that when many programmers use some front-end database development tools (such as PowerBuilder, Delphi, etc.) to develop database applications, they only pay attention to the splendor of the user interface and do not pay attention to the efficiency of query statements, 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 the application example and the database theory, this paper introduces the application of query optimization technology in the real system.

Analyze the problem

Many programmers think that query optimization is the task of DBMS (database management system), which has little to do with the SQL statements written by programmers, which is wrong. A good query plan can often improve the performance of the program dozens of times. Query plan is a collection of SQL statements submitted by users, and query planning is a collection of statements generated after optimization. The process of DBMS processing query plan is as follows: after checking the morphology and syntax of the query statement, the statement is submitted to the query optimizer of DBMS. After the optimizer completes algebraic optimization and access path optimization, the pre-compilation module processes the statement and generates the query plan, then submits it to the system for processing and execution at an appropriate time, and finally returns the execution result to the user. The cost-based optimization method is adopted in the high version of the actual database products (such as Oracle, Sybase, etc.). This optimization can estimate the cost of different query planning according to the information obtained from the system dictionary table, and then choose a better plan. Although the current database products have done better and better in query optimization, the SQL statements submitted by users are the basis of system optimization, so it is difficult to imagine that an originally bad query plan will become efficient after system optimization, so the quality of the statements written by users is very important. We will not discuss the query optimization made by the system for the time being, and the following focuses on the solutions to improve the user query plan.

Solve the problem

Taking the relational database system Informix as an example, this paper introduces the methods to improve the user query plan.

1. Rational use of index

Index is an important data structure in database, and its fundamental purpose is to improve query efficiency. Nowadays, most database products adopt the ISAM index structure first proposed by IBM. The use of the index should be appropriate, and the principles of its use are as follows:

● indexes columns that are frequently joined but not specified as foreign keys, while fields that are not frequently joined are automatically indexed by the optimizer.

● builds indexes on columns that are sorted or grouped frequently (that is, group by or order by operations).

● builds retrieval on columns with more different values that are often used in conditional expressions, and does not index columns with fewer values. For example, there are only two different values of "male" and "female" in the "gender" column of the employee table, so there is no need to establish an index. If the index is established, it will not improve the query efficiency, but will seriously reduce the update speed.

● if there are multiple columns to be sorted, you can build a composite index (compound index) on those columns.

● uses system tools. For example, the Informix database has a tbcheck tool that can be checked on suspicious indexes. On some database servers, the index may fail or the reading efficiency may be reduced due to frequent operations. if a query using the index slows down inexplicably, you can try to check the integrity of the index with the tbcheck tool and fix it if necessary. In addition, when the database table updates a large amount of data, deleting and rebuilding the index can improve the query speed.

2. Avoid or simplify sorting

Repetitive sorting of large tables should be simplified or avoided. The optimizer avoids sorting steps when the index can be used to automatically generate output in the appropriate order. Here are some influencing factors:

One or more columns to be sorted are not included in the ● index

The order of columns in the ● group by or order by clause is not the same as the order of the index

The columns sorted by ● come from different tables.

In order to avoid unnecessary sorting, it is necessary to correctly add indexes and merge database tables reasonably (although it may sometimes affect the standardization of tables, it is worth the improvement in efficiency). If sorting is inevitable, you should try to simplify it, such as narrowing the scope of sorted columns.

3. Eliminate sequential access to large table row data

In nested queries, sequential access to tables can have a fatal impact on query efficiency. For example, using a sequential access strategy, a nested three-tier query, if each layer queries 1000 rows, then the query will query 1 billion rows of data. The main way to avoid this is to index the connected columns. For example, two tables: student form (student number, name, age... ) and course schedule (student number, course number, grade). If two tables are to be joined, an index should be established on the connection field "student number".

You can also use union to avoid sequential access. Although there are indexes on all check columns, some forms of where clauses force the optimizer to use sequential access. The following query forces sequential operations on the orders table:

CODE: SELECT * FROM orders WHERE (customer_num=104 AND order_num > 1001) OR order_num=1008

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.