临时表tmp table如何避免

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会把它转化为使用硬盘空间的临时表。

使用临时表的情况:

  1. UNION查询;
  2. ORDER BY 或者GROUP BY查询;

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

  1. 同时使用DISTINCT和ORDER BY时;
  2. Derived tables(FROM语句中的子查询);
  3. 使用子查询或者 semi-join materialization创建表时;
  4. 使用了SQL_SMALL_RESULT参数时;
  5. 一些view查询,例如使用TEMPTABLE算法的计算或者使用UNION或者聚集计算;

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

  1. 表中包含BLOB或者TEXT字段;
  2. GROUP BY 或者DISTINCT的字段中,包含长度超过512字节或者512字符的字段;
  3. 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的表(临时表)的最大值。

5、关于LOAD DATA:

当LOAD DATA使用LOCAL参数时,会把文件复制到操作系统的临时目录中,这个操作不是mysql的tmpdir或者slave_load_tmpdir参数所指定的,不是MySQL可以配置的。

6、如何避免使用临时表:

表的设计原则 使用临时表一般都意味着性能比较低,特别是使用磁盘临时表,性能更慢,因此我们在实际应用中应该尽量避免临时表的使用。

如果实在无法避免,也应该尽量避免使用磁盘临时表。

常见的方法有:

1)创建索引:在ORDER BY或者GROUP BY的列上创建索引,这样可以避免使用临时表; 2)分拆很长的列,可以避免使用磁盘临时表:一般情况下,TEXT、BLOB,大于512字节的字符串,基本上都是为了显示信息,而不会用于查询条件,因此表设计的时候,应该将这些列独立到另外一张表。

原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2016-08-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏一个会写诗的程序员的博客

Spring Reactor 项目核心库Reactor Core

Non-Blocking Reactive Streams Foundation for the JVM both implementing a Reactiv...

2182
来自专栏跟着阿笨一起玩NET

c#实现打印功能

2782
来自专栏pangguoming

Spring Boot集成JasperReports生成PDF文档

由于工作需要,要实现后端根据模板动态填充数据生成PDF文档,通过技术选型,使用Ireport5.6来设计模板,结合JasperReports5.6工具库来调用渲...

1.2K7
来自专栏C#

DotNet加密方式解析--非对称加密

    新年新气象,也希望新年可以挣大钱。不管今年年底会不会跟去年一样,满怀抱负却又壮志未酬。(不过没事,我已为各位卜上一卦,卦象显示各位都能挣钱...)...

4918
来自专栏java 成神之路

使用 NIO 实现 echo 服务器

4667
来自专栏张善友的专栏

Silverlight + Model-View-ViewModel (MVVM)

     早在2005年,John Gossman写了一篇关于Model-View-ViewModel模式的博文,这种模式被他所在的微软的项目组用来创建Expr...

2968
来自专栏闻道于事

js登录滑动验证,不滑动无法登陆

js的判断这里是根据滑块的位置进行判断,应该是用一个flag判断 <%@ page language="java" contentType="text/html...

6848
来自专栏Golang语言社区

【Golang语言社区】GO1.9 map并发安全测试

var m sync.Map //全局 func maintest() { // 第一个 YongHuomap := make(map[st...

4728
来自专栏大内老A

The .NET of Tomorrow

Ed Charbeneau(http://developer.telerik.com/featured/the-net-of-tomorrow/) Exciti...

31810
来自专栏我和未来有约会

Kit 3D 更新

Kit3D is a 3D graphics engine written for Microsoft Silverlight. Kit3D was inita...

2546

扫码关注云+社区