共计 7529 个字符，预计需要花费 19 分钟才能阅读完成。
自动写代码机器人，免费开通
这篇文章给大家分享的是有关 MySQL 学习之临时表是什么的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考。一起跟随丸趣 TV 小编过来看看吧。
临时表
临时表可以分为磁盘临时表和内存临时表，而临时文件，只会存在于磁盘上，不会存在于内存中。具体来说，临时表的内存形态有 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 学习之临时表是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到吧！
向 AI 问一下细节
丸趣 TV 网 – 提供最优质的资源集合！
