MySQL临时表(内部和外部)
顾名思义,临时表就是临时用来存储数据的表,是建立在系统临时文件夹中的表,如果使用得当,完全可以像普通表一样进行各种操作。
我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时,可以把中间结果或小的子集放到一个临时表里,再对这些表进行查询,以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。
一般情况下,临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。
我们可以使用以下命令来查看临时表:
外部临时表也有一些限制,使用时需注意以下几点:
我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。
内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色,MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价,所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。
以下是生成内部临时表的几种可能:
内部临时表有两种类型,一种是 HEAP 临时表,这种临时表的所有数据都会存在内存中,对于这种表的操作不需要 IO 操作;另一种是 OnDisk 临时表,顾名思义,这种临时表会将数据存储在磁盘上。OnDisk 临时表用来处理中间结果比较大的操作。
以下情况可能阻碍 MySQL 使用 HEAP 临时表,改用 OnDisk 临时表:
我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时,可以把中间结果或小的子集放到一个临时表里,再对这些表进行查询,以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。
一般情况下,临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。
外部临时表
外部临时表也可称为会话临时表,这种临时表只对当前用户可见,它的数据和表结构都存储在内存中。当前会话中断或结束后,数据表数据就会丢失,MySQL 会自动删除表并释放其所占空间。1)创建临时表
创建临时表很容易,在 CREATE TABLE 语句上添加 TEMPORARY 关键字,如下所示:CREATE TEMPORARY TABLE <表名>...
临时表的命名可以和非临时表同名,但是同名后非临时表将对当前会话不可见,直到临时表被删除。2)查询临时表
创建了临时表之后,运行 SHOW TABLES 命令不会列出临时表,以及在 INFORMATION_SCHEMA 数据库中也不存在临时表的信息,这不是 Bug,而是设计就是如此。我们可以使用以下命令来查看临时表:
SHOW CREATE TABLE <表名>;
3)删除临时表
当然,我们也可以在当前会话中手动销毁临时表,SQL 语句如下:DROP TABLE <表名>;
例 1
下面我们创建 tmp_table 表,并对它进行操作。mysql> CREATE TEMPORARY TABLE tmp_table ( -> id INT NOT NULL, -> name VARCHAR(10) NOT NULL); Query OK, 0 rows affected (0.03 sec) mysql> SHOW TABLES; +-------------------+ | Tables_in_test | +-------------------+ | product | | product_price | | score | | student | | student_comment | | tb_student | | tb_student_course | | tb_students_info | | tb_students_score | | tb_usertest | +-------------------+ 10 rows in set (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G *************************** 1. row *************************** Table: tmp_table Create Table: CREATE TEMPORARY TABLE `tmp_table` ( `id` int(11) NOT NULL, `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1 row in set (0.00 sec) mysql> DROP TABLE tmp_table; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist如果你在执行 DROP TABLE 命令之前退出当前 MySQL 会话,再使用 SHOW CREATE TABLE 命令来读取 tmp_table 表,会发现数据库中没有该表的存在,因为在退出会话时该临时表已经被销毁了。
外部临时表也有一些限制,使用时需注意以下几点:
- 所用数据库账号需要有建立和使用临时表的权限
- 在同一条 SQL 语句中,不能关联2次相同的临时表
- 不能用 RENAME 来重命名一个临时表,可以用 ALTER TABLE 来代替。
内部临时表
内部临时表是一种特殊轻量级的临时表,不同于手工创建的临时表,它是被 MySQL 自动创建的。在 SQL 的执行过程中可能会用到临时表来存储某些操作的中间结果,该过程由 MySQL 自动完成,用户无法手工干预,且这种内部表对用户来说是不可见的。我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。
内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色,MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价,所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。
以下是生成内部临时表的几种可能:
- 使用 ORDER BY/GROUP BY 的列并非全来自于表连接的第一个表
- Distinct 和 ORDER BY 联合使用
- 多表连接需要保存中间结果集
内部临时表有两种类型,一种是 HEAP 临时表,这种临时表的所有数据都会存在内存中,对于这种表的操作不需要 IO 操作;另一种是 OnDisk 临时表,顾名思义,这种临时表会将数据存储在磁盘上。OnDisk 临时表用来处理中间结果比较大的操作。
以下情况可能阻碍 MySQL 使用 HEAP 临时表,改用 OnDisk 临时表:
- 数据表中含有 BLOB 类型或 TEXT 类型字段列;
- 在 GROUP BY 或 DISTINCT 任何条件中含有超过 512 字节的列;
- 如果使用了 UNION 或 UNION ALL,而且 SELECT 列中含有任何超过 512 字节的列;
- 如果 HEAP 临时表的大小大于 MAX_HEAP_TABLE_SIZE 系统变量的值,HEAP 临时表将会被自动转换成 OnDisk 临时表。
MySQL 5.7 版本中,OnDisk 临时表可以通过 INTERNAL_TMP_DISK_STORAGE_ENGINE 系统变量选择使用 MyISAM 引擎或者 InnoDB 引擎。
Created_tmp_tables 和 Created_tmp_disk_tables 变量用来记录所用临时表的数目。
所有教程
- C语言入门
- C语言编译器
- C语言项目案例
- 数据结构
- C++
- STL
- C++11
- socket
- GCC
- GDB
- Makefile
- OpenCV
- Qt教程
- Unity 3D
- UE4
- 游戏引擎
- Python
- Python并发编程
- TensorFlow
- Django
- NumPy
- Linux
- Shell
- Java教程
- 设计模式
- Java Swing
- Servlet
- JSP教程
- Struts2
- Maven
- Spring
- Spring MVC
- Spring Boot
- Spring Cloud
- Hibernate
- Mybatis
- MySQL教程
- MySQL函数
- NoSQL
- Redis
- MongoDB
- HBase
- Go语言
- C#
- MATLAB
- JavaScript
- Bootstrap
- HTML
- CSS教程
- PHP
- 汇编语言
- TCP/IP
- vi命令
- Android教程
- 区块链
- Docker
- 大数据
- 云计算