1. Introduction

Select * from ‘Extra’ where ‘Using index’ is used; select * from ‘Extra’ where ‘index’ is used; Using filesort or temporary you need to optimize the index if you want to create a temporary table. So, the question is, how do we optimize? Why do these two things happen, and how does Mysql perform? Why is it inefficient? So, if you don’t know how it works, you can’t really optimize it; So, this article is mainly to explore the Mysql Using filesort, Using temporary scenarios and optimization methods;

2.Using filesort

Using filesort: Sort files; Let’s first simulate file sorting, and then analyze why the file sorting efficiency is low; Finally, the solution;

2.1 Simulation of Using filesort phenomenon

Build table statements

CREATE table study (
	id int(11) not null,
	name varchar(32) DEFAULT NULL,
	score int(11) DEFAULT NULL.PRIMARY key (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
Copy the code

Now, there is a need to find all the students, and then rank them according to their grades from highest to lowest; Obviously, the SQL statement could be written like this:

select id, name, score FROM study order by score DESC;
Copy the code

We then analyze the SQL statement using Explain:

The analysis results are as follows: Using full table scan, in the process of scanning, Using filesort file sorting; Obviously, file sorting needs to be optimized;

2.2 Mysql implementation Using filesort

Using filesort to sort files, in fact, there are many kinds of filesort, such as: according to the size of the content to sort, there are internal sort and external sort; If the sort is small, it can be done in memory, which is internal sorting (using quicksort). If the content to sort is too large, then you need the help of disk, which is external sorting (using merge). Also, is to distinguish according to the size of a row, if the content of a row is not very large, then the whole field read out for sorting, called the whole field sort; If, the entire field content is large, then, use ROWID sort, read THE ROWID and the field first sort, and then, back to the table to find other contents; In the following sections, I’ll explain how this SQL is executed for full-field sort and ROwid sort respectively.

2.2.1 Sorting by all fields

SQL Execution Process

  1. Initialize sort_buffer to make sure that the id,name, and score fields are to be added
  2. The whole table was scanned, and the values of id, name and score were taken out and stored in sort_buffer.
  3. Quick sort sort_buffer by field score (where filesort is generated);
  4. Return the sorted result

Note: the third step is likely to be completed in the memory, but, if the memory of sorting data volume is too big, in the store, are having to use disk temporary file auxiliary sorting, if, in the store not bottom, using the external sorting, (will be the result of the fast line written to a temporary file) external sorting using the merge sort; Summary: For full-field sorting Using filesort, the time complexity of fast sorting is NlogN. Therefore, the efficiency is low; It should be avoided.

2.2.2 the rowid sorting

Rowid: If there is no primary key or non-empty unique index in a table, a roWID of 6 bytes is created. Generation background: In the process of sorting,sort_buffer is limited. If we need to query a large number of fields, the number of fields in sort_buffer will be too many,so that the memory can be put down at the same time,so we need to divide into multiple temporary files, and then merge, and the sorting performance will be poor. To solve this problem, Mysql is optimized to read rowid + columns when a row of data is greater than a threshold. Then, roWID back to the table to query the remaining fields; SHOW VARIABLES LIKE ‘% max_length_FOR_sort_data %’; This directive looks at the threshold; SQL execution procedures using ROWID

  1. Initialize sort_buffer to make sure the fields are ID and score;
  2. The whole table was scanned, and the values of id and SCORE were taken out and stored in sort_buffer.
  3. Quick sort sort_buffer data by field score (if there is too much data, external files may be sorted);
  4. After sorting all data, query the content of name field in the table according to the ID;
  5. Return all data to the client

2.3 Solutions

For this problem, the solution is relatively simple; The fields to be queried are ID, name, score; Then, you need to sort the score. Select * from score + name where id is the primary key; select * from score + name where id is the primary key;

create index index_score_name on study(score, name);
Copy the code

The explain analysis:

Type = index indicates scanning based on indexed columns. Extra = Using index = Backward index scan; Find from back to front based on index; Better effect; After testing, we used indexes, exchanged space for time, and arranged data submission according to score, which met our requirements.

3.Using temporary

Context when Mysql executes a query, filesort or temporary may be generated for order BY. Filesort can only be used on a single table. If multiple tables need to be sorted, Mysql will create a temporary table to store the data, and then use filesort to sort the data on the temporary table.

3.1 Scene Reproduction

Construction sentences:

create table t1(    
id int, col1 int, col2 varchar(10),
key(id, col1));
Copy the code
create table t2(
id int, col1 int, col2 varchar(10),
key(col1));
Copy the code

Case 1:

explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t1.col1;
Copy the code

Here, neither TEMPORARY nor filesort is used.

So, how does it work?

It actually locates t1.id; Then, locate T1.col1; Both of these are indexed; Then, the join with t2 table is performed (where), so there is no file sorting and temporary table;

Situation 2:

explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t1.col2;
Copy the code

This is generating filesort; The execution process is as follows:

  1. Based on the first location t1.id;
  2. I’m going to take out all of the fields where ID =1, I’m going to take out all the fields; Save to sort_buffer.
  3. After storing in sort_buffer, sort according to col2 of t1;
  4. After the sorted t1 table, join the data in T2 table according to the sorted result set; (Therefore, no temporary files are generated)

Case 3:

explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t2.col1 ;
Copy the code



Here, temporary and filesort are generated; The execution process is as follows:

  1. Locate the row where ID = 1 based on the id index.
  2. Because the order by field is on t2 table; Therefore, at this time, it is not possible to join t2 tables after sorting;
  3. Add table t1 and table T2 to temporary table filesort; So efficiency is slow;

3.2 Solutions

Temporary does not create temporary if the columns to be sorted are in the main table. So, if possible, we try to modify the SQL statement to place the fields to be sorted in the main table; Or use straight_join(forcing the left table to be driven); SQL rewrite for case 3:

select * from t2 force index(col1)  straight_join t1  on t1.id=1 and t1.col1 = t2.col2 order by t2.col1 ;
Copy the code

Explain analysis is as follows:

4. To summarize

This article is mainly based on personal write filesort, temporary cause, processing; Insufficient consideration may be given to treatment options if there are some other causes or treatment options. Welcome to exchange;