mysql是怎样使用内部临时表的
创建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字:
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)
临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。
DROP TABLE tmp_table
如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在,临时表将有必要屏蔽(隐藏)非临时表tmp_table。
如果你声明临时表是一个HEAP表,MySQL也允许你指定在内存中创建它:
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP
因为HEAP表存储在内存中,你对它运行的查询可能比磁盘上的临时表快些。然而,HEAP表与一般的表有些不同,且有自身的限制。详见MySQL参考手册。
正如前面的建议,你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引,临时表可能一点不快。
1. 临时表再断开于mysql的连接后系统会自动删除临时表中的数据,但是这只限于用下面语句建立的表:
定义字段:
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)
2)直接将查询结果导入临时表
CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name
2. 另外mysql也允许你在内存中直接创建临时表,因为是在内存中所有速度会很快,语法如下:
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP
3. 从上面的分析可以看出临时表的数据是会被清空的,你断开了连接就会被自动清空,但是你程序中不可能每发行一次sql就连接一次数据库吧(如果是这样的话,那就会出现你担心的问题,如果不是就没有问题),因为只有断开数据库连接才会被清空数据,在一个数据库连接里面发行多次sql的话系统是不会自动清空临时表数据的。
提到MySQL临时表，我们都很熟悉了，一般来说，分为两类：
1. MySQL 临时表引擎，名字叫做 Memory。比如
create table tmp1(id int, str1 varchar(100) ) engine = memory;
由参数max_heap_table_size 来控制，超过报错。
2. 非临时表的引擎，这里又分为两类：
用户自定义的临时表，比如:
- create temporary table (id int, str1 varchar(100) );
SQL执行过程中产生的内部临时表，比如：UNION , 聚合类ORDER BY，派生表，大对象字段的查询，子查询或者半连接的固化等等场景。
- mysql> show status like '%tmp_%tables%';``+-------------------------+-------+``| Variable_name | Value |``+-------------------------+-------+``| Created_tmp_disk_tables | 0 |``| Created_tmp_tables | 0 |``+-------------------------+-------+``2 rows in set (0.00 sec)
VARCHAR的变长存储。那就是如果临时表的字段定义是 VARCHAR(200)，那么映射到内存里处理的字段变为CHAR(200)。假设 VARCHAR(200) 就存里一个字符 "Y", 那岂不是很大的浪费。
大对象的默认磁盘存储，比如 TEXT，BLOB， JSON等，不管里面存放了啥，直接转化为磁盘存储。
- mysql> SELECT * FROM performance_schema. memory_summary_global_by_event_name WHERE event_name like '%temptable%'G*************************** 1. row *************************** EVENT_NAME: **memory/temptable/physical_disk** COUNT_ALLOC: 0 COUNT_FREE: 0 SUM_NUMBER_OF_BYTES_ALLOC: 0 SUM_NUMBER_OF_BYTES_FREE: 0 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 0 HIGH_COUNT_USED: 0 LOW_NUMBER_OF_BYTES_USED: 0CURRENT_NUMBER_OF_BYTES_USED: 0 HIGH_NUMBER_OF_BYTES_USED: 0*************************** 2. row *************************** EVENT_NAME: **memory/temptable/physical_ram** COUNT_ALLOC: 1 COUNT_FREE: 0 SUM_NUMBER_OF_BYTES_ALLOC: 1048576 SUM_NUMBER_OF_BYTES_FREE: 0 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 1 HIGH_COUNT_USED: 1 LOW_NUMBER_OF_BYTES_USED: 0CURRENT_NUMBER_OF_BYTES_USED: 1048576 HIGH_NUMBER_OF_BYTES_USED: 10485762 rows in set (0.03 sec)
默认内部临时表引擎。
支持变长字符类型的实际存储。
设置变量 temp_table_max_ram 来控制实际存储内存区域大小。
那么这两种临时表的计数器通常用 show global status like '%tmp_%tables%' 来查看。比如
以上结果分别代表，只创建磁盘上的临时表计数以及临时表的总计数。这两个计数器由参数 tmp_table_size 和 max_heap_table_size 两个取最小值来控制。
那在 MySQL 5.7 之前，这个 SQL 运行中产生的临时表是 MYISAM，而且只能是 MYISAM。那 MySQL 从 5.7 开始提供了参数 Internal_tmp_mem_storage_engine 来定义内部的临时表引擎，可选值为 MYISAM 和 INNODB 。当然这里我们选择 INNODB 。并且把内部的临时表默认保存在临时表空间 ibtmp1 (可以用参数 innodb_temp_data_file_path 设置大小以及步长等)下。当然这里我们得控制下 ibtmp1 的大小，要不然一个烂SQL就把磁盘整爆了。
但是MySQL 5.7 之前都没有解决如下问题:
MySQL 8.0 开始，专门实现了一个临时表的引擎 TempTable , 解决了 VARCHAR字段的边长存储以及大对象的内存存储。由变量 interal_tmp_mem_storage_engine来控制，可选值为 TempTable（默认）和 Memory；新引擎的大小由参数temp_table_max_ram 来控制，默认为1G。超过了则存储在磁盘上（ibtmp1）。并且计数器由性能字典的表 memory_summary_global_by_event_name 来存储。
以上 memory/temptable/physical_disk 代表放入磁盘上的临时表计数情况。
memory/temptable/physical_ram 代表放入内存的临时表计数情况。
那总结下MySQL 8.0 引入的 TempTable 引擎：
