mysql内部临时表

 51Testing软件测试网 w
U |Ce,H

内部临时表简单介绍

有时候数据库服务器在执行某些查询的时候会生成内部临时表，这些临时表有可能是生成在内存里的由MEMORY引擎处理的，也有可能是生成在磁盘上由MyISAM引擎处理的。如果说在内存中的临时表大小超过限制，服务器则会将临时表保存成磁盘临时表。用户无法直接控制这些内部临时表和管理这些临时表的数据库引擎。51Testing软件测试网)a&OP[p

Temporary tables can be created under conditions such as these:

内部临时表产生的时机有以下几种：51Testing软件测试网C3z6np%E9Y6` IT

1.使用ORDER BY 子句和一个不一样的 GROUP BY 子句（经过笔者实验，应该是GROUP BY一个无索引列,就会产生临时表），或者 ORDER BY 或 GROUP BY 的列不是来自JOIN语句序列的第一个表，就会产生临时表(经笔者实验，应该是使用JOIN时， GROUP BY 任何列都会产生临时表)51Testing软件测试网e6G K;XF)|i'x%[

 51Testing软件测试网hc$KxvZ

 51Testing软件测试网"L"a-}5CZ

2.DISTINCT 和 ORDER BY 一起使用时可能需要临时表（笔者实验是只要用了DISTINCT（非索引列），都会产生临时表）51Testing软件测试网-V\PY8ul\

3.用了SQL_SMALL_RESULT, mysql就会用内存临时表。

定义：SQL_BIG_RESULT/SQL_SMALL_RESULT可以使用GROUP BY或明显的告诉优化器有许多结果集行或很小。SQL_BIG_RESULT,MySQL直接使用基于磁盘的临时表如果需要,而更喜欢使用临时表排序关键组的元素。SQL_SMALL_RESULT,MySQL使用快速临时表来存储结果表而不是使用排序。这通常不应是必需的.51Testing软件测试网 R,d7a q)|/tO

可以用EXPLAIN来查看Extra字段判断是否使用了临时表

有些情况服务器会直接使用磁盘临时表

A.表里存在BLOB或者TEXT的时候(这是因为MEMORY引擎不支持这两种数据类型，这里笔者补充一下，并非只要查询里含有BLOB和TEXT类型的列就会产生磁盘临时表，按照高性能MYSQL里的话，应该这么说：“Because the Memory storage engine doesn't support the BLOB and TEXT types, queries that use BLOB or TEXT columns and need an implicit temporary tablewill have to use on-disk MyISAM temporry tables, even for only a few rows.”也就是说如果我们的查询中包含了BLOB和TEXT的列，而且又需要临时表，这时候临时表就被强制转成使用磁盘临时表，所以此书一直在提醒我们，如果要对BLOB和TEXT排序，应该使用SUBSTRING（column， length）将这些列截断变成字符串，这样就可以使用in-memory临时表了)51Testing软件测试网5P7FlW&W5Ly$j-r

B .GROUP BY 或者 DISTINCT 子句大小超过 512 Bytes

C .使用了UNION 或 UNION ALL 并且 SELECT 的列里有超过512 Bytes的列51Testing软件测试网-Pw5n'CA;M6P)Ejc

D.如果内置内存临时表创建后变得太大，MySQL会自动将它转换成磁盘临时表。内存临时表的大小取决与 tmp_table_size参数和max_heap_table_size参数的值。用 CREATE TABLE 产生的内存临时表的大小取决与 max_heap_table_size来决定是否要将其转换成磁盘临时表51Testing软件测试网E Z#G0M)I5v}#Z J,Uz

E .当服务器生成一个内存临时表，Created_tmp_tables状态变量值会增加，当服务器创建了一个磁盘临时表时，Created_tmp_disk_tables状态变量值会增加。(这几个变量可以通过 show status命令查看得到)

Tips:内部临时表的大小受限制的是tmp_table_size和max_heap_table_size的最小值；而 user-created temporary table的大小只受限与max_heap_table_size,而与tmp_table_size无关。以下是文档原文，注意粗体部分51Testing软件测试网d1u)f]7[#f0]

 tmp_table_size

内部的最大大小内存临时表。(实际限制最低oftmp_table_size和max_heap_table_size决定。)如果一个内存临时表超过极限,MySQL自动转换为一个磁盘上的MyISAM表。增加的价值tmp_table_size(如果必要andmax_heap_table_size)如果你许多先进的GROUP BY查询和你有很多的内存。这个变量并不适用于用户创建内存表。内部的最大大小内存临时表。(实际限制最低oftmp_table_size和max_heap_table_size决定。)如果一个内存临时表超过极限,MySQL自动转换为一个磁盘上的MyISAM表。增加的价值tmp_table_size(如果必要andmax_heap_table_size)如果你许多先进的GROUP BY查询和你有很多的内存。这个变量并不适用于用户创建内存表。51Testing软件测试网4N2jsF:t.N"oX7|

你可以比较的内部磁盘上的临时表创建内部创建的临时表的总数比较的值Created_tmp_disk_tables andCreated_tmp_tables变量。51Testing软件测试网7jV_g9GbK

 max_heap_table_size51Testing软件测试网;km|_K)Re

这个变量设置允许用户创建的内存表的最大尺寸增加。变量的值用于计算内存表MAX_ROWS值。设置此变量对任何existingMEMORY表没有影响,除非重新创建表的语句如用ALTER table创建表或改变或截断。服务器重启也将现有的内存表的最大大小设置为global max_heap_table_size值。也使用这个变量与tmp_table_size限制内部内存表的大小。51Testing软件测试网 AJe,q+R{#[

下面来说说filesort。什么是filesort？51Testing软件测试网o&Kzc,~z)s

以前理解是这样：“当行数据太大，导致内存无法容下这些数据产生的临时表时，他们就会被放入磁盘中排序。”  很不幸，这个答案是错的。大概非专业DBA技术人员都这样认为。首先，这个叫做Using temporary ；