Analysis of two ways to improve query efficiency of ORDER BY statement in Mysql _mysql

Because the records of the database may need to be reordered. In this article, I will talk about improving the order BY statement query efficiency of two ideas for your reference.

In the MySQL database, the ORDER BY statement is used more frequently. However, it is well known that the use of this statement tends to degrade the performance of the data query. Because the records of the database may need to be reordered. In this article, I will talk about improving the order BY statement query efficiency of two ideas for your reference.

498) this.width=498; "Border=0>

One, it is recommended to use an index to satisfy an ORDER BY clause.

When 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 I need to emphasize here is that the ORDER BY clause does not exactly match the index, but you can use the index as long as all unused index parts in the WHERE clause and all additional ORDER BY clauses are listed as constants. Specifically, the following query statement is 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, the record of the activity in the query 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 a table design, set an index for this field. When you use this statement to query, the query results do not require additional sorting, which can improve the query efficiency of the data.

This means that if a where conditional statement is used with an order by condition statement, if an index is needed to improve query efficiency, then a condition must be met, and the parameter value used in the Where condition statement is a constant, not a variable. If you use variables, this method will not work.

2, note that in some cases, the index can not be used to improve the query performance of the order by statement.

It should be noted here that it is not always possible to use indexes to improve the query efficiency of the Order Byz clause. Use this statement for different keywords, mix ASC mode and desc mode, keyword for query criteria different from the keyword used in the order BY statement, use the ORDER BY clause for noncontiguous elements of the keyword, use different order by and group in the same statement The by expression, the type of table index used, cannot be used to save rows in order, and the ordering by statement cannot be resolved by using an index. At this point you need to think of another way. If you can rearrange the table structure or query statements to meet specific criteria for using this attribute.

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 the parameters that the front-end user passes over. At this point, if the user has the need for sorting at the same time, according to the rules described above, can not use the index to improve the efficiency of the query. As a developer, you need to evaluate whether the flexibility of the statement or the performance of the query is required. Typically, queries that have a larger number of records, while their query formats are relatively fixed, such as large-capacity monthly and annual reports, tend to be the performance of query statements. For queries with fewer records, such as daily reports, or query statements that use a higher frequency, this will tend to be more flexible in the query. As a developer, the focus now is on choosing the right solution based on the actual situation of the user.

In general, to avoid the problem of slow query speed caused by using an ORDER BY statement, you first need to consider using an index to solve the problem. If you do not have the ability to solve the problem through the index, you can mitigate it by slowing down some degree. 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 it to a private file system with a lot of free space, and so on. Sometimes an administrator can use this feature to distribute the load evenly across multiple directories.

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

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

It is important to note that in general, file sort optimization is not only used to record the location of sort keys and rows, but also to record the columns required by the query. In this way, you can avoid reading the row information more than once. In order to let you understand the truth of the author briefly explain the process of this work. Typically, file ordering optimization consists of four steps. The first step reads the line information that matches the Where condition statement; the second step lists the values that make up the sort key and row positions for each row, record, and the columns that the query needs; The third step sorts the progenitor according to the sort key; the fourth step retrieves rows in sorted order, However, it is time to read the required columns directly from the sorted Ganso (using the results in the third step) without accessing the data in the table again. Obviously, using the idea of file sort optimization, we can avoid repeatedly accessing the table, thus improving the efficiency of query.

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