temptable engine
我们知道 UNION, DERIVED TABLE, CTE, 子查询或者 distinct order by 之类的查询都可能用到临时表来存储中间结果, 官方文档中列举了几种场景. 内存引擎可以通过参数
来选择: temptable(default) 或者 memory 引擎. 本文只讨论 temptable 引擎
当内存超出 temptable 引擎限制 ( , 默认 1GB) 时, 将转换成磁盘数据, 这里也可以选择是存储成 innodb 还是 myisam(参数). 但 COMMON TABLE EXPRESSION(CTE)不允许使用 myisam 引擎
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 表来监控内存和磁盘上的临时表占用空间:
- MySQL> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE event_name like '%temptable%'\G
- *************************** 1. row ***************************
- EVENT_NAME: memory/temptable/physical_disk
- COUNT_ALLOC: 0
- COUNT_FREE: 0
- SUM_NUMBER_OF_BYTES_ALLOC: 0
- SUM_NUMBER_OF_BYTES_FREE: 0
- LOW_COUNT_USED: 0
- CURRENT_COUNT_USED: 0
- HIGH_COUNT_USED: 0
- LOW_NUMBER_OF_BYTES_USED: 0
- CURRENT_NUMBER_OF_BYTES_USED: 0
- HIGH_NUMBER_OF_BYTES_USED: 0
- *************************** 2. row ***************************
- EVENT_NAME: memory/temptable/physical_ram
- COUNT_ALLOC: 2
- COUNT_FREE: 0
- SUM_NUMBER_OF_BYTES_ALLOC: 2097152
- SUM_NUMBER_OF_BYTES_FREE: 0
- LOW_COUNT_USED: 0
- CURRENT_COUNT_USED: 2
- HIGH_COUNT_USED: 2
- LOW_NUMBER_OF_BYTES_USED: 0
- CURRENT_NUMBER_OF_BYTES_USED: 2097152
- HIGH_NUMBER_OF_BYTES_USED: 2097152
- 2 rows in set (0.03 sec)
temptable 引擎实现了自己的内存分配器来减少对系统的内存分配释放调用, 封装从磁盘上通过 mmap 进行分配的策略. 先从系统分配大块的内存, 然后通过这些内存块来提供 malloc/free 请求. 每个 block 包含一个 header 以及一系列的 chunk:
每个 block 的结构如下:(quoted from worklog)
- - bytes [0, 3]: 4 bytes for the block size (set at block creation and never
- changed later).
- - bytes [4, 7]: 4 bytes for the number of used/allocated chunks from this
- block (set to 0 at block creation).
- - bytes [8, 11]: 4 bytes for the offset of the first byte from the block
- start that is free and can be used by the next allocation request (set
- to 12 at block creation (3 * 4 bytes)). We call this first pristine offset.
- - bytes [12, block size) a sequence of chunks appended to each other.
每个 chunk 的结构
- - bytes [0, 3]: 4 bytes that designate the offset of the chunk from
- the start of the block. This is used in order to be able to deduce
- the block start from a given chunk. The offset of the first chunk is
- 12 (appended after the block size (4), number of allocated chunks (4)
- and the first pristine offset (4)).
- - bytes [4, chunk size): user data, pointer to this is returned to the
- user after a successfull allocation request.
分配内存:
- - if the current block does not have enough space:
- create a new block and make it the current (lose the pointer to the
- previous current block).
- - increment the number of allocated chunks by 1.
- - in the first pristine location - write its offset from the block
- start (4 bytes).
- - increment the first pristine offset with 4 + requested bytes by the user.
- - return a pointer to the previous first pristine + 4 to the user.
释放内存:
- - read 4 bytes before the provided pointer and derive the block start.
- - decrement the number of used chunks by 1.
- - if this was the last chunk in the block and this is not the last block:
- destroy the block, returning the memory to the OS.
- - keep the last block for reuse even if all chunks from it are removed, it
- will be destroyed when the thread terminates. When the last chunk from
- the last block is removed, instead of destroying the block reset its first
- pristine byte offset to 12.
内存分配器的定义和实现在文件 storage/temptable/include/temptable/allocator.h
其他模块的定义都在目录 storage/temptable/include / 下, 如果想深入了解该引擎的实现, 可以阅读这些头文件代码, 有比较详细的注释
InnoDB 临时表
在 innodb 的代码里有大量使用 dict_table_t::is_intrinsic()来判定执行路径, 对于内部临时表而言, 会去消除不必要的开销, 例如表锁和事务开销等等. 这里简单介绍下相关的代码.
插入操作
当插入临时表时, 直接使用 cursor 进行操作, 跳过事务和锁相关操作:
- row_insert_for_mysql
- |--> 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 ? 代码中的解释:
- Intrinsic table are not shared so don't need a central trx-id
- but just need a increased counter to track consistent view while
- 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
查询操作
函数:
- row_search_for_mysql
- |--> 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)
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
- )
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, 一个用于显式创建临时表, 一个用于优化器创建的临时表. 需要两个独立表空间的原因是未来可以在链接断开之前就单独回收优化器表的空间
- dict_build_tablespace_for_table
- |--> innodb_session->get_instrinsic_temp_tblsp()
- |--> innodb_session->get_usr_temp_tblsp()
当 pool 中 space 不够用时, 会自动进行扩展, 每次扩展单位为 10 个文件
在 session 断开时, 将 tablespace truncate 并放回到 pool 中. 所以如果临时表空间占用过大, 可以通过中断链接的方式来释放
可以通过 is 表查询 tablespace 占用的 session id
- MySQL> SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES;
- +----+------------+----------------------------+-------+----------+-----------+
- | ID | SPACE | PATH | SIZE | STATE | PURPOSE |
- +----+------------+----------------------------+-------+----------+-----------+
- | 72 | 4294566162 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC |
- | 0 | 4294566153 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
- | 0 | 4294566154 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
- | 0 | 4294566155 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
- | 0 | 4294566156 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
- | 0 | 4294566157 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
- | 0 | 4294566158 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
- | 0 | 4294566159 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
- | 0 | 4294566160 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
- | 0 | 4294566161 | ./#innodb_temp/temp_9.ibt | 81920 | INACTIVE | NONE |
- +----+------------+----------------------------+-------+----------+-----------+
- 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 https://dev.mysql.com/worklog/task/?id=8117
- WL#11452 Support for BLOBs in temptable engine https://dev.mysql.com/worklog/task/?id=11452
- WL#11613: InnoDB: Reclaim disk space occupied by temporary tables online https://dev.mysql.com/worklog/task/?id=11613
- MySQL 8.0: Support for BLOBs in TempTable engine
- Internal Temporary Table Use in MySQL
- InnoDB Temporary Tablespaces
- .
来源: https://yq.aliyun.com/articles/689866