MySQL临时表优化性能详解（新手必看）

在 MySQL 中，用户可以把数据库和表归组到逻辑和物理表空间中，这样做可以提高资源的利用率。

在 MySQL 中，使用 CREATE TABLESPACE 语句可以创建一个通用表空间。通用表空间允许用户自由地选择表和表空间之间的映射，从而为数据表提供更大的灵活性。

例如，可以创建一个表空间并设置该表空间应包含的表类型。这使得在同一个表空间中的用户可以对所有表进行分组，从而在文件系统中使用单独的文件来存储他们的数据。同时，通用表空间还实现了元数据锁，以支持更高效的数据管理和访问。

优化普通 SQL 临时表性能是 MySQL 的目标之一。首先，通过优化临时表在磁盘中的不必要的步骤，使得临时表的创建和移除成为一个轻量级的操作。将临时表移动到一个单独的表空间中，恢复临时表的过程就变得非常简单，在启动时重新创建临时表即可。

MySQL 去掉了临时表中不必要的持久化。临时表仅仅在连接和会话内被创建，然后通过服务的生命周期绑定它们。通过移除不必要的 UNDO（回滚）和 REDO（重做）日志，改变缓冲和锁，从而为临时表做了优化操作。

MySQL 引入了一种名为 undo_tablespace 的额外表空间类型，用于存储 UNDO 日志。undo_tablespace 类型的日志存储在一个单独的临时表空间中，并且它在恢复期间不会被调用，而是在回滚操作中才会被调用。

MySQL 为临时表设定了一个特别类型，称为“内在临时表”。内在临时表和普通临时表很像，只是内在临时表使用宽松的 ACID 和 MVCC 语义。

MYSQL 为了提高临时表的相关性能，对临时表的相关部分进行了大幅修改，包括：引入新的临时表空间（ibtmp1）；对于临时表的 DDL，不持久化相关表定义；对于临时表的 DML，不写 redo、关闭 change buffer 等。

InnoDB 临时表元数据不再存储于 InnoDB 系统表，而是存储在 innodb_temp_table_info 中，包含所有用户和系统创建的临时表信息。该表在第一次运行 SELECT 时被创建，下面举例说明。

【实例】MySQL 的临时表。

mysql> SELECT * FROM information_schema.innodb_temp_table_info;
Empty set (0.00 sec)

mysql> create temporary table temp_1(id int,name varchar(100))default charset
utf8;
Query OK, 0 rows affected (0.00 sec)
　
mysql> select * from information_schema.innodb_temp_table_info;
+----------+--------------+--------+------------+
| TABLE_ID | NAME         | N_COLS | SPACE      |
+----------+--------------+--------+------------+
|     1142 | #sql14a0_9_1 |    5   | 4243767290 |
+----------+--------------+--------+------------+

MySQL 使用了独立的临时表空间来存储临时表数据，但不能是压缩表。临时表空间在实例启动的时候进行创建，在 shutdown 的时候进行删除，即为所有非压缩的 InnoDB 临时表提供一个独立的表空间。默认的临时表空间文件为 ibtmp1，位于数据目录中。通过 innodb_temp_data_file_path 参数可指定临时表空间的路径和大小（默认为 12MB）。只有重启实例才能回收临时表空间文件 ibtmp1 的大小。CREATE TEMPORARY TABLE 和 USING TEMPORARY TABLE 将共用这个临时表空间。

mysql> SHOW VARIABLES LIKE 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+

在 MySQL 中，临时表在连接断开或者数据库实例关闭的时候被删除，从而提高了性能。只有临时表的元数据使用了 redo 保护，以保护元数据的完整性，以便异常启动后进行清理工作。

临时表的元数据在 MySQL 中使用了一张独立的表（innodb_temp_table_info）进行保存，不用使用 redo 保护，元数据也只保存在内存中。但这有一个前提，即必须使用共享的临时表空间，如果使用 file-per-table，则仍然需要持久化元数据，以便异常恢复和清理。临时表需要 UNDO 日志，用于 MySQL 运行时的回滚。

在 MySQL 中有一个系统选项 internal_tmp_disk_storage_engine，可定义磁盘临时表的引擎类型，默认为 InnoDB，可选 MyISAM。在这以前，只能使用 MyISAM。在 MySQL 5.6.3 以后新增的参数 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 创建的临时表的存储引擎的，在以前默认是 MEMORY。

查看结果如下：

mysql> SHOW VARIABLES LIKE '%engine%';
+-----------------------------------------+---------------+
| Variable_name                           | Value         |
+-----------------------------------------+---------------+
| default_storage_engine                  | InnoDB        |
| default_tmp_storage_engine              | InnoDB        |
| disabled_storage_engines                |               |
| internal_tmp_disk_storage_engine        | InnoDB        |
| internal_tmp_mem_storage_engine         | TempTable     |
| secondary_engine_cost_threshold         | 100000.000000 |
| show_create_table_skip_secondary_engine | OFF           |
| use_secondary_engine                    | ON            |
+-----------------------------------------+---------------+

从结果中可以看出，当前 MySQL 实例中的默认存储引擎是 InnoDB，默认临时存储引擎也是 InnoDB。禁用的存储引擎为空，说明没有禁用的存储引擎。

此外，内部临时磁盘存储引擎和内部临时内存存储引擎分别是 InnoDB 和 TempTable。最后，使用 secondary 引擎的设置为 ON，这意味着 MySQL 可以在 InnoDB 存储引擎不可用时使用其他存储引擎（如 MyISAM）。