精华内容
下载资源
问答
  • 什么会产生临时表:一般由于复杂的SQL导致临时表被大量创建临时表分为两种,一种内存临时表,一种磁盘临时表。内存临时表采用的memory存储引擎,磁盘临时表采用的myisam存储引擎(磁盘临时表也可以使用...

    临时表介绍

    什么是临时表: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引擎的临时表

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

    本文标题: MySQL问答系列之什么情况下会用到临时表

    本文地址: http://www.cppcns.com/shujuku/mysql/239565.html

    展开全文
  • 介绍临时表之前,我们首先来看这么一句语句:CREATE TABLE`words` (`id`int(11) NOT NULLAUTO_INCREMENT,`word`varchar(64) DEFAULT NULL,PRIMARY KEY(`id`)) ENGINE=InnoDB;这一个单词表,除了一个主键id之外,...

    介绍临时表之前,我们首先来看这么一句语句:

    CREATE TABLE`words` (

    `id`int(11) NOT NULLAUTO_INCREMENT,

    `word`varchar(64) DEFAULT NULL,PRIMARY KEY(`id`)

    ) ENGINE=InnoDB;

    这是一个单词表,除了一个主键id之外,只有一个word字段,再来看下面这段sql:

    select word from words order by rand() limit 3;

    这段sql的语义其实就是按照随意的规则进行排序,然后取出前三个单词并返回,这段sql看起来很简单,但实际上要做的事还是比较繁琐的:

    (1)创建一个临时表。这个临时表使用的是 memory 引擎,表里有两个字段,第一个字段是 double 类型,为了后面描述方便,记为字段 R,第二个字段是 varchar(64) 类型,记为字段 W。并且,这个表没有建索引。从 words 表中,按主键顺序取出所有的 word 值。

    (2)对于每一个 word 值,调用 rand() 函数生成一个大于 0 小于 1 的随机小数,并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中,到此,扫描行数是 10000。

    (3)现在临时表有 10000 行数据了,接下来你要在这个没有索引的内存临时表(memory引擎的特性之后再说)上,按照字段 R 排序。

    (4)初始化 sort_buffer。sort_buffer 中有两个字段,一个是 double 类型,另一个是整型。从内存临时表中一行一行地取出 R 值和位置信息(我后面会和你解释这里为什么是“位置信息”),分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描,此时扫描行数增加 10000,变成了 20000。

    (5)在 sort_buffer 中根据 R 的值进行排序。注意,这个过程没有涉及到表操作,所以不会增加扫描行数。排序完成后,取出前三个结果的位置信息,依次到内存临时表中取出 word 值(这里还是有涉及到回表的操作,但是是回的内存表,所以没有磁盘io的消耗),返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了 20003。

    当然除了内存临时表之外还有磁盘临时表,磁盘临时表是在内存不足时才会产生,所以尽可能的保证内存临时表的空间要足够大,否则磁盘的操作要比内存的操作要耗时的多!!

    OK,我们再来看一个例子,首先定义一下表结构:

    create table t1(id int primary key, a int, b int, index(a));

    现在我们有这么一个sql:

    explain select b,count(*) as c from t1 group by b

    sql的语义很简单,对b字段进行分组,并且通过聚合函数统计每个组的个数并返回,但是需要注意的是b字段没有建立索引,于是返回的结果里:

    2776c932d727d22a079db7d1c2ee520c.png

    可以看到有使用到临时表,但是后面居然还跟了个using filesort,这是为啥??我们可以先来看一下这句sql的执行流程:

    (1)创建内存临时表,表里有两个字段 b 和 c,主键是 m;扫描表 t1,依次取出b的值;

    (2)如果临时表中没有对应的b的值的行,就插入一个记录 (x,1);如果表中有对应的b的值的行,就将这一行的 c 值加 1;遍历完成后,再根据字段 b 做排序,得到结果集返回给客户端;

    注意到了没,最后有一步是对b做排序,这一步是在内存中的sort_buffer区域完成的,这一步的诞生导致了using filesort,但是我们的sql寓意里并不需要排序啊,那么可以使用order by null使得group by 之后不需要做任何排序,执行完成后你会发现using filesort消失了。。。但是using temporary还是在的,那么我们可以尝试在b字段加索引,继续执行上面的sql,如果b有索引的话,那么b索引树的肯定是这样子的:

    1-2-2-2-5-5-5-5

    是一个有序的链表,mysql在扫描这个索引时,直接轧过去,到2的时候自然知道1的个数只有一个,到5的时候自然知道2的个数只有3个,统计的复杂度就是n,但是如果m没有索引,那么就是无序的,在统计时,扫到2的时候,得去一个中间表看是否存在,存在+1,不存在添加。。。到这里也就清晰了,因为b有了索引,所以这个统计操作,只需要遍历一遍索引即可:

    2b4b8f5dbb5e53c85c80e8c01620aeb2.png

    可以看到临时表没有再使用了,因为有了索引后,b字段不再需要临时表进行辅助统计,结果perfect!

    最后再提一提关于distinct和group by

    展开全文
  • 临时实例用于对rds进行部分数据的回滚,大致流程:创建临时实例(【问题1】这个过程中相当于把生产库中的部分数据拷贝出来吗?答:的),创建成功后需要的时候从临时实例中把需要的数据导回到生产库。 克隆实例...

    摘自:阿里云RDS通过克隆实例恢复到主实例
    阿里云RDS如何通过临时实例恢复到主实例
    数据仓库

    目前RDS支持数据回滚,回滚是针对整个实例的,不支持单表单库。

    临时实例用于对rds进行部分数据的回滚,大致流程是:创建临时实例(【问题1】这个过程中相当于把生产库中的部分数据拷贝出来吗?答:是的),创建成功后需要的时候从临时实例中把需要的数据导回到生产库。

    克隆实例可以按指定的RDS实例批量复制出与原实例一模一样的新实例,复制的内容包括实例数据和实例中可设置的参数(如备份设置、参数设置的参数)。对于需要批量创建相同实例的用户,可以使用克隆实例功能,在一个现有实例上快速复制出多个实例。

    临时实例创建之后 48 小时之内有效,48 小时后自动释放,可以手动删除。同一时间仅可生成一个临时实例。如果要创建新的临时实例,需先删除已存在的临时实例。

    【问题2】有个地方说创建临时实例有7天这个时间限制,不是48小时吗==
    答:不清楚。

    【问题3】据说MySQL类型的数据库已不再支持临时实例,推荐通过克隆实例进行备份并将数据恢复到主实例。是不是就不能单表迁移?
    答:应该是的。

    数据迁移 注意点:
      数据迁移只会将源数据库的数据(结构)复制一份到目标数据库,并不会对源数据库数据(结构)造成影响。
      数据迁移过程中,不支持DDL操作(数据库、表级别的操作),如进行DDL操作可能导致迁移失败。

    【问题4】临时实例不收钱,克隆实例才收钱吗 ?
    答:似乎都要收钱。
    【问题5】迁移前有个同名对象存在性检查,那如果想恢复一个特定表,是不是要先在目的库删除这个表再迁移?
    答:一般不这么做吧。大概会根据临时实例写个脚本直接去库里订正。


    数据仓库和数据集市的关系:
      首先,一个储存企业全部信息的数据仓库被创建,其中,数据均具备有组织的、一致的、不变的格式。数据集市随后被创立,其目的是为不同部门提供他们所需要的那部分信息。数据仓库聚集了所有详细的信息,而数据集市中的数据则是针对用户们的特定需求总结而出的。
      而另外一些专家则认为数据集市的建立并不需要首先建立一个数据仓库。在这个模型中,数据直接由事务型数据库转入数据集市中。一个公司可能建立有多个数据集市,而彼此之间毫无联系。
      这种不在建立数据仓库的基础上创建数据集市的方式会更便宜、更快速,因为它的规模更加易于管理。
      第二种观点的缺陷在于无法实现最初创建数据仓库的最主要的目的——将企业所有的数据统一为一致的格式。现有的事务处理系统的数据往往是不一致、冗余的。如果首先建立起一个全公司范围的数据仓库,组织就能够获得一个统一关于企业的活动和客户的知识库。如果先建立起一个个独立的数据集市,那么数据仓库的诸多优势都能够得以实现,但是企业远远无法做到对数据的一致的储存。


    临时表有两种类型:本地表和全局表
      在与首次创建或引用表时相同的 SQL Server 实例连接期间,本地临时表只对于创建者是可见的。当用户与 SQL Server 实例断开连接后,将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的,当引用该表的所有用户都与 SQL Server 实例断开连接后,将删除全局临时表。
      
      临时表分为SESSION、TRANSACTION两种。
      SESSION级的临时表数据在整个SESSION都存在,直到结束此次SESSION;而TRANSACTION级的临时表数据在TRANACTION结束后消失,即COMMIT/ROLLBACK或结束SESSION都会清除TRANACTION临时表数据。

    展开全文
  • sqlserver中说到临时表就不得不提到永久表1. 临时表与永久表相似,但临时表存储在tempdb中,当不再使用时会自动删除。临时表有两种类型:本地...全局临时表的名称以两个数字符号(##)打头,创建后对任何用户都可见...

    sqlserver中说到临时表就不得不提到永久表

    1. 临时表与永久表相似,但临时表存储在tempdb中,当不再使用时会自动删除。临时表有两种类型:本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头;它们仅对当前的用户连接是可见的;当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头,创建后对任何用户都是可见的,当所有引用该表的用户从 SQL Server 断开连接时被删除。

    2. 临时表就是用户在创建表的时候添加了“#”前缀的表,其特点是根据进程独立。只有进程的拥有者有表的访问权限,其它用户不能访问该表;

    3. 不同的用户进程,创建的临时表虽然“名字”相同,但是这些表之间相互并不存在任何关系;在SQLSERVER中,通过特别的命名机制保证临时表的进程独立性。

    4. 定单和购买意向一般不会保存在真正的“临时表”中,而是实际的普通表,之所以称之为“临时表”,只是一种叫法而已。因为随着一个用户进程的结束,真正的临时表会自动清除,而定单和购买意向数据一般是定时清除,所以一定是保存在普通表中,具备数据的持久性特征(临时表最缺乏的就是数据的持久性)。

    5. 真正的临时表利用了数据库临时表空间,由数据库系统自动进行维护,因此节省了表空间。并且由于临时表空间一般利用虚拟内存,大大减少了硬盘的I/O次数,因此也提高了系统效率。

    展开全文
  • 什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。为什么会产生临时表:一般由于复杂的SQL导致临时表被大量创建 临时表分为两种,一种...
  • 临时表是MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。为什么会产生临时表?一般是由于复杂的SQL导致临时表被大量创建。临时表在MySQL 3.23版本中...
  • MySQL在很多情况下都会用到临时表,总结一下什么时候会用到临时表什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。为什么会产生临时表...
  • MySQL在很多情况下都会用到临时表,总结一下什么时候会用到临时表什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。为什么会产生临时表...
  • 什么是临时表

    2019-06-09 20:11:31
    会话级临时表是指临时表中的数据只在会话生命周期之中存在,当用户退出会话结束的时候,Oracle自动清除临时表中数据。 具体测试方法: insert 之后(事物提交),select 能查询出来,但是这时当你在打开另一个会话窗口的...
  • MySQL在很多情况下都会用到临时表,总结一下什么时候会用到临时表什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。为什么会产生临时表...
  • MySQL 在什么情况下会使用到临时表MySQL在执行SQL查询时可能会用到临时表,一般情况下,用到临时表就意味着性能较低。临时表存储,MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表使用MySQL的MEMORY...
  • 什么会产生临时表:一般由于复杂的SQL导致临时表被大量创建临时表分为两种,一种内存临时表,一种磁盘临时表。内存临时表采用的memory存储引擎,磁盘临时表采用的myisam存储引擎(磁盘临时表也可以使用...
  • CSDN@极客小俊,原创文章, B站技术分享个人博客: cnblogs.com前端html+css+javascript技术讨论交流群: 281499395后端php+mysql+Linux技术交流群: 855256321临时表是MySQL用于存储一些中间结果集的表,临时表只在...
  • MySQL在很多情况下都会用到临时表,总结一下什么时候会用到临时表什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。为什么会产生临时表...
  • MySQL有一些平常会在一些配置里看到,但似乎没有太多的关注的参数或则功能,都默默支持着整个MySQL体系运转,这里所介绍的就是临时表。MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时...
  • 摘要: MySQL在很多情况下都会用到临时表总结一下什么时候会用到临时表 什么是临时表MySQL用于存储一些中间结果集的表临时表只在当前连接可见当关闭连接时Mysql会自动删除表并释放所有空间。MySQL在很多情况下都会...
  • MySQL有一些平常会在一些配置里看到,但似乎没有太多的关注的参数或则功能,都默默支持着整个MySQL体系运转,这里所介绍的就是临时表。MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时...
  • MySQL在以下几种情况会创建临时表:> 1、UNION查询;> 2、用到TEMPTABLE算法或者UNION查询中的视图;> 3、ORDER BY和GROUP BY的子句不一样时;> 4、表连接中,ORDER BY的列不是驱动表中的;> 5、...
  • MySQL有一些平常会在一些配置里看到,但似乎没有太多的关注的参数或则功能,都默默支持着整个MySQL体系运转,这里所介绍的就是临时表。MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时...
  • MySQL有一些平常会在一些配置里看到,但似乎没有太多的关注的参数或则功能,都默默支持着整个MySQL体系运转,这里所介绍的就是临时表。MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时...
  • 临时表是存储在临时表空间里面的,且不记录日志的,NOLOGGING的。1.临时表可以创建临时的索引、视图、触发器2.如果要DROP会话级别临时表,并且其中包含数据时,必须先截断其中的数据。否则会报错。3.临时表不会有DML...
  • oracle中临时表是用来做什么的 某些情况下, 需要 多个非常大的表关联的情况下, 但是需要检索的, 是少量的数据的时候.可以先把 大表的数据, 检索出那一小部分, 然后插入到 临时表中, 最后再关联处理....
  • MySQL会话临时表空间有什么作用发布时间:2020-05-25 11:45:31来源:51CTO阅读:132作者:三月本文主要给大家介绍MySQL会话临时表空间有什么作用,文章内容都笔者用心摘选和编辑的,具有一定的针对性,对大家的...
  • 什么会产生临时表:一般由于复杂的SQL导致临时表被大量创建临时表分为两种,一种内存临时表,一种磁盘临时表。内存临时表采用的memory存储引擎,磁盘临时表采用的myisam存储引擎(磁盘临时表也可以使用...
  • 有没有大佬可以看懂的 ...其次什么是临时表啊)<img alt="" height="485" width="800" src="https://userblink.csdnimg.cn/20210506/weixin_55733315/pic/9ef244f188ad465c4c3bcf95a6aedcca-0.jpg" />

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,922
精华内容 768
关键字:

临时表是什么