首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >临时表tmp table如何避免

临时表tmp table如何避免

作者头像
MySQL轻松学
发布于 2018-03-09 06:35:14
发布于 2018-03-09 06:35:14
3.8K00
代码可运行
举报
文章被收录于专栏:MYSQL轻松学MYSQL轻松学
运行总次数:0
代码可运行

1、配置文件参数my.cnf

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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 删除。

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

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
ERROR 1062 (23000) at line 1: Duplicate entry '1332883220' for key 'group_key'
我有一个数据表,记录一个QQ号加好友的活跃天数、加好友次数、加好友的toUin数等信息。数据表的建表语句如下:
恋喵大鲤鱼
2018/08/03
2K0
ERROR 1062 (23000) at line 1: Duplicate entry '1332883220' for key 'group_key'
多场景下MySQL临时表的作用
墨墨导读:MySQL临时表在很多场景中都会用到,比如用户自己创建的临时表用于保存临时数据,以及MySQL内部在执行复杂SQL时,需要借助临时表进行分组、排序、去重等操作。下面将会对MySQL临时表的一些概念、分类和常见问题进行整理。
数据和云
2020/08/06
5K0
多场景下MySQL临时表的作用
MySQL8.0中消失又回来的磁盘临时表
首先,我是一个标题党^_^,写这样的标题就是为了吸引你们来看的。标题中说的磁盘临时表消失,并不是真的消失了,而是在MySQL默认配置下,从8.0.15到8.0.27的版本都不会产生磁盘临时表(8.0.15之前的版本没有做个测试,就不妄下结论了),在8.0.28及之后的版本中,默认配置又放开了磁盘临时表(文章中未特殊说明时都指内部临时表,非用户创建临时表)的使用,这是什么情况?请容我慢慢道来。
GreatSQL社区
2023/02/23
1K0
MySQL8.0内存相关参数介绍
MySQL理论上使用的内存 = 全局共享内存 + max_connections×线程独享内存。
MySQL技术
2020/06/17
2.4K0
MySQL设置临时表大小
临时表是执行sql语句过程中创建的中间过渡表,例如多表联合操作,就需要建立临时表 查看临时表的使用状态 mysql>show global status like 'created_tmp%'; cr
dys
2018/04/02
8.2K0
MySQL设置临时表大小
MySQL临时表空间避坑指南
最近遇到一个MySQL数据导入时候遇到问题,先来看下问题产生的具体报错信息如下所示:
SEian.G
2022/03/30
4.1K0
数据库MySQL-优化配置参数
Mysql可以通过启动时指定参数和使用配置文件两种方法进行配置,在大多数情况下配置文件位于/etc/my.cnf 或者是 /etc/mysql/my.cnf在Windows系统配置文件可以是位于C://windows//my.ini文件,MySQL查找配置文件的顺序可以通过以下方法获得。
cwl_java
2020/02/13
7.6K0
数据库MySQL-优化配置参数
Mysql上线后优化项
mysql> show variables like 'max_connections';
子润先生
2021/07/12
4240
mysql造数据占用临时表空间
MySQL在处理复杂查询时,有时会使用临时表来存储中间结果。当这些临时表占用大量空间时,可能导致性能下降甚至服务中断。本文将深入探讨临时表空间的占用问题,分析常见问题,指出易错点,并提供避免和优化的策略。
Jimaks
2024/05/20
4670
Mysql配置文件 扩展详细配置(下)
避免MySQL的外部锁定,减少出错几率增强稳定性。 5以前版本skip-locking,新版本skip-external-locking
陈不成i
2021/06/15
1.2K0
MySQL8的临时表
在磁盘上的内部临时表过多时(created_tmp_disk_tables),需要增大参数tmp_talble_size,temptable_max_ram,temptable_max_mmap。
cuiyi
2023/01/03
4K0
MySQL配置优化
转载自http://www.cnblogs.com/luyucheng/p/6340076.html
allsmallpig
2021/02/25
1.2K0
mysql 性能优化方案 (转)
网 上有不少mysql 性能优化方案,不过,mysql的优化同sql server相比,更为麻烦与复杂,同样的设置,在不同的环境下 ,由于内存,访问量,读写频率,数据差异等等情况,可能会出现不同的结果,因此简单地根据某个给出方案来配置mysql是行不通的,最好能使用 status信息对mysql进行具体的优化。 mysql> show global status;   可以列出mysql服务器运行各种状态值,另外,查询mysql服务器配置信息语句: mysql> show variables; 一、
wangxl
2018/03/07
1.5K0
你好奇过 MySQL 内部临时表存了什么吗?
MySQL 临时表分为两种:外部临时表、内部临时表。用户通过 CREATE TEMPORARY TABLE 创建的是外部临时表。SQL 语句执行过程中 MySQL 自行创建的是内部临时表,explain 输出结果的 Extra 列出现了 Using temporary 就说明 SQL 语句执行时使用了内部临时表。
csch
2022/09/05
1.8K0
你好奇过 MySQL 内部临时表存了什么吗?
无惧双十二Or 黑五,这些 MySQL 性能调优技巧看过来
企鹅号小编
2018/01/09
8050
Mysql优化系列(1)--Innodb引擎下mysql自身配置优化
1.简单介绍 InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。 2.之所以选用innodb作为存储引擎的
洗尽了浮华
2018/01/23
2.6K0
MySQL 临时数据空间不足导致SQL被killed 的问题与扩展
最近在MySQL运行中应用程序报错,/home/mysql/data3009/tmp/#sql_14cdb_24' is full" 。
AustinDatabases
2023/12/01
6130
MySQL  临时数据空间不足导致SQL被killed 的问题与扩展
MySQL 数据库上线后根据 status 状态优化
马哥linux运维 | 最专业的linux培训机构 ---- 网上有很多的文章教怎么配置mysql服务器,但考虑到服务器硬件配置的不同,具体应用的差别,那些文章的做法只能作为初步设置参考,我们需要根据自己的情况进行配置优化,好的做法是MySQL服务器稳定运行了一段时间后运行,根据服务器的”状态”进行优化。 查看MySQL服务器配置信息: show variables; 查看MySQL服务器运行的各种状态值: show global status; 1. 慢查询 show variables like '%
小小科
2018/05/02
1.3K0
Mysql占用过高CPU时的优化手段
Mysql占用CPU过高的时候,该从哪些方面下手进行优化? 占用CPU过高,可以做如下考虑: 1)一般来讲,排除高并发的因素,还是要找到导致你CPU过高的哪几条在执行的SQL,show processlist语句,查找负荷最重的SQL语句,优化该SQL,比如适当建立某字段的索引; 2)打开慢查询日志,将那些执行时间过长且占用资源过多的SQL拿来进行explain分析,导致CPU过高,多数是GroupBy、OrderBy排序问题所导致,然后慢慢进行优化改进。比如优化insert语句、优化group by语句、
洗尽了浮华
2018/01/23
5K0
Mysql占用过高CPU时的优化手段
Mysql show status核心参数含义
innodb行锁 mysql> show global status like '%innodb%lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time
mingjie
2022/05/12
5890
相关推荐
ERROR 1062 (23000) at line 1: Duplicate entry '1332883220' for key 'group_key'
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档