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

This article will explain in detail how the optimization of MySQL 5.7ORDER BY sorting is. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

In some cases, MySQL can use indexes to perform ORDER BY sorting without the need for additional sorting.

Indexes can be used even if ORDER BY does not match the index specified on, as long as all unused index parts and all ORDER BY fields are constants of the WHERE statement.

SELECT * FROM T1 ORDER BY key_part1,key_part2,...; SELECT * FROM T1 WHERE key_part1 = constant ORDER BY key_part2;SELECT * FROM T1 ORDER BY key_part1 DESC, key_part2 DESC;SELECT * FROM T1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC;SELECT * FROM T1 WHERE key_part1 > constant ORDER BY key_part1 ASC;SELECT * FROM T1 WHERE key_part1

Constant2 ORDER BY key_part2

In some cases, MySQL does not use indexes for sorting

Queries are sorted using multiple different indexes

SELECT * FROM T1 ORDER BY key1, key2

Queries use sorting on discontiguous parts of the index

SELECT * FROM T1 WHERE key2=constant ORDER BY key_part2

There are both ASC and DESC in the sort

SELECT * FROM T1 ORDER BY key_part1 DESC, key_part2 ASC

The index used to obtain data is different from the index sorted by ORDER BY

SELECT * FROM T1 WHERE key2=constant ORDER BY key1

Queries are sorted using expressions instead of the field names of the index

SELECT * FROM T1 ORDER BY ABS (key); SELECT * FROM T1 ORDER BY-key

The query has many tables associated, and the field used by ORDER BY is not the field in the first non-constant table that returns the row (the join type of the first table in the EXPLAIN execution plan output is not const)

Queries use different ORDER BY and GROUP BY expressions

The prefix index of only one field is used in the sort

The index does not store rows in order, such as the HASH index of the MEMORY table

If a query contains GROUP BY, but you do not want to sort the results of the query, you can unsort it through the ORDER BY NULL statement

INSERT INTO fooSELECT a, COUNT (*) FROM bar GROUP BY an ORDER BY NULL

System parameters related to increasing ORDER BY speed:

Increase the value of the sort_buffer_size parameter. Ideally, the value of this parameter should be large enough to put the entire result set into sort buffer, avoiding sorting on disk.

The size of the column values stored in the buffer is determined by the max_sort_length parameter.

To monitor the number of merges sorted on disk, you can look at the status parameter Sort_merge_passes.

Mysql > show global status like 'Sort_merge_passes'

+-+ +

| | Variable_name | Value |

+-+ +

| | Sort_merge_passes | 0 | |

+-+ +

1 row in set (0.11 sec)

Increase the value of the read_rnd_buffer_size parameter.

When defining columns, only the values they need are assigned so that each row can use less memory when sorting. For example, if the value of each column does not exceed 16 characters, CHAR (16) is better than CHAR (200).

Change the tmpdir system variable to point to a dedicated high-capacity file system directory.

If ORDER BY sorting does not use an index, but there is a LIMIT statement in the statement, the optimizer may avoid using merged files and sort in memory instead.

On MySQL 5.7 ORDER BY sorting optimization is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.