在只读节点上创建自定义临时表

背景信息

PolarDB为了提高查询性能和数据隔离性，引入了临时表机制。临时表可以存储查询过程中的中间结果，减少重复扫描，提高查询效率，并确保不同会话之间的数据隔离。然而，在读写分离的PolarDB集群中，对临时表的创建和DML操作会增加写节点的负担。为了解决这个问题，PolarDB扩展了临时表的功能，支持在只读节点上创建和查询临时表操作，并建议使用存储过程进行批量封存和调用处理操作，从而优化性能和减少写节点的负载。

版本要求

版本需满足以下条件之一：

• PolarDB MySQL版MySQL 8.0.1版本，且修订版本为8.0.1.1.28及以上。

• PolarDB MySQL版MySQL 8.0.2版本，且修订版本为8.0.2.2.5及以上。

• PolarDB MySQL版MySQL 5.7版本，且修订版本为5.7.1.0.35及以上。

使用限制

只读节点仅支持MEMORY或MYISAM引擎创建自定义临时表，读写节点可以支持在MEMORY和INNODB引擎下创建自定义临时表。由于临时表的生命周期为Session级别，目前Proxy不支持自动路由创建临时表和后续增删改查到同一个节点。因此需要结合PROXY HINT方式指定集群节点，了解更多详细请参见HINT语法使用说明。

使用方法

在PolarDB MySQL版中，临时表通常使用CREATE TEMPORARY TABLE语句创建，它们的作用域限制在创建它们的会话中，一旦会话结束，临时表及其数据就会被自动销毁。

创建只读自定义临时表

请创建临时表时指定相关节点，否则会路由到写节点。

/*force_node='pi-bpxxxxxxxx'*/ CREATE TEMPORARY TABLE new_tbl (c1 int PRIMARY KEY, c2 varchar(100)) ENGINE=MEMORY;

/*force_node='pi-bpxxxxxxxx'*/ CREATE TEMPORARY TABLE new_tbl (c1 int PRIMARY KEY, c2 varchar(100)) ENGINE=MYISAM;

/*force_node='pi-bpxxxxxxxx'*/ CREATE TEMPORARY TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

-- SQL语句运行之前需添加/*force_node='pi-bpxxxxxxxx'*/来强制指定这条SQL的路由方向
/*force_node='pi-bpxxxxxxxx'*/ INSERT INTO new_tbl VALUES(1, 'test_string');

/*force_node='pi-bpxxxxxxxx'*/ SELECT * FROM new_tbl;

在只读节点上执行临时表

请确保数据库集群已成功连接。如尚未连接，请参考连接数据库集群相关文档进行连接操作。从而验证只读节点是否支持临时表操作。

mysql -u <user> -h test-4.xxx.polardb.aliyuncs.com -P3306 -p<pwd>

SELECT * FROM new_tbl;
+----+-------------+
| c1 | c2          |
+----+-------------+
|  1 | test_string |
+----+-------------+
1 row in set (0.07 sec)

mysql> SHOW CREATE TABLE new_tbl;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                          |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| new_tbl | CREATE TEMPORARY TABLE `new_tbl` (
  `c1` int(11) NOT NULL,
  `c2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

通过存储过程创建自定义临时表

1. 通过集群地址或者RW节点创建存储过程。

   CREATE TABLE t1 (c1 int PRIMARY KEY, c2 varchar(100));
   INSERT INTO t1 VALUES(1, 'test_string');
   
   DELIMITER //;
   CREATE  PROCEDURE tmp_p0()
   BEGIN
   
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table engine = MYISAM AS
   SELECT * FROM t1 limit 10; 
   SELECT * FROM tmp_table;
   
   DROP TABLE tmp_table;
   
   END //;
   
   DELIMITER ;

2. 指定在只读节点上执行存储过程。

   /*force_node='pi-bpxxxxxxxx'*/ call tmp_p0();