> 关于mysql什么时候会生成磁盘上的临时表的问题原文地址:一、磁盘临时表与内存临时表的差异从磁盘临
关于mysql什么时候会生成磁盘上的临时表的问题原文地址:一、磁盘临时表与内存临时表的差异从磁盘临
pishen007 & &
发布时间： & &
浏览：1 & &
回复：2 & &
悬赏：0.0希赛币
关于mysql什么时候会生成磁盘上的临时表的问题原文地址: 一、磁盘临时表与内存临时表的差异　　从磁盘临时表与内存临时表的差异中大家可以看到，磁盘临时表只是内存临时的一个替代品。这就好像操作系的虚拟内存一样。当内存不够用时，可以在硬盘上的一个空间作为其替代品，将内存中的部门数据转移到虚拟内存中。这个磁盘临时表也是相同的道理。　　但是这里需要注意的是，硬盘的效率与内存的效率是不同的。在执行相同的一个作业时，内存的性能要高于硬盘的性能，一般会高上百倍，甚至上千倍。从这里就可以看出，为了提高数据库系统的性能，我们最好选择内存临时表，而放弃使用磁盘临时表。　　二、BLOB和TEXT数据类型与临时表的关系　　在讨论如何来取磁盘临时表舍内存临时表这个话题时，我认为有必要先谈谈BLOB和TEXT这两个数据类型。这两个数据类型都用来存储大容量的数据。前者是采用二进制的形式来保存，而后者是采用字符形式来保存。　　这两个数据类型与其他数据类型有本质的不同。在MYSQL数据库中，是将这两个数据类型当做有实体的对象来处理。存储引擎也会采用特别的方式来保存他们。BLOB数据类型是采用二进制的方式来存储数据。而采用二进制来存储数据时，系统没有字符集的要求，也不会设置排序规则。相反，TEXT采用字符形式来存储数据，为此有字符集和排序规则的限制。　　这两种数据类型，跟今天要谈到的临时表有什么关系呢 其实很有关系。(1)因为这两种数据类型的容量比较大，为此对对这些类型的字段进行操作时，临时表就会一下子变得很大。此时就很容易超过上面两个参数的限制。系统就会将内存临时表转换为磁盘临时表。为此这两种数据类型会增加产生磁盘临时表的几率。　　三、项目建议　　采用磁盘临时表会在很大程度上降低数据库的性能开销。为此在实际工作中要尽量的避免。那么该如何来限制这个磁盘临时表的数量呢 这是一个很复杂的话题。在这里，我只结合自己的工作经验，谈谈一些经验心得。大家在实际工作中，可以尝试着试一下，看看是否有效。　　一是不同的存储类型对于数据类型的支持力度是不同的。如果某种存储类型不支持某些数据类型，那么系统就会直接采用磁盘临时表，即使数据没有超过其规定的大小。简单的说，就是对于存储引擎，如果其不支持某些数据类型，那么对这些数据类型进行操作时，系统只能够使用磁盘临时表，而不能够使用磁盘临时表。如对于Memory存储引擎来说，其不支持BLOB和TEXT数据类型。(2)在系统运行中，如果使用了BLOB和TEXT列，并且需要隐式临时表时，查询将不会使用内存临时表，而直接采用磁盘临时表。即使两个数据类型中的列存储的数据不多，也是如此。显然这会大大的降低数据库的性能。　　二是尽可能的避免使用BLOB和TEXT数据类型。因为这两种数据类型存储的数据比较大，而且某些存储引擎并不支持这两种数据类型，为此在实际工作中，最好尽量避免使用这两种数据类型。如果真的需要使用，那么也需要采取一些措施来避免其带来的负面影响。如只在特定的情况下(用户特别指定时)，前台客户端才调用这两个数据对象。另外，从数据库角度出发，也可以采取一些措施。如数据库管理员可以采用ORDER BY SUBSTRING子句，将这些值转换为字符串，他某些存储引擎可以使用内存中的临时表。RDER BY SUBSTRING这个子句，顾名思义，是对这两个数据类型的记录进行排序。不过因为其内容太长，往往无法对某个字段的全部的值进行比较，然后进行排序。此时为了提高排序的效率，往往是采用SUBSTRING关键字来限制，只比较某几位的值。为了保证其数据量不超过内存临时表的限制值，此时要确保使用的子字符串足够的短，不能够让临时表变得很大。就是说，SUBSTRING关键字的参数要短一点。否则的话，容量足够大时，系统还是会将内存临时表转换为磁盘临时表。========================================================================上面文章我有两处不解(1)&这两种数据类型会增加产生磁盘临时表的几率&--------------------------------这里的意思似乎是使用text与blob并不一定会生成磁盘上的临时表, 而是大小超过了tmp_table_size才使用临时表(2), &而直接采用磁盘临时表。即使两个数据类型中的列存储的数据不多&--------------------似乎又与第一条的tmp_table_size相冲突到底text, blob列的大小与是否使用磁盘上的临时表有没有关系呢另外还想问一个问题 select * from t1 where id in (select id from t2)-----------这里子查询生成的结果集是不是放在临时表中的, 还是结果集本身就是一个独立的不同于临时表的概念　 谢谢大家了另外, 我tmp_table_size设置为1024
然后 select * from t2 order by content desc这里记录有118行, 每行content(text类型)都超过了1024字节可是还是没有在show status like '%tmp%'显示出使用了磁盘上的临时表
临时文件大家都不陌生，就是为了各种不同的目的，产生的中间文件。使用完毕后会被及时的回收和清理。临时表也是如此，它是mysql 在进行一些内部操作的时候生成的数据库表。这些操作主要包括，group by, distinct，一些order by 查询语句，UNION，一些from 语句中的子查询（derived tables）等。例如：
使用了 order by 子句和一个不同的group by 子句，或者order by（或group by）包含了JOIN queue 上非第一个表中的列，临时表将被创建。
使用了 SQL_SMALL_RESULT 选项，mysql 会使用in‐memory 临时表
DISTINCT 和order by 一起使用可能会用到临时表
pissdog & &
20:03:53 & &
& & （0）（0）
1：你的意思正确，大小超过了tmp_table_size 会使用 磁盘临时表，没操作则用内存临时表。2：不冲突，这句话还有个条件，如果使用了BLOB和TEXT列，“并且需要隐式临时表时”，直接用磁盘临时表，不会用到内存。即使没有超过max_heap_table_size的限制&他们的类型决定的&，（varchar,char 都会用内存临时表）。3：用explain查看，其实你可以在每次执行前 用 explain 分析下，要是让你使用临时表 会在extra里面现实出来的。4：那就表示没有使用临时表。tmp_table_size设置为1024，这个设置的小了，后期明显会出现问题。pitt123456 & &
20:03:53 & &
& & （0）（0）
本问题标题：
本问题地址：
温馨提示：本问题已经关闭，不能解答。
暂无合适的专家
&&&&&&&&&&&&&&&
希赛网 版权所有 & &&1378人阅读
MySQL（76）
看到很多朋友对这些概念有些混淆，我来发表下我自己的观点。
1. 参数控制：max_heap_table_size
2. 到达上线后报错。
3. 表定义保存在磁盘上，数据和索引保存在内存里面。
4. 不能包含TEXT,BLOB等字段。
1. 参数控制：tmp_table_size。
2. 到达上线后创建文件在磁盘上。
3. 表定义和数据都在内存里。
4. 可以包含TEXT, BLOB等字段。
而且tmp_table_size和max_heap_table_size的范围挺复杂。
有三个范围： global, session, table。 而且都适用。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：304434次
积分：4103
积分：4103
排名：第6120名
原创：88篇
评论：158条
(4)(3)(2)(4)(1)(1)(2)(5)(4)(5)(8)(1)(1)(1)(1)(3)(3)(1)(1)(4)(2)(1)(3)(1)(1)(1)(1)(1)(1)(1)(1)(2)(1)(3)(1)(1)(1)(1)(3)(3)(1)(4)(6)(1)(1)博客访问： 678751
博文数量： 368
博客积分： 7872
博客等级： 少将
技术积分： 2155
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： LINUX
看到很多朋友对这些概念有些混淆，我来发表下我自己的观点。
内存表：1. 参数控制：max_heap_table_size2. 到达上线后报错。3. 表定义保存在磁盘上，数据和索引保存在内存里面。4. 不能包含TEXT,BLOB等字段。
临时表：1. 参数控制：tmp_table_size。2. 到达上线后创建文件在磁盘上。3. 表定义和数据都在内存里。4. 可以包含TEXT, BLOB等字段。
而且tmp_table_size和max_heap_table_size的范围挺复杂。
有三个范围： global, session, table。 而且都适用。
阅读(483) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。mysql（9）
mysql5.5性能优化-内存表
临时表与内存表
内存表分为2种，但共同点是，重起数据库以后，内存中的数据全部丢失,内存表的功能有部分的限制，有些属性不能像正常表一样使用，所以请大家使用的时候谨慎参照官方文档.下面只是抛砖引玉.&
1.临时表:表建在内存里，数据在内存里&
2.内存表:表建在磁盘里，数据在内存里&
其中包括2个重要的参数&
# 内存表容量&
max_heap_table_size=1024M&
# 临时表容量&
tmp_table_size=1024M&
建立内存表的时候，在5.5里，需要指定表的引擎类型 ENGINE=MEMORY&
CREATE TABLE coldtest_vardata (&
& Id int(11) AUTO_INCREMENT,&
& name varchar(255)&
) ENGINE=MEMORY DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;&
create temporary table tmp1(id int not null);&
其他常用参数&
# skip hostname,just use ip&
skip-name-resolve&
# auto start event&
event_scheduler=1
错误信息:Can't get hostname for your address&
修改配置文件&
在windows下面，文件时my.ini,在Linux下面是my.cnf&
解决方案是在&
skip-name-resolve & & & & & & & & & & &#加上这一个属性&
它将禁止 MySql Server 对外部连接进行 DNS 解析，使用这一选项可以消除 MySql 进行 DNS 解析的时间。
但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求。
可以这么理解mysql处理客户端解析的过程：
1，当 mysql client 发起连接请求时，MySql Server 会主动去查 client 的主机名。
2，首先查找Windows系统目录下 /etc/hosts 文件，搜索域名和IP的对应关系。
3，如果hosts文件没有，则查找DNS设置，如果没有设置DNS服务器，会立刻返回失败；如果设置了DNS服务器，就进行反向解析，直到timeout。
注意：如果开启 skip-name-resolve 选项，要确认 MySql 是否采用过主机名的授权，
在 mysql 中运行如下命令：
mysql& select user,host from mysql.user where host && 'localhost' ;
一般会得到以“%”授权（也就是任何地址）的记录：
+------------------+-------------+
| user & & & & & & | host & & & &|
+------------------+-------------+
| root & & & & & & | % & & & & & |
| user_sync | 192.168.0.113 |
如果有host名是什么“DB1”“DB2”的，那么删除授权表中有 hostanme 的记录，然后重启mysqld。
3。mysql JDBC&驱动常用的有两个，一个是gjt（Giant
Java Tree）组织提供的mysql驱动，其JDBC Driver名称（JAVA类名）为：org.gjt.mm.mysql.Driver
详情请参见网站：另一个是mysql官方提供的JDBC Driver，其JAVA类名为：com.mysql.jdbc.Driver
驱动下载网址：，进入其中的MySQL Connector/J区域下载。
mysql JDBC URL格式如下：
jdbc:mysql://[host:port]/[database][?参数名1][=参数值1][&参数名2][=参数值2]...
缺省值
最低版本要求
数据库用户名（用于连接数据库）
用户密码（用于连接数据库）
useUnicode
是否使用Unicode字符集，如果参数characterEncoding设置为gb2312或gbk，本参数值必须设置为true
characterEncoding
当useUnicode设置为true时，指定字符编码。比如可设置为gb2312或gbk
autoReconnect
当数据库连接异常中断时，是否自动重新连接？
autoReconnectForPools
是否使用针对数据库连接池的重连策略
failOverReadOnly
自动重连成功后，连接是否设置为只读
maxReconnects
autoReconnect设置为true时，重试连接的次数
initialTimeout
autoReconnect设置为true时，两次重连之间的时间间隔，单位：秒
connectTimeout
和数据库服务器建立socket连接时的超时，单位：毫秒。&0表示永不超时，适用于JDK 1.4及更高版本
socketTimeout
socket操作（读写）超时，单位：毫秒。0表示永不超时
对应中文环境，通常mysql连接URL可以设置为：
jdbc:mysql://localhost:3306/test?user=root&password=&useUnicode=true&characterEncoding=gbk&autoReconnect=true&failOverReadOnly=false
在使用数据库连接池的情况下，最好设置如下两个参数：
autoReconnect=true&failOverReadOnly=false
需要注意的是，在xml配置文件中，url中的&符号需要转义。比如在tomcat的server.xml中配置数据库连接池时，mysql jdbc url样例如下：
jdbc:mysql://localhost:3306/test?user=root&password=&useUnicode=true&characterEncoding=gbk
4. Mysql 的定时备份过程
&&mysql支持命令行导入导出数据文件，格式是*.sql&
mysql -u用户名 -p密码 数据库名 & 文件路径\文件名&
mysqldump -u用户名 -p密码 数据库名 & 文件路径\文件名&
3)真实案例&
本人是在windows 2003 开发服务器上，实现了定时自动备份&
一般文件名，用日期和时间.sql来使用.下面是我的*.bat批处理文件&
总共3行：&
第一行，表示切换到d盘&
第二行，找到mysql安装目录的bin目录，这样不需要设置环境变量&
第三行，导出*.sql文件，同时使用当前时间作为文件名，格式如下：cold_4.sql，表示cold数据库，在日，中午12点44分生成的备份文件&
cd D:\Program Files\MySQL\MySQL Server 5.1\bin&
mysqldump -uroot -p123456 cold && d:\mysql_data\cold_%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%.sql&
然后，将该批处理文件，加到windows任务计划中，&
点击 控制面板&任务计划&添加任务计划。&
顺便给出还原代码，还原的时候要首先手工建立数据库，默认情况下，是不会自动创建数据库的&
cd D:\Program Files\MySQL\MySQL Server 5.1\bin&
mysql -uroot -p cold & D:\mysql_data\cold_4.sql&
注：oracle的备份，也可以这么实现，跨平台也是如此，需要把脚本加到任务计划中来。&
5.mysql的性能优化使用技巧
在windows下,配置文件为%mysql_home%/my.ini
在linux下，配制文件为/f
一 性能优化
&&& 1--------INNODB_BUFFER_POOL_SIZE
&&&&该参数是innodb引擎的最主要的性能参数,对数据库的性能起了决定性作用.说白了就是数据库的使用内存.
&&& 2--------性能分析,
show status like '%'; #查看数据库状态
show variables like %; #查看数据库的变量
show engine innodb status\G; #查看innodb的监控状态
二 使用技巧
&&& 1--------字符集尽量使用uft-8,这样更容易解决乱码问题,在linux下修改my.cnf的3处,修改前后可以通过命令
show variables like 'character%';
查看字符集状态.
&&& 找到客户端配置[client] 在下面添加
&&& #默认字符集为utf8
&&& default-character-set=utf8
&&& 在找到[mysqld] 添加
&&& #默认字符集为utf8
&&& default-character-set=utf8
&&& #设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行
&&& init_connect='SET NAMES utf8'&
6.mysql统计数据 ，但是空的数据也要显示
&在实际中，有这样一个需求，就是需要统计24个小时的相关数据.有的朋友认为简单的一句group by就可以解决。其实不然,真正的统计,24个小时都得有数据的，不允许出现缺失的现象.其实这个有点像废话，处理结果集ResultSet也可以,但为了减少JAVA的负担和增强java代码的可读性,所以尽量用SQL语句，一次性把数据处理好.&
表结构,有三个字段一看就明白&
&&& 下面我想统计12个月的每月数据，如果数据存在，则显示数据;如果数据不存在，则现实我们约定的错误码(-601).下面只是给出了3条SQL语句提供样例.不管存不存在数据，肯定会查出3条记录来.&
&&& 在真正的开发中,要把其中前面的time和后面where里的time,用所查询的语句,动态组装.如果朋友们还不理解，就给我留言吧 ^-^&
7.mysql中动态sql语句的用法
在存储过程中，动态拼接一个字符串,然后执行之.&
8.mysql中的触发器
9.mysql中大幅度提高性能方案———分区表
工作中仍然是海量数据出现的情况.每年大概会有几亿条记录.而且数据的时效性比较强.但历史数据仍然要求保留.这个时候经过分析和研究,最终决定通过时间字段进行分区.下面是分区表的创建代码.读者门在插入了不同年份时间段以后，可以时间字段为条件进行查询，可以看到数据库扫描过的区段.&
分区查询,可以查看扫描过的区段.当然要加上where子句,以c3时间为条件进行检索.若不使用时间字段，分区将失去作用.&
10.mysql中海量数据统计处理，模拟物化视图
一 物化视图&
&&& 物化视图，它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。&
&&& 只有大型数据库oracle10g,db2才支持这个功能,而MySQL5.1暂时还没有这个功能。本人通过事件调度和存储过程模拟了物化视图。下面大家一起来讨论吧。^-^&
二 准备知识&
&&& 1)存储过程:玩过数据库的人，都知道他是啥~，~&
&&& 2)事件调度:在MySQL5.1开始才有的新功能。说白了就是个定时器。跟java里的timer差不多。
mysql默认不会启动event,所以需要修改你的my.ini或my.cnf[mysqld]的下面加入如下行
event_scheduler=1
三 模拟物化视图&
&&& 1)先建个基表吧。
&&& 2)设计视图&
我想查询所有18岁员工的数量。&
很简单，select count(*) from user where age=18;&
如果是传统概念的视图，在MySQL中，每次访问视图的时候，他都会创建个临时表，然后执行一次。在海量数据的情况下，这样的效率是非常低的。而物化视图，则他会定时去刷新这个临时表，而不是你在用的时候才会去刷新。并且物化视图的&临时表&是一直存在的。所以效率高出非常多。拿空间换时间^-^&
&&& 3)创建&物化视图&的表
&&& 4)设计存储过程
&&& 5)设计调度并执行，为了使实验明显，我就把调度设置成5秒一次吧。周期可以自己调节。
&&& 上面的实现，可能不太明显，但在海量数据处理的时候进行统计，性能有明显的提升。大家做实验的时候，可以增加一个insert语句，方便观察。在更新数据的时候，如果数据量大，则需要按下面步骤来处理&
1)delete 1条记录&
2)insert 1条记录&
&&& 如果一次性把数据全部删除，在查询的时候，有可能会出现空表的现象。而且会影响统计使用。&
&&& 通过利用这个事件调度，定时备份的事情也同时解决了.
&&& 建议大家配合表分区,索引同时使用,这样可以提高性能.
未完成:在更新表的时候，需要采用某种算法来执行，这样才能提高性能，而不是单纯的删除再插入.
11.mysql中按月统计数据
& &表finance有俩个字段如下&
date date&
money double(15,2)&
&&& 下面需要对表finance的2010年财务数据，按月进行统计&
&&& 下面是按周统计&
查看MySQL的manual&
%X Year for the week where Sunday is the first day of the week, numeric, used with %V&
%x Year for the week, where Monday is the first day of the week, numeric, used with %v&
1如果周一为一周的第一天,则(小写) DATE_FORMAT(date,'%x %v')&
2如果周日为一周的第一天,则(大写) DATE_FORMAT(date,'%X %V')&
12.mysql中数据的重复判断
工作中的实际应用.在采集数据的时候，要求数据采集以后，不能重复.同时也要求有多个实例同时运转，保证数据采集的连续性.因此总结了一下,做成了如下的小试验.核心代码如下:&
表结构:只有3个字段&
id,name,password&
2)不推荐，因为insert的时候select，会锁定select的表&
这个SQL语句的含义是，如果插入的数据 name='phl',password='123'不存在,则执行插入;&
方法1里面，之所以没有过滤。是因为没有建立name与password的联合主建;
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：43970次
排名：千里之外
原创：18篇
转载：130篇
评论：11条
(7)(5)(3)(9)(23)(1)(6)(1)(1)(3)(2)(2)(4)(4)(1)(2)(1)(15)(7)(4)(3)(4)(23)(12)(7)