MySQL 临时表

MySQL执行大数据量join、sort等操作时，会在tmpdir目录下生成中间结果的MyISAM格式的临时文件。

mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+

这些文件在SQL执行完成之前会一直存在，占用大量磁盘空间，如果在执行过程中，磁盘空间用尽之际会中断此操作，释放这些临时文件。因此，设法减少大数据量join、 sort等操作，不仅仅会提升运行效率还会避免上述问题。

解析：

mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 759     |
| Created_tmp_files       | 74      |
| Created_tmp_tables      | 5226311 |
+-------------------------+---------+

每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数：
Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 0.01% （理想值<= 25%）

可以用EXPLAIN来查看Extra字段判断是否使用了临时表

再来查看临时表的配置

mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+
只有16MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表。

即有时候数据库服务器在执行某些查询的时候会生成内部临时表，这些临时表有可能是生成在内存里的由MEMORY引擎处理的，也有可能是生成在磁盘上由MyISAM引擎处理的。如果说在内存中的临时表大小超过限制，服务器则会将临时表保存成磁盘临时表。用户无法直接控制这些内部临时表和管理这些临时表的数据库引擎。
内部临时表产生的时机有以下几种：

使用 ORDER BY 子句和一个不一样的 GROUP BY 子句（经过笔者实验，应该是GROUP BY一个无索引列,就会产生临时表），或者 ORDER BY 或 GROUP BY 的列不是来自JOIN语句序列的第一个表，就会产生临时表(经笔者实验，应该是使用JOIN时， GROUP BY 任何列都会产生临时表)

DISTINCT 和 ORDER BY 一起使用时可能需要临时表（笔者实验是只要用了DISTINCT（非索引列），都会产生临时表）


用了 SQL_SMALL_RESULT, mysql就会用内存临时表。

有些情况服务器会直接使用磁盘临时表：

表里存在BLOB或者TEXT的时候(这是因为MEMORY引擎不支持这两种数据类型，这里笔者补充一下，并非只要查询里含有BLOB和TEXT类型的列就会产生磁盘临时表），也就是说如果我们的查询中包含了BLOB和TEXT的列，而且又需要临时表，这时候临时表就被强制转成使用磁盘临时表，所以此书一直在提醒我们，如果要对BLOB和TEXT排序，应该使用SUBSTRING（column， length）将这些列截断变成字符串，这样就可以使用in-memory临时表了)

GROUP BY 或者 DISTINCT 子句大小超过 512 Bytes

使用了UNION 或 UNION ALL 并且 SELECT 的列里有超过512 Bytes的列

参考： http://www.cnblogs.com/zemliu/archive/2012/10/29/2744913.html