下面只介绍在查询中生成的临时表, 总结下来就是, 感觉用临时表了, 就去explain看下是不是到底用了, 最好每个sql都查下

在EXPLAIN中可以查看有没有用到temptable:EXPLAIN SELECT emp_no FROM employees.salaries union select emp_no from employees.salaries;

有临时表

但是使用union all没有临时表EXPLAIN SELECT emp_no FROM employees.salaries union all select emp_no from employees.salaries;:

有临时表

然后执行show status like "%tmp%";如果有临时表会在下面的字段中体现:

mysql临时表

新增一个临时表会在对应的字段上+1

生成临时表的条件

UNION statements

  • 但是UNION ALL不会创建临时表
  • 不是由全局的ORDER BY引起的
  • 这个可以在上面的例子中看到, 执行union all没有用到临时表

somw views, such those that use the TEMPTABLE algorithm, UNION or aggregation

这个没有实际用过, 待补充

derived tables

这个执行下面sql但是没有使用临时表, 有用的还是因为加了group by语法, 待解决:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

use employees;
CREATE TABLE employees.t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

INSERT INTO employees.t1 VALUES (1,'1',1.0);
INSERT INTO employees.t1 VALUES (2,'2',2.0);
-- 下面没有用临时表
explain SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM employees.t1) AS sb
  WHERE sb1 > 1;

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
-- 用了临时表 
explain SELECT AVG(sum_column1)
  FROM (SELECT SUM(s1) AS sum_column1
        FROM t1 GROUP BY s1) AS t1;
--  没有用临时表
explain SELECT AVG(sum_column1)
  FROM (SELECT SUM(s1) AS sum_column1
        FROM t1) AS t1;

table created for subquery or semijoin materialization

这里说是使用子查询的是会用到, 但是子查询和驱动表的区别是啥

common table expressions

使用with语法的时候会创建临时表

DISTINCT combined with ORDER BY may require a temporary table

1
2
3
4
5
6
7
8
9
explain select distinct salary from employees.salaries  order by salary ;
-- filesort: MySQLmust do an extra pass to find out how to retrieve the rows in sorted order. Thesort is done by going through all rows according to the join type and storingthe sort key and pointer to the row for all rows that match the WHERE clause.
-- # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
-- 1, SIMPLE, salaries, p01,p02,p03,p04,p05,p06,p07,p08,p09,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19, ALL, , , , , 2841613, 100.00, Using temporary; Using filesort

-- 这里给salary建立索引, 然后就不用临时表了
alter table employees.salaries add index keke(salary);
explain select distinct salary from employees.salaries  order by salary ;
-- '1', 'SIMPLE', 'salaries', 'p01,p02,p03,p04,p05,p06,p07,p08,p09,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19', 'range', 'keke', 'keke', '4', NULL, '703151', '100.00', 'Using index for group-by'

order by和group by的子句不一样时，或者表连接中order by或group by的列是被驱动表中的列

INSERT ... SELECT

to evaluate INSERT ... SELECT statements that select from and insert into the same table, mysql creates an internal temporary table to hold te rows form the SELECT, then inserts those rows into the target table.

multiple-table update statements

GROUP_CONCAT() or COUNT(DISTINCT)

为啥试了没有???

1
2
3
explain select count(distinct salary) from employees.salaries;
-- # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
-- 1, SIMPLE, salaries, p01,p02,p03,p04,p05,p06,p07,p08,p09,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19, ALL, , , , , 2841613, 100.00, null

SQL_SMALL_RESULT

for queries that use the SQL_SMALL_RESULT modifier, MySQL users an in-memory temporary table, unless the query also contains elements that require on-disk storage