How to optimize the order by statement in Mysql

In some cases, MySQL can use an index to satisfy the order by clause without additional sorting. The where condition and order by condition use the same index, and the order by order is the same as the index order, and the order by field is both ascending or descending.

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.

The code is as follows:	Copy code
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.

For example, the following SQL statements can use indexes.

 

The code is as follows:	Copy code
SELECT * FROM t1 order by key_part1, key_part2 ,...; SELECT * FROM t1 WHERE key_part1 = 1 order by key_part1 DESC, key_part2 DESC; SELECT * FROM t1 order by key_part1 DESC, key_part2 DESC;

 
However, indexes are not used in the following cases.

The code is as follows:	Copy code
1) SELECT * FROM t1 order by key_part1 DESC, key_part2 ASC; -- Order by field mixing ASC and DESC 2) SELECT * FROM t1 WHERE key2 = constant order by key1; -- The keywords used to query rows are different from those used in order. 3) SELECT * FROM t1 order by key1, key2;

-- Use order by for different keywords

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, using the idea of file sorting optimization can avoid repeated table access, thus improving the query efficiency.

One mysql order by optimization case

The code is as follows:

Copy code

1. desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID, Reason, ObjectID, ByUserID, ByUserName, subjectID FROM wikiuseractionlog Where CreateDate> '2017-01-01 00:00:00 'and ActionType in (0001,) order by CreateDate DESC limit 93060,20;     | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------------------- + ------ + ---------------------- + ------ + --------- + ------ + ------- + ----------------------------- + | 1 | SIMPLE | wikiuseractionlog | ALL | idx_date_type, idx_date | NULL | 91859 | Using where; Using filesort

The execution plan is not indexed. Although I have created a composite index idx_date_type (createdate, actiontype) and a column index idx_date (createdate ), to make the preceding statement take the index as long as there are the following points:

1) you need to change the date '2017-01-01 00:00:00 'to 0001 00:00:00 (none of them can be found below 2013. I don't know why )!! In addition, the DBA group has a powerful saying: the result set of the select statement accounts for 20% of the total table, and the entire table is taken without an index! I think it may be that the MYSQL Optimizer estimates the cost based on the CBO. If the select statement contains a large amount of data, it is estimated that the cost of indexing will be greater than that of the entire table, so the full table scan will not go through the index!

2) create a composite index for createdate and actiontype! In general, if the order by field needs to be optimized by the index, you need to perform a joint index for the condition field and the order by field under the where condition! For example, select * from test_0719 order by name; in this condition, there is no where condition field. Even if the name has an index, file sorting will still occur. In this condition, you can either add a where condition field and then perform a joint index on the field and name! Either add the name field to the where condition!

3) limit 93060,20 should be avoided. If this table is not very large! The 93060 rows have occupied a large amount of data in the entire table, so the mysql optimizer may not select an index, even if there is an index, it will not go! Instead, select full table scan! Because the amount of data to be retrieved is very large! The mysql optimizer may think that the indexing cost is higher than the full table scan cost! The first point above!

The code is as follows:

Copy code

Mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID, Reason, ObjectID, ByUserID, ByUserName, SubjectID FROM wikiuseractionlog order by CreateDate; + ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------------ + | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------------ + | 1 | SIMPLE | wikiuseractionlog | ALL | NULL | 95220 | Using filesort | + ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------------ + 1 row in set (0.00 sec) Mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID, Reason, ObjectID, ByUserID, ByUserName, subjectID FROM wikiuseractionlog Where CreateDate> '2017-01-01 00:00:00 'order by CreateDate DESC limit 93060,20; + ---- + ------------- + ------------------- + ------- + ---------------------- + --------------- + --------- + ------ + ------------- + | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------------------- + ------- + ---------------------- + --------------- + --------- + ------ + ------------- + | 1 | SIMPLE | wikiuseractionlog | range | idx_date_type, idx_date | idx_date_type | 8 | NULL | 3737 | Using where

4) in this case, although there is a separate index idx_date (createdate), the index still cannot be used because the order by field must also appear in the where condition, the reason here is probably that the sorting operation is at the end. When we extract data first, if the field in the where condition has an index, the sorting operation has been performed when the data is extracted, and filesort is not used when order by is later. Otherwise, when order by IS in the final extracted data, filesort is required !!

5) The first example below won't be indexed! The second one is OK! Changing ActionType in (,) to or does not work! If in optimization is involved here, you can try to change it to union all.

The code is as follows:

Copy code

Mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, PointsRulesID, Reason, ObjectID, ByUserID, ByUserName, SubjectID FROM wikiuseractionlog Where ActionType in) order by CreateDate; + ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------ + ------- + ----------------------------- + | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------ + ------- + ----------------------------- + | 1 | SIMPLE | wikiuseractionlog | ALL | idx_type_date | NULL | 90747 | Using where; Using filesort | + ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------ + ------- + ----------------------------- + 1 row in set (0.00 sec) Mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, PointsRulesID, Reason, ObjectID, ByUserID, ByUserName, SubjectID FROM wikiuseractionlog Where ActionType = '10' order by CreateDate; + ---- + ------------- + ------------------- + ------ + --------------- + --------- + ------- + ------------- + | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------------------- + ------ + --------------- + --------- + ------- + ------------- + | 1 | SIMPLE | wikiuseractionlog | ref | idx_type_date | 3 | const | 45373 | Using where | + ---- + ------------- + ------------------- + ------ + --------------- + --------- + ------- + ------------- + 1 row in set (0.00 sec)

Conclusion: 1

If you want to index the field after order by, you must create a composite index with a field in the where condition !! In other words, if the field after orcer by needs to be sorted by index, it must either establish a composite index with the field in the where condition [when a composite index is created here, note that the column order of the composite index is (where field, order by field), so that the leftmost column principle can be met, probably because the order by field can be included in the where query condition! ], Or it must be referenced in the where condition!

Conclusion: 2

Table a id is a common field with an index
Select * from a order by id (index unavailable)
Select id from a order by id (can be indexed)
Select * from a where id = XX order by id (can be indexed)
This means that order by should not be sorted by the file system. Either the order by field appears after select or the order by field appears in the where condition, you can create a composite index between the order by field and the where condition field!