为什么突然想说说临时表呢，之前都没有太过多的留意这些知识，是昨天以前的一个同事问了我一句关于临时表的疑问，我也顺便加深写认识。其实，我对于MySQL临时表的应用可以说是一无所知，说的最多听得最多的也都是，查询语句时用到临时表的情况，就是说的internal temporary table，是MySQL自己因为执行某个操作需要额外使用的，而另一种就是user-defined，用户自己定义的(create temporary table)； 之前在798game的时候，公司的手游数据库的设计用到了大量临时表。下面是一些可能在某种意义上，你会觉得很不靠谱的认识，因为这些只是看了一些文字后的皮毛理解。
Internal Temporary Table
当我们的SQL语句相对“复杂”，需要MySQL思考更多时，就会出发自身使用internal temp table，比如：语句中有order by或是group by，还有当我们要修改表的一些属性，比如增加加索引或是添加字段，这些都有可能使用到临时表，我们通过explain这个命令输出的extra列可以看到这样的提示using temporary。这类临时表多数都是内存表也就是MEMORY的存在于内存中，当临时表使用的空间大于tmp_table_size参数设置的值时，该临时表的类型就会自动变成MyISAM的表，那么会被存储到磁盘上，很显然当存取数据从内存转变到磁盘上，这个性能的落差将是我们无法容忍的，所以我们要通过观察Created_tmp_disk_tables参数增长的程度来调整tmp_table_size的到小，该参数仅对于memory类型的internal临时表有效。
User-defined Temporary Table
通过语句CREATE TEMPORARY TABLE XXX 来创建应用所需的临时表。我们自定义的临时表有以下一些特点：
1. 临时表的存在的周期是当前登录的session有效，断开连接将会被立即删除，也可手动将其drop table，无论以任何类型创建的表，在对应的数据库目录下都没有具体的文件生成，但是所有的DML语句都会被记录（后面会说复制的问题）；既然是session有效，那么不同的session间是不能访问别人定义的临时表的，不同session定义的临时表名就可以相同。
2. 支持多种类型的表：MYISAM、MEMORY、MERGE、INNODB，其中对于内存表，通过max_heap_table_size参数值来控制表的大小；peter曾经测试基于memory的临时表的查询速度要比mysiam的快10到100倍，还发现key_buffer_size对于查询有相当重要的影响，即便应用所有的表都是innodb类型的，设置该参数对于提高查询性能有很大帮助；
3. 为什么要用它呢？临时表可以将我们之后可能频繁使用到的中间数据集临时保存下来，这样就会提高业务的处理速度，再有就是可以减少程序代码量，将一定的逻辑处理放到数据库上去完成，这之间的代价需要实际去评估；到了5.0以后出现了视图，对于开发人员有多了一种可选择的方法，而视图的定义是可以永久保存到磁盘上的，视图是不能重名的。
4. 对于临时表的DML操作都会被记录到binlog中，但是前提是binlog的日志格式需要设置为statement模式；主从间对于临时表的操作复制，由于从库slave线程的不恰当关闭，将会导致临时表的复制失败，关闭前需要保证所有的临时表都不处在打开更新的状态，这样再次恢复主从是从库对于临时表的复制才能继续正常进行（想不明白？？？）。
关于临时表的复制，问题是：
既然临时表的存在是session级的，那么从库的sql_thread是不可以中断的，但在生产中从库是不可能完全保持正常，当复制的sql线程临时中断，再次恢复以后，之前的临时表也就不存在了，那么之后有依赖于临时表中数据跟新的语句不都会面临找不到该临时表的问题吗？
相关文章
转载请注明：爱开源 » MySQL临时表:Internal和Used-defined