MySQL 临时表与内存表

mysql 同时被 2 个专栏收录
117 篇文章 1 订阅
122 篇文章 0 订阅

下面是对MYSQL  临时表的理解

 1、局部临时表(#开头)只对当前连接有效,当前连接断开时自动删除。

 2、全局临时表(##开头)对其它连接也有效,在当前连接和其他访问过它的连接都断开时自动删除。

 3、不管局部临时表还是全局临时表,只要连接有访问权限,都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。


临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。

内存表:

        1.缺省存储引擎为MEMORY,表建在磁盘里,数据在内存里

        2.可以通过参数max_heap_table_size来设定内存表大小
        3.到达max_heap_table_size设定的内存上限后将报错
        4.表定义保存在磁盘上,数据和索引保存在内存中
        5.不能包含TEXT、BLOB等字段


   临时表:

        1.缺省存储引擎为MySQL服务器默认引擎,引擎类型只能是:memory(heap)、myisam、merge、innodb(memory临时表由于表的增大可能会转变为myisam临时表,表建在内存里,数据在内存里
        2.可以通过参数 tmp_table_size 来设定临时表大小。
        3.到达tmp_table_size设定的内存上限后将在磁盘上创建临时文件
        4.表定义和数据都保存在内存中
        5.可以包含TEXT, BLOB等字段

        临时表一般比较少用,通常是在应用程序中动态创建或者由MySQL内部根据SQL执行计划需要自己创建。

        内存表则大多作为Cache来使用,特别在没有第三方cache使用时。如今随着memcache、NoSQL的流行,越来越少选择使用内存表。

MySQL服务器使用内部临时表

        在某些情况下,mysql服务器会自动创建内部临时表。查看查询语句的执行计划,如果extra列显示“using temporary”即使用了内部临时表。内部临时表的创建条件:

        *  group by 和 order by中的列不相同

        *  order by的列不是引用from 表列表中 的第一表

        *  group by的列不是引用from 表列表中 的第一表

        *  使用了sql_small_result选项

        *  含有distinct 的 order by语句

        初始创建内部myisam临时表的条件:

        *  表中存在text、blob列

        *  在group by中的 列 有超过512字节

        *  在distinct查询中的 列 有超过512字节

        *  在union、union all联合查询中,select 列 列表中的 列 有超过512字节的

重启MySQL 数据库后,内存中的数据全部丢失。内存表的功能有部分的限制,有些属性不能像正常表一样使用。

创建一个临时表,但是到相应的数据目录下则找不到.frm文件的。


其中包括2个重要的参数 
[mysqld] 
# 内存表容量 
max_heap_table_size=1024M 
# 临时表容量 
tmp_table_size=1024M 

SQL 语法:

1)创建临时表:

mysql> CREATE TEMPORARY TABLE tmp_table (
    -> name VARCHAR(10) NOT NULL,
    -> value INTEGER NOT NULL
    -> ) ;

Query OK, 0 rows affected (0.38 sec)

  查看表状态
mysql> show CREATE  TABLE tmp_table \G
*************************** 1. row ***************************
       Table: tmp_table
Create Table: CREATE TEMPORARY TABLE `tmp_table` (
  `name` varchar(10) NOT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

它和正常表没有什么区别,但是数据是存在内存中的,你在响应的数据目录下找不到数据文件。
一旦断开连接你再查该表就会消失。

临时表使用有一些限制条件

  *  临时表在 memory、myisam、merge或者innodb上使用,并且不支持mysql cluster簇);

               show tables语句不会列出临时表,在information_schema中也不存在临时表信息;show create table可以查看临时表;

  *  不能使用rename来重命名临时表。但是可以alter table rename代替:

             mysql>ALTER TABLE orig_name RENAME new_name;

  *  可以复制临时表得到一个新的临时表,如:

                mysql>create temporary table new_table select * from old_table;

  *  但在同一个query语句中,相同的临时表只能出现一次。如:

               可以使用:mysql> select * from temp_tb;

               但不能使用:mysql> select * from temp_tb, temp_tb as t;

               错误信息:   ERROR 1137 (HY000): Can't reopen table: 'temp_tb'

        同样相同临时表不能在存储函数中出现多次,如果在一个存储函数里,用不同的别名查找一个临时表多次,或者在这个存储函数里用不同的语句查找,都会出现这个错误。

  *  但不同的临时表可以出现在同一个query语句中,如临时表temp_tb1, temp_tb2:

                 Mysql> select * from temp_tb1, temp_tb2;

        临时表可以手动删除:

                 DROP TEMPORARY TABLE IF EXISTS temp_tb;

       临时表主要用于对大数据量的表上作一个子集,提高查询效率


2)创建内存表:

和临时表有点相似的是内存表,有的也称堆表。

mysql> CREATE TABLE mem_table (
    -> name VARCHAR(10) NOT NULL,
    -> value INTEGER NOT NULL
    -> ) TYPE = HEAP;
Query OK, 0 rows affected, 1 warning (0.01 sec)
查看表状态
mysql> show CREATE  TABLE mem_table \G
*************************** 1. row ***************************
       Table: mem_table
Create Table: CREATE TEMPORARY TABLE `mem_table` (
  `name` varchar(10) NOT NULL,
  `value` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

你在数据目录下回发现一个mem_table.frm文件,所以内存表的数据是放在磁盘上的,但是插入数据后发现磁盘上没有数据文件,所以数据是在内存中的,因为采用的是memory 引擎。一旦Mysql shutdown了,则该表的数据将不存在。

该表因为数据是在内存中,所以数据会很快。缺点是一旦数据库shutdown,则数据会丢失。

  • 0
    点赞
  • 1
    评论
  • 3
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

评论 1 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页

打赏作者

大树叶

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值