临时表介绍
什么是临时表：mysql用于存储一些中间结果集的表，临时表只在当前连接可见，当关闭连接时，mysql会自动删除表并释放所有空间。为什么会产生临时表：一般是由于复杂的sql导致临时表被大量创建
临时表分为两种，一种是内存临时表，一种是磁盘临时表。内存临时表采用的是memory存储引擎，磁盘临时表采用的是myisam存储引擎（磁盘临时表也可以使用innodb存储引擎，通过internal_tmp_disk_storage_engine参数来控制使用哪种存储引擎，从mysql5.7.6之后默认为innodb存储引擎，之前版本默认为myisam存储引擎）。分别通过created_tmp_disk_tables 和 created_tmp_tables 两个参数来查看产生了多少磁盘临时表和所有产生的临时表（内存和磁盘）。
mysql在以下几种情况会创建临时表：
1、union查询；
2、用到temptable算法或者是union查询中的视图；
3、order by和group by的子句不一样时；
4、表连接中，order by的列不是驱动表中的；
5、distinct查询并且加上order by时；
6、sql中用到sql_small_result选项时；
7、from中的子查询；
8、子查询或者semi-join时创建的表；
explain 查看执行计划结果的 extra 列中，如果包含 using temporary 就表示会用到临时表。
当然了，如果临时表中需要存储的数据量超过了上限（ tmp-table-size 或 max-heap-table-size 中取其大者），这时候就需要生成基于磁盘的临时表了。
在以下几种情况下，会创建磁盘临时表：
1、数据表中包含blob/text列；
2、在 group by 或者 dstinct 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）；
3、在select、union、union all查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）；
4、执行show columns/fields、describe等sql命令，因为它们的执行结果用到了blob列类型。
从5.7.5开始，新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 innodb，而在这以前，只能使用 myisam。而在5.6.3以后新增的系统选项 default_tmp_storage_engine 是控制 create temporary table 创建的临时表的引擎类型，在以前默认是memory，不要把这二者混淆了。
详见下例
mysql> set default_tmp_storage_engine = "innodb"; -rw-rw---- 1 mysql mysql 8558 jul 7 15:22 #sql4b0e_10_0.frm -- innodb引擎的临时表 -rw-rw---- 1 mysql mysql 98304 jul 7 15:22 #sql4b0e_10_0.ibd -rw-rw---- 1 mysql mysql 8558 jul 7 15:25 #sql4b0e_10_2.frm mysql> set default_tmp_storage_engine = "myisam"; -rw-rw---- 1 mysql mysql 0 jul 7 15:25 #sql4b0e_10_2.myd -- myisam引擎的临时表 -rw-rw---- 1 mysql mysql 1024 jul 7 15:25 #sql4b0e_10_2.myi mysql> set default_tmp_storage_engine = "memory"; -rw-rw---- 1 mysql mysql 8558 jul 7 15:26 #sql4b0e_10_3.frm -- memory引擎的临时表
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对移动技术网的支持。
如对本文有疑问， 点击进行留言回复！！
服务器Centos部署MySql并连接Navicat过程详解
我要评论
网友评论