关于mysql什么时候会生成磁盘下的临时表的有关问题

关于mysql什么时候会生成磁盘下的临时表的有关问题

关于mysql什么时候会生成磁盘上的临时表的问题

原文地址: http://unix-cd.com/vc/www/26/2011-05/17916.html

一、磁盘临时表与内存临时表的差异

  从磁盘临时表与内存临时表的差异中大家可以看到,磁盘临时表只是内存临时的一个替代品。这就好像操作系的虚拟内存一样。当内存不够用时,可以在硬盘上的一个空间作为其替代品,将内存中的部门数据转移到虚拟内存中。这个磁盘临时表也是相同的道理。

  但是这里需要注意的是,硬盘的效率与内存的效率是不同的。在执行相同的一个作业时,内存的性能要高于硬盘的性能,一般会高上百倍,甚至上千倍。从这里就可以看出,为了提高数据库系统的性能,我们最好选择内存临时表,而放弃使用磁盘临时表。

  二、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 一起使用可能会用到临时表




1:你的意思正确,大小超过了tmp_table_size 会使用 磁盘临时表,没操作则用内存临时表。

2:不冲突,这句话还有个条件,如果使用了BLOB和TEXT列,“并且需要隐式临时表时”,直接用磁盘临时表,不会用到内存。即使没有超过max_heap_table_size的限制<他们的类型决定的>,(varchar,char 都会用内存临时表)。

3:用explain查看,其实你可以在每次执行前 用 explain 分析下,要是让你使用临时表 会在extra里面现实出来的。

4:那就表示没有使用临时表。tmp_table_size设置为1024,这个设置的小了,后期明显会出现问题。

关于mysql什么时候会生成磁盘下的临时表的有关问题

相关文章:

你感兴趣的文章:

标签云: