Design efficient and reasonable MySQL query statement

From the application examples of most systems, query operation occupies the most proportion in various database operations, and the SELECT statement based on query operation is the most expensive statement in SQL statement. For example, if the amount of data accumulated to a certain extent, such as a bank's account database table information accumulated to millions or even thousands records, a full table scan often takes 10 minutes, or even hours. If you adopt a better query strategy than full table scan, you can often reduce the query time to a few minutes, thus the importance of query optimization technology.

The author found in the implementation of the application project, many programmers in the use of some front-end database development tools (such as PowerBuilder, Delphi, etc.) to develop database applications, only pay attention to the gorgeous user interface, do not pay attention to the efficiency of query statements, resulting in the development of the application system inefficient, The waste of resources is serious. Therefore, how to design efficient and reasonable query statement is very important. Based on the application example and database theory, this paper introduces the application of query optimization technology in the real system.

Analyze problems

Many programmers think that query optimization is the task of DBMS (database management System), which is not related to the SQL statements written by programmers, which is wrong. A good query plan can often improve the performance of the program by dozens of times times. A query plan is a collection of SQL statements that a user submits, and a query plan is a collection of statements that are produced after an optimized process. The process of the DBMS processing the query plan is as follows: After the lexical and grammatical checking of the query sentence, the statement is submitted to the query optimizer of the DBMS, after the optimizer finishes the optimization of algebraic optimization and access path, the statement is processed by the precompiled module and the query plan is generated. It is then submitted to the system for execution at the right time, and the execution results are returned to the user at the end. In the high version of the actual database products, such as Oracle, Sybase, and so on, the cost-based optimization method is used to estimate the cost of different query plans based on the information obtained from the System Dictionary table, and then choose a better plan. Although the current database products in query optimization has been doing better, but the user-submitted SQL statements is the basis for system optimization, it is difficult to imagine a bad query plan after the system optimization will become efficient, so users write the pros and cons of the sentence is critical. Query optimization for the system we do not discuss, the following highlights to improve the user query plan solution.

Solve the problem

The following is an example of a relational database system, Informix, that improves the user query plan.

1. Rational use of indexes

Index is an important data structure in database, and its basic aim is to improve the efficiency of query. Most of the database products now use IBM's first proposed ISAM index structure. The use of indexes is just right, with the following principles:

Indexes are established on columns that are frequently connected but not specified as foreign keys, while fields that are not frequently connected are automatically generated by the optimizer.

Index on a column that is frequently sorted or grouped (that is, a group by or order by operation).

A search is established on columns with more values that are often used in conditional expressions, and no index is established on columns with fewer values. For example, there are only two different values for "male" and "female" on the "Sex" column of the employee table, so there is no need to index. If indexing does not improve query efficiency, it can significantly reduce the speed of updates.

If there are multiple columns to be sorted, you can set up a composite index on those columns (compound index).

Use System Tools. If the Informix database has a Tbcheck tool, it can be checked on suspicious indexes. On some database servers, the index may fail or the read efficiency is reduced because of frequent manipulation, and if a query using an index slows down, try using the Tbcheck tool to check the integrity of the index and fix it if necessary. In addition, when a database table updates a large amount of data, deleting and rebuilding the index can increase the query speed.

2. Avoid or simplify sorting

You should simplify or avoid repeating sorting of large tables. The optimizer avoids sorting steps when it is possible to use indexes to automatically produce output in the appropriate order. Here are some of the factors that affect:

The index does not include one or several columns to be sorted;

The order of the columns in the group BY or ORDER BY clause is not the same as the index;

The sorted columns come from different tables.

In order to avoid unnecessary sorting, it is necessary to build the index correctly and consolidate the database table reasonably (although it may sometimes affect the normalization of the table, but it is worthwhile relative to the increase in efficiency). If sorting is unavoidable, try simplifying it, such as narrowing the range of sorted columns.