preface

In the process of interview, the investigation of order by and group by generally focuses on order by and group by. Today, we will study the knowledge points related to order by. What are the points we answer about order by

  • How to optimize group by,order by in MySQL
  • The difference between group by and order by
  • The execution of order by is slow when sorting. How to optimize it

concept

When we query, if we need to sort the data read in the query, we need to use the order by field to sort, the syntax of sorting is

SELECTfield1, field2,... fieldNFROM table_name ORDER BY field1 desc/ASC 
Copy the code

Mysql > execute Mysql > execute Mysql > execute Mysql > execute Mysql > execute Mysql > execute Mysql

Sorting algorithm

We analyze the relevant process through a specific case, and the construction sentences are shown as follows

CREATE TABLE user (
  id int(11) AUTO_INCREMENT COMMENT 'primary key id',
  city varchar(16) NOT NULL COMMENT 'city'.name varchar(16) NOT NULL COMMENT 'name', 
  age int(11) NOT NULL COMMENT 'age',
  PRIMARY KEY (id),
  KEY city (city)
) ENGINE=InnoDB;
insert into `user` (city,name,age) VALUES ('Beijing'.'Programmer Fly'.20);
insert into `user` (city,name,age) VALUES ('Beijing'.'little red'.22);
insert into `user` (city,name,age) VALUES ('Shanghai'.'a little'.22);
insert into `user` (city,name,age) VALUES ('in suzhou'.'big fly'.30);
insert into `user` (city,name,age) VALUES ('hangzhou'.'Programmer Fly'.26);
insert into `user` (city,name,age) VALUES ('guangzhou'.'Programmer Fly'.21);
insert into `user` (city,name,age) VALUES (Changchun, ' '.'Programmer Fly'.20);
Copy the code

Full field sort

Now there is such a demand, according to the age from small to large, query the first three cities in Beijing user information, we write SQL should be written like this, this SQL is simple to execute, next we through explain keywords to see the SQL execution plan

Select * from 'user' WHERE 'city' =' Beijing 'ORDER by age limit 3;Copy the code

* * * * * * * * * * * * * * * *

  1. Initialize sort_buffer and make sure to put the fields ID, city, name and age
  2. Select the first primary key id from index city that meets the condition city=’ Beijing ‘
  3. Select id, city, name, age and sort_buffer from sort_buffer
  4. Gets the primary key ID of a record from index City
  5. Repeat Steps 3 and 4 until the value of city does not meet the query conditions
  6. Quicksort sort_buffer by field age
  7. Return the first three rows according to the sorting result to the client

Mysql allocates a portion of memory (sort_buffer) for each query thread. The size of memory is controlled by sort_buffer_size. In the preceding procedure, we put the id, city, name and age of the query thread into sort_buffer. We call the algorithm used in the above process full-field sorting.

If sort_buffer does not fit, mysql will borrow temporary files from disk to help sort, and will use a merge algorithm. The specific process is as follows

  1. First, find the first primary key ID that meets the condition of city=’ Beijing ‘from index city to perform table back operation to find relevant data
  2. Put the queried data into SORt_buffer. When SORt_buffer is almost full, sort this part of data in SORt_buffer first. The sorted records are temporarily put into the disk small file, and continue to read data to SORt_buffer
  3. Step 2 will get a lot of ordered small files, use merge sort to merge into a large file, complete the sorting process

The rowId sorting

Sort_buffer is used to sort all fields, such as ID, city, name, and age. In our SQL, the sorting rule is order by age. If we only need to put age into sort_buffer, Mysql provides a new sort algorithm, rowid, that can be used to sort the sort_buffer from temporary files. This algorithm can be used to sort the sort_buffer from temporary files

  1. Initialize sort_buffer and make sure to put two fields, age and ID;
  2. Select first primary key id from index city where city=’ Beijing ‘;
  3. Select * from sort_buffer; select * from sort_buffer; select * from sort_buffer; select * from sort_buffer;
  4. Fetch the primary key ID of a record from index city;
  5. Repeat steps 3 and 4 until the condition city=’ Beijing ‘is not met.
  6. Sort the data in sort_buffer by field age;
  7. Select id, name, city, age from ‘sort’; select * from ‘sort’; select * from ‘sort’; select * from ‘sort’;

Optimization idea

Resize sort_buffer

Memory is definitely faster than disk. Mysql has the idea that if you have enough memory, you should use more memory to minimize disk access, so we tried to make sort_buffer larger

Avoid Rowid sorting

For InnoDB tables, roWID sorting will add one more table back operation, which will increase disk read. We can adjust the configuration parameter max_LENGTH_FOR_sort_data properly. If the size of the fields in the sort_buffer is larger than this, Mysql will use rowid sorting, which defaults to 1KB (try not to write select * during development).

Using federated indexes

Mysql > select * from ‘city’; Mysql > select ‘city’; Mysql > select * from ‘city’; Mysql > select * from ‘city’; When city is the same, age is naturally ordered, avoid sorting, B+ tree storage structure is shown in the figure

select * from `user` where `city`='Beijing' order by age limit 3; //order by age, age is not requiredCopy the code

Using fileSort is not used in Extra when creating index (city, age).

gossip

If you feel helpful, please click “like” to pay attention, which will be a great encouragement to me ~, the official account has its own series of articles, you need to pay attention to the personal official account programmer Fly, I hope we can grow up together.

Refer to the link

www.cnblogs.com/Chenjiabing…

Juejin. Cn/post / 684490…

Time.geekbang.org/column/intr…