Create a table, then use the query statement: query city is “Hangzhou” all the names, and return the first 1000 people by name, age

create table 't' (
	'id' int(11) not null.'city' vachar(16) not null.'name' vachar(16) not null.'age' vachar(16) not null.'addr' varchar(128) default null.primary key('id'),
	key 'city'('city')
)engine = InnoDB;

select city,name,age from t where city = 'hangzhou' order by name limit 1000;
Copy the code

Its execution flow is as follows:

Full field sort

To avoid a full table scan, you need to index the city field

Suppose the rows that satisfy the condition city = ‘hangzhou’ are the records from ID_X to ID_(X+N). Execution process: 1. Initialize sort_buffer to ensure that the name, city and AGE fields are added; Select * from city where id = ‘hangzhou’; select * from city where id = ‘hangzhou’; Select name, city, age, and sort_buffer from primary key ID index; Select * from city; select * from city; 5. Repeat step3 and 4 until the value of city does not meet the query conditions, and the corresponding ID(X+N); 6. Quicksort the data in sort_buffer by field name. 7

Tip: Sort_buffer is the memory MySQL allocates to each thread for sorting. Sort_buffer is the amount of memory MySQL allocates to each thread for sorting. Sort_buffer_size is the size of sort_buffer. If the amount of data to be sorted is smaller than sort_buffer_size, the sort is done in memory. If the amount of data to be sorted is too large, the sort is assisted by disk temporary files. External sort usually uses merge sort algorithm.

The rowid sorting

Disadvantages of the full-field sorting method: if a single line is large, it takes up memory space. By modifying a parameter in MySQL that specifically controls the length of rows used for sorting. What that means is that if the length of a single row exceeds this value, it’s going to do a different algorithm

SET max_length_for_sort_data = 16;
Copy the code

The new algorithm will only put sorted columns (name field) and primary key IDS into sort_buffer; So the result of sorting is missing the city and age fields and cannot be returned directly. The execution process is as follows: 1. Initialize sort_buffer and confirm the name and ID fields; Select the first primary key id (ID_X) from the city index that meets the condition city = ‘hangzhou’; Select * from sort_buffer; select * from sort_buffer; Select * from sort_buffer where city = ‘hangzhou’; select * from primary key where city = ‘hangzhou’; select * from primary key where city = ‘hangzhou’; And return the city, name, and age fields to the client according to the id value

The ROWID mode is the same as the full-field mode. You need to store all the queried results in the memory or hard disk first and then sort them using related algorithms. After sorting, because there is no required field, you need to use the primary key in order and then query from the index tree. If you find one, you will return one, rather than putting all the contents in memory and then return one at a time.

Both are

Full field sort The rowid sorting

If the sorting memory is too small, it will affect the sorting efficiency, so the ROWID sorting algorithm is adopted. In this way, more rows can be sorted in sequence during the sorting process, but it needs to go back to the original table to fetch data. If MySQL determines that the memory is large enough, it will select the full field sort preferentially, and put all the required fields into sort_buffer, so that after sorting, the query results will be returned directly from memory, without having to retrieve the data from the original table.

For InnoDB, roWID sorting will require multiple disk reads from tables, so it will not be preferred.