Two ideas for improving the query efficiency of Order by statements in mysql

Because the database records may need to be reordered. In this article, I will talk about two ideas to improve the query efficiency of Order By statements for your reference.

In MySQL databases, Order by statements are frequently used. However, it is well known that when using this statement, data query performance is often reduced. Because the database records may need to be reordered. In this article, I will talk about two ideas to improve the query efficiency of Order By statements for your reference.

1. We recommend that you use an index to satisfy the Order By clause.

If conditions permit, we recommend that you use an index to satisfy the Order By clause. In this case, you can avoid additional sorting work. Here, I need to emphasize that the timely Order By clause does not exactly match the index, but as long as all unused index parts in the Where clause and columns in all additional Order by clauses are constant, now you can use the index. Specifically, the following query statements are recommended.

1. select * from ad_user where is_active = 'y' order by value;

Two columns are used in this query statement. In the Where query statement, query the activity records in the table. In this case, a constant condition is used. In the Order By clause, values in the Value column are sorted. If you set an index for this field in table design. When this statement is used for query, no additional sorting is required for the query results, which improves the data query efficiency.

That is to say, if the Where and Order By condition statements are used together and an index is required to improve the query efficiency, a condition must be met, the parameter values used in the where Condition Statement are constants rather than variables. If variables are used, this method will not work.

2. In some cases, indexes cannot be used to improve the query performance of Order By statements.

Note that indexes can not be used to improve the query efficiency of the Order Byz clause in any case. For example, you can use this statement for different keywords, use both ASC mode and DESC mode, use the keyword used for the query condition different from the keyword used in the Order By statement, and use Order for the non-continuous element of the keyword. by clause, use different Order BY and Group BY expressions in the same statement, and use the table index type to save rows in sequence, you cannot use indexes to sort Order By statements. In this case, another method is required. For example, you can re-adjust the table structure or query statements to meet the specific conditions for using this feature.

In fact, there is a balance problem here. For example, a Where Condition Statement usually uses a variable during query, which is mainly used to improve the flexibility of the statement. This variable accepts parameters passed by the front-end user. In this case, if you want to sort data at the same time, you cannot use indexes to improve query efficiency based on the rules described above. As a developer, You need to evaluate the flexibility of statements and query performance. Generally, for queries with a large record volume, the Query format is relatively fixed. For example, large-capacity monthly reports and annual reports tend to query the statement performance. Queries with a relatively small number of records, such as daily reports or frequently used query statements, tend to be more flexible in queries. As a developer, You need to select an appropriate solution based on your actual situation.

In general, to avoid the slow query speed caused By the use of the Order By statement, you must first consider using indexes to solve the problem. If the index cannot be used to solve the problem, the cache can be used to mitigate the problem to a certain extent. For example, 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, and change the tmpdir directory to direct it to a dedicated File System with a large amount of free space. Sometimes administrators can use this feature to evenly distribute loads to multiple directories.

2. Use the Explain keyword to confirm whether the Order BY speed problem can be solved BY indexing.

If you cannot determine whether the query efficiency of the Order By statement can be improved By indexing, you can use the Explain keyword to help key personnel determine. For example, you can use explain select * from ad_user where is_active = 'y' order by value (that is, add an explain keyword before a regular query statement ), used to determine whether indexes can be used to improve query efficiency. The method of determination is: if the query statement contains a using filesort field, you are very sorry. You cannot use indexes to improve the query efficiency of this statement. Otherwise, the query efficiency can be improved through indexes.

In general, File Sorting optimization can be used not only to record sorting keywords and row locations, but also to record the columns required for query. In this case, you can avoid reading row information multiple times. In order to let everyone better understand the truth, I will briefly explain the process of this work. Generally, File Sorting is optimized in four steps. The first step reads the row information that matches the Where Condition Statement. The second step constructs a series of values for each row and record, which are sorted keywords and row locations, and records the columns required for query; step 3: sort the ancestor by the sorting keyword; Step 4: Search the row in the sorting order, however, in this case, the required columns (using the results in step 3) are directly read from the sorted ancestor without re-accessing the data in the table. Obviously, the idea of File Sorting optimization can avoid repeated access to tables, thus improving the query efficiency.

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