37 | 什么时候会使用内部临时表？-MySQL 实战 45 讲

当前位置:　首页>> 技术小册>> MySQL 实战 45 讲

### 37 | 什么时候会使用内部临时表？

在深入探讨MySQL数据库的高级优化策略时，理解内部临时表的使用场景及其背后的机制至关重要。内部临时表是MySQL在执行复杂查询或优化查询执行计划时自动创建的一种数据结构，它们不直接对应于磁盘上的物理文件，而是存储在内存或临时文件系统中，根据查询的复杂度和数据量的大小动态调整其存储位置。了解何时MySQL会选择使用内部临时表，以及这些选择如何影响查询性能，对于数据库管理员和开发者来说都是一项宝贵的技能。

#### 一、内部临时表的基本概念

在MySQL中，内部临时表主要用于以下几种情况：

1. **解决复杂连接（JOINs）**：当MySQL优化器决定使用嵌套循环连接（Nested Loop Join）或块嵌套循环连接（Block Nested Loop Join）等算法来执行多表连接时，可能会为了减少重复计算或保存中间结果而创建内部临时表。

2. **子查询优化**：在查询中使用了子查询，尤其是那些被优化为半连接（Semi-Join）、物化子查询（Materialized Subquery）或派生表（Derived Table）时，MySQL可能会使用内部临时表来存储子查询的结果集。

3. **排序和分组（ORDER BY & GROUP BY）**：当查询包含复杂的排序和分组操作时，如果无法直接利用索引进行排序，MySQL可能会先将结果集存储到内部临时表中，然后再对临时表进行排序或分组。

4. **DISTINCT去重**：在处理包含DISTINCT关键字的查询时，如果去重操作不能通过索引直接完成，MySQL可能会使用内部临时表来存储去重后的结果。

5. **UNION、INTERSECT、EXCEPT操作**：当执行这些集合操作时，MySQL通常需要将每个集合的结果存储到内部临时表中，以便后续进行合并、交集或差集运算。

6. **复杂的窗口函数（Window Functions）**：在MySQL 8.0及以上版本中，窗口函数提供了一种强大的数据处理能力，但在处理复杂的窗口函数时，如果不能直接通过索引或内存中的数据结构完成计算，MySQL可能会借助内部临时表来辅助计算。

#### 二、内部临时表的使用场景详解

##### 1. 解决复杂连接

在执行多表连接时，如果连接条件复杂或数据量庞大，MySQL优化器可能会选择使用内部临时表来减少中间结果集的重复计算。例如，当使用多个JOIN条件且这些条件之间存在复杂的逻辑依赖时，MySQL可能会先将部分表的结果集存储在临时表中，然后再与下一个表进行连接。

##### 2. 子查询优化

子查询是SQL中常见的特性，用于在查询中嵌套另一个查询。当子查询被优化为物化子查询时，MySQL会首先执行子查询，将结果存储在内部临时表中，然后在主查询中使用这个临时表。这种优化策略可以减少重复计算，并可能提高查询性能，特别是在子查询结果集较大或子查询本身计算复杂时。

##### 3. 排序和分组

对于包含ORDER BY或GROUP BY子句的查询，如果MySQL无法直接利用索引进行排序或分组，它可能会将查询结果先存储到内部临时表中，然后再对临时表进行排序或分组操作。这种情况下，查询性能会受到内存大小和临时表空间配置的影响。如果排序或分组的数据量非常大，可能会导致大量磁盘I/O，从而降低查询性能。

##### 4. DISTINCT去重

当查询中包含DISTINCT关键字时，MySQL需要去除结果集中的重复行。如果去重操作不能通过索引快速完成，MySQL可能会使用内部临时表来存储去重后的结果。这种做法在数据量不大时通常不会影响性能，但在处理大规模数据集时可能会成为性能瓶颈。

##### 5. UNION、INTERSECT、EXCEPT操作

这些集合操作通常要求MySQL将每个操作数（即参与操作的查询结果集）存储在内部临时表中，然后再进行合并、交集或差集运算。由于这些操作可能涉及多个查询结果集的组合，因此内部临时表的使用是不可避免的。然而，通过合理的查询优化和索引策略，可以减少内部临时表的使用，并提高查询性能。

##### 6. 复杂的窗口函数

窗口函数为SQL提供了强大的数据处理能力，允许在结果集的每一行上执行复杂的计算。然而，当窗口函数涉及复杂的分区、排序和聚合操作时，MySQL可能会选择使用内部临时表来辅助计算。特别是当窗口函数与ORDER BY或GROUP BY子句结合使用时，内部临时表的使用变得更加普遍。

#### 三、优化内部临时表使用的策略

尽管内部临时表是MySQL优化查询执行计划的重要工具之一，但它们的使用也会带来一定的性能开销。以下是一些优化内部临时表使用的策略：

1. **优化查询逻辑**：通过重写查询逻辑、减少不必要的JOIN操作、合并子查询等方式来减少内部临时表的使用。

2. **使用合适的索引**：为查询中涉及的表和列创建合适的索引，以便MySQL能够直接利用索引进行排序、分组和去重等操作，从而减少内部临时表的使用。

3. **调整内存和临时表空间配置**：根据服务器的硬件配置和查询需求调整MySQL的内存和临时表空间配置，以确保内部临时表有足够的空间在内存中执行，从而减少磁盘I/O开销。

4. **分析查询执行计划**：使用EXPLAIN或EXPLAIN ANALYZE命令分析查询的执行计划，了解MySQL是如何处理查询的以及内部临时表是如何被使用的。根据执行计划的结果调整查询和索引策略。

5. **升级MySQL版本**：随着MySQL版本的更新，内部临时表的使用和优化策略也在不断改进。升级到较新的MySQL版本可能会获得更好的查询性能和更高效的内部临时表使用策略。

总之，了解内部临时表的使用场景及其背后的机制是优化MySQL查询性能的关键一步。通过合理的查询优化、索引策略和配置调整，可以减少内部临时表的使用并提高查询性能。

该分类下的相关小册推荐：

MySQL必会核心问题

MySQL8.0入门与实践

MySQL从入门到精通(四)

MySQL从入门到精通(三)

细说MySQL(零基础到高级应用)

MySQL从入门到精通(一)

MySQL从入门到精通(二)

SQL零基础到熟练应用(增删改查)

MySQL从入门到精通(五)