[MySQL FAQ]系列 — 什么情况下会用到临时表

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: [MySQL FAQ]系列 — 什么情况下会用到临时表

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-sizemax-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引擎的临时表

            </div>
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
SQL 关系型数据库 MySQL
mysql加索引的时候到底会不会锁表.深入解析
默认大部分情况下 mysql的在线DDL可以让我们加索引的时候不锁表,但是也有一些限制的场景,跟本次问题相关的限制情况是在线DDL 操作完成之前,它必须等待在表上持有元数据锁(Metadata Lock)的事务提交或回滚。
8303 0
mysql加索引的时候到底会不会锁表.深入解析
|
SQL 存储 数据可视化
删库跑路?不可回滚?MySQL创建和管理表,修改清空表,MySQL8新特性DDL原子化,完整详细可收藏
删库跑路?不可回滚?MySQL创建和管理表,修改清空表,MySQL8新特性DDL原子化,完整详细可收藏
250 0
删库跑路?不可回滚?MySQL创建和管理表,修改清空表,MySQL8新特性DDL原子化,完整详细可收藏
|
SQL 存储 算法
[MySQL FAQ]系列 — 什么情况下会用到临时表
[MySQL FAQ]系列 — 什么情况下会用到临时表
|
SQL Oracle 关系型数据库
PG数据库版本查看方法,sql语句查pg数据库版本方法
PG数据库版本查看方法,sql语句查pg数据库版本方法
515 0
PG数据库版本查看方法,sql语句查pg数据库版本方法
|
SQL 关系型数据库 MySQL
mysql之修改表引擎
mysql之修改表引擎
123 0
|
存储 SQL 缓存
|
存储 关系型数据库 MySQL
MySQL 如何查看表的存储引擎
MySQL 如何查看表的存储引擎   在MySQL中如何查看单个表的存储引擎? 如何查看整个数据库有那些表是某个特殊存储引擎,例如MyISAM存储引擎呢?下面简单的整理一下这方面的知识点。   如果要查看单个表的存储引擎,可以用show create table命令查看该表的存储引擎,那么有下面一些...
2062 0