MySQL 一時テーブルについての深い理解

概要

MySQL には、外部一時テーブルと内部一時テーブルの 2 つの主なタイプの一時テーブルがあります。 。外部一時テーブルは、ステートメント createtemporary table.... によって作成された一時テーブルです。一時テーブルはこのセッションでのみ有効です。セッションが切断されると、一時テーブルのデータは自動的にクリアされます。内部一時テーブルには主に 2 つのタイプがあり、1 つは information_schema 内の一時テーブル、もう 1 つはセッションがクエリを実行するときであり、実行計画に「一時テーブルを使用する」が含まれている場合、一時テーブルが生成されます。内部一時テーブルと外部一時テーブルの違いの 1 つは、内部一時テーブルからはテーブル構造定義ファイルを参照できないことです。外部一時テーブルのテーブル定義ファイル frm は通常 #sql{プロセス ID}_{スレッド ID}_シリアル番号で構成されているため、異なるセッションでも同じ名前の一時テーブルを作成できます。

一時テーブル

一時テーブルと通常のテーブルの主な違いは、インスタンス、セッション、またはステートメントの終了後にデータが自動的にクリーンアップされるかどうかです。たとえば、内部一時テーブルでクエリ中に中間結果セットを保存したい場合、クエリの完了後に一時テーブルはユーザー テーブルの構造やデータに影響を与えることなく自動的にリサイクルされます。また、異なるセッションの一時テーブルには同じ名前を付けることができるため、複数のセッションでクエリを実行する場合に一時テーブルを使用する場合、名前が重複する心配がありません。 5.7 で一時表スペースが導入された後は、すべての一時表スペースが (非圧縮で) 一時表スペースに保管され、一時表スペース内のデータを再利用できます。一時テーブルは Innodb エンジンだけでなく、myisam エンジン、メモリ エンジンなどもサポートします。したがって、一時テーブルにエンティティ (idb ファイル) が表示されることはありませんが、必ずしもメモリ テーブルである必要はなく、一時テーブル スペースに格納される場合もあります。

一時テーブル VS メモリ テーブル

一時テーブルは、innodb エンジン テーブルまたはメモリ エンジン テーブルのいずれかになります。ここでのいわゆるメモリ テーブルはメモリ エンジン テーブルを指します。テーブル作成ステートメント create table...engine=memory を通じて、すべてのデータはメモリ内にあります。テーブル構造は frm を通じて管理されます。同じ内部メモリ エンジンの場合table では、frm ファイルが表示されません。ディスク上の information_schema ディレクトリさえも表示されません。 MySQL 内では、information_schema の一時テーブルには、innodb エンジンの一時テーブルとメモリ エンジンの一時テーブルの 2 つのタイプが含まれます。たとえば、TABLES テーブルはメモリ一時テーブルに属し、列と processlist は innodb エンジン一時テーブルに属します。メモリ テーブル内のすべてのデータはメモリ内にあります。メモリ内のデータ構造は配列 (ヒープ テーブル) です。すべてのデータ操作はメモリ内で完了します。データ量が少ないシナリオでは、速度は比較的高速です (物理 IO 操作はありません)。が関与している）。しかし、結局のところ、メモリは有限なリソースです。したがって、データ量が比較的大きい場合、メモリ テーブルの使用は適していません。代わりに、ディスク一時テーブル (innodb エンジン) を使用することを選択してください。この一時テーブルは、B を使用します。 -tree ストレージ構造 (innodb エンジン) Innodb バッファプール リソースは共有されており、一時テーブル内のデータはバッファプールのホット データに一定の影響を与える可能性があり、また、操作には物理 IO が含まれる場合があります。メモリ エンジン テーブルは実際に Btree インデックスやハッシュ インデックスを含むインデックスを作成できるため、クエリ速度は非常に高速ですが、主な欠点はメモリ リソースが限られていることです。

一時テーブルを使用するシナリオ

前述したように、実行計画に「一時テーブルを使用する」が含まれている場合、一時テーブルが使用されます。

テスト テーブルの構造は次のとおりです。

mysql> show create table t1_normal\G
*************************** 1. row ***************************
       Table: t1_normal
Create Table: CREATE TABLE `t1_normal` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=770023 DEFAULT CHARSET=utf8

シナリオ 1:結合

mysql> explain select * from t1_normal union select * from t1_normal; 
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ 
| 1 | PRIMARY | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | 2 | UNION | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

結合演算の意味は、2 つのサブクエリ結果の結合を取得することです。重複データは 1 行のみ保持します。主キーを持つ一時テーブルを作成することで、「重複」の問題を解決し、一時テーブルを介して最終結果セットを保存できます。そのため、追加項目の「一時テーブルの使用」を確認できます。実行計画。 Union に関連する操作は Union All です。これも 2 つのサブクエリの結果をマージしますが、重複の問題は解決しません。したがって、union all の場合は「重複除去」の意味がないため、一時テーブルは必要ありません。

mysql> explain select * from t1_normal  union  all select * from t1_normal;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | PRIMARY     | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  |
|  2 | UNION       | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+

シナリオ 2: group by

mysql> explain select c1,count(*) as count from t1_normal group by c1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                           |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
|  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+

group by の意味は、指定された列によってグループ化され、デフォルトで指定された列によって順序付けされることです。上記の SQL ステートメントの意味は、t1_normal 内のデータを列 c1 の値でグループ化し、c1 の各列値のレコード数をカウントすることです。実行計画から、「一時テーブルの使用; ファイルソートの使用」が表示されます。グループ化の場合、最初に各値の出現回数をカウントする必要があります。これには、一時テーブルを使用して素早く見つける必要があります。テーブルが存在しない場合は、レコードが存在する場合は、レコードを挿入します。レコードが存在する場合、カウントが累積されるため、「一時的なものを使用しています」と表示されます。また、グループ化は並べ替えを意味するため、列 c1 に従ってレコードを並べ替える必要があるため、「ファイルソートの使用」と表示されます。

1). filesort の削除

実は、group by では「並べ替えの意味」の削除も表示できます。

mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
|  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

ステートメントに「order by null」を追加すると、「Using filesort」が実行計画に表示されなくなることがわかります。

2). 一時テーブルを削除します

mysql> explain select SQL_BIG_RESULT c1,count(*) as count from t1_normal group by c1; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+

可以看到执行计划中已经没有了“Using temporary”，所以group by并非一定依赖临时表，临时表在group by中的作用主要是“去重”。所以，实际上有另外一种方式，不使用临时表，直接利用sort_buffer排序(sort_buffer不够时，进行文件排序，具体而言是每一个有序数组作为一个单独文件，然后进行外排归并)，然后再扫描得到聚合后的结果集。

3).SQL_BIG_RESULT

同时我们语句中用到了“SQL_BIG_RESULT”这个hint，正是因为这个hint导致了我们没有使用临时表，先说说SQL_BIG_RESULT和SQL_SMALL_RESULT的含义。

SQL_SMALL_RESULT：显示指定用内存表(memory引擎)

SQL_BIG_RESULT：显示指定用磁盘临时表(myisam引擎或innodb引擎)

两者区别在于，使用磁盘临时表可以借助主键做去重排序，适合大数据量；使用内存表写入更快，然后在内存中排序，适合小数据量。下面是从MySQL手册中摘录的说明。

SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. 

For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements. 

For SQL_SMALL_RESULT, MySQL uses fast temporary tables to store the resulting table instead of using sorting. 

This should not normally be needed.

回到问题本身，这里MySQL优化器根据hint知道需要使用磁盘临时表，而最终直接选择了数组存储+文件排序这种更轻量的方式。

如何避免使用临时表

通常的SQL优化方式是让group by 的列建立索引，那么执行group by时，直接按索引扫描该列，并统计即可，也就不需要temporary和filesort了。

mysql> alter table t1_normal add index idx_c1(c1);
Query OK, 0 rows affected (1 min 23.82 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null;
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t1_normal | NULL       | index | idx_c1        | idx_c1 | 5       | NULL | 523848 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+

相关参数与状态监控

1).参数说明

max_heap_table_size

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow,The value of the variable is used to calculate MEMORY table MAX_ROWS values.

这个参数主要针对用户创建的MEMORY表，限制内存表最大空间大小，注意不是记录数目，与单条记录的长度有关。如果超出阀值，则报错。ERROR 1114 (HY000): The table 'xxx' is full

tmp_table_size

The maximum size of internal in-memory temporary tables.

对于用户手工创建的内存表，只有参数max_heap_table_size起作用；对于内部产生的内存表，则参数max_heap_table_size和tmp_table_size同时起作用。对于内部产生的内存表(比如union，group by等产生的临时表)，先是采用内存表(memory表)，然后超过设置的阀值(max_heap_table_size,tmp_table_size)就会转为磁盘表，使用innodb引擎或者myisam引擎，通过参数internal_tmp_disk_storage_engine指定。

tmpdir

如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下

2.状态监控

Created_tmp_tables，内部临时表数目

Created_tmp_disk_tables，磁盘临时表数目

3.information_schema相关

mysql> create temporary table t1_tmp(id int primary key,c1 int); Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO; +----------+---------------+--------+-------+----------------------+---------------+ | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED | +----------+---------------+--------+-------+----------------------+---------------+ | 10063 | #sql693d_29_0 | 5 | 45 | FALSE | FALSE | +----------+---------------+--------+-------+----------------------+---------------+

总结

本文详细介绍了MySQL中临时表的核心特征，按需创建并且自动销毁，对于纯内存的数据特别适合，但为了避免内存不可控，实际上不仅仅有内存临时表，还有磁盘临时表。临时表和内存表本没有直接关联，因为临时表既可以是memory引擎，又可以innodb引擎将两者联系到了一起，实际上不同类别的临时表也是用到了不同引擎的优势。临时表使用的典型场景是union和group by。为了消除临时表，我们需要对group by列添加索引，或者对于大结果集，使用SQL_BIG_RESULT等。最后本文介绍了临时表相关的参数和状态变量，以及information_schema中的临时表信息。

推荐教程：《MySQL教程》

以上がMySQL 一時テーブルについての深い理解の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。