MySQL问答系列之什么情况下会用到临时表

MySQL中的临时表是一种在当前连接中使用的特殊类型的表，它仅对创建它的会话可见，并在该会话结束时自动删除。临时表主要用于存储中间结果，以帮助处理复杂的查询操作。临时表分为内存临时表和磁盘临时表，前者使用memory存储引擎，后者通常使用myisam（在MySQL 5.7.6之前）或innodb（5.7.6之后，默认存储引擎可通过`internal_tmp_disk_storage_engine`参数设置）存储引擎。 **临时表的用途：** 1. **UNION查询**：在合并多个查询结果时，MySQL可能会创建临时表来合并结果集。 2. **TEMPTABLE算法或UNION查询中的视图**：当查询涉及到视图并且使用了UNION操作，MySQL可能需要临时表来存储中间结果。 3. **ORDER BY和GROUP BY子句不一致**：如果在查询中同时使用了ORDER BY和GROUP BY，但它们不是基于相同的列，MySQL可能会创建临时表。 4. **连接操作中，ORDER BY的列不在驱动表中**：在多表连接时，如果ORDER BY的列不是连接操作的主表（驱动表）的一部分，临时表可能会被用来进行排序。 5. **DISTINCT查询加上ORDER BY**：当执行DISTINCT操作后，还要求对结果进行排序，MySQL可能使用临时表。 6. **SQL_SMALL_RESULT选项**：当在查询语句中使用SQL_SMALL_RESULT，MySQL会尝试将临时表保留在内存中，即使数据量较大。 7. **FROM子查询**：从子查询中获取的结果可能被放入临时表，以便进一步处理。 8. **子查询或semi-join操作**：在执行这类操作时，MySQL可能会创建临时表来存储中间结果。 **何时创建磁盘临时表：** 1. **包含BLOB/TEXT列**：如果临时表需要存储这些大数据类型，将自动创建磁盘临时表。 2. **GROUP BY或DISTINCT列超过512字符**：对于非二进制列，长度超过512字符；对于二进制列，超过512字节。 3. **查询中的列长度超过512**：在SELECT、UNION、UNION ALL查询中，涉及的列最大长度超过512字节。 4. **执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令**：这些命令可能涉及BLOB列类型，因此可能创建磁盘临时表。 **配置临时表存储引擎：** 自MySQL 5.7.5起，可以通过`internal_tmp_disk_storage_engine`参数设置磁盘临时表的存储引擎，默认为InnoDB。而`default_tmp_storage_engine`则用于设置CREATE TEMPORARY TABLE时创建的临时表的默认引擎。在MySQL 5.6.3之后，这个参数允许用户在创建临时表时指定内存或磁盘存储引擎。 总结来说，MySQL在处理复杂查询和需要存储中间结果的情况下使用临时表，以优化查询性能。临时表的选择和存储位置取决于数据量、数据类型以及MySQL服务器的配置。了解何时会创建临时表以及如何配置存储引擎，对于优化数据库性能和管理资源至关重要。