MySQL briefly understands how "order by" works

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

First define a table 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;

Now we write a query statement

select city,name,age from t where city= ' 杭州' order by name limit 1000 ;

According to the above table definition, city=xxx can use an index we defined. But we obviously don’t have an index for order by name, so we must first use the index to query city=xxx, then query the table back, and finally sort.

Full field sorting

After creating an index on the city field, we use the execution plan to view this statement

You can see that even if there is an index, we still use "Using filesort" to indicate that sorting is required. MySQL will allocate a piece of memory to each thread for sorting, called sort_buffer.

When we execute the above select statement, we usually go through such a process

1. Initialize sort_buffer and confirm that the three fields name, city, and age are added.

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

3. Return the table to get the values ​​of the three fields: name, city, and age, and store them in the sort_buffer.

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

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

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

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

This is called full-field sorting.

Sorting by name can be done either in memory or using an external file. This depends on sort_buffer_size. The default value of sort_buffer_size is 1048576 bytes, which is 1M. If the amount of data to be sorted is less than 1M, the sorting is done in memory. If the amount of data to be sorted is large and cannot be stored in memory, a temporary disk file is used to assist in the sorting.

Rowid sorting

If a single row is very large, putting all the required fields into the sort_buffer will not be very effective.

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

1. Initialize sort_buffer and make sure to put two fields, name and id.

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

3. Return the name and id fields to the table and store them in the sort_buffer.

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

5. Sort the names in sort_buffer.

6. Traverse the results and take the first 1000 rows. Then the result fields are retrieved from the table again according to the id and returned to the client.

In fact, not all order by statements require the above secondary sorting operation. From the execution process analyzed above, we can notice. The reason why MySQL needs to generate a temporary table is because it needs to sort the temporary table because the data we obtained before was unordered.

If we modify the previous index to make it a joint index, then the values ​​we get from the second field will actually be ordered.

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

This ensures that if we create a (city, name) index, when we search for city='Hangzhou', the second field name of the target 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).

2. Return the three values ​​of name, city and age from the table.

3. Get an id.

4. Repeat steps 2 and 3 until there are 1,000 records, or the condition "city = 'Hangzhou'" is no longer met.

Also, because the query process can use the orderliness of the index, there is no need to sort or use the sort buffer.

A further optimization is the index coverage mentioned earlier. The fields that need to be queried are also covered in the index, and the step of returning to the table is omitted, which can make the entire query faster.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.