max_heap_table_sizeとinnodb_temp_table_size
2024-07-27
MySQLとMariaDBにおける一時テーブルのサイズ制限
一時テーブルのサイズが大きくなると、パフォーマンスやメモリ使用量に影響を与える可能性があります。そのため、一時テーブルのサイズを制限することが重要です。
一時テーブルのサイズ制限方法
一時テーブルのサイズを制限するには、以下の方法があります。
- MySQL
max_heap_table_sizeサーバ変数を設定するCREATE TEMPORARY TABLEステートメントでMAX_ROWSオプションを使用する
max_heap_table_size サーバ変数は、メモリ上に作成できる一時テーブルの最大サイズを指定します。この変数のデフォルト値は 16MB です。
MAX_ROWS オプション
CREATE TEMPORARY TABLE ステートメントで MAX_ROWS オプションを使用すると、一時テーブルに保存できる行の最大数を指定できます。
例
# MySQL
SET max_heap_table_size = 32M;
CREATE TEMPORARY TABLE t (
id INT,
name VARCHAR(255)
) MAX_ROWS 1000;
# MariaDB
SET innodb_temp_table_size = 32M;
CREATE TEMPORARY TABLE t (
id INT,
name VARCHAR(255)
) MAX_ROWS 1000;
- 一時テーブルのサイズ制限は、クエリのパフォーマンスに影響を与える可能性があります。
- 一時テーブルのサイズ制限が小さすぎると、クエリが失敗する可能性があります。
- 一時テーブルのサイズ制限を設定する前に、ワークロードを分析することをお勧めします。
-- MySQL
SET max_heap_table_size = 32M;
CREATE TEMPORARY TABLE t (
id INT,
name VARCHAR(255)
) MAX_ROWS 1000;
INSERT INTO t (id, name) VALUES (1, 'John Doe');
INSERT INTO t (id, name) VALUES (2, 'Jane Doe');
SELECT * FROM t;
DROP TEMPORARY TABLE t;
-- MariaDB
SET innodb_temp_table_size = 32M;
CREATE TEMPORARY TABLE t (
id INT,
name VARCHAR(255)
) MAX_ROWS 1000;
INSERT INTO t (id, name) VALUES (1, 'John Doe');
INSERT INTO t (id, name) VALUES (2, 'Jane Doe');
SELECT * FROM t;
DROP TEMPORARY TABLE t;
説明
SET max_heap_table_size = 32M;ステートメントは、メモリ上に作成できる一時テーブルの最大サイズを 32MB に設定します。CREATE TEMPORARY TABLE t (id INT, name VARCHAR(255)) MAX_ROWS 1000;ステートメントは、tという名前の一時テーブルを作成します。このテーブルは 2 つの列を持ち、最大 1000 行を保存できます。INSERT INTO t (id, name) VALUES (1, 'John Doe');およびINSERT INTO t (id, name) VALUES (2, 'Jane Doe');ステートメントは、2 つの行をtテーブルに挿入します。SELECT * FROM t;ステートメントは、tテーブル内のすべての行を選択します。DROP TEMPORARY TABLE t;ステートメントは、tテーブルを削除します。
実行結果
| id | name |
|-----|----------|
| 1 | John Doe |
| 2 | Jane Doe |
- MySQL
CREATE TEMPORARY TABLEステートメントでENGINEオプションを使用してMEMORYストレージエンジンを指定する
ENGINE オプション
# MySQL
SET tmp_table_size = 32M;
CREATE TEMPORARY TABLE t (
id INT,
name VARCHAR(255)
) ENGINE = MEMORY;
# MariaDB
SET tmp_table_size = 32M;
CREATE TEMPORARY TABLE t (
id INT,
name VARCHAR(255)
) ENGINE = MEMORY;
tmp_table_sizeサーバ変数は、ディスク上に作成できる一時テーブルのサイズのみを制限します。メモリ上に作成される一時テーブルには影響しません。MEMORYストレージエンジンは、ディスク上のストレージエンジンよりも高速ですが、メモリ使用量が多くなります。
mysql mariadb temp-tables
