MySQL进修之暂时表相干总结【MySQL教程】,mysql,临时表
综述
正确的说，我们常说的暂时表分为两种，一种真的是表，用来存储用户发送的数，读写走的是表读写接口，读写的时刻表一定在文件体系上存在，别的一种，应该是一种暂时文件，用来存储SQL盘算中心历程的数据，读写走的是文件读写接口，读写的时刻文件能够已被删除了，留一个文件句柄举行操纵。
相干教程：mysql视频教程
暂时表
暂时表能够分为磁盘暂时表和内存暂时表，而暂时文件，只会存在于磁盘上，不会存在于内存中。详细来说，暂时表的内存形状有Memory引擎和Temptable引擎，重要区别是对字符范例(varchar, blob，text范例)的存储体式格局，前者不论现实字符若干，都是用定长的空间存储，后者会用变长的空间存储，如许进步了内存中的存储效力，有更多的数据能够放在内存中处置惩罚而不是转换成磁盘暂时表。Memory引擎从初期的5.6就可以够运用，Temptable是8.0引入的新的引擎。别的一方面，磁盘暂时表也有三种形状，一种是MyISAM表，一种是InnoDB暂时表，别的一种是Temptable的文件map表。个中末了一种体式格局，是8.0供应的。
在5.6以及之前的版本，磁盘暂时表都是放在数据库设置的暂时目次，磁盘暂时表的undolog都是与平常表的undo放在一同(注重由于磁盘暂时表在数据库重启后就被删除了，不须要redolog经由过程奔溃恢复来保证事件的完整性，所以不须要写redolog，然则undolog照样须要的，由于须要支撑回滚)。
在MySQL 5.7后，磁盘暂时表的数据和undo都被自力出来，放在一个零丁的表空间ibtmp1内里。之所以把暂时表自力出来，重如果为了削减建立删除表时保护元数据的开支。
在MySQL 8.0后，磁盘暂时表的数据零丁放在Session暂时表空间池(#innodb_temp目次下的ibt文件)内里，暂时表的undo放在global的表空间ibtmp1内里。别的一个大的革新是，8.0的磁盘暂时表数据占用的空间在衔接断开后，就可以开释给操纵体系，而5.7的版本中须要重启才开释。
现在有以下两种状况会用到暂时表：
用户显式建立暂时表
这类是用户经由过程显式的实行敕令create temporary table建立的表，引擎的范例要么显式指定，要么运用默许设置的值(default_tmp_storage_engine)。内存运用就遵照指定引擎的内存管理体式格局，比方InnoDB的表会先缓存在Buffer Pool中，然后经由过程刷脏线程写回磁盘文件。
在5.6中，磁盘暂时表位于tmpdir下，文件名相似#sql4d2b_8_0.ibd，个中#sql是牢固的前缀，4d2b是历程号的十六进制示意，8是MySQL线程号的十六进制示意(show processlist中的id)，0是每一个衔接从0最先的递增值，ibd是innodb的磁盘暂时表(经由过程参数default_tmp_storage_engine掌握)。在5.6中，磁盘暂时表建立好后，对应的frm以及引擎文件就在tmpdir下建立终了，能够经由过程文件体系ls敕令检察到。在衔接封闭后，响应文件自动删除。因而，我们假如在5.6的tmpdir内里看到许多相似花样文件名，能够经由过程文件名来推断是哪一个历程，哪一个衔接运用的暂时表，这个技能在排查tmpdir目次占用过量空间的题目时，特别实用。用户显式建立的这类暂时表，在衔接开释的时刻，会自动开释并把空间开释回操纵体系。暂时表的undolog存在undo表空间中，与平常表的undo放在一同。有了undo回滚段，用户建立的这类暂时表也能支撑回滚了。
在5.7中，暂时磁盘表位于ibtmp文件中，ibtmp文件位置及大小掌握体式格局由参数innodb_temp_data_file_path掌握。显式建立的表的数据和undo都在ibtmp内里。用户衔接断开后，暂时表会开释，然则仅仅是在ibtmp文件内里标记一下，空间是不会开释回操纵体系的。假如要开释空间，须要重启数据库。别的，须要注重的一点是，5.6能够在tmpdir下直接看到建立的文件，然则5.7是建立在ibtmp这个表空间内里，因而是看不到详细的表文件的。假如须要检察，则须要检察INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO这个表，内里有一列name，这里能够看到表名。定名规格与5.6的相似，因而也能够疾速找到占用空间大的衔接。
在8.0中，暂时表的数据和undo被进一步离开，数据是寄存在ibt文件中(由参数innodb_temp_tablespaces_dir掌握)，undo依旧寄存在ibtmp文件中(依旧由参数innodb_temp_data_file_path掌握)。寄存ibt文件的叫做Session暂时表空间，寄存undo的ibtmp叫做Global暂时表空间。这里引见一下这个寄存数据的Session暂时表空间。Session暂时表空间，在磁盘上的表现是一组以ibt文件组成的文件池。启动的时刻，数据库会在设置的目次下从新建立，封闭数据库的时刻删除。启动的时刻，默许会建立10个ibt文件，每一个衔接最多运用两个，一个给用户建立的暂时表用，别的一个给下文形貌的优化器建立的隐式暂时表运用。固然只要在须要暂时表的时刻，才会建立，假如不须要，则不会占用ibt文件。当10个ibt都被运用完后，数据库会继承建立，最多建立四十万个。当衔接开释时刻，会自动把这个衔接运用的ibt文件给开释，同时接纳空间。假如要接纳Global暂时表空间，依旧须要重启。然则由于已把寄存数据的文件分离出来，且其支撑动态接纳(即衔接断开即开释空间)，所以5.7上搅扰人人多时的空间占用题目，已得到了很好的减缓。固然，照样有优化空间的，比方，空间须要在衔接断开后，才开释，而理论上，许多空间在某些SQL(如用户drop了某个显式建立的暂时表)实行后，即能够开释。别的，假如须要检察表名，依旧检察INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO这个表。须要注重的是，8.0上，显式暂时表不能是紧缩表，而5.6和5.7能够。
优化器隐式建立暂时表
这类暂时表，是数据库为了辅佐某些庞杂SQL的实行而建立的辅佐表，是不是须要暂时表，平常都是由优化器决议。与用户显式建立的暂时表直接建立磁盘文件差别，假如须要优化器以为SQL须要暂时表辅佐，会先运用内存暂时表，假如凌驾设置的内存(min(tmp_table_size, max_heap_table_siz))，就会转化成磁盘暂时表，这类磁盘暂时表就相似用户显式建立的，引擎范例经由过程参数internal_tmp_disk_storage_engine掌握。平常轻微庞杂一点的查询，包含且不限于order by, group by, distinct等，都邑用到这类隐式建立的暂时表。用户能够经由过程explain敕令，在Extra列中，看是不是有Using temporary如许的字样，假如有，就一定要用暂时表。
在5.6中，隐式暂时表依旧在tmpdir下，在庞杂SQL实行的历程当中，就可以看到这暂时表，一旦实行完毕，就被删除。值得注重的是，5.6中，这类隐式建立的暂时表，只能用MyISAM引擎，即没有internal_tmp_disk_storage_engine这个参数能够掌握。所以，当我们的体系中只要innodb表时，也会看到MyISAM的某些目标在更改，这类状况下，平常都是隐式暂时表的缘由。
在5.7中，隐式暂时表是建立在ibtmp文件中的，SQL完毕后，会标记删除，然则空间依旧不会返还给操纵体系，假如须要返还，则须要重启数据库。别的，5.7支撑参数internal_tmp_disk_storage_engine，用户能够挑选InnoDB或许MYISAM表作为磁盘暂时表。
在8.0中，隐式暂时表是建立在Session暂时表空间中的，即与用户显式建立的暂时表的数据放在一同。假如一个衔接第一次须要隐式暂时表，那末数据库会从ibt文件组成的池子中掏出一个给这个衔接运用，直到衔接开释。上文中，我们也提到过，在8.0中，用户显式建立的暂时表也会从池子中分派一个ibt来运用，每一个衔接最多运用两个ibt文件用来存储暂时表。我们能够查询INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES来肯定ibt文件的去处。这个表中，每一个ibt文件是一行，当前体系中有几个ibt文件就有几行。有一列叫做ID，假如此列为0，示意此ibt没有被运用，假如非0，示意被此ID的衔接在用，比方ID为8，则示意process_id为8的衔接在用这个ibt文件。别的，另有一列purpose，值为INTRINSIC示意是隐式暂时表在用这个ibt，USER则示意是显现暂时表在用。另外，另有一列size，示意当前的大小。用户能够查询这个表来肯定全部数据库暂时表的运用状况，非常轻易。
在5.6和5.7中，内存暂时表只能运用Memory引擎，到了8.0，多了一种Temptable引擎的挑选。Temptable在存储花样有采用了变长存储，能够节约存储空间，进一步进步内存运用率，削减转换成磁盘暂时表的次数。假如设置的磁盘暂时表是InnoDB或许MYISAM，则须要一个转换拷贝的斲丧。为了尽量削减斲丧，Temptable提出了一种overflow机制，即假如内存暂时表凌驾设置大小，则运用磁盘空间map的体式格局，即翻开一个文件，然后删除，留一个句柄举行读写操纵。读写文件花样和内存中花样一样，如许就略过了转换这一步，进一步进步机能。注重，这个功用是在还没宣布的8.0.16版本中才有的，由于还看不到代码，只能经由过程文档猜想其完成。在8.0.16中，参数internal_tmp_disk_storage_engine已被去掉，磁盘暂时表只能运用InnoDB情势或许TempTable的这类overflow情势。从文档中，我们好像看出官方比较引荐运用TempTable这个新的引擎。详细机能提拔状况，还须要等代码宣布后，测试过才得出结论。
暂时文件
比拟暂时表，暂时文件对人人能够越发生疏，暂时文件更多的被运用在缓存数据，排序数据的场景中。平常状况下，被缓存或许排序的数据，起首放在内存中，假如内寄存不下，才会运用磁盘暂时文件的体式格局。暂时文件的运用体式格局与平常的表也不太一样，平常的表建立完后，就最先读写数据，运用完后，才把文件删除，然则暂时文件的运用体式格局不一样，在建立完后(运用mkstemp体系函数)，立时挪用unlink删除文件，然则不close文件，后续运用本来的句柄操纵文件。如许的优点是，当历程非常crash，不会有暂时文件由于没被删除而残留，然则害处也是显著的，我们在文件体系上运用ls敕令就看不到这个文件，须要运用lsof +L1来检察这类deleted属性的文件。
现在，我们重要在一下场景运用暂时文件：
DDL中的暂时文件
在做online DDL的历程当中，许多操纵须要对原表举行重修，对表重修前，须要对种种二级索引排序，而大批数据的排序，不太能够在内存中完成，须要依靠外部排序算法，MySQL运用了合并排序。这个历程当中就须要建立暂时文件。平常须要的空间大小与原表差不多。然则在运用完今后，会立时清算，所以在做DDL的时刻，须要保留出充足的空间。用户能够经由过程指定innodb_tmpdir来指定这类排序文件的途径。这个参数能够动态修正，平常把他设置在有充足磁盘空间的途径上。暂时文件的名字平常是相似ibXXXXXX，个中ib是牢固前缀，XXXXXX是大小写字母以及数字的随机组合。
在做online DDL中，我们是许可用户对原表做DML操纵的，即增编削查。我们不能直接插入原表中，因而须要一个处所纪录对原表的修正操纵，在DDL完毕后，再运用在新表上。这个纪录的处所就是online log，固然假如修改少的话，直接存在内存里(参数innodb_sort_buffer_size可掌握，同时这个参数也掌握online log每一个读写块的大小)面即可。这个onlinelog也是用暂时文件存，建立在innodb_tmpdir，最大大小为参数innodb_online_alter_log_max_size掌握，假如凌驾这个大小了，DDL就会失利。暂时文件的名字也相似上述的排序暂时文件的名字。
在online DDL的末了阶段，须要把排序完的文件和半途发生的DML全都运用到一个中心文件上，中心文件文件名相似#sql-ib53-522550444.ibd，个中#sql-ib是牢固的前缀，53是InnoDB层的table id，522550444是随机生成的数字。同时，在server层也会生成一个frm文件(8.0中没有)，文件名相似#sql-4d2b_2a.frm，个中#sql是牢固前缀，4d2b是历程号的十六进制示意，2a是线程号的十六进制示意(show processlist中的id)。因而我们也能够经由过程这个定名划定规矩来找到哪一个线程在做DDL。这里须要注重一点，这里说的中心文件，实在算是一个暂时表，并非上文说中暂时文件，这些中心文件能够经由过程ls来检察。当在DDL中的末了一步，会把这两个暂时文件定名回本来的表名。正由于这个特征，所以当数据库半途crash的时刻，能够会在磁盘上留下剩余无用的文件。碰到这类状况，能够先把frm文件重定名成与ibd文件一样的名字，然后运用DROP TABLE#mysql50##sql-ib53-522550444`来清算剩余的文件。注重，假如不必drop敕令，直接删除ibd文件，能够会致使数据字典内里依旧有剩余的信息，做法不太文雅。固然，在8.0中，由于运用了原子的数据字典，就不会涌现这类剩余文件了。
BinLog中的缓存操纵
BinLog只要在事件提交的时刻才会写入到文件中，在没提交前，会先放在内存中(由参数binlog_cache_size掌握)，假如内寄存慢了，就会建立暂时文件，运用方法也是先经由过程mkstemp建立，然后直接unlink，留一个句柄读写。暂时文件名相似MLXXXXXX，个中ML是牢固前缀，XXXXXX是大小写字母以及数字的随机组合。单个事件的BinLog太大，能够会致使全部BinLog的大小也过大，从而影响同步，因而我们须要尽量掌握事件大小。
优化建立的暂时文件
有些操纵，除了在引擎层须要依靠隐式暂时表来辅佐庞杂SQL的盘算，在Server层，也会建立暂时文件来辅佐，比方order by操纵，会挪用filesort函数。这个函数也会先运用内存(sort_buffer_size)排序，假如不够，就会建立一个暂时文件，辅佐排序。文件名相似MYXXXXXX，个中MY是牢固前缀，XXXXXX是大小写字母以及数字的随机组合。
Load data顶用的暂时文件
在BinLog复制中，假如在主库上运用了Load Data敕令，即从文件中导数据，数据库会把全部文件写入到RelayLog中，然后传到备库，备库剖析RelayLog，从中抽掏出对应的Load文件，然后在备库上运用。备库上这个文件存储的位置由参数slave_load_tmpdir掌握。文档中发起这个目次不要设置在物理机的内存目次或许重启后会删除的目次。由于复制依靠这个文件，假如不测被删除，会致使复制中断。
其他
除了上文所述的几个处所外，另有其他几个处所也会用到暂时文件：
- 在InnoDB层，启动的时刻会建立多个暂时文件用来存储：末了一次外键或许唯一键毛病; 末了一次死锁的信息; 末了的innodb状况信息。用暂时文件而不必内存的缘由猜想是，内存运用率不会由于写这些目标而波动。
- 在Server层，分区表运用show create table时，会用到暂时文件。别的在MYISAM表内部排序的时刻也会用到暂时文件。
相干参数
*** tmpdir: *** 这个参数是暂时目次的设置，在5.6以及之前的版本，暂时表/文件默许都邑放在这里。这个参数能够设置多个目次，如许就可以够轮流在差别的目次上建立暂时表/文件，假如差别的目次离别指向差别的磁盘，就可以够到达分流的目标。
*** innodb_tmpdir: *** 这个参数只如果被DDL中的排序暂时文件运用的。其占用的空间会很大，发起零丁设置。这个参数能够动态设置，也是一个Session变量。
*** slave_load_tmpdir: *** 这个参数重如果给BinLog复制中Load Data时，设置备库寄存暂时文件位置时运用。由于数据库Crash后还须要依靠Load数据的文件，发起不要设置重启后会删除数据的目次。
*** internal_tmp_disk_storage_engine: *** 当隐式暂时表被转换成磁盘暂时表时，运用哪一种引擎，默许只要MyISAM和InnoDB。5.7及今后的版本才支撑。8.0.16版本后作废的这个参数。
*** internal_tmp_mem_storage_engine: *** 隐式暂时表在内存时用的存储引擎，能够挑选Memory或许Temptable引擎。发起挑选新的Temptable引擎。
*** default_tmp_storage_engine: *** 默许的显式暂时表的引擎，即用户经由过程SQL语句建立的暂时表的引擎。
*** tmp_table_size: *** min(tmp_table_size,max_heap_table_size)是隐式暂时表的内存大小，凌驾这个值会转换成磁盘暂时表。
*** max_heap_table_size: *** 用户建立的Memory内存表的内存限定大小。
*** big_tables: *** 内存暂时表转换成磁盘暂时表须要有个转化操纵，须要在差别引擎花样中转换，这个是须要斲丧的。假如我们能提早晓得实行某个SQL须要用到磁盘暂时表，即内存一定不够用，能够设置这个参数，如许优化器就跳过运用内存暂时表，直接运用磁盘暂时表，削减开支。
*** temptable_max_ram: *** 这个参数是8.0后才有的，重如果给Temptable引擎指定内存大小，凌驾这个后，要么就转换成磁盘暂时表，要么就运用自带的overflow机制。
*** temptable_use_mmap: *** 是不是运用Temptable的overflow机制。
总结发起
MySQL的暂时表以及暂时文件现实上是一个比较庞杂的话题，触及的模块比较多，涌现的机遇比较难把握，致使排查题目比拟平常表也难不少。发起读者连系代码细细研讨，如许才定位在线上能够涌现的辣手题目。
以上就是MySQL进修之暂时表相干总结的细致内容，更多请关注ki4网别的相干文章！
