一時テーブル
一時テーブルは、クエリ結果を再利用するための手法と考えることができます。
書店アプリケーションにおける最年長の著者について何かを知りたい場合は、最年長の著者のリストを使用する複数のクエリを記述する場合があります。
たとえば、次のステートメントを使用して、 authorsテーブルから最年長の著者上位 50 人を取得できます。
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
結果は以下のようになります。
+------------+---------------------+------+
| id | name | age |
+------------+---------------------+------+
| 4053452056 | Dessie Thompson | 80 |
| 2773958689 | Pedro Hansen | 80 |
| 4005636688 | Wyatt Keeling | 80 |
| 3621155838 | Colby Parker | 80 |
| 2738876051 | Friedrich Hagenes | 80 |
| 2299112019 | Ray Macejkovic | 80 |
| 3953661843 | Brandi Williamson | 80 |
...
| 4100546410 | Maida Walsh | 80 |
+------------+---------------------+------+
50 rows in set (0.01 sec)
後続のクエリの利便性のために、このクエリの結果をキャッシュする必要があります。storageに一般的なテーブルを使用する場合は、異なるセッション間でのテーブル名の重複の問題を回避する方法と、バッチクエリ後にこれらのテーブルが使用されない場合があるため、中間結果を時間内にクリーンアップする必要性に注意する必要があります。
一時テーブルを作成する
中間結果をキャッシュするために、TiDB v5.3.0 で一時テーブル機能が導入されました。TiDB はセッション終了後にローカル一時テーブルを自動的に削除するため、中間結果の増加によって生じる管理上のトラブルを心配する必要がなくなります。
一時テーブルの種類
TiDB の一時テーブルは、ローカル一時テーブルとグローバル一時テーブルの 2 種類に分かれています。
- ローカル一時テーブルの場合、テーブル定義とテーブル内のデータは現在のセッションでのみ表示されます。このタイプは、セッションで中間データを一時的に保存するのに適しています。
- グローバル一時テーブルの場合、テーブル定義は TiDB クラスター全体に表示され、テーブル内のデータは現在のトランザクションにのみ表示されます。このタイプは、トランザクション内の中間データを一時的に保存するのに適しています。
ローカル一時テーブルを作成する
ローカル一時テーブルを作成する前に、現在のデータベース ユーザーにCREATE TEMPORARY TABLES権限を追加する必要があります。
- SQL
- Java
CREATE TEMPORARY TABLE <table_name>ステートメントを使用して一時テーブルを作成できます。デフォルトのタイプはローカル一時テーブルであり、現在のセッションでのみ表示されます。
CREATE TEMPORARY TABLE top_50_eldest_authors (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
);
一時テーブルを作成した後、 INSERT INTO table_name SELECT ...ステートメントを使用して、上記のクエリの結果を作成した一時テーブルに挿入できます。
INSERT INTO top_50_eldest_authors
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
結果は以下のようになります。
Query OK, 50 rows affected (0.03 sec)
Records: 50 Duplicates: 0 Warnings: 0
public List<Author> getTop50EldestAuthorInfo() throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
Statement stmt = conn.createStatement();
stmt.executeUpdate("""
CREATE TEMPORARY TABLE top_50_eldest_authors (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
);
""");
stmt.executeUpdate("""
INSERT INTO top_50_eldest_authors
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
""");
ResultSet rs = stmt.executeQuery("""
SELECT id, name FROM top_50_eldest_authors;
""");
while (rs.next()) {
Author author = new Author();
author.setId(rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}
グローバル一時テーブルを作成する
- SQL
- Java
グローバル一時テーブルを作成するには、 GLOBALキーワードを追加し、 ON COMMIT DELETE ROWSで終了します。これは、現在のトランザクションが終了した後にテーブルが削除されることを意味します。
CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS top_50_eldest_authors_global (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
) ON COMMIT DELETE ROWS;
グローバル一時テーブルにデータを挿入する場合、 BEGINでトランザクションの開始を明示的に宣言する必要があります。そうしないと、 INSERT INTOステートメントの実行後にデータがクリアされます。自動コミット モードでは、 INSERT INTOステートメントの実行後にトランザクションが自動的にコミットされ、トランザクションが終了するとグローバル一時テーブルがクリアされるためです。
グローバル一時テーブルを使用する場合は、まず自動コミット モードをオフにする必要があります。JavaJava、 conn.setAutoCommit(false);ステートメントでこれを行うことができ、 conn.commit();でトランザクションを明示的にコミットできます。トランザクション中にグローバル一時テーブルに追加されたデータは、トランザクションがコミットまたはキャンセルされた後にクリアされます。
public List<Author> getTop50EldestAuthorInfo() throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.executeUpdate("""
CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS top_50_eldest_authors (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
) ON COMMIT DELETE ROWS;
""");
stmt.executeUpdate("""
INSERT INTO top_50_eldest_authors
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
""");
ResultSet rs = stmt.executeQuery("""
SELECT id, name FROM top_50_eldest_authors;
""");
conn.commit();
while (rs.next()) {
Author author = new Author();
author.setId(rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}
一時テーブルをビュー
SHOW [FULL] TABLESステートメントでは、既存のグローバル一時テーブルのリストを表示できますが、リストにローカル一時テーブルは表示されません。現時点では、TiDB には一時テーブル情報を格納するための同様のinformation_schema.INNODB_TEMP_TABLE_INFOシステム テーブルはありません。
たとえば、テーブル リストにはグローバル一時テーブルtop_50_eldest_authors_globalが表示されますが、テーブルtop_50_eldest_authorsは表示されません。
+-------------------------------+------------+
| Tables_in_bookshop | Table_type |
+-------------------------------+------------+
| authors | BASE TABLE |
| book_authors | BASE TABLE |
| books | BASE TABLE |
| orders | BASE TABLE |
| ratings | BASE TABLE |
| top_50_eldest_authors_global | BASE TABLE |
| users | BASE TABLE |
+-------------------------------+------------+
9 rows in set (0.00 sec)
一時テーブルをクエリする
一時テーブルの準備ができたら、通常のデータ テーブルとしてクエリを実行できます。
SELECT * FROM top_50_eldest_authors;
複数テーブル結合クエリを介して一時テーブルからクエリにデータを参照できます。
EXPLAIN SELECT ANY_VALUE(ta.id) AS author_id, ANY_VALUE(ta.age), ANY_VALUE(ta.name), COUNT(*) AS books
FROM top_50_eldest_authors ta
LEFT JOIN book_authors ba ON ta.id = ba.author_id
GROUP BY ta.id;
ビューとは異なり、一時テーブルをクエリすると、データ挿入で使用された元のクエリを実行するのではなく、一時テーブルから直接データが取得されます。場合によっては、これによりクエリのパフォーマンスが向上することがあります。
一時テーブルを削除する
セッション内のローカル一時テーブルは、セッションの終了後、データとテーブル スキーマの両方とともに自動的に削除されます。トランザクション内のグローバル一時テーブルは、トランザクションの終了時に自動的にクリアされますが、テーブル スキーマは残るため、手動で削除する必要があります。
ローカル一時テーブルを手動で削除するには、 DROP TABLEまたはDROP TEMPORARY TABLE構文を使用します。例:
DROP TEMPORARY TABLE top_50_eldest_authors;
グローバル一時テーブルを手動で削除するには、 DROP TABLEまたはDROP GLOBAL TEMPORARY TABLE構文を使用します。例:
DROP GLOBAL TEMPORARY TABLE top_50_eldest_authors_global;
制限
TiDB の一時テーブルの制限については、 他の TiDB 機能との互換性の制限参照してください。