mysql派生表内存_浅谈MySQL临时表与派生表

末蓝、 2023-01-11 13:27 170阅读 0赞

关于派生表

当主查询中包含派生表，或者当select 语句中包含union字句，或者当select语句中包含一个字段的order by 子句(对另一个字段的group by 子句)时，MySQL为了完成查询，则需要自动创建临时表存储临时结果集，这种临时表由MySQL自行创建，自行维护，成为自动创建的临时表。对于自动创建的临时表，由于内存临时表的性能更为优越，mysql总是首先使用内存临时表，而当内存临时表变得太大时，达到某个阈值的时候，内存临时表就转存为外存临时表。也就是说，外存临时表是内存临时表在存储空间上的一种延伸。内存临时表转存为外存临时表的阈值由系统变量max\_heap\_table\_size和tmp\_table\_size的较小值决定。

派生表一般在from子句中使用。如：

select \* from (select \* from table) as t;

关于临时表

当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让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的话系统是不会自动清空临时表数据的。

发表评论取消回复

表情：

评论列表（有 0 条评论，170人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL SQL 优化--JOIN派生表

SELECT ap.institution\_id, date(ra.create\_time) date, sum( IF ( ra.confirm

偏执的太偏执、/ 2024年02月18日 20:21/ 0 赞/ 65 阅读

相关 mysql派生表内存_浅谈MySQL临时表与派生表

关于派生表当主查询中包含派生表，或者当select 语句中包含union字句，或者当select语句中包含一个字段的order by 子句(对另一个字段的group by

末蓝、/ 2023年01月11日 13:27/ 0 赞/ 171 阅读

相关 MySQL-37：内存临时表

37.1 临时表的组织结构 InnoDB引擎： B+树方式的组织结构，前面是索引，节点是索引+数据，节点是有序的。 ![img][] Memory引擎：索引是has

缺乏、安全感/ 2023年01月01日 02:59/ 0 赞/ 171 阅读

相关 mysql优化：内存表和临时表

由于直接使用临时表来创建中间表，其速度不如人意，因而就有了把临时表建成内存表的想法。但内存表和临时表的区别且并不熟悉，需要查找资料了。一开始以为临时表是创建后存在，当连接

超、凢脫俗/ 2022年09月17日 13:24/ 0 赞/ 209 阅读

相关 Mysql 内存表和临时表的区别

内存表，就是放在内存中的表，所使用内存的大小可通过My.cnf中的max\_heap\_table\_size指定，如max\_heap\_table\_size=1024M，

向右看齐/ 2022年08月26日 08:43/ 0 赞/ 228 阅读

相关 MySQL SQL 优化案例：JOIN派生表

优化之前： SELECT ap.institution_id, date(ra.create_time) date, sum(

ゞ浴缸里的玫瑰/ 2022年08月19日 08:13/ 0 赞/ 212 阅读

相关 mysql 内存表和临时表学习

前景 mysql三种虚拟表临时表内存表视图临时表简介：临时表是建立在系统

待我称王封你为后i/ 2022年05月29日 23:11/ 0 赞/ 296 阅读

相关 Mysql 临时表

当工作在非常大的表上时，你可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些

古城微笑少年丶/ 2022年01月06日 13:29/ 0 赞/ 364 阅读

相关 MySQL临时表与内存表

在MySQL中有三种虚拟表：临时表、内存表、视图。下面简单介绍一下临时表和内存表的使用。 1、临时表 MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表在

偏执的太偏执、/ 2021年09月17日 17:08/ 0 赞/ 466 阅读

相关 mysql内存表、临时表

内存表，就是放在内存中的表，所使用内存的大小可通过My.cnf中的max\_heap\_table\_size指定，如max\_heap\_table\_siz

爱被打了一巴掌/ 2021年06月24日 14:00/ 0 赞/ 580 阅读