The union executive

To facilitate analysis, use SQL as an example

CREATE TABLE t1 ( id INT PRIMARY KEY, a INT, b INT, INDEX ( a ) );

delimiter ;;
CREATE PROCEDURE idata ( ) BEGIN
DECLARE
        i INT;
	SET i = 1;
	WHILE
			( i <= 1000 ) DO
			INSERT INTO t1
		VALUES
			( i, i, i );
		SET i = i + 1;
	END WHILE;
	END;;

delimiter ;
CALL idata ( );
Copy the code

Then we execute the following SQL

(select 1000 as f) union (select id from t1 order by id desc limit 2);
Copy the code

The semantics of this SQL is to take the union of the two subqueries and then re-analyze the corresponding execution plan through Explain

As you can see, the key in the second row is primary, indicating that the second subquery uses the index ID. The Extra column in the third line indicates that the subquery union is Using temporary.

The execution flow of this statement looks like this:

1) Create an in-memory temporary table with only one integer field f, and f is the primary key field

2) Execute the first subquery, save 1000 to temporary table

Insert temporary table (id=1000); insert temporary table (id=1000); insert temporary table (id=1000)

Select * from temporary table; select * from temporary table; select * from temporary table

As can be seen, temporary tables play a role of temporary storage of data, and there is a unique constraint, to achieve the semantics of union deduplication

group by

Another common example of using temporary tables is group by. Let’s look at the following SQL

select id%10 as m, count(*) as c from t1 group by m;
Copy the code

Select * from t1; select * from t1; select * from t1; select * from t1

In the Extra field, we see three messages:

1) select index A from index A;

2) Using temporary;

3) Using filesort

The execution flow of this statement looks like this:

Select * from table where primary key = m and primary key = m;

2) Scan index A of table T1, extract the ID values above leaf nodes in turn, calculate the result of ID %10, and write it as X; If the temporary table has no primary key x, insert a record (x,1); If there are rows in the table with primary key X, increment the c value of the row x by 1;

3) After traversal, sort according to field M to get the result

The tmp_table_size parameter controls the size of temporary tables in memory. The default value is 16MB. If the size of temporary tables in memory reaches the upper limit, then the temporary tables in memory are converted to disk temporary tables. It is likely that queries will take up a lot of disk space