全部产品
Search
文档中心

云原生数据库 PolarDB:在只读节点上创建自定义临时表

更新时间:Oct 17, 2024

本文介绍了如何在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)

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

  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();