mysql temp table_temptable

mysql何时会使用临时表

1、UNION查询；——最新版本的MariaDB10.1中UNION ALL不再使用临时表

2、用到TEMPTABLE算法或者是UNION查询中的视图；

3、ORDER BY和GROUP BY的子句不一样时；

4、表连接中，ORDER BY的列不是驱动表中的；

5、DISTINCT查询并且加上ORDER BY时；

6、SQL中用到SQL_SMALL_RESULT选项时；

7、FROM中的子查询；

8、子查询或者semi-join时创建的表；

可以通过explain查看extra列：Using temporary表示使用临时表

临时表何时会变成磁盘临时表写入临时文件

如果临时表中需要存储的数据量超过了上限(tmp_table_size或max_heap_table_size中取其大者)，这时候就需要生成磁盘临时表。

Mariadb的磁盘临时表默认是aria存储引擎的。

所以会在tmpdir目录下生成类似如下的文件：

-rw-rw—-. 1 mariadb mariadb 1.2G Aug 14 10:06 #sql_750a_0.MAD

-rw-rw—-. 1 mariadb mariadb 8.0K Aug 12 17:52 #sql_750a_0.MAI

其中MAD为aria表的数据文件，MAI为aria表的索引文件

在以下几种情况下，会直接创建磁盘临时表：

1、数据表中包含BLOB/TEXT列；

2、在GROUP BY或者DSTINCT的列中有超过512字符的字符类型列(或者超过512字节的二进制类型列)；

3、在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列(对于字符串类型是512个字符，对于二进制类型则是512字节)；

4、执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结果用到了BLOB列类型。

还有什么操作会使用临时文件

仅列出已知的一些操作：

Order by非索引字段

增加索引

创建分区表

Show create table分区表

等

如何查看是否使用了临时表、磁盘临时表、临时文件

MariaDB [lzk]> show status like ‘Created_tmp%’;

+————————-+——-+

| Variable_name           | Value |

+————————-+——-+

| Created_tmp_disk_tables | 3     |    —-使用磁盘临时表次数

| Created_tmp_files       | 38    |   —-使用临时文件次数

| Created_tmp_tables      | 9     |   —-使用临时表次数

+————————-+——-+

3 rows in set (0.00 sec)