Shulou(Shulou.com)06/01 Report--

For sorting, order by is a keyword that we use very frequently. Combined with our previous understanding of indexes, this article will give us a deep understanding of how indexes can be used to scan fewer tables or use external sorting when sorting.

Define a table first to help us understand later:

CREATE TABLE `t` (`id` int (11) NOT NULL, `city` varchar (16) NOT NULL, `name` varchar (16) NOT NULL, `age` int (11) NOT NULL, `addr` varchar (128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`) ENGINE=InnoDB

Then we write a query statement.

Select city,name,age from t where city=' Hangzhou 'order by name limit 1000

Based on the table definition above, city=xxx can use an index that we define. But order by name obviously we don't have an index, so we definitely need to query to city=xxx with the index first, then query back to the table, and finally sort it.

Full-field sorting

After creating the index above the city field, we use the execution plan to view the statement

You can see that when there is an index, we still use "Using filesort" here to indicate that sorting is needed, and MySQL allocates a piece of memory to each thread for sorting called sort_buffer.

We usually go through this process when executing the above select statement

1. Initialize sort_buffer and make sure to put in the three fields name, city and age.

two。 Find the first primary key id that satisfies the city=' Hangzhou 'condition from the index city.

3. Take the three field values of name, city and age in the return table and store them in sort_buffer.

4. Fetch a primary key id record from the index city.

5. Repeat steps 3-4 until city does not meet the criteria.

6. Quickly sort the data in sort_buffer by field name.

7. The first 1000 rows of the sort result are returned to the client.

This is what we call full-field sorting.

Sorting by name can be done either in memory or using external file sorting. It depends on sort_buffer_size. The default value of sort_buffer_size is 1048576 byte, that is, 1m. If the amount of data to be sorted is less than 1m, the sort is done in memory. If the amount of data to be sorted is too large to be stored in it, temporary disk files are used to assist sorting.

Rowid sorting

If the single line is very large, it will not work well to put all the required fields into the sort_buffer.

The length of row data specifically used to control sorting in MySQL has a parameter, max_length_for_sort_data, which defaults to 1024. If this value is exceeded, rowid will be used for sorting. Then the process of executing the above statement becomes

1. Initialize sort_buffe to make sure you put in two fields, name and id.

two。 Find the first primary key id that satisfies the condition city = 'Hangzhou' from the index city.

3. The return table takes the fields name and id and stores them in sort_buffer.

4. Take the next record that meets the conditions and repeat the 23 steps.

5. Sorts the name in sort_buffer.

6. The traversal result takes the first 1000 rows. Then return the result field from the table to the client according to id.

In fact, not all oder by statements need to perform the above secondary sorting operation. From the above analysis of the implementation process, we can note. MySQL needs to generate temporary tables because it needs to be sorted on temporary tables, because what we got before is that the data is unordered.

If we modify the previous index so that it is a federated index, then the value we get for the second field is actually in order.

The federated index satisfies the condition that when our first index field is equal, the second field is ordered.

This ensures that if we build a (city,name) index, when we search for city=' Hangzhou', the second field we find, name, is actually in order. So the query process can be simplified to.

1. Find the first primary key id that satisfies the condition city = 'Hangzhou' from the index (city, name).

two。 Go back to the table and return three values of name city age.

3. Take down an id.

4. Repeat 2 / 3 steps until 1000 records are completed, or the city = 'Hangzhou' condition is not met.

Also because the ordering of the index can be used in the query process, sorting is no longer needed and sort buffer is no longer needed.

A further optimization is the index coverage mentioned earlier, which covers the fields that need to be queried into the index, and then omits the step of returning to the table, which can make the whole query faster.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.