最新release的MySQL 8.0.16中包含的临时表改动:

  • WL#11974, 不再支持myisam作为内部临时表转储磁盘时使用，参数internal_tmp_disk_storage_engine被移除掉了， 直接使用innodb作为内部内存表的持久化引擎
  • 新增参数temptable_use_mmap, 默认打开，表示当使用TempTable的临时表的内存占用超过temptable_max_ram之后，就使用memory map的方式去扩展临时文件到内存。如果为off，则使用innodb引擎来存储temptable数据

temptable engine

我们知道UNION, DERIVED TABLE, CTE, 子查询或者distinct order by之类的查询都可能用到临时表来存储中间结果,官方文档中列举了几种场景。内存引擎可以通过参数 internal_tmp_mem_storage_engine来选择: temptable(default) 或者memory引擎。本文只讨论temptable引擎

当内存超出temptable引擎限制( temptable_max_ram, 默认1GB)时，将转换成磁盘数据，这里也可以选择是存储成innodb还是myisam(参数). 但COMMON TABLE EXPRESSION(CTE)不允许使用myisam引擎

  1. Note: 由于innodb有行长度限制，可能报row size too large 或者too many columns之类的错误，可以通过设置internal_tmp_disk_storage_engine来绕过限制。

MySQL8.0.16引入了新的参数temptable_use_mmap，用来控制temptable引擎是否磁盘数据转换成Innodb存储，还是内存映射文件。

temptable引擎和memory引擎本质上类似，但最大的不同时可以支持变长类型(例如blob, text, json, geometry等)，例如varchar(100)的数据”abcd”应该只占用4个字节而非100个字节。

在之前的版本中当存在Lob类型时，数据会直接转换成磁盘存储。而WL#11613对此做了修改：在内存中使用数组来维护大字段，每个字段包含数据长度和数据指针。在数组之后连续的存储列值，没有padding(如果使用memory引擎，则会padding)。官方博客的评测中由于无需在遇到lob时转换成磁盘存储，相比之前的版本可能获得数倍的性能提升。

从设计上temptable引擎支持hash Index和tree index，允许一个inserter和多个reader, 插入不影响reader的cursor。

笔者的主要关注点在innodb，由于从5.7开始MySQL对Innodb做了大量的优化(cursor优化，无redo log， 去除代码路径上的各种锁)，因此默认情况下使用innodb作为内部临时表的磁盘存储.

可以通过查询performance schema表来监控内存和磁盘上的临时表占用空间：

  1. mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE event_name like '%temptable%'\G
  2. *************************** 1. row ***************************
  3. EVENT_NAME: memory/temptable/physical_disk
  4. COUNT_ALLOC: 0
  5. COUNT_FREE: 0
  6. SUM_NUMBER_OF_BYTES_ALLOC: 0
  7. SUM_NUMBER_OF_BYTES_FREE: 0
  8. LOW_COUNT_USED: 0
  9. CURRENT_COUNT_USED: 0
  10. HIGH_COUNT_USED: 0
  11. LOW_NUMBER_OF_BYTES_USED: 0
  12. CURRENT_NUMBER_OF_BYTES_USED: 0
  13. HIGH_NUMBER_OF_BYTES_USED: 0
  14. *************************** 2. row ***************************
  15. EVENT_NAME: memory/temptable/physical_ram
  16. COUNT_ALLOC: 2
  17. COUNT_FREE: 0
  18. SUM_NUMBER_OF_BYTES_ALLOC: 2097152
  19. SUM_NUMBER_OF_BYTES_FREE: 0
  20. LOW_COUNT_USED: 0
  21. CURRENT_COUNT_USED: 2
  22. HIGH_COUNT_USED: 2
  23. LOW_NUMBER_OF_BYTES_USED: 0
  24. CURRENT_NUMBER_OF_BYTES_USED: 2097152
  25.   HIGH_NUMBER_OF_BYTES_USED: 2097152
  26. 2 rows in set (0.03 sec)

temptable引擎实现了自己的内存分配器来减少对系统的内存分配释放调用，封装从磁盘上通过mmap进行分配的策略。先从系统分配大块的内存，然后通过这些内存块来提供malloc/free请求. 每个block包含一个header以及一系列的chunk:

  • 每个block的结构如下：(quoted from worklog)

    1. - bytes [0, 3]: 4 bytes for the block size (set at block creation and never
    2.     changed later).
    3. - bytes [4, 7]: 4 bytes for the number of used/allocated chunks from this
    4. block (set to 0 at block creation).
    5. - bytes [8, 11]: 4 bytes for the offset of the first byte from the block
    6. start that is free and can be used by the next allocation request (set
    7.     to 12 at block creation (3 * 4 bytes)). We call this first pristine offset.
    8. - bytes [12, block size) a sequence of chunks appended to each other.

  • 每个chunk的结构

    1. - bytes [0, 3]: 4 bytes that designate the offset of the chunk from
    2. the start of the block. This is used in order to be able to deduce
    3. the block start from a given chunk. The offset of the first chunk is
    4. 12 (appended after the block size (4), number of allocated chunks (4)
    5. and the first pristine offset (4)).
    6. - bytes [4, chunk size): user data, pointer to this is returned to the
    7. user after a successfull allocation request.

  • 分配内存:

    1. - if the current block does not have enough space:
    2. create a new block and make it the current (lose the pointer to the
    3. previous current block).
    4. - increment the number of allocated chunks by 1.
    5. - in the first pristine location - write its offset from the block
    6. start (4 bytes).
    7. - increment the first pristine offset with 4 + requested bytes by the user.
    8. - return a pointer to the previous first pristine + 4 to the user.

  • 释放内存

    1. - read 4 bytes before the provided pointer and derive the block start.
    2. - decrement the number of used chunks by 1.
    3. - if this was the last chunk in the block and this is not the last block:
    4. destroy the block, returning the memory to the OS.
    5. - keep the last block for reuse even if all chunks from it are removed, it
    6. will be destroyed when the thread terminates. When the last chunk from
    7. the last block is removed, instead of destroying the block reset its first
    8. pristine byte offset to 12.

内存分配器的定义和实现在文件storage/temptable/include/temptable/allocator.h

其他模块的定义都在目录storage/temptable/include/下，如果想深入了解该引擎的实现，可以阅读这些头文件代码，有比较详细的注释

InnoDB临时表

在innodb的代码里有大量使用dict_table_t::is_intrinsic()来判定执行路径，对于内部临时表而言，会去消除不必要的开销，例如表锁和事务开销等等。这里简单介绍下相关的代码。

插入操作

当插入临时表时，直接使用cursor进行操作，跳过事务和锁相关操作：

  1. row_insert_for_mysql 
  2.     |--> row_insert_for_mysql_using_cursor

对于临时表记录：

  • 其row_id取自表上递增计数器dict_table_t::sess_row_id, 事务id取自dict_table_t::sess_trx_id而非全局计数器(trx_sys->max_trx_id). 事务Id写入到记录中。

为什么还需要trx id ? 代码中的解释：

  1. Intrinsic table are not shared so don't need a central trx-id
  2. but just need a increased counter to track consistent view while
  3. proceeding SELECT as part of UPDATE
  • 插入操作无需记录undo log, 因此需要通过插入的记录显式回滚(row_explicit_rollback)，实际上就是将插入的记录进行标记删除

  • 索引上dict_index_t::last_ins_cur维护了上次插入位点的cursor, 这样对于顺序插入操作，无需每次都commit mtr，并能快速定位到btre上的插入点(row_ins_sorted_clust_index_entry)

    delete/update操作会自动把cursor提交掉 当存在blob/text类型时，不能cache cursor

查询操作

函数：

  1. row_search_for_mysql
  2.      |--> row_search_no_mvcc

由于表只对当前session可见，因此无需走mvcc判断。 查询在满足一定条件时也使用了缓存策略cursor的策略, 上次查询的cursor存储在dict_index_t::last_sel_cur中，无需频繁提交mini transaction, 该特性仅限于auto-generated clust index

临时表空间

在当前版本(8.0.15)的MySQL中，有两类临时表空间:

ibtmp1

在data目录下，具有固定的space id(s_temp_space_id = 0xFFFFFFFD)

  1. Note: 在之前的版本中(例如5.7)，使用ibtmp1来存储临时表数据和undo信息等，在每次重启时重新创建并使用新的space id.

在内存中对应的对象为srv_tmp_space，目前用于存储临时表的Undo log:

  • 正常shutdown(innodb_space_shutdown())或者重启时(srv_open_tmp_tablespace())重建文件
  • 回滚段初始化(trx_rseg_adjust_rollback_segments())
  • 回滚段内存对象在trx_sys_t::tmp_rsegs中，默认128个回滚段，与正常回滚段在事务开始时分配不同，临时表回滚段是在使用时才分配(trx_undo_report_row_operation() –> trx_assign_rseg_temp() –>get_next_temp_rseg)
  1. Note: 通常查询产生的内部中间表只有插入和查询，因此无需记录undo log。但对于用户显式创建的临时表依然需要

innodb_temp目录下的临时表空间文件

这些文件以temp_{id}.ibt命名，主要是避免所有文件都存储在ibtmp1中，而ibtmp1是在重启时才会重置，就算表被删除了也不会缩减空间。

  • 在实例启动时，这些文件在目录innodb_temp_tablespaces_dir或者#innodb_temp（如果未显式指定）下被创建(ibt::open_or_create), 初始化创建10个文件.
  • 每个session在第一次请求创建临时表时，会从池中分配一个tablespace. 当这个tablespace被attach到该session时，所有临时表都创建在其中. 每个session最多可以有两个独立的tablespace，一个用于显式创建临时表，一个用于优化器创建的临时表。需要两个独立表空间的原因是未来可以在链接断开之前就单独回收优化器表的空间
  1. dict_build_tablespace_for_table 
  2.     |--> innodb_session->get_instrinsic_temp_tblsp()
  3.     |--> innodb_session->get_usr_temp_tblsp()
  • 当pool中space不够用时，会自动进行扩展，每次扩展单位为10个文件
  • 在session断开时，将tablespace truncate并放回到pool中。所以如果临时表空间占用过大，可以通过中断链接的方式来释放
  • 可以通过is表查询tablespace占用的session id
  1. mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES;
  2. +----+------------+----------------------------+-------+----------+-----------+
  3. | ID | SPACE      | PATH                       | SIZE  | STATE    | PURPOSE   |
  4. +----+------------+----------------------------+-------+----------+-----------+
  5. | 72 | 4294566162 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE   | INTRINSIC |
  6. |  0 | 4294566153 | ./#innodb_temp/temp_1.ibt  | 81920 | INACTIVE | NONE      |
  7. |  0 | 4294566154 | ./#innodb_temp/temp_2.ibt  | 81920 | INACTIVE | NONE      |
  8. |  0 | 4294566155 | ./#innodb_temp/temp_3.ibt  | 81920 | INACTIVE | NONE      |
  9. |  0 | 4294566156 | ./#innodb_temp/temp_4.ibt  | 81920 | INACTIVE | NONE      |
  10. |  0 | 4294566157 | ./#innodb_temp/temp_5.ibt  | 81920 | INACTIVE | NONE      |
  11. |  0 | 4294566158 | ./#innodb_temp/temp_6.ibt  | 81920 | INACTIVE | NONE      |
  12. |  0 | 4294566159 | ./#innodb_temp/temp_7.ibt  | 81920 | INACTIVE | NONE      |
  13. |  0 | 4294566160 | ./#innodb_temp/temp_8.ibt  | 81920 | INACTIVE | NONE      |
  14. |  0 | 4294566161 | ./#innodb_temp/temp_9.ibt  | 81920 | INACTIVE | NONE      |
  15. +----+------------+----------------------------+-------+----------+-----------+
  16. 10 rows in set (0.00 sec)
  • temp tablespace有单独space id 段，内部预留了400k的space id 给temporary tablespace (s_min_temp_space_id , s_max_temp_space_id)，足够使用.
  • space pool中大小不会缩小，也就是说只会扩展，不会收缩!

Reference

WL#8117: Compact In-Memory Temporary Tables

WL#11452 Support for BLOBs in temptable engine

WL#11613: InnoDB: Reclaim disk space occupied by temporary tables online

MySQL 8.0: Support for BLOBs in TempTable engine

Internal Temporary Table Use in MySQL

InnoDB Temporary Tablespaces