本文介绍了如何在PolarDB MySQL版的只读节点上创建自定义临时表。
背景信息
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语句创建,它们的作用域限制在创建它们的会话中,一旦会话结束,临时表及其数据就会被自动销毁。
创建只读自定义临时表
需要在SQL语句前加上/*force_node='pi-bpxxxxxxxx'*/强制指定这条SQL的路由方向,否则会收到如下错误Table 'test.new_tbl' doesn't exist
。
请创建临时表时指定相关节点,否则会路由到写节点。
/*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)
通过存储过程创建自定义临时表
通过集群地址或者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 ;
指定在只读节点上执行存储过程。
/*force_node='pi-bpxxxxxxxx'*/ call tmp_p0();