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

相关文章

来自专栏Snova云数仓

Greenplum资源队列初识

在Greenplum的4.x版本之后,加入了资源队列的概念,其主要作用就是限制用户或者单个SQL对资源的消耗。避免出现消耗过多资源,影响其他用户或者SQL计算。...

51214
来自专栏Linyb极客之路

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

1003
来自专栏我爱编程

Python数据库操作之pymysql模块和sqlalchemy模块

参考博客https://www.cnblogs.com/aylin/p/5770888.html

1414
来自专栏SpringBoot 核心技术

第六章:使用QueryDSL的聚合函数

1822
来自专栏微信公众号:Java团长

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

953
来自专栏Spark学习技巧

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

1092
来自专栏DOTNET

【翻译】MongoDB指南/CRUD操作(四)

【原文地址】https://docs.mongodb.com/manual/ CRUD操作(四) 1 查询方案(Query Plans) MongoDB 查询优...

29810
来自专栏琯琯博客

Yii2 开发小技巧

2434
来自专栏逍遥剑客的游戏开发

关于Singleton

1302
来自专栏杨建荣的学习笔记

MySQL中的大小写敏感

今天同事问了一个问题,是关于MySQL大小写敏感的。 如果根据关键字case来搜索,会发现有两个参数。 mysql> show variables like ...

3696

扫码关注云+社区