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

Today to talk with you about MySQL database ORDER BY optimization is how, many people may not understand, in order to let you better understand, the editor summed up the following content, I hope you can gain something according to this article.

Using filesort often occurs when using order by, so we need to try our best to optimize this kind of sql statement to use Using index as much as possible.

So, how do we optimize this type of statement? Because this piece is easy to be confused, so I have done an experiment, I believe you can understand it when you do the experiment with me.

1. Environmental preparation

Drop table if exists test; create table test (id int primary key auto_increment, C1 varchar (10), c2 varchar (10), c3 varchar (10), c4 varchar (10), c5 varchar (10)) ENGINE=INNODB default CHARSET=utf8; insert into test (C1, c2, varchar, c3, and c5) values ("A1", "a2", "a2", "a3", "4", "a5"); insert into test ("C1", c2, c3, c4, c5) values ("b1", "b2", "b2", "b4", "b4", "b5") Insert into test (c1meme, c2rec, c4, minus, c5) values (c1m, c2, c2, c4, and c5); insert into test (c1, c2, c3, c4, and c5) values (d1s, c2, d4, and d4); insert into test (c1mins, c2, c3, c4, and c5) values (e1mings, e2, c4, c4, c5); insert into test (c1s, c2, c4, c4, c5, c5).

two。 Create a btree index

Create index idx_c1234 on test (C1, c2, c3, c4); show index from test

3. Range scan causes full table scan

Explain select * from test where C1 > 'a1' order by C1

Analysis:

The index is created on C1, c2, c3, and c4, and the range is directly used on C1, resulting in index failure. Full table scan: type=ALL,ref=Null. Because at this time, C1 is mainly used for sorting, not a query.

Sort using C1, and Using filesort appears.

Workaround: use an override index.

4. Overlay index-"optimization"

Explain select C1 from testwhere C1 > 'a1' order by C1

Analysis:

Use overlay index, do not scan all, scan the index range

Sort according to the order of the index, so there is no Using filesort.

It doesn't matter if I don't understand here. Later, I will share the eight rules of the index to make sure I can understand it.

5. Not sorted by leftmost column index

Explain select C1 from testwhere C1 > 'a1' order by c2

Analysis:

Using filesort appears here because the c2 used for sorting is not consistent with the order in which the index is created (C1, Magi, c2, c3, and c4).

6. Sorted index columns are created in the opposite order.

Explain select C1 from testwhere C1 > 'a1' order by c2

Analysis:

Here comes the Using filesort. Because the sorted index column (c2memc1) is the opposite of the order in which the index is created (c1Powerc2), there is a rearrangement, which leads to the emergence of Using filesort.

7. Inconsistent sorting of order by index columns

Explain select C1 from testwhere C1 > 'a1' order by C1 asc,c2 desc

Analysis:

Although the sorted field columns are in the same order as the index, and order by defaults to ascending order, where c2 desc becomes descending, resulting in a different sort from the index, resulting in Using filesort. If it is order by C1 asc,c2 asc or order by C1 desc,c2 desc, it will be using index.

Summary of experiment

1. MySQL supports two sorting methods: filesort and index.

Using index means that MySQL scans the index itself to complete the sorting. The efficiency of index is high, while that of filesort is low.

two。 Use an index for sorting

Suppose KEY test (a _

(1) order by can use the leftmost prefix of the index

-order by a-order by an order by a desc,b desc,c desc b-order by a minute b c-order by an asc,b asc,c asc-order by a desc,b desc,c desc

(2) if where is positioned as constant using the leftmost prefix of the index, then order by can use the index

-where a = const order by bmai c-where a = const and b = const order by c-where a = const and b > consst order by bmai c

(3) the index cannot be used for sorting

-order by an asc,b desc, c desc / * inconsistent sorting * /-where g=const order by bdiary c / * missing an index * /-where a=const order by c / * missing b index * /-where a=const order by Aso d / * d is not part of the index * /-where an in (....) Order by bpencil c / * for sorting, multiple equality conditions are also range queries * /

3. Filesort has two sorting algorithms: two-way sorting and one-way sorting.

Two-way sorting: use two-way sorting before MySQL4.1, that is, two disk scans to get the final data. Read the row pointer and order by column, sort them, then scan the sorted list, and re-read the corresponding data output from the list according to the values in the list. That is, read the sort field from disk, sort it in buffer, and then fetch other fields from disk. If two-way sorting is used, the disk will be scanned twice for a batch of data. It is well known that the Imax O operation is very time-consuming, so after MySQL4.1, there is an improved algorithm: single-path sorting.

One-way sorting: query the required columns from disk, sort them in buffer by the order by column, and then scan the sorted list for output. It is more efficient, avoids the second reading of the data, and changes the random Imax O to the sequential Imax O, but uses more space because it keeps each row in memory. However, when the read data exceeds the capacity of the sort_buffer, it will lead to multiple reads of the data, and the creation of temporary tables, and finally multiplex merging, resulting in multiple Imax O, on the contrary, it increases its Imax O operation.

Solution:

Increase the setting of sort_buffer_size parameter.

Increase the setting of the max_length_for_sort_data parameter.

4. Increase the speed of order by

When using order by, don't use select *, just query the required fields. Because when there are too many query fields, there will be not enough sort_buffer, which can lead to the use of multiplex sorting or multiple Icano operations.

Add sort_buffer_size.

Add max_length_for_sort_data.

5. Optimize group by

Group by is very similar to order by in that it essentially sorts and then groups, following the best left prefix rule in the order in which the index is created. The sort_buffer_size and max_length_for_sort_data parameters should also be adjusted when index columns are not available. Note that where is higher than having, so don't go to having for the qualification that can be written in where.

After reading the above, do you have any further understanding of MySQL database ORDER BY optimization? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.