你有没有想过MySQL的内部临时表中存储了什么？_1

MySQL临时表分为外部临时表和内部临时表两种。用户通过CREATETEMPORARYTABLE创建一个外部临时表。SQL语句执行过程中，MySQL自己创建了一个内部临时表。如果explain输出结果的Extra列中出现Usingtemporary，说明SQL语句执行过程中使用了内部临时表。为了描述方便，本文后面内容中的临时表和内部临时表含义相同，都是指MySQL在SQL语句执行过程中自己创建的临时表。1.准备本文使用了两个示例表：t_recbuf和t_internal_tmp_table。两个表的结构完全一样。t_recbuf的表结构如下：CREATETABLE`t_recbuf`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`i1`int(10)unsignedDEFAULT'0',`str1`varchar(32)DEFAULT'',`str2`varchar(255)DEFAULT'',`c1`char(11)DEFAULT'',`e1`enum('北京','上海','广州','深圳','天津','杭州','成都','重庆','苏州','南京','恰尔滨','沉阳','长春','厦门','福州','南昌','泉州','德清','长沙','武汉')DEFAULT'北京',`s1`set('eat','drink','play','music','clothes','food','living','walking','前后','左右','上下','里外','远近','长度','黑白','水星','金星','地球','火星','木星','土星','天王星','海王星','冥王星')DEFAULT'',`bit1`bit(8)DEFAULTb'0',`bit2`bit(17)DEFAULTb'0',`blob1`blob,`d1`decimal(10,2)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=2001DEFAULTCHARSET=utf8;2、哪些场景会用到临时表？MySQL使用临时表的场景有很多，下面列举其中的一些场景：orderby和groupby字段是不同的。join语句中orderby或groupby字段不属于执行计划中的第一个表。包含distinct关键字的聚合函数，例如：count(distincti1)、sum(distincti1)等。使用union或uniondistinct关键字的SQL语句。派生表（说明输出的select_type列的值为DERIVED）。子查询半连接物化（将子查询结果保存在临时表中，然后与主查询进行join连接）。子查询物化（半连接物化以外的场景，如非关联子查询、半连接去重等）。insert...select语句的源表和目标表是同一张表，例如：insertintot_recbuf(i1,str1)selecti1,str1fromt_recbuf)。上面列出的场景是基于官方文档做的一些改动。您不必担心您是否记住了上述场景。这是判断SQL语句是否使用临时表最方便快捷的方法。查看执行计划是最方便快捷的方式。只要在explain输出的Extra列中出现Usingtemporary，就说明使用了临时表。3、临时表用的是哪个存储引擎？MySQL临时表可以选择3种存储引擎：MEMORY、MyISAM、InnoDB。MEMORY是内存引擎，数据和索引都存储在内存中；MyISAM和InnoDB是磁盘存储引擎，数据和索引都存储在磁盘中。SQL执行过程中，如果需要使用临时表，MySQL默认使用MEMORY存储引擎。有两个条件会影响MySQL的默认行为。如果满足以下2个条件中的任何一个，临时表将使用MyISAM或InnoDB存储引擎。情况1，写入临时表的字段包含大对象（BLOB）字段。关于哪些类型的字段属于大对象，可以看看这篇文章：MySQL大对象（BLOB）与字符串double技术。情况2，系统变量big_tables的值为ON，也就是说如果要使用临时表，必须使用MyISAM或者InnoDB存储引擎。但是在big_tables=ON的前提下，如果能确定某条SQL语句写入临时表的数据会很小，MEMORY存储引擎完全够用，可以对单条SQL语句进行特殊处理.在SQL语句中加入SQL_SMALL_RESULT提示，告诉MySQL：我只需要MEMORY存储引擎。SQL_SMALL_RESULT的用法是这样的：selectSQL_SMALL_RESULT*fromt_recbuf前面已经介绍过了。MySQL如何选择内存和磁盘存储引擎？如果MySQL决定使用磁盘存储引擎，使用MyISAM还是InnoDB？这个选择很简单。如果系统变量internal_tmp_disk_storage_engine的值为MyISAM，则选择MyISAM存储引擎，如果值为InnoDB，则使用InnoDB存储引擎。internal_tmp_disk_storage_engine的值只能从MyISAM和InnoDB中选择，默认为InnoDB。选择存储引擎4.内存临时表变成磁盘临时表。MEMORY存储引擎表的记录是定长的，不支持大对象（BLOB）字段。变长类型的字段（VARCHAR、VARBINARY）也会按照定义的最大长度存储，实际上相当于CHAR和BINARY字段。内存临时表插入记录占用的空间加上待插入记录占用的空间，如果超过阈值，临时表的存储引擎将从内存存储引擎变为磁盘存储引擎。内存占用超过阈值临时表占用内存空间的阈值由系统变量tmp_table_size和max_heap_table_size中较小的一个决定。tmp_table_size的默认大小为16M，最小可以设置为1K，最大值为超大值。max_heap_table_size默认为16M，最小可以设置为16K，最大也是一个超级巨大的值。由于MEMORY引擎的固定记录长度，判断临时内存表占用的空间是否超过阈值非常简单。将临时表存储引擎改为磁盘存储引擎的过程如下：创建一个MyISAM或InnoDB临时表，选择哪种存储引擎由internal_tmp_disk_storage_engine控制。将内存临时表中的所有记录一一复制到磁盘临时表中。将原计划插入到内存临时表但还没有插入到磁盘临时表中的记录插入。删除内存临时表。直到内存临时表满了才创建磁盘临时表，比如创建磁盘临时表，代价太大。如果我们一开始就知道执行SQL语句需要临时表，而内存临时表不能存放那么多记录，直接告诉MySQL使用磁盘临时表岂不是可以节省很多开销桌子？是的，如果我们一开始就知道SQL语句中数据量大会导致使用临时磁盘表，在SQL语句中添加SQL_BIG_RESULT（这个在MySQL中叫做hint），在MySQL选择存储引擎的时候对于临时表，它会直接选择磁盘存储引擎。SQL_BIG_RESULT的用法是这样的：selectSQL_BIG_RESULTe1,min(i1)fromt_internal_tmp_tablegroupbye1如果我们在SQL语句中加入SQL_BIG_RESULT提示，查询优化器通过使用磁盘临时表评估执行成本，它也可能来到使用磁盘临时表如果对t_internal_tmp_table表中的记录进行排序后成本高于groupby的成本，则会选择先对t_internal_tmp_table表中的记录进行排序，然后对排序后的记录进行groupby操作,所以首先不需要内存临时表和磁盘临时表。5.哪些字段写入临时表？从哪些字段写入临时表来看，临时表可以分为两类：为整个SQL语句服务的临时表。为单个聚合函数提供服务的临时表。对于为整个SQL语句服务的临时表，在SQL语句执行过程中，需要将存储引擎返回给Server层的字段写入到临时表中。写入临时表的字段内容可以是字段值，也可以是根据字段值计算函数的结果。以两条SQL为例进行说明。selecte1,count(i1)fromt_internal_tmp_tablegroupbye1ExampleSQL1.SQL执行过程中，MySQL会将t_internal_tmp_table表的e1字段值和count(i1)的计算结果写入临时表。selecta.e1,b.c1,count(a.i1)astfromt_internal_tmp_tableasainnerjoint_recbufasbona.id=b.idgroupbya.e1,b.c1withrollup示例SQL2，由于汇总存在，聚合函数的计算结果不能写入临时表，但是聚合函数的参数中的字段值应该写入临时表。SQL执行过程中，MySQL会将t_internal_tmp_table表的e1、i1字段值和t_recbuf表的c1字段值写入临时表。将t_internal_tmp_table和t_recbuf两张表的连接查询得到的记录全部写入临时表后，对临时表中的记录进行groupby和聚合（count）操作。对于为单个聚合函数服务的临时表，SQL语句执行过程中只会将聚合函数中的字段写入临时表。下面以一条SQL为例进行说明。selecte1,count(distincti1)astfromt_internal_tmp_tablegroupbye1ExampleSQL3，临时表只对count(distincti1)中的i1字段进行去重，所以只会将t_internal_tmp_table表的i1字段值写入临时表，并且会在临时表中为i1字段创建唯一索引，对i1字段进行去重。6.索引了哪些字段？MySQL使用临时表，可能是为了groupby分组和聚合，也可能是为了记录的去重（distinct），或者只是为了存储子查询的执行结果，避免重复执行子查询。对于groupby和distinct，为了保证临时表中的一个groupbygroup只有一条记录，distinct字段只保留一条内容相同的记录，并会为对应的创建唯一索引临时表中的字段。非常重要的注意事项：临时表中最多会有一个索引，要么用于groupby，要么用于distinct。6.1groupbyselecte1,count(i1)fromt_internal_tmp_tablegroupbye1这是上一节（5.哪些字段写入临时表？）的例子SQL1，写入e1字段值和count(i1)的计算在临时表结果中（每个组中i1字段的值不为NULL的记录数）。为了保证e1字段的每个值在临时表中只有一条记录，MySQL会为e1字段创建一个唯一索引，索引名称为。临时表的e1字段存在唯一索引是为了保证每组记录的唯一性。保证唯一性的过程如下：Step1.从t_internal_tmp_table表中读取一条记录后，以该记录的e1字段值作为查询条件，查询临时表中是否存在对应的记录。步骤2，如果e1字段值对应的记录在临时表中已经存在，则执行count(i1)函数获取当前组的新计数，然后将组的新计数更新到临时表中。第3步，如果e1字段值对应的记录在临时表中不存在，则执行count(i1)函数初始化组计数，然后将e1字段值和组计数插入到临时表中。执行流程示意图6.2distinctselecte1,count(distincti1)astfromt_internal_tmp_tablegroupbye1这是上一节（5.哪些字段写入临时表？）中的例子SQL3，和例子的区别SQL1为count()函数多了一个distinct，意思是统计每组中不同的i1字段值（不包括NULL）的个数。写入临时表的字段只有i1。为了保证临时表每组中i1字段的值是唯一的，MySQL会为i1字段创建唯一索引，索引名称为。distinct唯一索引的名字好像有点模糊，源码上说以后会改成。为保证每个组中i1字段的唯一性，执行过程如下：前言，在向临时表写入数据之前，MySQL已经读取了t_internal_tmp_table表中的记录，并按照e1字段进行了排序。第一步，读取一条已经排序好的记录，将i1字段的值写入临时表（如果i1字段的值为NULL，则不写入）。如果写入成功，说明临时表中没有i1字段值对应的记录。如果写入失败，说明临时表中已经有对应i1字段值的记录。这时会忽略写失败的错误，因为这正是我们想要的：对i1字段的值进行去重。插入操作直接利用了唯一索引中记录不能重复的特性。虽然有点简单粗暴，但也方便快捷。步骤2：判断步骤1读取的记录的e1字段值是否与上一条记录的e1字段值相同。如果相同，说明同组，回到步骤1继续执行，将当前组中下一条记录的i1字段值写入临时表。如果不相同，则表示本组结束，转步骤3处理组结束逻辑。第三步：获取临时表中的记录条数，即组中i1字段值不为NULL且已经去重的条数，发送给客户端。在这里获取临时表的记录数非常方便。统计时不需要扫描临时表中的所有记录，而是直接读取临时表的统计信息（stats.records）。第四步：分组后的数据发送给客户端后，清空临时表中的所有记录，为下次分组时将i1字段值写入临时表做准备。执行过程示意图6.3hash字段为groupby和distinct字段建立唯一索引，可以保证临时表中记录的唯一性，看起来很完美。然而，世界上总有例外。存储引擎对索引的字段数、单个字段的长度、索引记录的长度都有限制。一旦超过限制，索引的创建就会失败，无法为groupby和distinct字段创建unique字段。索引。存储引擎有限，无法为groupby和distinct字段创建唯一索引，那么这两种情况下如何保证记录的唯一性呢？别担心，你永远可以相信MySQL有大动作。如果由于溢出问题，无法为groupby和distinct字段建立唯一索引，MySQL会在临时表中添加一个hash字段（字段名），并为该字段创建一个非唯一索引（因为不同内容是计算出的哈希值可能会重复）。字段值可能会重复，那么如何保证临时表中记录的唯一性呢？过程如下：第一步，在向临时表插入记录之前，计算字段的值，计算过程如下：计算groupby和distinct各字段的hash值，然后计算所有字段的哈希值，作为字段值。第二步，以第一步计算出的字段值作为查询条件，在临时表中查找记录。Step3，如果在临时表中没有找到记录，说明该记录不存在，进行插入操作。第四步，如果在临时表中找到记录，则读取该记录（存放在table->record[1]中）。这时候不能说明表中存在groupby和distinct字段对应的记录，因为hash值可能重复。第五步，将groupby或distinct中的字段与第四步读取的记录中的对应字段一一进行比较。如果有任何一个字段值不相等，则说明临时表中不存在groupby和distinct字段对应的记录，执行insert操作。如果所有字段值都相等，说明临时表中已经存在groupby和distinct字段对应的记录。对于groupby，更新临时表中对应的记录；对于distinct，可以忽略要插入的记录，不需要插入操作。groupbydistinct的执行流程示意图执行流程示意图7.内部临时表使用情况统计MySQL每创建一个临时表，状态变量created_tmp_tables的值就加1。临时表的存储引擎为从MEMORY中替换为MyISAM或InnoDB，状态变量created_tmp_disk_tables的值增加1。created_tmp_disk_tables除以created_tmp_tables得到的结果越大，表示创建的临时表中磁盘临时表的比例越高。减少内存临时表转换为磁盘临时表有两种可能的优化方案：减少内存临时表转换为磁盘临时表的比例：修改系统变量tmp_table_size和max_heap_table_size的值，使临时表可以使用更多内存并减少此转换。强制临时表使用磁盘存储引擎：如果业务类型比较特殊，临时表的数据必然很大。如果提高临时表占用内存阈值效果不明显，可以将系统变量big_tables的值设置为ON。强制内部临时表使用磁盘存储引擎可以避免不必要的内存临时表到磁盘临时表的转换。8.总结第2节，列出MySQL使用临时表的一些场景。这些场景反正是记不住的，就不用记了。了解他们很好。了解了临时表的用途和SQL语句的执行过程后，大致可以推断出是否会使用到临时表，然后结合explain查看执行计划就知道结果了。第3节介绍了临时表默认的存储引擎是MEMORY。如果写入临时表的字段包含大对象（BLOB）字段，或者系统变量big_tables的值为ON，则使用MyISAM或InnoDB作为临时表的存储引擎。第4节介绍当内存临时表占用大于tmp_table_size和max_heap_table_size中较小的值时，内存临时表将被替换为磁盘临时表。如果要指定单个SQL语句直接使用磁盘临时表，可以在SQL语句中添加SQL_BIG_RESULT提示。第5节描述哪些字段将写入临时表。对于groupby，存储引擎返回给server层的所有字段都会写入临时表，写入临时表的字段内容可能是字段值，也可能是基于聚合函数计算的结果字段值；对于distinct，临时表将写入聚合函数中的字段。第6节介绍了临时表中的groupby和distinct字段会建立唯一索引。如果groupby或distinct索引字段个数、单个字段长度、索引记录长度超过限制，将不会建立唯一索引，会在临时创建一个名为的字段到表中，并在该字段上建立非唯一索引。第七节介绍了created_tmp_tables和created_tmp_disk_tables这2个系统变量可以用来查看MySQL临时表的使用情况，通过调整tmp_table_size、max_heap_table_size和big_tables这3个系统变量可以减少或避免将内存临时表转为磁盘临时表。