临时表存储

MySQL临时表分为“内存临时表”和“磁盘临时表”，其中内存临时表使用MySQL的MEMORY存储引擎，磁盘临时表使用MySQL的MyISAM存储引擎；
一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表。

使用临时表的场景
1）ORDER BY子句和GROUP BY子句不同，
例如：ORDERY BY price GROUP BY name；

2）在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列
例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
3）ORDER BY中使用了DISTINCT关键字
ORDERY BY DISTINCT(price)
4）SELECT语句中指定了SQL_SMALL_RESULT关键字
SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很小，请直接使用内存临时表，不需要使用索引排序
SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用
一般情况下，我们没有必要使用这个选项，让MySQL服务器选择即可。

直接使用磁盘临时表的场景
1）表包含TEXT或者BLOB列；
2）GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；
3）使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列；

临时表相关配置
tmp_table_size：指定系统创建的内存临时表最大大小；
max_heap_table_size: 指定用户创建的内存表的最大大小；
注意：最终的系统创建的内存临时表大小是取上述两个配置值的最小值。

表的设计原则
使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。

如果实在无法避免，也应该尽量避免使用磁盘临时表。
常见的方法有：

1）创建索引：在ORDER BY或者GROUP BY的列上创建索引，这样可以避免使用临时表；
2）分拆很长的列，可以避免使用磁盘临时表：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件，因此表设计的时候，应该将这些列独立到另外一张表。

如何判断使用了临时表？
使用explain查看执行计划，Extra列看到Using temporary就意味着使用了临时表。

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

　　*  临时表在 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;

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

在创建临时表时声明类型为HEAP，则Mysql会在内存中创建该临时表，即内存表：如：

                CREATE TEMPORARY TABLE 表名 (。。。。) TYPE = HEAP

因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。如：

mysql> create temporary table temp_tb type='heap' select * from temptb;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show create table temp_tb \G;
       Table: temp_tb
Create Table: CREATE TEMPORARY TABLE `temp_tb` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `Name` char(20) NOT NULL,
  `Age` tinyint(4) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

ERROR:

No query specified

可以看出来临时表和内存表的ENGINE 不同，临时表默认的是Mysql指定的默认Engine,而内存表是MEMORY。

内存表的建立还有一些限制条件：
      MEMORY tables cannot contain        BLOB or TEXT columns. HEAP不支持BLOB/TEXT列。    
      The server needs sufficient memory to maintain all   MEMORY tables that are in use at the same time. 在同一时间需要足够的内存.
      To free memory used by a MEMORY table when   you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or remove the table altogether using DROP        TABLE.为了释放内存，你应该执行DELETE FROM heap_table或DROP TABLE heap_table。

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频道！