PolarDB：在唯讀節點上建立自訂暫存資料表

背景資訊

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