前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >临时表tmp table如何避免

临时表tmp table如何避免

作者头像
MySQL轻松学
发布2018-03-09 14:35:14
3.5K0
发布2018-03-09 14:35:14
举报
文章被收录于专栏:MYSQL轻松学

1、配置文件参数my.cnf

代码语言:javascript
复制
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字节的字符串,基本上都是为了显示信息,而不会用于查询条件,因此表设计的时候,应该将这些列独立到另外一张表。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2016-08-31,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 MYSQL轻松学 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 6、如何避免使用临时表:
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档