关于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%'显示出使用了磁盘上的临时表

[rucypli]

(1)"这两种数据类型会增加产生磁盘临时表的几率"
这意思是说有这两种类型有很多sql情况会产生临时表
(2), "而直接采用磁盘临时表。即使两个数据类型中的列存储的数据不多"
比如unin的时候必然产生临时表
（3）select * from t1 where id in (select id from t2)
这个不产生临时表


可是还是没有在show status like '%tmp%'显示出使用了磁盘上的临时表
转瞬即逝的吧

[rucypli]

临时文件大家都不陌生，就是为了各种不同的目的，产生的中间文件。使用完毕后会被及时
的回收和清理。临时表也是如此，它是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，这个设置的小了，后期明显会出现问题。

[北风之神]

谢谢大家

[repls]

1. "另外还想问一个问题 select * from t1 where id in (select id from t2)-----------这里子查询生成的结果集是不是放在临时表中的, 还是结果集本身就是一个独立的不同于临时表的概念???"

这个应该不需要临时表吧，因为我完全可以select di from t2这个子查询取出一条记录，然后去探测外面的查询。

2. “(1)"这两种数据类型会增加产生磁盘临时表的几率"--------------------------------这里的意思似乎是使用text与blob并不一定会生成磁盘上的临时表, 而是大小超过了tmp_table_size才使用临时表”

你的表里面包含text/blob字段不一定会使用临时表，除非你使用了text/blob字段，比如select blob_column，也就是说查询中有text/blob字段才会一定使用临时表，因此文章里面就说这两者类型会增大使用临时表的概率，因为你既然表中有text/blob字段，那一定有它的价值，也就有使用它的时候，那时候就必须建临时表了

3. (2), "而直接采用磁盘临时表。即使两个数据类型中的列存储的数据不多"--------------------似乎又与第一条的tmp_table_size相冲突
在第二点我已经说了，查询中使用了text/blob字段一定会使用临时表，但是有一个问题你的注意一下：临时表类型默认是memory的，而memory引擎的字段不支持类型，因此也就自动转化为myisam类型存储在磁盘上了，在文章里作者称为磁盘临时表。所以你即使一个表里只有一行数据，但因为有blob/text字段那么也是会在磁盘上创建临时表的。

希望你能理解

[repls]

1. "另外还想问一个问题 select * from t1 where id in (select id from t2)-----------这里子查询生成的结果集是不是放在临时表中的, 还是结果集本身就是一个独立的不同于临时表的概念???"

这个应该不需要临时表吧，因为我完全可以select di from t2这个子查询取出一条记录，然后去探测外面的查询。

2. “(1)"这两种数据类型会增加产生磁盘临时表的几率"--------------------------------这里的意思似乎是使用text与blob并不一定会生成磁盘上的临时表, 而是大小超过了tmp_table_size才使用临时表”

你的表里面包含text/blob字段不一定会使用临时表，除非你使用了text/blob字段，比如select blob_column，也就是说查询中有text/blob字段才会一定使用临时表，因此文章里面就说这两者类型会增大使用临时表的概率，因为你既然表中有text/blob字段，那一定有它的价值，也就有使用它的时候，那时候就必须建临时表了

3. (2), "而直接采用磁盘临时表。即使两个数据类型中的列存储的数据不多"--------------------似乎又与第一条的tmp_table_size相冲突
在第二点我已经说了，查询中使用了text/blob字段一定会使用临时表，但是有一个问题你的注意一下：临时表类型默认是memory的，而memory引擎的字段不支持类型，因此也就自动转化为myisam类型存储在磁盘上了，在文章里作者称为磁盘临时表。所以你即使一个表里只有一行数据，但因为有blob/text字段那么也是会在磁盘上创建临时表的。

希望你能理解

[repls]

1. "另外还想问一个问题 select * from t1 where id in (select id from t2)-----------这里子查询生成的结果集是不是放在临时表中的, 还是结果集本身就是一个独立的不同于临时表的概念???"

这个应该不需要临时表吧，因为我完全可以select di from t2这个子查询取出一条记录，然后去探测外面的查询。

2. “(1)"这两种数据类型会增加产生磁盘临时表的几率"--------------------------------这里的意思似乎是使用text与blob并不一定会生成磁盘上的临时表, 而是大小超过了tmp_table_size才使用临时表”

你的表里面包含text/blob字段不一定会使用临时表，除非你使用了text/blob字段，比如select blob_column，也就是说查询中有text/blob字段才会一定使用临时表，因此文章里面就说这两者类型会增大使用临时表的概率，因为你既然表中有text/blob字段，那一定有它的价值，也就有使用它的时候，那时候就必须建临时表了

3. (2), "而直接采用磁盘临时表。即使两个数据类型中的列存储的数据不多"--------------------似乎又与第一条的tmp_table_size相冲突
在第二点我已经说了，查询中使用了text/blob字段一定会使用临时表，但是有一个问题你的注意一下：临时表类型默认是memory的，而memory引擎的字段不支持类型，因此也就自动转化为myisam类型存储在磁盘上了，在文章里作者称为磁盘临时表。所以你即使一个表里只有一行数据，但因为有blob/text字段那么也是会在磁盘上创建临时表的。

希望你能理解