MySQL 一時テーブルについての深い理解-mysql チュートリアル-php.cn

概要

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

ログイン後にコピー