PolarDB:読み取り専用ノードでのカスタム一時テーブルの作成

背景情報

PolarDBは一時テーブルをサポートしています。 これにより、クエリのパフォーマンスとデータの分離が向上します。 一時テーブルは、データクエリプロセス中に生成された中間結果を格納することができる。 これにより、データスキャンの繰り返しを減らし、クエリのパフォーマンスを向上させ、セッション間のデータ分離を確実にします。 ただし、読み書き分離が有効になっているPolarDBクラスターのプライマリノードの場合、一時テーブルを作成して一時テーブルでDMLステートメントを実行すると、ワークロードが増加します。 この問題に対処するために、PolarDBでは、読み取り専用ノードで一時テーブルを作成およびクエリできます。 ストアドプロシージャを使用して、操作をバッチで実行することを推奨します。 これにより、パフォーマンスが最適化され、プライマリノードの負荷が軽減されます。

サポートされているバージョン

クラスターは、次のいずれかのバージョンを実行する必要があります。

• リビジョンバージョンが5.7.1.0.35以降のPolarDB for MySQL 5.7クラスター。

• リビジョンバージョンが8.0.1.1.28以降のPolarDB for MySQL 8.0.1クラスター。

• リビジョンバージョンが8.0.2.2.5以降のPolarDB for MySQL 8.0.2クラスター。

制限事項

MEMORYまたはMyISAMエンジンのみを使用して、読み取り専用ノードに一時テーブルを作成できます。 MEMORYまたはInnoDBエンジンを使用して、プライマリノードに一時テーブルを作成できます。 一時テーブルのライフサイクルはセッションレベルです。 PolarProxyは、一時テーブルの作成とその後の変更操作を同じノードに自動的にルーティングすることはできません。 したがって、ヒントを使用してクラスターノードを指定する必要があります。 詳細については、「HINT構文」をご参照ください。

使用法

PolarDB for 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;

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

読み取り専用ノードからの一時テーブルに対する操作の実行

クラスターに接続できることを確認してください。 クラスターへの接続方法の詳細については、「クラスターへの接続」をご参照ください。 クラスターに接続した後、読み取り専用ノードからテーブルに対して操作を実行できるかどうかを確認できます。

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. クラスターエンドポイントまたはプライマリノードを使用して、ストアドプロシージャを作成します。

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