数据库内核月报

数据库内核月报 - 2019 / 09

MySQL · 引擎特性 · 临时表改进

Author: weixiang

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

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引擎

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:

内存分配器的定义和实现在文件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

对于临时表记录:

为什么还需要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

查询操作

函数:

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:

Note: 通常查询产生的内部中间表只有插入和查询,因此无需记录undo log。但对于用户显式创建的临时表依然需要

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

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

dict_build_tablespace_for_table 
    |--> innodb_session->get_instrinsic_temp_tblsp()
    |--> innodb_session->get_usr_temp_tblsp()
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)

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