时间：2021-07-01 10:21:17 帮助过：4人阅读
MySQL临时表分为系统使用的临时表和用户使用的临时表。
系统使用的临时表是指MySQL在执行某些SQL语句时需要依赖临时表来完成整个过程。系统使用的临时表的情况可以分为以下几种：
* group by和order by中的列不相同，例如：ORDERY BY price GROUP BY name；
* order by的列或者group by的列不是引用from 表列表中 的第一表，例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
* 使用了sql_small_result选项 ，SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很小，请直接使用内存临时表
* 含有distinct的order by语句，例如：ORDERY BY DISTINCT(price)
* from子查询会用到临时表
查看MySQL中关于临时表的配置参数：
mysql> show variables like ‘%tmp_table%‘;
+-----------------------+--------------+
| Variable_name | Value |
+-----------------------+--------------+
| max_tmp_tables | 32 |
| tmp_table_size | 47185920 |
+-----------------------+---------------+
下面是2个查询中用到了临时表的例子：
mysql> select * from person;
+----------+---------+
| name | age |
+-----------+--------+
| tom | 22 |
| tony | 31 |
+-----------+--------+
mysql> select * from grade;
+------------+-----------+
| name | grade |
+------------+------------+
| tom | 22 |
| tony | 31 |
| unkown | 40 |
+-------------+-------------+
mysql> explain select g.name,g.grade from person p,grade g where p.name = g.name group by(g.name);
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary; Using filesort |
| 1 | SIMPLE | g | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
mysql> explain select m.name,p.age from person as p inner join(select person.name from person,grade where person.name = grade.name )as m on p.name = m.name;
+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------+
| 1 | PRIMARY | p | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 2 | DERIVED | person | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | DERIVED | grade | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using join buffer |
+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------+
当查询的数据量很大时，临时表又转换为了磁盘临时表，这时的查询速度就会较慢。
有些情况下MySQL很可能会直接使用磁盘临时表：
* 表包含TEXT或者BLOB列；
* GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；
* 使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列；
常见的避免临时表的方法有：
* 创建索引：在ORDER BY或者GROUP BY的列上创建索引；
* 分拆很长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。
临时表主要是用于排序和分组，很多业务都是要求排序后再取出详细的分页数据，这种情况下可以将排序和取出详细数据拆分成不同的SQL，以降低排序或分组时临时表的大小，提升排序和分组的效率。
自己定义的临时表可以作为大数据表的一部分，方便查询。
通过语句CREATE TEMPORARY TABLE XXX 来创建应用所需的临时表。我们自定义的临时表有以下一些特点：
* 临时表的存在的周期是当前登录的session有效，断开连接将会被立即删除，也可手动将其drop table，无论以任何类型创建的临时表，在对应的数据库目录下都没有具体的文件生成；既然是session有效，那么不同的session间是不能访问别人定义的临时表的，不同session定义的临时表名就可以相同。
* 支持多种类型的表：MyISAM、InnoDB等，通过max_heap_table_size参数值来控制临时表的大小；
* 为什么要用它呢？临时表可以将我们之后可能频繁使用到的中间数据集临时保存下来，这样就会提高业务的处理速度，再有就是可以减少程序代码量，将一定的逻辑处理放到数据库上去完成，这之间的代价需要实际去评估；到了5.0以后出现了视图，对于开发人员有多了一种可选择的方法，而视图的定义是可以永久保存到磁盘上的，视图是不能重名的。
MySQL系统临时表、用户临时表
标签：