精华内容
下载资源
问答
  • MYSQL临时表使用场景

    千次阅读 2019-12-24 22:47:30
    1、union,将两个子查询的结果进行...可以使用临时表去重。记录不在临时表中则插入,记录已存在则取下一条记录。 2、group by,将制定列分组,并统计每组记录个数,如果记录不存在则插入临时表,否则累计计数。 ...

    1、union,将两个子查询的结果进行合并,重复数据只取一条。可以使用临时表去重。记录不在临时表中则插入,记录已存在则取下一条记录。

    2、group by,将制定列分组,并统计每组记录个数,如果记录不存在则插入临时表,否则累计计数。

    展开全文
  • MySql临时表的用法

    2021-04-13 15:07:10
    mysql临时表应用场景,报表sql中的多层嵌套 # mysql临时表应用场景,报表sql中的多层嵌套 # mysql临时表的用法 DROP TABLE IF EXISTS su; CREATE TEMPORARY TABLE su( SELECT e.empno e_empno,e.ename e_ename,e1....

    MySql临时表的用法

    MySql临时表的用法

    mysql临时表应用场景,报表sql中的多层嵌套

    # mysql临时表应用场景,报表sql中的多层嵌套
    # mysql临时表的用法
    DROP TABLE IF EXISTS su;
    CREATE TEMPORARY TABLE su(
        SELECT  e.empno e_empno,e.ename e_ename,e1.empno e1_empno,e1.ename e1_ename from emp e, emp e1  WHERE e.mgr=e1.empno
    );
    SELECT *FROM su;
    # 注意最前面必须加drop table 不然每次执行会报 表或者视图不存在异常
    

    注意最前面必须加drop table 不然每次执行会报 表或者视图不存在异常

    展开全文
  • Mysql 临时表

    2019-01-21 17:28:59
    (本文提到的MySQL临时表均为外部临时表) 临时表 临时表是建立在系统临时文件夹中的表。临时表的数据和表结构都存储在内存之中,退出的时候所占的空间会被释放。 语法 //创建临时表tmp_table CREATE TEMPORARY ...

    Mysql 临时表

    之前对oracle的临时表和内存表有过一篇探索文档,现在对MySQL的临时表进行一些探索。(本文提到的MySQL临时表均为外部临时表

    临时表

    临时表是建立在系统临时文件夹中的表。临时表的数据表结构都存储在内存之中,退出的时候所占的空间会被释放。

    语法

    //创建临时表tmp_table
    CREATE TEMPORARY TABLE tmp_table (
    	NAME VARCHAR (10) NOT NULL,
    	VALUE1 INTEGER NOT NULL
    );
    
    //插入数据
    INSERT INTO tmp_table VALUES ('赵','1');
    
    //查询数据
    select * from tmp_table;
    
    //删除数据
    delete from tmp_table;
    
    //删除表
    drop table tmp_table;
    

    应用场景

    在程序对数据量很大的表有较频繁的读取操作,每次都多表关联进行查询效率不高。这时候可以使用临时表,先查询出需要的子集放入临时表中,在处理中读取临时表的数据进行处理。

    特性

    生命周期

    MySQL的临时表是会话级的。不同的会话中的临时表彼此互不可见,因此可以创建同名的临时表。

    创建临时表,插入并查询数据。

    //创建临时表
    CREATE TEMPORARY TABLE tmp_table (
    	NAME VARCHAR (10) NOT NULL,
    	VALUE1 INTEGER NOT NULL
    )ENGINE=MEMORY;
    //插入数据
    INSERT INTO tmp_table VALUES ('赵','1');
    //当前会话查询数据
    select * from tmp_table;
    

    在这里插入图片描述
    新开一个查询窗口,查询数据,可见新的会话并不能看到这个临时表。

    //新建会话中查询数据库
    select * from tmp_table;
    

    在这里插入图片描述
    在新的会话中建同名的临时表,插入并查询数据,可见互相并不冲突。

    //创建同名的临时表
    CREATE TEMPORARY TABLE tmp_table (
    	NAME VARCHAR (10) NOT NULL,
    	VALUE2 INTEGER NOT NULL
    );
    //插入数据
    INSERT INTO tmp_table VALUES ('钱','2');
    //新的会话中查询数据
    select * from tmp_table;
    

    在这里插入图片描述
    回到原会话中,查询数据

    //原会话查询数据
    select * from tmp_table;
    

    在这里插入图片描述
    在会话分别关闭后,这两个临时表的数据和表结构均会被删除。

    屏蔽同名表

    在会话中,可以创建和已存在的表同名的临时表。创建后在此会话中原表将会被屏蔽,对该表名的所有操作均指向临时表,直到临时表被删除或者会话结束

    创建表,插入并查询数据

    //创建表tmp_table
    CREATE TABLE tmp_table (
    	NAME VARCHAR (10) NOT NULL,
    	VALUE1 INTEGER NOT NULL
    );
    //插入数据
    INSERT INTO tmp_table VALUES ('孙','3');
    //查询数据
    select * from tmp_table;
    

    在这里插入图片描述
    创建同名临时表,插入数据并查询,可见此时插入和查询的对象均是临时表。

    //创建表临时表tmp_table
    CREATE TEMPORARY TABLE tmp_table (
    	NAME VARCHAR (10) NOT NULL,
    	VALUE1 INTEGER NOT NULL
    );
    //插入数据
    INSERT INTO tmp_table VALUES ('李','4');
    //查询数据
    select * from tmp_table;
    

    在这里插入图片描述
    删除数据,删除临时表,再进行查询,可见原表的数据并没有被污染。

    //删除数据
    delete from tmp_table;
    //删除临时表
    drop table tmp_table;
    //查询数据
    select * from tmp_table;
    

    在这里插入图片描述

    临时表不能自连接

    同一个查询语句,只能用一次临时表。

    //自连接查询数据
    SELECT
    	*
    FROM
    	tmp_table t1,
    	tmp_table t2
    WHERE
    	t1. NAME = t2. NAME;
    

    在这里插入图片描述
    自连接会报错:[Err] 1137 - Can’t reopen table: XXX

    临时表‘不可见’

    使用查询表的语句不能查询到临时表。

    //查询表
    show tables;
    show table status;
    

    引擎类型

    临时表的引擎只能是:memory(heap)、myisam、merge、innodb ,不支持mysql cluster(簇)。

    视图和临时表的区别

    1. 视图只是一条预编译的SQL语句,并不保存实际数据;
      临时表是保存在tempdb中的实际的表。
    2. 物理空间的分配不一样
      视图不分配空间;
      临时表会分配空间。
    3. 视图是一个快照,是一个虚表;
      临时表是客观存在的表类型对象。
    展开全文
  • 墨墨导读:MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时数据,以及MySQL内部在执行复杂SQL时,需要借助临时表进行分组、排序、去重等操作。下面将会对MyS...

    墨墨导读:MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时数据,以及MySQL内部在执行复杂SQL时,需要借助临时表进行分组、排序、去重等操作。下面将会对MySQL临时表的一些概念、分类和常见问题进行整理。


    MySQL临时表类型

    1. 外部临时表,通过create temporary table语法创建的临时表,可以指定存储引擎为memory,innodb, myisam等等,这类表在会话结束后,会被自动清理。如果临时表与非临时表同时存在,那么非临时表不可见。show tables命令不显示临时表信息。
    可通过information_schema.INNODB_TEMP_TABLE_INFO系统表可以查看外部临时表的相关信息,这部分使用的还是比较少。


    2. 内部临时表,通常在执行复杂SQL,比如group by, order by, distinct, union等,执行计划中如果包含Using temporary,还有undo回滚的时候,但空间不足的时候,MySQL内部将使用自动生成的临时表,以辅助完成工作。

    MySQL临时表相关参数

    1. max_heap_table_size:用户创建的内存表的最大值,也用于和tmp_table_size一起,限制内部临时表在内存中的大小。
    2. tmp_table_size:内部临时表在内存中的的最大值,与max_heap_table_size参数共同决定,取二者的最小值。如果临时表超过该值,就会从内存转移到磁盘上。
    3. innodb_tmpdir:online ALTER TABLE operations that rebuild the table max_tmp_tables

    4. default_tmp_storage_engine:外部临时表(create temporary table创建的表)默认的存储引擎。

    5. innodb_temp_data_file_path:innodb引擎下temp文件属性。建议限制innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:30G

    6. Internal_tmp_disk_storage_engine:磁盘上的内部临时表存储引擎,可选值为myisam或者innodb。使用innodb表在某些场景下,比如临时表列太多,或者行大小超过限制,可能会出现“ Row size too large or Too many columns”的错误,这时应该将临时表的innodb引擎改回myisam。tmpdir:临时表目录,当临时表大小超过一定阈值,就会从内存转移到磁盘上。
    7. tmpdir变量表示磁盘上临时表所在的目录。

    MySQL临时表相关状态变量

    1. Created_tmp_disk_tables:执行SQL语句时,MySQL在磁盘上创建的内部临时表数量,如果这个值很大,可能原因是分配给临时表的最大内存值较小,或者SQL中有大量排序、分组、去重等操作,SQL需要优化。

    2. Created_tmp_files:创建的临时表数量

    3. Created_tmp_tables:执行SQL语句时,MySQL创建的内部临时表数量。

    4. Slave_open_temp_tables statement 或则 mix模式下才会看到有使用。
    slave_open_temp_tables 的值显示,通过复制,当前slave创建了多少临时表,binlog_format只能是statement 和 mixed 下有效。

    备注:stop slave 也没有用,必须主库手动删除 或则 session退出 才可以。

    以下是从库binlog记录信息:

    MySQL临时表注意事项

    1. MySQL临时表可能导致磁盘可用空间减少:

    在MySQL5.7版本之前,临时表的存储引擎默认为myisam,myisam临时表在SQL执行结束后,会自动删除临时表。然而从5.7版本开始,临时表的默认存储引擎变为innodb,虽然在性能上有了一定的提升,但是由于innodb引擎的临时表共用表空间ibtmp1,导致在高并发下,多个session同时创建临时表时,该表空间会变得非常大,并且不能动态缩小,除非重启MySQL,否则无法释放。


    可以为临时表空间设置一个最大值,比如10G,如下:

    innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
    

    当临时表空间达到最大值10G时,SQL执行将会报错,影响应用的正常执行。
    对于临时表空间过大的问题,通常也有一些其他方法解决,比如:
    将临时表的存储引擎设置为myisam,虽然可能有一些性能问题,但不会导致磁盘空间问题。

    2. SQL语句:


    (1)加上合适的索引
    (2)在where条件中过滤更多的数据
    (3)重写SQL,优化执行计划
    (4)如果不得不使用临时表,那么一定要减少并发。建议使用SSD硬盘。

    3. undo相关

    1)使用innodb_rollback_segments配置选项定义回滚segment的数量,默认设置是128,也是最大值。一个回滚segment总是分配给系统表空间,32个回滚segment预留给临时表空间(ibtmp1)。因此,要分配回滚段来撤消表空间,将innodb_rollback_segments设置为大于33的值。配置单独的undo表空间时,system表空间中的回滚段将呈现为非活动状态。

    就是说超过128回滚segement的时候,就需要临时表出来救急。

    tablespace -> segment -> extent(64个page,1M) -> page(16kb)
    

    2)truncate undo
    当innodb_undo_log_truncate触发的时候,undo表空间截断操作在服务器日志目录中创建一个临时的undo_space_number_trunc.log文件,该日志目录由innodb_log_group_home_dir定义。如果在truncate操作期间发生系统故障,临时日志文件允许启动进程识别被截断的undo表空间,并继续操作。

    4. binlog 缓存相关
    使用二进制日志缓存并且值达到了binlog_cache_size设置的值,用临时文件存储来自事务的变化这样的事务数量。可通过Binlog_stmt_cache_disk_use状态变量中单独跟踪。

    总结

    1. 从上述了解来看MySQL临时表,平时的监控和优化是避免不了的。
    2. 除此之外,在业务实现中也可以适当的使用,如作为中间表临时保存少量信息 等。
    3. 在复制环节中binlog_format等于ROW模式时,临时表相关是不记录binlog日志的(除了drop命令),这部分需要注意。

    墨天轮原文链接:https://www.modb.pro/db/27858(复制到浏览器中打开或者点击“阅读原文”)

    推荐阅读:144页!分享珍藏已久的数据库技术年刊


    视频号,新的分享时代,关注我们,看看有什么新发现?

    数据和云

    ID:OraNews

    如有收获,请划至底部,点击“在看”,谢谢!

    点击下图查看更多 ↓

    云和恩墨大讲堂 | 一个分享交流的地方

    长按,识别二维码,加入万人交流社群

    请备注:云和恩墨大讲堂

      点个“在看”

    你的喜欢会被看到❤

    展开全文
  • MySQL临时表

    千次阅读 2016-11-23 15:53:41
    1.临时表存储MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表默认使用MySQL的MEMORY存储引擎, default_tmp_storage_engine 可以控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型,默认是MEMORY...
  • mysql临时表

    2017-11-16 09:03:29
    临时表与永久表相似,但临时表存储在tempdb中,当不再使用时会自动删除。什么时候使用临时表? 在SQL Server的性能调优中,如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集,表变量和临时表是两种选择...
  • MySQL在处理请求的某些场景中,服务器创建内部临时表。即表以MEMORY引擎在内存中处理,或以MyISAM引擎储存在磁盘上处理.如果表过大,服务器可能会把内存中的临时表转存在磁盘上。
  • MYSQL 临时表用法总结

    2020-03-13 17:57:07
    老项目最近被吐槽性能不行,经排查发现mysql占用cpu过高,梳理流程发现一些经常被使用的字段需要多张表关联之后可获取,于是想到了临时表; 2.场景 将正常的CREATE TABLE语句改为CREATE TEMPORARY TABLE即可; 临时...
  • MySQL临时表详细解释

    2020-06-04 17:08:44
    MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时数据,以及MySQL内部在执行复杂SQL时,需要借助临时表进行分组、排序、去重等操作。临时表的作用仅限于本次会话,等连接关闭后重新打开连接...
  • 内存表的表结构是存储到数据库上的,数据放在内存中,网上推荐使用内存表,但我觉得我的应用场景更合适临时表。 我的需求: 一个表中存储了孩子、双亲的vcf数据(测序数据,每一个样本的单体数据百万行),我需要将...
  • MySQL临时表的作用

    2021-06-02 20:34:27
    MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时数据,以及MySQL内部在执行复杂SQL时,需要借助临时表进行分组、排序、去重等操作。下面将会对MySQL临时表的一些概念、分类和常见问题进行...
  • MySQL 临时表、内存表的使用 一、临时表临时表的创建语法:create temporary table 1.临时表有哪些特点? 一个临时表只能被创建它的session访问,对其他线程不可见。 SessionA创建临时表t,SessionB是看不见的...
  • 如果你使用Java的MySQL客户端程序连接MySQL数据库服务器来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,当然也可手动销毁。 实例 建表 插入数据 查询 当使用 SHOW TABLES显示数据表列表时,...
  • Mysql临时表

    2016-08-14 17:43:01
    当你创建临时表的时候,你可以使用temporary关键字。如:  create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)‘ 或  CREATE TEMPORARY TABLE IF NOT EXISTS sp_output_...
  •  今天分享的内容是MySQL中的临时表,对于临时表,之前我其实没有过多的研究,只是知道MySQL在某些特定场景下会使用临时表来辅助进行group by等一些列操作,今天就来认识下临时表吧。 1、首先。临时表是session级别...
  • mysql临时表的产生

    2016-12-21 16:10:00
    MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表使用MySQL的MEMORY存储引擎,磁盘临时表使用MySQL的MyISAM存储引擎; 一般情况下,MySQL会先创建内存临时表,但内存临时表超过配置指定的值后,MySQL...
  • mysql 临时表和视图

    千次阅读 2017-04-26 10:50:27
    会话结束临时表自动释放应用场景: 视图 应用场景1:保密工作,比如有一个员工工资表,如果你只希望财务看到员工工资这个字段,而其他人不能看到工资字段,那就用一个视图,把工资这个敏感字段过滤
  • 目录临时表外部临时表内部临时表 临时表 顾名思义,临时表就是临时用来存储...一般情况下,临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。 外部临时表
  • Mysql临时表和分区表

    2019-07-25 09:42:00
    临时表与内存表 内存表,指的是使用Memory引擎的表,建表语法是create table … engine=memory。这种 表的数据都保存在...如果是使用InnoDB引擎或者MyISAM引擎的临时表,写 数据的时候是写到磁盘上的。当然,临时...
  • mysql 内存表和临时表学习

    千次阅读 2018-02-27 22:15:37
    临时表 内存表 视图 临时表 简介: 临时表是建立在系统临时文件夹中的表。临时表的数据和表结构都存储在内存之中,退出的时候所占的空间会被释放 创建临时表 create temporary table tmp_table( name ...
  • 主要使用场景是:一个 group by 语句中需要放到临时表上的数据量特别大,却还是要按照“先放到内存临时表,数据不够放再转成磁盘临时表”。内部原理:1)初始化 sort_buffer,确定放入一个整型字段,记为 m;2)扫描...
  • mysql各类临时表

    2020-07-22 17:39:18
    内存临时表有memory引擎和temptable引擎,memory引擎是从mysql5.6开始使用,temptable引擎是8.0引入的新的引擎。 memory引擎不管实际字符多少,都是用定长的空间存储的。 temptable引擎会使用变长的空间存储,提高了...
  • mysql 临时表 效率问题改进

    千次阅读 2016-05-08 17:20:57
    近日,线上mysql查出一个慢sql,每次都要查询1000ms以上,严重影响用户体验 今得空去诊断一番,记录如下: sql原句: SELECT r.object_id AS cardId, count(1) AS attachs FROM hzresource_object r LEFT ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 34,515
精华内容 13,806
关键字:

mysql临时表应用场景

mysql 订阅