Two ways to improve query efficiency of ORDER BY statement

Improve Order byTwo ideas on the efficiency of statement query 2011-03-01 13:07 The water is too deep itpubfont Size:T | T

In the MySQL database, theOrder by statement is used more frequently. However, it is well known that the performance of data queries is often reduced when using this statement. Because the records of the database may need to be reordered. In this article, I would like to talk about improving the order by statement query efficiency of two ideas, for everyone's reference.

AD:

In the MySQL database, theOrder by statement is used more frequently. However, it is well known that the performance of data queries is often reduced when using this statement. Because the records of the database may need to be reordered. In this article, I would like to talk about improving the order by statement query efficiency of two ideas, for everyone's reference.

First, it is recommended to use an index to satisfy the Order by clause.

Where conditions permit, it is advisable to use an index to satisfy the Order by clause. That way, you can avoid the extra sort work. The point that I need to emphasize here is that the Order by clause does not exactly match the index, but as long as all the unused index parts in the WHERE clause and all the extra order by clauses in the column are constant , you can use the index at this point. Specifically, the following query statements are recommended.

1. Select * from Ad_user where is_active= ' Y ' order by value;

In this query statement, two columns are used. In the WHERE query statement, query the record for the activity in the table. The condition of a constant is used at this time. In the order by clause, the values of the Value column are sorted. If you are in the table design, set an index for this field. When you use this statement to query, the query results do not require additional sorting work, which can improve the efficiency of the data query.

This means that if a WHERE condition statement is used with an Order by condition statement, you must satisfy a condition if you need to improve the query efficiency through an index, and the parameter value used in the Where condition statement is a constant. Instead of a variable. If you use variables, this method will not work.

2. Note that in some cases, indexes cannot be used to improve the query performance of an Order by statement.

It is important to note that it is not always possible to improve the query efficiency of the Order byz clause by using an index. Use this statement for different keywords, mix the ASC mode with the DESC pattern, use the keyword for query criteria differently from the keyword used in the order by statement, and order for the non-contiguous elements of the keyword The By clause, the use of different order by and group by expressions in the same statement, the types of table indexes used cannot be saved sequentially, and so on, cannot be resolved by using an index Order The sort problem of the by statement. At this point you need to think another way. If you can readjust the table structure or query statements to meet specific conditions that use this feature.

In fact, there is a balanced problem here. In the case of a query, a variable is often used in a where condition statement, mainly to improve the flexibility of the statement. This variable accepts parameters passed by the front-end user. At this point, if the user has a sorting requirement at the same time, the index cannot be used to improve the efficiency of the query, based on the rules described above. At this point, as a developer, you need to evaluate whether you need the flexibility of the statement or the performance of the query. In general, for a large number of records of the query, while its query format is relatively fixed, such as large-capacity monthly and annual reports, this time will be inclined to query the performance of the statement. For queries with fewer records, such as daily reports, or query statements that use more frequently, they tend to be more flexible to query. As a developer, the right thing to do now is to choose the right solution based on the actual situation of the user.

In general, in order to avoid problems caused by the use of an order by statement, you first need to consider using an index to solve the problem. If the problem cannot be solved by indexing, it can be mitigated by a certain degree of ease. If you can increase the size of the soft_buffer_size variable, adjust the size of the read_buffer_size variable according to the actual situation, change the Tmpdir directory to point to a dedicated file system with lots of free space, and so on. Sometimes administrators can use this feature to distribute loads evenly across multiple directories.

Second, use the Explain keyword to confirm whether the Order by speed problem can be resolved by index.

If the user cannot determine whether an index can be used to improve the query efficiency of an Order by statement, the Explain keyword can be used to help key members to judge. If you can use explain select * from Ad_user where is_active= ' Y ' order by value (that is, precede a regular query statement with a explain keyword), Used to determine whether an index can be used to improve the efficiency of a query. The method of judging is: if there is a using filesort field in this query statement, then it is very sorry that the query efficiency of this statement cannot be improved by using the index. Conversely, without this field, the index can be used to improve query efficiency.

It is important to note that in general, file sort optimization can not only be used to record the location of sort keys and rows, but also to record the columns required by the query. In this case, you can avoid reading the line information more than once. In order to make everyone more aware of the truth of the author briefly explain the process of the work. Typically, file ordering optimization consists of four steps. The first step reads the row information that matches the Where condition statement, and the second step is a series of values for each row, record that forms the sort key and row position, and records the columns required by the query; The third step is to sort the meta-ancestors according to the sort key; The fourth step retrieves the rows in sorted order. However, the required columns are read directly from the sorted Ganso (using the results from the third step) without re-accessing the data in the table. It is clear that the idea of using file sorting optimization avoids repeated access to the table, thus improving the efficiency of the query.

Original link: http://publish.itpub.net/a2011/0225/1160/000001160766.shtml

Two ways to improve query efficiency of ORDER BY statement