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学习之暂时表相干总结的细致内容，更多请关注 百分百源码网 其它相干文章！
