Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to improve the query efficiency of Order by statements in MySQL". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to improve the query efficiency of Order by sentences in MySQL".

In MySQL database, Order by statements are frequently used. However, it is well known that when using this statement, it often degrades the performance of data queries. Because you may need to reorder the records in the database. In this article, the author talks about two ideas to improve the query efficiency of Order By sentences for your reference.

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

When conditions permit, the author suggests that it is best to use an index to satisfy the Order By clause. In this way, extra sorting work can be avoided. The point I need to emphasize here is that just in time the Order By clause does not exactly match the index, but the index can be used as long as all unused index parts of 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, query the active records in the table. In this case, a constant condition is used. In the Order By clause, the sort is based on the value of the Value column. If you are in a table design, set an index for this field. At this time, using this statement to query, the query results do not need additional sorting work, which can improve the query efficiency of the data.

That is, if Where conditional statements are used with Order By conditional statements, if an index is needed to improve query efficiency, a condition must be satisfied, and the parameter values used in the where conditional statement are constants, not variables. If variables are used, this method will not work.

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

It is important to note that it is not always possible to improve the query efficiency of the Order Byz clause by using indexes. Such as using this statement for different keywords, using a mixture of ASC schema and DESC schema, using keywords for query conditions different from those used in Order By statements, using Order By clauses for non-contiguous elements of keywords, using different Order BY and Group BY expressions in the same statement, using table indexes that cannot save rows sequentially, etc. You cannot solve the sorting problem of Order By statements by using indexes. At this point, we need to find another way. For example, you can readjust the table structure or query statements to meet specific conditions for using this feature.

In fact, there is a problem of equilibrium. For example, when querying, Where conditional statements often use a variable, which is mainly to improve the flexibility of the statement. This variable accepts the parameters passed by the front-end user. At this point, if the user has the need for sorting at the same time, according to the rules described above, it is impossible to use the index to improve the efficiency of the query. At this point, as a developer, you need to evaluate whether you need statement flexibility or query performance. In general, for queries with large records, and the query format is relatively fixed, such as large-capacity monthly and annual reports, it will tend to query the performance of the statement. For queries with less records, such as daily reports, or query statements with high frequency, they tend to be more flexible at this time. As a developer, what you need to pay attention to now is to choose the appropriate solution according to the actual situation of the user.

In general, in order to avoid the problem of slower queries caused by using Order By statements, you need to consider using indexes to solve the problem first. If the problem cannot be solved through the index, it can be alleviated to some extent by caching. For example, you can increase the size of the soft_buffer_size variable, resize the Read_buffer_size variable according to the actual situation, change the tmpdir directory to point it to a dedicated file system with a lot of free space, and so on. Sometimes administrators 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 indexing.

If the user is not sure whether the query efficiency of the Order By statement can be improved through the index, then the Explain keyword can be used to help the key person to judge. For example, by using explain select * from ad_user where is_active='Y' order by value (that is, adding an explain keyword before a regular query statement), you can determine whether an index can be used to improve the efficiency of the query. The way to judge is: if there is a using filesort field in this query statement, then I am very sorry and cannot improve the query efficiency of this statement by using the index. Conversely, without this field, it means that the query efficiency can be improved through the index.

It is important to note that, in general, file sorting optimization can be used to record not only the location of sorting keywords and rows, but also the columns required by the query. In this way, you can avoid reading the message of the bank for many times. In order to make you understand the truth more clearly, the author briefly explains the process of this work. In general, file sorting optimization consists of four steps. The first step reads the row information matched with the Where conditional statement; the second step forms a series of values of the sort keyword and row position for each row and record, and records the columns needed for the query; the third step sorts meta-ancestors according to the sort keywords The fourth step retrieves the rows in sorted order, but this time reads the required columns directly from the sorted meta-ancestor (using the results in the third step) without revisiting the data in the table. Obviously, using the idea of file sorting optimization can avoid repeated access to the table, thus improving the efficiency of the query.

Thank you for your reading, the above is the content of "how to improve the query efficiency of MySQL Order by statements". After the study of this article, I believe you have a deeper understanding of how to improve the query efficiency of MySQL Order by statements, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.