临时表要舍磁盘临时表取内存临时表

　【IT168 技术】临时表是系统采取某些作业时所需要用到的一些临时数据。根据其存储的形态不同，可以分为磁盘临时表和内存临时表。在系统参数中，有MAX_HEAP_SIZE和TMP_TABLE_SIZE两个参数来控制临时表的大小。当临时数据超过这两个参数的规定时，系统就会将内存临时表转换为磁盘临时表。这也就是说，磁盘临时表是内存临时表的一个替代品。

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

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

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

　　二、BLOB和TEXT数据类型与临时表的关系

　　在讨论如何来取磁盘临时表舍内存临时表这个话题时，我认为有必要先谈谈BLOB和TEXT这两个数据类型。这两个数据类型都用来存储大容量的数据。前者是采用二进制的形式来保存，而后者是采用字符形式来保存。

　　这两个数据类型与其他数据类型有本质的不同。在MYSQL数据库中，是将这两个数据类型当做有实体的对象来处理。存储引擎也会采用特别的方式来保存他们。BLOB数据类型是采用二进制的方式来存储数据。而采用二进制来存储数据时，系统没有字符集的要求，也不会设置排序规则。相反，TEXT采用字符形式来存储数据，为此有字符集和排序规则的限制。

　　这两种数据类型，跟今天要谈到的临时表有什么关系呢?其实很有关系。因为这两种数据类型的容量比较大，为此对对这些类型的字段进行操作时，临时表就会一下子变得很大。此时就很容易超过上面两个参数的限制。系统就会将内存临时表转换为磁盘临时表。为此这两种数据类型会增加产生磁盘临时表的几率。

　　三、项目建议

　　采用磁盘临时表会在很大程度上降低数据库的性能开销。为此在实际工作中要尽量的避免。那么该如何来限制这个磁盘临时表的数量呢?这是一个很复杂的话题。在这里，我只结合自己的工作经验，谈谈一些经验心得。大家在实际工作中，可以尝试着试一下，看看是否有效。

　　一是不同的存储类型对于数据类型的支持力度是不同的。如果某种存储类型不支持某些数据类型，那么系统就会直接采用磁盘临时表，即使数据没有超过其规定的大小。简单的说，就是对于存储引擎，如果其不支持某些数据类型，那么对这些数据类型进行操作时，系统只能够使用磁盘临时表，而不能够使用磁盘临时表。如对于Memory存储引擎来说，其不支持BLOB和TEXT数据类型。在系统运行中，如果使用了BLOB和TEXT列，并且需要隐式临时表时，查询将不会使用内存临时表，而直接采用磁盘临时表。即使两个数据类型中的列存储的数据不多，也是如此。显然这会大大的降低数据库的性能。

　　二是尽可能的避免使用BLOB和TEXT数据类型。因为这两种数据类型存储的数据比较大，而且某些存储引擎并不支持这两种数据类型，为此在实际工作中，最好尽量避免使用这两种数据类型。如果真的需要使用，那么也需要采取一些措施来避免其带来的负面影响。如只在特定的情况下(用户特别指定时)，前台客户端才调用这两个数据对象。另外，从数据库角度出发，也可以采取一些措施。如数据库管理员可以采用ORDER BY SUBSTRING子句，将这些值转换为字符串，他某些存储引擎可以使用内存中的临时表。RDER BY SUBSTRING这个子句，顾名思义，是对这两个数据类型的记录进行排序。不过因为其内容太长，往往无法对某个字段的全部的值进行比较，然后进行排序。此时为了提高排序的效率，往往是采用SUBSTRING关键字来限制，只比较某几位的值。为了保证其数据量不超过内存临时表的限制值，此时要确保使用的子字符串足够的短，不能够让临时表变得很大。就是说，SUBSTRING关键字的参数要短一点。否则的话，容量足够大时，系统还是会将内存临时表转换为磁盘临时表。

　　四、MAX_HEAP_SIZE和TMP_TABLE_SIZE参数

　　在文章一开头，笔者就谈到MAX_HEAP_SIZE和TMP_TABLE_SIZE这两个参数。这是用来控制磁盘临时表和内存临时表的一把钥匙。当临时表的容量超过这两个参数的限制时，系统就会将内存临时表转换为磁盘临时表。那么有些用户就会考虑，直接将MAX_HEAP_SIZE和TMP_TABLE_SIZE这两个参数设置为最大就可以了。却是，调整这两个参数，能够达到取内存临时文件、舍磁盘临时文件的目的。但是这往往是有限制的。

　　因为内存的容量是有限制的，而且这个内存不光光是MYSQL数据库在使用。内存是服务器上各个服务所共用的。像操作系统、杀毒软件以及其他应用服务，都需要用到。此时系统管理员就需要考虑，系统资源在各个服务之间的分配，包括内存。通常情况下，如果一台服务器上部署有多种服务，如数据库、ERP、文件服务器等等，系统管理员就会设置各个服务可以使用的最大内存数量，以防止不同应用之间相互的干扰。

　　为此在调整MAX_HEAP_SIZE和TMP_TABLE_SIZE这两个参数时，数据库管理员需要考虑，调整这两个参数之后，对其他服务的影响。至少提高这两个参数的值之后，不能够对其他的应用程序产生不利的影响。

　　在实际项目中，一般建议调整MAX_HEAP_SIZE和TMP_TABLE_SIZE这两个参数要慎重行事。在调整之前，需要先对服务器内存的使用做一段时间的追踪。然后评估各种服务(包括数据库和其他应用程序)所占用的内存。然后根据其最大峰值时剩余内存的情况，来调整这两个参数的值。

　　如果这么操作觉得麻烦的话，那么笔者就建议，在调整参数之前，先升级内存。给服务器加一条新的内存，或者提升现有内存的大小，然后将增加的内存的一部分作为临时文件的存储区域。这也是可行的。不过一般在这么操作时，建议系统管理员先根据服务器上现有的服务，重新调整各个应用程序可以使用的内存。然后数据库管理员再相应的调整临时文件所能够占用内存的大小。通过在这种顺序来进行配置的话，比较保险、不容易出错。

　　最后需要说明的是，磁盘临时文件我们只能够采取措施去尽量的避免，而不能够完全的舍弃。系统中也没有相关的参数说不能够使用磁盘文件。某些作业(如对某个大表进行排序)等等，其容量足够大时，还是要能够允许系统采用磁盘临时表。不然的话，前端应用程序就会出错。在遇到这种作业时，一般可以在前台进技巧性的设置。如在应用程序开发时，开发人员根据经验判断某个作业可能会用到磁盘临时表。此时用户的等待时间会比较长。那么在应用程序开发时，就可以在前台客户端加一个控制标签，可以让用户选择让这个作业在后台运行。结果出来后再反馈给用户。这也是避免磁盘临时表负面影响的一个常用措施。