MySQL 临时表与内存表

下面是对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，则数据会丢失。