临时表

临时表可以被认为是一种复用查询结果的技术。

假设希望知道 Bookshop 应用当中最年长的作家们的一些情况,可能需要编写多个查询,而这些查询都需要使用到这个最年长作家列表。可以通过下面的 SQL 语句从 authors 表当中找出最年长的前 50 位作家作为研究对象。

SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age FROM authors a ORDER BY age DESC LIMIT 50;

查询结果如下:

+------------+---------------------+------+ | id | name | age | +------------+---------------------+------+ | 4053452056 | Dessie Thompson | 80 | | 2773958689 | Pedro Hansen | 80 | | 4005636688 | Wyatt Keeling | 80 | | 3621155838 | Colby Parker | 80 | | 2738876051 | Friedrich Hagenes | 80 | | 2299112019 | Ray Macejkovic | 80 | | 3953661843 | Brandi Williamson | 80 | ... | 4100546410 | Maida Walsh | 80 | +------------+---------------------+------+ 50 rows in set (0.01 sec)

在找到这 50 位最年长的作家后,希望缓存这个查询结果,以便后续的查询能够方便地使用到这组数据。如果使用一般的数据库表进行存储的话,在创建这些表时,需要考虑如何避免不同会话之间的表重名问题,而且可能在一批查询结束之后就不再需要这些表了,还需要及时地对这些中间结果表进行清理。

创建临时表

为了满足这类缓存中间结果的需求,TiDB 在 v5.3.0 版本中引入了临时表功能,对于临时表当中的本地临时表而言,TiDB 将会在会话结束的一段时间后自动清理这些已经没用的临时表,用户无需担心中间结果表的增多会带来管理上的麻烦。

临时表类型

TiDB 的临时表分为本地临时表和全局临时表:

  • 本地临时表的表定义和表内数据只对当前会话可见,适用于暂存会话内的中间数据。
  • 全局临时表的表定义对整个 TiDB 集群可见,表内数据只对当前事务可见,适用于暂存事务内的中间数据。

创建本地临时表

在创建本地临时表前,你需要给当前数据库用户添加上 CREATE TEMPORARY TABLES 权限。

  • SQL
  • Java

在 SQL 中,通过 CREATE TEMPORARY TABLE <table_name> 语句创建临时表,默认临时表的类型为本地临时表,它只能被当前会话所访问。

CREATE TEMPORARY TABLE top_50_eldest_authors ( id BIGINT, name VARCHAR(255), age INT, PRIMARY KEY(id) );

在创建完临时表后,你可以通过 INSERT INTO table_name SELECT ... 语句,将上述查询得到的结果导入到刚刚创建的临时表当中。

INSERT INTO top_50_eldest_authors SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age FROM authors a ORDER BY age DESC LIMIT 50;

运行结果为:

Query OK, 50 rows affected (0.03 sec) Records: 50 Duplicates: 0 Warnings: 0

在 Java 中创建本地临时表的示例如下:

public List<Author> getTop50EldestAuthorInfo() throws SQLException { List<Author> authors = new ArrayList<>(); try (Connection conn = ds.getConnection()) { Statement stmt = conn.createStatement(); stmt.executeUpdate(""" CREATE TEMPORARY TABLE top_50_eldest_authors ( id BIGINT, name VARCHAR(255), age INT, PRIMARY KEY(id) ); """); stmt.executeUpdate(""" INSERT INTO top_50_eldest_authors SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age FROM authors a ORDER BY age DESC LIMIT 50; """); ResultSet rs = stmt.executeQuery(""" SELECT id, name FROM top_50_eldest_authors; """); while (rs.next()) { Author author = new Author(); author.setId(rs.getLong("id")); author.setName(rs.getString("name")); authors.add(author); } } return authors; }

创建全局临时表

  • SQL
  • Java

在 SQL 中,你可以通过加上 GLOBAL 关键字来声明你所创建的是全局临时表。创建全局临时表时必须在末尾 ON COMMIT DELETE ROWS 修饰,这表明该全局数据表的所有数据行将在事务结束后被删除。

CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS top_50_eldest_authors_global ( id BIGINT, name VARCHAR(255), age INT, PRIMARY KEY(id) ) ON COMMIT DELETE ROWS;

在对全局临时表导入数据时,你需要特别注意,你必须通过 BEGIN 显式声明事务的开始。否则导入的数据在 INSERT INTO 语句执行后就清除掉,因为 Auto Commit 模式下,INSERT INTO 语句的执行结束,事务会自动被提交,事务结束,全局临时表的数据便被清空了。

在 Java 中使用全局临时表时,你需要将 Auto Commit 模式先关闭。在 Java 语言当中,你可以通过 conn.setAutoCommit(false); 语句来实现,当你使用完成后,可以通过 conn.commit(); 显式地提交事务。事务在提交或取消后,在事务过程中对全局临时表添加的数据将会被清除。

public List<Author> getTop50EldestAuthorInfo() throws SQLException { List<Author> authors = new ArrayList<>(); try (Connection conn = ds.getConnection()) { conn.setAutoCommit(false); Statement stmt = conn.createStatement(); stmt.executeUpdate(""" CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS top_50_eldest_authors ( id BIGINT, name VARCHAR(255), age INT, PRIMARY KEY(id) ) ON COMMIT DELETE ROWS; """); stmt.executeUpdate(""" INSERT INTO top_50_eldest_authors SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age FROM authors a ORDER BY age DESC LIMIT 50; """); ResultSet rs = stmt.executeQuery(""" SELECT id, name FROM top_50_eldest_authors; """); conn.commit(); while (rs.next()) { Author author = new Author(); author.setId(rs.getLong("id")); author.setName(rs.getString("name")); authors.add(author); } } return authors; }

查看临时表信息

通过 SHOW [FULL] TABLES 语句可以查看到已经创建的全局临时表,但是无法看到本地临时表的信息,TiDB 暂时也没有类似的 information_schema.INNODB_TEMP_TABLE_INFO 系统表存放临时表的信息。

例如,你可以在 table 列表当中查看到全局临时表 top_50_eldest_authors_global,但是无法查看到 top_50_eldest_authors 表。

+-------------------------------+------------+ | Tables_in_bookshop | Table_type | +-------------------------------+------------+ | authors | BASE TABLE | | book_authors | BASE TABLE | | books | BASE TABLE | | orders | BASE TABLE | | ratings | BASE TABLE | | top_50_eldest_authors_global | BASE TABLE | | users | BASE TABLE | +-------------------------------+------------+ 9 rows in set (0.00 sec)

查询临时表

在临时表准备就绪之后,你便可以像对一般数据表一样对临时表进行查询:

SELECT * FROM top_50_eldest_authors;

你可以通过表连接将临时表中的数据引用到你的查询当中:

EXPLAIN SELECT ANY_VALUE(ta.id) AS author_id, ANY_VALUE(ta.age), ANY_VALUE(ta.name), COUNT(*) AS books FROM top_50_eldest_authors ta LEFT JOIN book_authors ba ON ta.id = ba.author_id GROUP BY ta.id;

视图有所不同,在对临时表进行查询时,不会再执行导入数据时所使用的原始查询,而是直接从临时表中获取数据。在一些情况下,这会帮助你提高查询的效率。

删除临时表

本地临时表会在会话结束后连同数据和表结构都进行自动清理。全局临时表在事务结束后会自动清除数据,但是表结构依然保留,需要手动删除。

你可以通过 DROP TABLEDROP TEMPORARY TABLE 语句手动删除本地临时表。例如:

DROP TEMPORARY TABLE top_50_eldest_authors;

你还可以通过 DROP TABLEDROP GLOBAL TEMPORARY TABLE 语句手动删除全局临时表。例如:

DROP GLOBAL TEMPORARY TABLE top_50_eldest_authors_global;

限制

关于 TiDB 在临时表功能上的一些限制,你可以通过阅读参考文档中的临时表与其他 TiDB 功能的兼容性限制小节进行了解。

扩展阅读