MySQL&MariaDB临时表与临时文件

  1. mysql何时会使用临时表

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

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

3ORDER BYGROUP BY的子句不一样时;

4、表连接中,ORDER BY的列不是驱动表中的;

5DISTINCT查询并且加上ORDER BY时;

6SQL中用到SQL_SMALL_RESULT选项时;

7FROM中的子查询;

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

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

  

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

如果临时表中需要存储的数据量超过了上限( 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

其中MADaria表的数据文件,MAIaria表的索引文件

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

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

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

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

4、执行SHOW COLUMNS/FIELDSDESCRIBESQL命令,因为它们的执行结果用到了BLOB列类型。

  

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

 仅列出已知的一些操作:

  1. Order by非索引字段
  2. 增加索引
  3. 创建分区表
  4. Show create table 分区表

 

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

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)