临时表tmp table如何避免

MySQL轻松学

发布于 2018-03-09 14:35:14

3.5K0

发布于 2018-03-09 14:35:14

文章被收录于专栏：MYSQL轻松学

1、配置文件参数my.cnf

tmp_table_size=64M
max_heap_table_size=64M
tmpdir = /data/mysql/tmp

2、优化Tips：

如果Created_tmp_disk_tables/ Created_tmp_tables应该小于20%，如果比值较高，就需要适当调高tmp_table_size或者max_heap_table_size的值，让Mysql在内存中完成临时表的操作，减少使用硬盘对性能和响应时长的影响。

在调高tmp_table_size或者max_heap_table_size的值之前，要注意观察MySQL的内存使用情况，如果MySQL的内存使用率非常高，服务器的可用内存已经非常少了，需要进行评估调高tmp_table_size或者max_heap_table_size的值是否会导致内存不足或者其它问题；在调高之后，也要注意观察服务器的内存使用情况。

3、什么情况下会使用临时表：

当MySQL使用临时表的时候，会先在内存中创建临时表，如果临时表的大小超过了配置的临时表的最大值，Mysql会把它转化为使用硬盘空间的临时表。

使用临时表的情况：

UNION查询；
ORDER BY 或者GROUP BY查询；

ORDER BY 或 GROUP BY中包含的列不是join中第一个表的列；

同时使用DISTINCT和ORDER BY时；
Derived tables（FROM语句中的子查询）；
使用子查询或者 semi-join materialization创建表时；
使用了SQL_SMALL_RESULT参数时；
一些view查询，例如使用TEMPTABLE算法的计算或者使用UNION或者聚集计算；

使用临时表时，不使用in-memory临时表，而直接使用on-disk临时表的情况：

表中包含BLOB或者TEXT字段；
GROUP BY 或者DISTINCT的字段中，包含长度超过512字节或者512字符的字段；
UNION或者UNION ALL查询中，SELECT的字段中存在长度超过512字节或字符的字段。

4、查看临时表的使用情况：

Created_tmp_disk_tables	表示MySQL执行语句时，mysql累积创建的使用硬盘空间的内部临时表的数量。如果MySQL创建的临时表大小太大了（超过了tmp_table_size和max_heap_table_size的最小值）,就会使用硬盘来存放临时表，使用完毕之后再删除掉。如果该值比较大，应该适当调高tmp_table_size和max_heap_table_size的值。
Created_tmp_tables	表示MySQL执行语句时，mysql累积创建的内部临时表的数量。
Created_tmp_files	mysqld累积创建的临时文件的总数

跟临时表配置相关的参数变量：

max_tmp_tables	每个客户端连接能同时保持的最大临表数量（该参数在新版本中会被移除，并且是无效的）
tmp_table_size	临时表可以在内存中占用的最大大小，如果临时表的大小超过了tmp_table_size的值，会转换为tmpdir参数指定的目录下的硬盘上的临时文件。这也是为什么要把操作系统的/tmp目录挂载为tmpfs,/dev/shm 加载到内存中的原因。
tmpdir	MySQL用来存放临时文件的路径，如果Mysql是Master主库，建议把tmpdir的路径挂载为tmpfs,/dev/shm的模式，加载到内存中，提高临时文件的访问速度。如果Mysql是slave角色，不建议把tmpdir加载为tmpfs,/dev/shm的模式，建议加载为操作系统重启之后，数据不会被删除的分区。
slave_load_tmpdir	指定slave在复制LOAD DATA INFILE语句时，创建临时文件的目录。由于/tmp目录中的文件在操作系统重启的时候会丢失，所以slave上，不建议设置slave_load_tmpdir或者tmpdir的目录为/tmp或者tmpfs模式。
default_tmp_storage_engine	设置临时表（create temporary table）的默认存储引擎，默认是InnoDB。MySQL5.6.3版本中心增加的。
max_heap_table_size	设置用户创建的MEMORY引擎表的最大大小。max_heap_table_size参数和tmp_table_size参数的最小值会成为in-memory的表（临时表）的最大值。