位置: 编程技术 - 正文
MySQL两种临时表的用法详解(mysql 临时表)
编辑：rootadmin推荐整理分享MySQL两种临时表的用法详解(mysql 临时表)，希望有所帮助，仅作参考，欢迎阅读内容。
文章相关热门搜索词:mysql临时表的作用,mysql临时表会占用运行内存吗?,mysql临时表会占用运行内存吗?,mysql中临时表的特点,mysql中临时表,mysql使用临时表提高查询效率,mysql临时表的创建表语句,mysql临时表的创建表语句,内容如对您有帮助，希望把文章链接给更多的朋友！
外部临时表
通过CREATE TEMPORARY TABLE 创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。
内部临时表
内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。内部临时表在SQL语句的优化过程中扮演着非常重要的角色， MySQL中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。
内部临时表有两种类型：一种是HEAP临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要IO操作。另一种是OnDisk临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk临时表用来处理中间结果比较大的操作。如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE（详情请参考MySQL手册中系统变量部分），HEAP临时表将会被自动转换成OnDisk临时表。OnDisk临时表在5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎。
本篇文章主要介绍哪些操作可能会利用到内部临时表。如果用户在书写SQL语句的时候能够尽量少的使用内部临时表进行查询优化，将有效的提高查询执行的效率。
首先我们定义一个表t1，
下面所有的操作都是基于表t1进行举例的。
在SQL语句中使用SQL_BUFFER_RESULT hint
SQL_BUFFER_RESULT主要用来让MySQL尽早的释放表上的锁。因为如果数据量很大的话，需要较长时间将数据发送到客户端，通过将数据缓冲到临时表中可以有效的减少读锁对表的占用时间。
例如：
如果SQL语句中包含了DERIVED_TABLE。
在5.7中，由于采用了新的优化方式，我们需要使用 set optimizer_switch='derived_merge=off'来禁止derived table合并到外层的Query中。
例如：
如果我们查询系统表的话，系统表的数据将被存储到内部临时表中。
我们当前不能使用EXPLAIN来查看是否读取系统表数据需要利用到内部临时表，但是可以通过SHOW STATUS来查看是否利用到了内部临时表。
例如：
如果DISTINCT语句没有被优化掉，即DISTINCT语句被优化转换为GROUP BY操作或者利用UNIQUE INDEX消除DISTINCT, 内部临时表将会被使用。
如果查询带有ORDER BY语句，并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据，然后对中间数据进行排序。
1）如果连接表使用BNL（Batched Nestloop）/BKA(Batched Key Access)
例如：
1))BNL默认是打开的
2))关掉BNL后，ORDER BY将直接使用filesort。
2）ORDER BY的列不属于执行计划中第一个连接表的列。
例如：
3）如果ORDER BY的表达式是个复杂表达式。
那么什么样的ORDER BY表达式，MySQL认为是复杂表达式呢？
1))如果排序表达式是SP或者UDF。
例如：
2))ORDER BY的列包含聚集函数
为了简化执行计划，我们利用INDEX来优化GROUP BY语句。
例如：
3))ORDER BY的列中包含有SCALAR SUBQUERY,当然该SCALAR SUBQUERY没有被优化掉。
例如：
4) 如果查询既带有ORDER BY同时也有GROUP BY语句，但是两个语句使用的列不相同。
注意: 如果是5.7，我们需要将sql_mode设置为非only_full_group_by模式，否则会报错。
同样为了简化执行计划，我们利用INDEX来优化GROUP BY语句。
例如：
如果查询带有GROUP BY语句，并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据，然后对中间数据进行GROUP BY。
1）如果连接表使用BNL（Batched Nestloop）/BKA(Batched Key Access)。
例如：
2) 如果GROUP BY的列不属于执行计划中的第一个连接表。
例如：
3) 如果GROUP BY语句使用的列与ORDER BY语句使用的列不同。
例如：
4) 如果GROUP BY带有ROLLUP并且是基于多表外连接。
例如：
5) 如果GROUP BY语句使用的列来自于SCALAR SUBQUERY，并且没有被优化掉。
例如：
IN表达式转换为semi-join进行优化
1) 如果semi-join执行方式为Materialization
例如：
2) 如果semi-join执行方式为Duplicate Weedout
例如：
如果查询语句带有UNION，MySQL将利用内部临时表帮助UNION操作消除重复。
例如：
如果查询语句使用多表更新。
这里Explain不能看到内部临时表被利用，所以需要查看status。
例如：
如果聚集函数中包含如下函数，内部临时表也会被利用。
例如：
总之，上面列出了种情况，MySQL将利用内部临时表进行中间结果缓存，如果数据量比较大的话，内部临时表将会把数据存储在磁盘上，这样显然会对性能有所影响。为了尽可能的减少性能损失，我们需要尽量避免上述情况的出现。
总结
标签: mysql 临时表
本文链接地址:https://www.jiuchutong.com/biancheng/348351.html 转载请保留说明！上一篇：初探SQL语句复合主键与联合主键(sql 复合语句)
下一篇：mysql创建删除表的实例详解(mysql语句删除表)
