临时表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 条评论
登录 后参与评论

相关文章

来自专栏好好学习吧

Pycharm2018中DataBase的使用

1、点击右侧边栏的DataBase,在出现的Database窗口下点击绿色小加号,选择Data Source,选择需要的数据库类型,此处选择Sqlite

923
来自专栏xcywt

makefile中伪目标的理解

1. 我们知道Makefile中的语法是这样: target ... : prerequisites ... command ...

2148
来自专栏北京马哥教育

HA专题: corosync+pacemaker实现nginx高可用

前言 这几天都会学习高可用集群, 也会将其中的一些实验写出来分享给大家, 这个专题估计会写5篇左右, 实验介绍 这次的实验比较简单,在CentOS7使用cor...

37110
来自专栏开源优测

[快学Python3]开发工具

Python有丰富的开发工具,本书不一一进行介绍,只推荐大家使用PyCharm,本书接下来章节的示例代码均基于PyCharm社区版进行演示。 下载 下载地址: ...

3037
来自专栏木头编程 - moTzxx

PHP 获取网络图片资源并保存

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u011415782/article/de...

991
来自专栏野路子程序员

从已有安装包(vendor)恢复 composer.json

2485
来自专栏葡萄城控件技术团队

MultiRow发现之旅(二)- 详解属性管理器

前文回顾 MultiRow发现之旅(一)- 高效模板设计器 属性管理器 上一篇提到MultiRow模板设计器中有一个“属性管理器”,这里就聊一聊这个属性管理器。...

1739
来自专栏FreeBuf

代码审计之任意文件下载漏洞案例分享

继上次审计HDWiki之后,最近拿到一套新的源码Ear_Music_20180510_UTF8最新版进行审计,发现这套cms还是比较安全的,而当我审计遇到一处下...

1014
来自专栏前端探索

Laravel Mix 初探

针对 Laravel 优化了的 Laravel Mix, 提供了高效优雅的 API,用于使用几个常见的 CSS 和 JavaScript 预处理器为应用定义 W...

1536
来自专栏菜鸟致敬

提供免费MySQL数据库学习

最近买了一个服务器,放着也没有什么作用,于是搭建了MySQL 提供大家学习数据使用吧,想知道数据的用户信息请在后台回复MySQL 如果大家有其他想搭建的数据库环...

3529

扫码关注云+社区