-
2021-01-21 11:48:45
MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加,如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
MySQL临时表只在当前连接可见,如果你使用PHP脚本来创建MySQL临时表,那没当PHP脚本执行完成后,该临时表也会自动销毁。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,当然你也可以手动销毁。
实例
以下展示了使用MySQL 临时表的简单实例,以下的SQL代码可以适用于PHP脚本的mysql_query()函数。
mysql>CREATE TEMPORARY TABLESalesSummary (
->product_name VARCHAR(50)NOT NULL-> ,total_sales DECIMAL(12,2)NOT NULL DEFAULT0.00
-> ,avg_unit_price DECIMAL(7,2)NOT NULL DEFAULT0.00
-> ,total_units_sold INT UNSIGNED NOT NULL DEFAULT0
);
QueryOK, 0rows affected(0.00sec)mysql>INSERT INTOSalesSummary
-> (product_name,total_sales,avg_unit_price,total_units_sold)
->VALUES-> ('cucumber', 100.25, 90, 2);mysql>SELECT*FROMSalesSummary;
+--------------+-------------+----------------+------------------+
|product_name|total_sales|avg_unit_price|total_units_sold|
+--------------+-------------+----------------+------------------+
|cucumber| 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1rowin set (0.00sec)
当你使用 SHOW TABLES命令显示数据表列表时,你将无法看到 SalesSummary表。
如果你退出当前MySQL会话,再使用 SELECT命令来读取原先创建的临时表数据,那你会发现数据库中没有该表的存在,因为在你退出时该临时表已经被销毁了。
删除MySQL 临时表
默认情况下,当你断开与数据库的连接后,临时表就会自动被销毁。当然你也可以在当前MySQL会话使用DROP TABLE命令来手动删除临时表。
以下是手动删除临时表的实例:
mysql>CREATE TEMPORARY TABLESalesSummary (
->product_name VARCHAR(50)NOT NULL-> ,total_sales DECIMAL(12,2)NOT NULL DEFAULT0.00
-> ,avg_unit_price DECIMAL(7,2)NOT NULL DEFAULT0.00
-> ,total_units_sold INT UNSIGNED NOT NULL DEFAULT0
);
QueryOK, 0rows affected(0.00sec)mysql>INSERT INTOSalesSummary
-> (product_name,total_sales,avg_unit_price,total_units_sold)
->VALUES-> ('cucumber', 100.25, 90, 2);mysql>SELECT*FROMSalesSummary;
+--------------+-------------+----------------+------------------+
|product_name|total_sales|avg_unit_price|total_units_sold|
+--------------+-------------+----------------+------------------+
|cucumber| 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1rowin set (0.00sec)mysql>DROP TABLESalesSummary;mysql>SELECT*FROMSalesSummary;ERROR1146: Table 'CDADATA.SalesSummary'doesn't exist
喜欢 (1)or分享 (0)
更多相关内容 -
对比Oracle临时表和SQL Server临时表的不同点
2020-12-15 20:33:16Oracle数据库创建临时表的过程以及和SQL Server临时表的不同点的对比的相关知识是本文我们主要要介绍的内容,接下来就让我们一起来了解一下这部分内容吧,希望能够对您有所帮助。 1.简介 Oracle数据库除了可以保存... -
MYSQL 临时表用法总结
2020-12-14 17:27:16MYSQL 临时表用法总结 1.背景 老项目最近被吐槽性能不行,经排查发现mysql占用cpu过高,梳理流程发现一些经常被使用的字段需要多张表关联之后可获取,于是想到了临时表; 2.临时表用法总结 将正常的CREATE TABLE语句... -
Mysql临时表及分区表区别详解
2020-12-14 06:13:44临时表与内存表 内存表,指的是使用Memory引擎的表,建表语法是create table … engine=memory。这种 表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在。除了这两个特性看 上去比较“奇怪”外,从... -
MySQL中的两种临时表
2020-12-14 21:18:52这种临时表的命名与非临时表可以同名(同名后非临时表将对当前会话不可见,直到临时表被删除)。 内部临时表 内部临时表是一种特殊轻量级的临时表,用来进行性能优化。这种临时表会被MySQL自动创建并用来存储... -
MySQL两种临时表的用法详解
2020-12-16 00:40:43这种临时表的命名与非临时表可以同名(同名后非临时表将对当前会话不可见,直到临时表被删除)。 内部临时表 内部临时表是一种特殊轻量级的临时表,用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些... -
sqlserver 临时表的用法
2020-12-15 05:40:38用法: 用于复杂查询时可以用临时表来暂存相关记录,能够提高效率、提高程序的可读性,类似于游标中的 my_cursor declare my_cursor cursor scroll for select 字段 from tablename 临时表分为:用户临时表和系统... -
浅谈MySQL临时表与派生表
2020-12-16 12:03:55对于自动创建的临时表,由于内存临时表的性能更为优越,mysql总是首先使用内存临时表,而当内存临时表变得太大时,达到某个阈值的时候,内存临时表就转存为外存临时表。也就是说,外存临时表是内存临时表在存储空间... -
MySQL中临时表的基本创建与使用教程
2020-12-15 02:52:54当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后在这些表运行查询。... -
sql server 临时表 查找并删除的实现代码
2020-12-15 12:37:33if exists(select * from tempdb..sysobjects where id=object_id(‘tempdb..#temp’)) drop table #temp临时表 可以创建本地和全局临时表。本地临时表仅在当前会话中可见;全局临时表在所有会话中都可见。 本地临时... -
Oracle 临时表之临时表的应用问题
2020-12-14 22:41:20临时表本质上是一种cache的表现形式,Oracle的临时表都是事先建好的,一旦用了临时表,存放的是和本会话相关的数据,没有人会傻乎乎地用临时表来保存本应该共享的数据。 with子查询实际上也是用了临时表,... -
Oracle释放临时表空间脚本
2020-12-23 15:50:55Oracle释放临时表空间脚本 -
SQL Server 向临时表插入数据示例
2020-09-10 14:56:28SQL Server 向临时表插入数据,用临时表和表变量代替游标会极大的提高性能,下面有个示例,大家可以参考下 -
sql server 创建临时表的使用说明
2020-09-10 06:23:57主要介绍了sql server 创建临时表的使用说明,需要的朋友可以参考下 -
oracle临时表(事务级、会话级).docx
2021-04-02 11:18:50oracle临时表(事务级、会话级).docx -
深度解析MySQL 5.7之临时表空间
2021-01-21 15:01:45临时表 临时表顾名思义,就是临时的,用完销毁掉的表。 数据既可以保存在临时的文件系统上,也可以保存在固定的磁盘文件系统上。 临时表有下面几种: 1、全局临时表 这种临时表从数据库实例启动后开始生效,在... -
postgresql临时表
2018-08-03 10:28:33postgresql临时表postgresql临时表postgresql临时表postgresql临时表 -
SQL Server+MySQL数据库 临时表 4.6SQL的临时表.pptx
2020-09-08 08:36:271临时表以#开头而临时表又分为局部临时表与全局临时表一个#即为局部临时表两个#即为全局临时表 2局部临时表只对当前连接有效当前连接断开时自动删除? ? ?全局临时表对其它连接也有效在当前连接和其他访问过它的连接... -
Oracle 临时表用法汇总
2015-01-09 09:57:02临时表的应用 1)、当某一个SQL语句关联的表在2张及以上,并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。 2)、程序执行过程中可能需要存放一些临时的数据,这些数据在整个... -
MySQL 临时表
2020-12-16 16:20:16MySQL 临时表 MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。 临时表在MySQL 3.23版本中添加,如果你的MySQL版本低于 3.23版本... -
oracle查找定位占用临时表空间较大的SQL语句方法
2016-07-23 18:28:07oracle查找定位占用临时表空间较大的SQL语句方法,包括:(1)造成临时表空间暴涨的SQL还在运行中(2)造成临时表空间暴涨的SQL已经运行过了。 -
MySQL 中的临时表
2021-01-28 06:55:24在使用 explain解析一个 sql时,有时我们会发现在 extra列上显示 using temporary ,这表示这条语句用到了临时表,那么临时表究竟是什么?它又会对 sql的性能产生什么影响?又会在哪些场景中出现?本文根据 <>...在使用 explain 解析一个 sql 时,有时我们会发现在 extra 列上显示 using temporary ,这表示这条语句用到了临时表,那么临时表究竟是什么?它又会对 sql 的性能产生什么影响?又会在哪些场景中出现?本文根据 <> 学习整理。
出现场景
其实临时表在之前的博客就已经出现过了,在 MySQL 中的排序 一文中就说到如果 order by 的列上没有索引,或者说没有用到索引,那么就需要进行额外排序(using filesort),而额外排序优先在一块 sort_buffer 空间中进行,如果这块空间大小小于要加载的字段总长度,那么就会用到临时文件辅助排序,这个临时文件就是临时表。临时表的作用就是作为中间表优化操作,比如 group by 作为分组的中间表, order by rand() (MySQL 中的排序 中的例子)作为中间表帮助运算等。
特点
1、建表语法是 create temporary table …。
2、一个临时表只能被创建它的 session 访问,对其他线程不可见,在会话结束后自动删除。所以,图中 session A 创建的临时表 t,对于 session B 就是不可见的。(所以特别适合用于join 优化)
3、临时表可以与普通表同名。
4、session A 内有同名的临时表和普通表的时候,show create 语句,以及增删改查语句访问的是临时表。
5、show tables 命令不显示临时表。
种类
临时表分为磁盘临时表和内存临时表。磁盘临时表指的是存储在磁盘上的临时表,因为在磁盘上,所以执行效率比较低,优点结构可以是有序的,实现可以是 InnoDB(默认),MyISAM 引擎;内存临时表就是存储在内存中,执行效率高,常用的实现引擎是 Memory。
磁盘临时表和内存临时表的区别
1、相比于 InnoDB 表,使用内存表不需要写磁盘,往表 temp_t 的写数据的速度更快;
2、索引 b 使用 hash 索引,查找的速度比 B-Tree 索引快;
3、临时表数据只有 2000 行,占用的内存有限。
Memory 引擎
与 InnoDB 的区别
1、InnoDB 表的数据总是有序存放的,而内存表的数据就是按照写入顺序存放的;关于这点可以通过创建 b+ 索引来进行排序,优化查询。alter table t1 add index a_btree_index using btree (id);
2、当数据文件有空洞的时候,InnoDB 表在插入新数据的时候,为了保证数据有序性,只能在固定的位置写入新值,而内存表找到空位就可以插入新值;
3、数据位置发生变化的时候,InnoDB 表只需要修改主键索引,而内存表需要修改所有索引;
4、InnoDB 表用主键索引查询时需要走一次索引查找,用普通索引查询的时候,需要走两次索引查找。而内存表没有这个区别,所有索引的“地位”都是相同的。
5、InnoDB 支持变长数据类型,不同记录的长度可能不同;内存表不支持 Blob 和 Text 字段,并且即使定义了 varchar(N),实际也当作 char(N),也就是固定长度字符串来存储,因此内存表的每行数据长度相同。
6、内存表支持 hash 索引,并且数据存储在内存中,所以执行比数据存储在磁盘上的 Innodb 快。
缺点
1、锁粒度大,只支持表级锁,并发度低。
2、数据持久性差。因为是内存结构,所以在重启后数据会丢失 。由此会导致备库在硬件升级后数据就会丢失,并且如果主从库互为 "主备关系" ,备库在关闭后还会将删除数据记录进 binlog,重启后主机会执行备库发送过来的 binlog ,导致主库数据也会丢失。
虽然 Memory 引擎看起来缺点很多,但是因为其存储在内存中,并且关机后会自动清除数据,所以其是作为临时表的一个绝佳选择。
常见的应用场景
分库分表查询
将一个大表 ht,按照字段 f,拆分成 1024 个分表,然后分布到 32 个数据库实例上(水平分表)。一般情况下,这种分库分表系统都有一个中间层 proxy。不过,也有一些方案会让客户端直接连接数据库,也就是没有 proxy 这一层。假设分区键是 列 f 。
1、如果只使用分区键作为查询条件如 select v from ht where f=N,那么直接通过分表规则找到 N 所在的表,然后去该表上查询就可以了。
2、如果使用其他字段作为条件且需要排序如 select v from ht where k >= M order by t_modified desc limit 100,那么非但不能确定要查询的记录在哪张表上,而且因为默认使用的是分区键排序,所以得到的结果还是无序的,需要额外排序。
1)在 proxy 层完成排序。优势是速度快,缺点是开发工作量比较大,如果涉及复杂的操作如 group by,甚至 join 这样的操作,对中间层的开发能力要求比较高。并且还容易出现内存不够、CPU 瓶颈的问题。
2)将各个分区的查询结果(未排序)总结到一张临时表上进行排序。
Ⅰ、在汇总库上创建一个临时表 temp_ht,表里包含三个字段 v、k、t_modified;
Ⅱ、在各个分库上执行 select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;
Ⅲ、把分库执行的结果插入到 temp_ht 表中;
Ⅳ、执行 select v from temp_ht order by t_modified desc limit 100;
union 作为中间表
有表t1: create table t1(id int primary key, a int, b int, index(a)); 有记录(1,1,1) 到 (1000,1000,1000) 执行 (select 1000 as f) union (select id from t1 order by id desc limit 2);
解析这条 sql:
可以知道:
1、左边语句没有进行查表操作 2、右边语句使用了 id 索引 3、联合时使用了临时表
具体过程:
1、创建一个内存临时表,这个临时表只有一个整型字段 f,并且 f 是主键字段。
2、执行第一个子查询,得到 1000 这个值,并存入临时表中。
3、执行第二个子查询:
1)拿到第一行 id=1000,试图插入临时表中。但由于 1000 这个值已经存在于临时表了,违反了唯一性约束,所以插入失败,然后继续执行;
2)取到第二行 id=999,插入临时表成功。
4、从临时表中按行取出数据,返回结果,并删除临时表,结果中包含两行数据分别是 1000 和 999。
排序返回的字段过大
举一个在 MySQL中的排序 中提到过的例子。
select word from words order by rand() limit 3; 表数据有10000行 SQL是从10000行记录中随机获取3条记录返回。
这个执行过程因为涉及到 rand() 且数据量比较大,所以单靠 sort_buffer 排序空间不够,所以还用到临时表。
过程:
1、从缓冲池依次读取记录,每次读取后都调用 rand() 函数生成一个 0-1 的数存入内存临时表,W 是 word 值,R 是 rand() 生成的随机数。到这扫描了 10000 行。
2、初始化 sort_buffer,从内存临时表中将 rowid(这张表自动生成的) 以及 排序数据 R 存入 sort_buffer。到这因为要遍历内存临时表所以又扫描了 10000 行。
3、在 sort_buffer 中根据 R 排好序,然后选择前三个记录的 rowid 逐条去内存临时表中查到 word 值返回。到这因为取了三个数据去内存临时表去查找所以又扫描了 3 行。总共 20003 行。
group by 作为中间表
执行:select id%10 as m, count(*) as c from t1 group by m;
首先解析 SQL:
可以看到使用了临时表和额外排序,接下来来解析
执行过程:
1、创建内存临时表,表里有两个字段 m 和 c,主键是 m;
2、扫描表 t1 的索引 a,依次取出叶子节点上的 id 值,计算 id%10 的结果,记为 x;
1)如果临时表中没有主键为 x 的行,就插入一个记录 (x,1);
2)如果表中有主键为 x 的行,就将 x 这一行的 c 值加 1;
遍历完成后,再根据字段 m 做排序,得到结果集返回给客户端。
排序的过程就按照排序规则进行,用到 sort_buffer ,可能用到临时表。
优化 BNL 排序
表结构:
CREATE TABLE`t2` (
`id`int(11) NOT NULL,
`a`int(11) DEFAULT NULL,
`b`int(11) DEFAULT NULL,PRIMARY KEY(`id`),KEY`a` (`a`)
) ENGINE=InnoDB;
t1、t2 结构相等,t2 100万条数据,t1 1000行数据,t1 的数据在 t2 上都有对应,相等。执行语句:select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;
分析:因为字段b 没有创建索引,所以排序是属于 BNL 排序,再加上数据量比较大,所以在比较时扫描的总行数就等于 100万*1000,也就是10亿次。
具体过程:
1、把表 t1 的所有字段取出来,存入 join_buffer 中。这个表只有 1000 行,join_buffer_size 默认值是 256k,可以完全存入。
2、扫描表 t2,取出每一行数据跟 join_buffer 中的数据进行对比,
1)如果不满足 t1.b=t2.b,则跳过;
2)如果满足 t1.b=t2.b, 再判断其他条件,也就是是否满足 t2.b 处于[1,2000]的条件,如果是,就作为结果集的一部分返回,否则跳过。
优化:
如果筛选字段用的比较多,那么可以为其创建索引,使 BNL 优化成 NLJ,但是如果这个字段使用的不多,那么为其创建索引反倒会因为多了不必要的维护成本而降低总体的性能。所以。针对于使用率不高的 BNL 筛选字段的优化,可以创建一个临时表,让这个临时表作为一个索引表,来优化成 NLJ,同时因为临时表在会话结束后会自动删除,省去了维护成本。
create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;insert into temp_t select * from t2 where b>=1 and b<=2000;select * from t1 join temp_t on (t1.b=temp_t.b);
这样执行过程就变成:
1、执行 insert 语句构造 temp_t 表并插入数据的过程中,对表 t2 做了全表扫描,这里扫描行数是 100 万。
2、之后的 join 语句,扫描表 t1,这里的扫描行数是 1000;join 比较过程中,做了 1000 次带索引的查询(因为t1 1000行,作为驱动表,t2作为被驱动表)。相比于优化前的 join 语句需要做 10 亿次条件判断来说,这个优化效果还是很明显的。
为什么临时表可以重名
可以看到在 sessionA 在已经创建了一个名为 t1 的临时表,并且 sessionA 未结束前,sessionB 也创建了一个名为 t1 的临时表,没有发生异常。这是为什么?
首先要知道在 MySQL 启动后每张表都会加载到内存中,所以每张表都分为内存表和磁盘表。
1、对于磁盘表:
1)普通表的表结构和数据文件都是存储在库名文件夹下的,文件名就是表名。
2)结构文件存储在临时文件夹下,文件的后缀是 frm,前缀是 "#sql{进程 id}_{线程id}_序列号";
数据文件在 5.6 及之前是存储在临时文件夹下的,5.7 开始存放在专门存放临时文件数据的临时表空间。
2、对于内存表:
1)普通表的命名是 "库名 + 表名"。
2)临时表的命名则在 " 库名 + 表名 " 的基础上,加入了 " server_id + thread_id "。比如:
session A 的临时表 t1,在备库的 table_def_key 就是:库名 +t1+“M 的 serverid”+“session A 的 thread_id”;
session B 的临时表 t1,在备库的 table_def_key 就是 :库名 +t1+“M 的 serverid”+“session B 的 thread_id”。
综上所述,因为临时表在磁盘和内存中表的命名都取自具体的进程id、线程id、所以可以实现不同的会话创建相同的表名。
如果 binlog 的格式是 row,那么是不会记录临时表的各个操作的,因为临时表就是用于辅助各自操作的,所以在 row 格式下直接记录的是经过临时表得出的具体要操作的数据。
总结
临时表是一种非常方便的结构么,因为其会随着会话结束而自动删除,所以在一些查询效率较低但筛选字段使用很少的场景,就可以通过创建临时表,然后在临时表上创建索引来提高查询效率,同时也避免了索引的后续维护,而在其他复杂操作中,临时表也可以充当中间表的作用。所以临时表广泛出现在查询(多表联查)、分组、排序(排序返回的字段总长度过大)等场景中。
总结:
1、如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就需要额外的内存,来保存中间结果;
2、join_buffer 是无序数组,sort_buffer 是有序数组,内存临时表是二维表结构,无序;磁盘临时表默认是B+结构,可以是数组,有序。
3、如果执行逻辑需要用到二维表特性,就会优先考虑使用临时表。比如我们的例子中,union 需要用到唯一索引约束, group by 还需要用到另外一个字段来存累积计数。
-
SQL中的临时表
2021-08-25 01:03:43点击关注上方“SQL数据库开发”,设为“置顶或星标”,第一时间送达干货SQL专栏SQL基础知识第二版SQL高级知识第二版临时表定义临时表与实体表类似,只是在使用过程中,临时表是存储在系统数...点击关注上方“SQL数据库开发”,
设为“置顶或星标”,第一时间送达干货
SQL专栏
临时表定义
临时表与实体表类似,只是在使用过程中,临时表是存储在系统数据库tempdb中。当我们不再使用临时表的时候,临时表会自动删除。
临时表分类
临时表分为本地临时表和全局临时表,它们在名称、可见性以及可用性上有区别。
临时表的特性
对于临时表有如下几个特点:
本地临时表就是用户在创建表的时候添加了"#"前缀的表,其特点是根据数据库连接独立。只有创建本地临时表的数据库连接有表的访问权限,其它连接不能访问该表;
不同的数据库连接中,创建的本地临时表虽然"名字"相同,但是这些表之间相互并不存在任何关系;在SQLSERVER中,通过特别的命名机制保证本地临时表在数据库连接上的独立性,意思是你可以在不同的连接里使用相同的本地临时表名称。
全局临时表是用户在创建表的时候添加"##"前缀的表,其特点是所以数据库连接均可使用该全局临时表,当所有引用该临时表的数据库连接断开后自动删除。
全局临时表相比本地临时表,命名上就需要注意了,与本地临时表不同的是,全局临时表名不能重复。
临时表利用了数据库临时表空间,由数据库系统自动进行维护,因此节省了物理表空间。并且由于临时表空间一般利用虚拟内存,大大减少了硬盘的I/O次数,因此也提高了系统效率。
临时表在事务完毕或会话完毕数据库会自动清空,不必记得用完后删除数据。
本地临时表
本地临时表的名称以单个数字符号"#" 打头;它们仅对当前的用户连接(也就是创建本地临时表的connection)是可见的;当用户从 SQL Server 实例断开连接时被删除。
本地临时表实例
我们以Customers表为实例,表数据如下:
我们新建一个连接,每当“新建查询”就代表打开了一个连接,连接的ID就是sa后面的数字,我们的这个连接ID是57.
下面我们在这个查询页面建立一个临时表。
SELECT * INTO #Customers FROM Customers
这样我们就建好了一个临时表,可以查询一下临时表#Customers的数据。与Customers内容一致。
SELECT * FROM #Customers
如果我们再打开一个页面,同样查询#Customers表会怎么样呢?
我们在新开的查询页面执行上述查询语句,得到的结果如下:
说明本地临时表不支持跨连接查询。只能在当前连接(或者当前查询页面)访问。
那本地临时表具体在什么地方呢?它又是怎么存放的呢?
这就是我们刚才建立的临时表,在系统中并不是用#Cusomters表示的。
全局临时表
全局临时表的名称以两个数字符号 "##"打头,创建后对任何数据库连接都是可见的,当所有引用该表的数据库连接从 SQL Server 断开时被删除。
全局临时表实例
我们还是按照上面的步骤走一遍
先打开一个查询页面,输入如下查询语句:
SELECT * INTO ##Customers FROM Customers
执行完上面的查询语句后,我们关掉查询页面,再重新开一个页面查询##Customers中的内容
SELECT * FROM ##Customers
结果如下:
此时并不会像本地临时表那样报错了。
全局临时表的位置如下:
它的名称与我们自定义的名称一致,系统不会额外添加其他信息。
临时表的用途
介绍完临时表,我们来说说如何用它来进行优化
临时表的优化一般使用在子查询较多的情况下,也称为嵌套查询。我们写如下子查询:
SELECT * FROM sales.Temp_Salesorder WHERE SalesOrderDetailID IN (SELECT SalesOrderDetailID FROM sales.SalesOrderDetail WHERE UnitPrice IN (SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0) )
(提示:代码可以左右滑动)
这是一个比较简单的两层嵌套子查询,我们看一下执行情况:
可以看到这里的逻辑读取是比较高的。
我们用临时表重新来看下执行情况如何,我们将第一二层的查询结果插入到#temp中,然后从临时表中查询结果。
注:上图的统计信息需开启SSMS相关功能,具体如下:
工具—选项—查询执行—高级
SELECT SalesOrderDetailID INTO #temp FROM sales.SalesOrderDetail WHERE UnitPrice IN (SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0) SELECT * FROM sales.Temp_Salesorder WHERE SalesOrderDetailID IN (SELECT SalesOrderDetailID FROM #temp)
执行情况如下:
相比上一次的逻辑读,成倍的减少了逻辑读取次数。在对查询的性能进行调节时,如果逻辑读值下降,就表明查询使用的服务器资源减少,查询的性能有所提高。如果逻辑读值增加,则表示调节措施降低了查询的性能。在其他条件不变的情况下,一个查询使用的逻辑读越少,其效率就越高,查询的速度就越快。
因此我们可以看出临时表在比较复杂的嵌套查询中是可以提高查询效率的。
总结
临时表不管是在SQL Server还是其他平台都有使用,其在查询优化方面可以极大的提高查询效率,而SQL Server平台的临时表相比其他平台更容易创建和使用,其优越性不言而喻。所以如果平时工作或学习过程中,临时表可以作为一个必备技能经常使用。
最后给大家分享我写的SQL两件套:《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等,非常通俗易懂,方便大家跟着一起来实操。
有需要的读者可以下载学习,在下面的公众号「数据前线」(非本号)后台回复关键字:SQL,就行
数据前线
后台回复关键字:1024,获取一份精心整理的技术干货
后台回复关键字:进群,带你进入高手如云的交流群。
推荐阅读
-
Mysql临时表原理及创建方法解析
2020-12-15 02:09:35这篇文章主要介绍了Mysql临时表原理及创建方法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 mysql 利用 temporary 关键字就可以创建出一个临时表。创建... -
MySQL问答系列之什么情况下会用到临时表
2021-01-21 15:09:17临时表介绍 什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。为什么会产生临时表:一般是由于复杂的SQL导致临时表被大量创建 临时表分为... -
MySQL如何创建和删除临时表
2021-01-18 21:49:591.介绍:MySQL临时表,属于session级别,当session退出时,临时表被删除。临时表允许与其他表同名,并单独维护在thd的结构体中;因此,不同的session可以创建同名的临时表,并且只操作自己拥有的临时表;创建临时表...1.介绍:
MySQL临时表,属于session级别,当session退出时,临时表被删除。临时表允许与其他表同名,并单独维护在thd的结构体中;因此,不同的session可以创建同名的临时表,并且只操作自己拥有的临时表;
创建临时表的语法很简单:
root@test 03:26:44>show create table tmp1\G
*************************** 1. row ***************************
Table: tmp1
Create Table: CREATE TEMPORARY TABLE `tmp1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
当创建临时表后,会在tmp文件夹下生成两个文件:
#sql3e95_1a_0.frm
#sql3e95_1a_0.ibd
那么MySQL本身究竟是如何创建和删除临时表的呢?
2.创建
执行SQL:
CREATE TEMPORARY TABLE `tmp1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`) );
1)断点:ysql_execute_command
mysql_execute_command:
2205 switch (lex->sql_command) {
(gdb)
2532 if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE))
(gdb) p lex->create_info.options --------if语句里为false
$2 = 1
create_table_precheck------检查是否具有创建表的权限,以及表名在全局链表上是否已存在(临时表无需检查)
append_file_to_dir ------Fix names if symlinked tables
if (select_lex->item_list.elements) -------------------当为create ....select这样的语句时select_lex->item_list.elements为非0值,这里我们只考虑简单的情况
if ((result= new select_create))
res= handle_select(thd, lex, result, 0);
else
(1)mysql_create_like_table ---------------create table like...类似的语句
(2)mysql_create_table ---------------主要分析这个函数
2)断点:mysql_create_table
mysql_create_table
mysql_create_table_no_lock
check_engine
file = get_new_handler
3842 set_table_default_charset(thd, create_info, (char*) db);
3844 if (mysql_prepare_create_table(thd, create_info, alter_info,
3854 path_length= build_tmptable_filename(thd, path, sizeof(path)); -----创建临时表文件名:#sql{进程id}_{thread_id}_{当前线程的临时表整数标识thd->tmp_table}
3978 rea_create_table --------------------------------------------------------------------创建frm文件和ibd文件
3986 open_temporary_table-------------------------------------------------------------打开临时表
(1)构建table和table_share结构体
(2)将table结构体加入到thd->temporary_tables链表中
4009 error= write_create_table_bin_log----------------------------------------------写入binlog
3.删除临时表
手动执行 drop table tmp1
mysql_execute_command
case SQLCOM_DROP_TABLE:
mysql_rm_table
mysql_rm_table_part2
for (table= tables; table; table= table->next_local)
drop_temporary_table-----------------------------从thd->temporary_tables上查找临时表
调用close_temporary_table来关闭、删除临时表文件,并从thd->temporary_tables上删除相应节点
if (!drop_temporary)-------------------------------当删除的是非临时表时,执行下面的逻辑
----------------------------------------
4. 当session退出时。
看看堆栈:
Breakpoint 16, rm_temporary_table (base=0xc8c560, path=0x1427c10 "/u01/mysql-5148.stock/tmp/#sql3e95_1d_0") at sql_base.cc:5634
5634 bool rm_temporary_table(handlerton *base, char *path)
(gdb)
5641 strmov(ext= strend(path), reg_ext);
(gdb) bt
#0 rm_temporary_table (base=0xc8c560, path=0x1427c10 "/u01/mysql-5148.stock/tmp/#sql3e95_1d_0") at sql_base.cc:5641
#1 0x00000000005f6eaa in close_temporary (table=0x1427030, free_share=true, delete_table=true) at sql_base.cc:1928
#2 0x00000000005f725f in close_temporary_tables (thd=0x14065f0) at sql_base.cc:1549
#3 0x0000000000592d9b in THD::cleanup (this=0x14065f0) at sql_class.cc:967
#4 0x00000000005a3579 in unlink_thd (thd=0xc8c560) at mysqld.cc:1858
#5 0x00000000005a35dc in one_thread_per_connection_end (thd=0xc8c560, put_in_cache=16) at mysqld.cc:1945
#6 0x00000000005ac208 in handle_one_connection (arg=0x14065f0) at sql_connect.cc:1141
#7 0x0000003e638064a7 in start_thread () from /lib64/libpthread.so.0
#8 0x0000003e630d3c2d in clone () from /lib64/libc.so.6
#9 0x0000000000000000 in ?? ()
在session结束的时候,会调用THD::cleanup来做临时表的清理工作
-
MySQL临时表与内存表
2021-01-18 20:40:24在MySQL中有三种虚拟表:临时表、内存表、视图。下面简单介绍一下临时表和内存表的使用。1、临时表MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表在MySQL 3.23版本中添加。临时表只在当前连接可见,... -
MySQL 临时表的原理以及优化手段
2021-08-27 11:35:55MySQL的临时表的原理以及优化手段。