PolarDB:Create custom temporary tables on read-only nodes

Background information

PolarDB supports temporary tables. This improves the query performance and data isolation. Temporary tables can store intermediate results generated during the data query process. This can reduce repetitive data scans, improve query performance, and ensure data isolation between sessions. However, for a primary node in a PolarDB cluster for which read/write splitting is enabled, the workload increases when you create temporary tables and execute DML statements on temporary tables. To address this issue, PolarDB allows you to create and query temporary tables on read-only nodes. We recommend that you use stored procedures to perform operations in batches. This optimizes the performance and reduces the load on the primary node.

Supported versions

Your cluster must run one of the following versions:

• A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.35 or later.

• A PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.28 or later.

• A PolarDB for MySQL 8.0.2 cluster whose revision version is 8.0.2.2.5 or later.

Limitations

You can create temporary tables on read-only nodes by using only the MEMORY or MyISAM engine. You can create temporary tables on primary nodes by using the MEMORY or InnoDB engine. The lifecycle of a temporary table is at the session level. PolarProxy cannot automatically route the temporary table creation and subsequent modification operations to the same node. Therefore, you must use hints to specify the cluster node. For more information, see HINT syntax.

Usage

In PolarDB for MySQL, a temporary tables is usually created by executing the CREATE TEMPORARY TABLE statement. The statement takes effect in the session in which the table is created. When the session ends, the temporary table and its data are automatically cleared.

Create a read-only custom temporary table

Specify the node when you create the temporary table. Otherwise, the table is routed to the primary node.

/*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;

-- Add /*force_node='pi-bpxxxxxxxx'*/ before the statement to specify the routing direction.
/*force_node='pi-bpxxxxxxxx'*/ INSERT INTO new_tbl VALUES(1, 'test_string');

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

Perform operations on a temporary table from a read-only node

Make sure that you can connect to the cluster. For more information about how to connect to the cluster, see Connect to a cluster. Afer you connect to the cluster, you can verify whether you can perform operations on the table from the read-only node.

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)

Create custom temporary tables by using stored procedures

1. Create the stored procedure by using the cluster endpoint or the primary node.

   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. Execute the stored procedure on a specified read-only node.

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