`MySQL`的`临时表`：`内部`临时表的`创建`、`使用`和`销毁`机制。

MySQL 内部临时表：创建、使用与销毁机制详解

大家好，今天我们来深入探讨 MySQL 中内部临时表的创建、使用和销毁机制。临时表是 MySQL 在执行复杂查询过程中用于存储中间结果的一种重要机制。理解它的运作方式对于优化 SQL 查询，提升数据库性能至关重要。

临时表主要分为两种：用户临时表和内部临时表。用户临时表是我们通过 CREATE TEMPORARY TABLE 语句显式创建的，而内部临时表则是由 MySQL 服务器自动创建的，用于辅助查询执行。今天我们重点关注内部临时表。

内部临时表的创建时机

内部临时表并非总是会被创建，只有当查询需要时，MySQL 才会动态地创建它们。以下是一些常见的触发内部临时表创建的情况：

1. ORDER BY 和 GROUP BY 子句与索引冲突: 当查询包含 ORDER BY 或 GROUP BY 子句，但优化器无法使用索引来满足排序或分组需求时，MySQL 可能会创建一个临时表来进行排序或分组。
2. UNION 和 UNION ALL 操作: UNION 操作需要对结果集进行去重，UNION ALL 虽然不进行去重，但在某些情况下也可能使用临时表。
3. 子查询物化: 某些类型的子查询（尤其是相关子查询）可能会被物化，即将其结果存储在一个临时表中，以便后续查询使用。
4. 多表连接中的中间结果: 在复杂的 JOIN 操作中，MySQL 可能会创建临时表来存储中间连接结果，然后再与其他表进行连接。
5. DISTINCT 操作: 当查询包含 DISTINCT 关键字，且没有合适的索引可以使用时，MySQL 可能会创建一个临时表来进行去重。

举例说明:

考虑以下查询：

SELECT col1, col2
FROM my_table
ORDER BY col3;

如果 my_table 表的 col3 列上没有索引，或者优化器认为使用索引的成本过高，MySQL 可能会创建一个临时表来存储 my_table 的数据，然后对临时表按照 col3 进行排序。

内部临时表的存储引擎选择

内部临时表可以使用不同的存储引擎，具体选择取决于多个因素，包括 MySQL 的版本、配置参数以及查询的具体需求。常见的存储引擎包括：

• MEMORY 存储引擎 (Heap): MEMORY 存储引擎将数据存储在内存中，速度非常快，但数据易失，服务器重启后数据会丢失。MEMORY 存储引擎适用于小规模的临时表，对性能要求较高，且数据不需要持久化的情况。
• MyISAM 存储引擎: MyISAM 存储引擎将数据存储在磁盘上，数据持久化，但速度相对较慢。MyISAM 存储引擎适用于大规模的临时表，或者需要持久化的情况。
• InnoDB 存储引擎: 从 MySQL 5.6.3 版本开始，InnoDB 存储引擎也可以用于内部临时表。InnoDB 存储引擎提供事务支持和行级锁，适用于需要事务安全性和并发控制的场景。
• TempTable 存储引擎: MySQL 5.7.2 及更高版本引入了 TempTable 存储引擎，它是一个基于内存的存储引擎，旨在取代 MEMORY 存储引擎，提供更好的性能和可扩展性。 如果 internal_tmp_mem_storage_engine 系统变量设置为 TempTable 并且 temptable_max_ram 系统变量允许，则使用 TempTable。
• Disk TempTable 存储引擎: 当 TempTable 存储引擎使用的内存超过 temptable_max_ram 限制时，MySQL 会自动切换到 Disk TempTable 存储引擎，将数据存储在磁盘上。

系统变量影响:

以下系统变量会影响内部临时表存储引擎的选择：

• internal_tmp_mem_storage_engine: 指定用于内部临时表的内存存储引擎。默认值是 TempTable (MySQL 5.7.2 及更高版本) 或 MEMORY (早期版本)。
• tmp_table_size: 指定 MEMORY 存储引擎的临时表的最大大小。如果临时表的大小超过这个值，MySQL 会将其转换为磁盘上的 MyISAM 临时表。
• max_heap_table_size: 指定 MEMORY 存储引擎的最大大小。这个变量的值必须大于或等于 tmp_table_size。
• temptable_max_ram: 指定 TempTable 存储引擎可以使用的最大内存量。

存储引擎选择的优先级 (大致):

1. 首先根据 internal_tmp_mem_storage_engine 确定内存存储引擎 (TempTable 或 MEMORY)。
2. 如果选择了 TempTable，并且临时表的大小超过 temptable_max_ram，则切换到 Disk TempTable 存储引擎。
3. 如果选择了 MEMORY，并且临时表的大小超过 tmp_table_size，则切换到 MyISAM 存储引擎。

如何查看临时表使用的存储引擎:

可以使用 EXPLAIN 语句来查看查询的执行计划，从而判断是否使用了临时表，并推断其使用的存储引擎。

EXPLAIN SELECT col1, col2 FROM my_table ORDER BY col3;

在 EXPLAIN 的输出中，如果 Extra 列包含 "Using temporary"，则表示使用了临时表。

内部临时表的创建过程

内部临时表的创建过程大致如下：

1. 优化器判断: 查询优化器根据查询的结构和数据分布，判断是否需要创建临时表。
2. 存储引擎选择: 优化器根据配置参数和查询需求，选择合适的存储引擎。
3. 表结构定义: 优化器定义临时表的结构，包括列名、数据类型等。
4. 数据填充: 将查询的中间结果插入到临时表中。
5. 索引创建 (可选): 根据查询的需求，优化器可能会在临时表上创建索引，以提高查询效率。

代码模拟 (伪代码，仅为说明原理):

// 优化器判断需要创建临时表
if (needsTemporaryTable(query)) {

    // 选择存储引擎
    StorageEngine engine = chooseStorageEngine(query, config);

    // 定义表结构
    TableDefinition tableDef = defineTableStructure(query);

    // 创建临时表
    TemporaryTable tempTable = createTemporaryTable(tableDef, engine);

    // 填充数据
    populateTemporaryTable(tempTable, queryResult);

    // 创建索引 (可选)
    if (needsIndex(query)) {
        createIndex(tempTable, indexColumns);
    }

    // 后续查询使用临时表
    useTemporaryTable(query, tempTable);
}

内部临时表的使用方式

MySQL 在创建内部临时表之后，会将其作为查询的一部分来使用。具体使用方式取决于查询的类型和优化器的选择。

• 排序和分组: 临时表可以用于存储排序或分组的结果，然后对结果进行进一步处理。
• 子查询物化: 临时表可以用于存储子查询的结果，避免重复执行子查询。
• 连接操作: 临时表可以用于存储中间连接结果，简化复杂的连接操作。

举例说明:

考虑以下查询：

SELECT dept_name, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_name
ORDER BY avg_salary DESC;

在这个查询中，MySQL 可能会创建一个临时表来存储每个部门的平均工资，然后对临时表按照平均工资进行排序。

内部临时表的销毁时机

内部临时表在查询执行完成后会被自动销毁。具体销毁时机取决于临时表的类型和作用域。

• 查询结束: 大多数内部临时表在查询执行完成后立即被销毁。
• 存储过程结束: 在存储过程中创建的临时表在存储过程执行完成后被销毁。
• 连接断开: 如果临时表与某个连接相关联，则在连接断开时被销毁。

注意事项:

• 内部临时表的生命周期很短，只在查询执行期间存在。
• 应用程序无法直接控制内部临时表的创建和销毁。
• 过度使用临时表可能会降低查询性能，因此需要优化 SQL 查询，尽量避免创建不必要的临时表。

如何避免或减少内部临时表的创建

虽然内部临时表在某些情况下是必要的，但过度使用临时表可能会降低查询性能。以下是一些避免或减少内部临时表创建的技巧：

1. 创建合适的索引: 为经常用于 WHERE 子句、ORDER BY 子句和 GROUP BY 子句的列创建索引，可以避免 MySQL 创建临时表来进行排序或分组。
2. 优化 SQL 查询: 尽量避免使用复杂的子查询和 UNION 操作，可以减少临时表的创建。
3. 使用 EXISTS 代替 DISTINCT: 在某些情况下，可以使用 EXISTS 关键字代替 DISTINCT 关键字，避免创建临时表来进行去重。
4. 调整配置参数: 可以调整 tmp_table_size 和 max_heap_table_size 等配置参数，控制临时表的大小和存储引擎，从而影响临时表的创建和使用。
5. 使用 STRAIGHT_JOIN: 在某些情况下，使用 STRAIGHT_JOIN 强制 MySQL 按照指定的顺序进行连接，可以避免创建不必要的临时表。 (慎用，除非非常清楚执行计划)

举例说明:

假设有一个查询：

SELECT DISTINCT city FROM customers WHERE country = 'USA';

如果 city 列上没有索引，MySQL 可能会创建一个临时表来进行去重。为了避免创建临时表，可以在 city 列上创建一个索引：

CREATE INDEX idx_city ON customers (city);

或者，可以使用 EXISTS 关键字代替 DISTINCT 关键字：

SELECT city FROM customers WHERE country = 'USA' GROUP BY city; -- 或者使用 GROUP BY，效果可能类似

使用EXPLAIN分析临时表的使用

EXPLAIN语句是分析MySQL查询执行计划的关键工具。它可以帮助我们了解查询是否使用了临时表，以及使用了哪种类型的临时表。

EXPLAIN输出中的关键列:

• select_type: 显示查询的类型。如果包含 DERIVED，则表示使用了派生表，派生表通常会被物化为临时表。
• Extra: 包含关于查询执行的额外信息。以下是一些与临时表相关的常见值：
  • Using temporary: 表示使用了临时表。
  • Using filesort: 表示需要对结果进行排序，并且无法使用索引，因此可能需要创建临时表。
  • Using index: 表示使用了索引，可以避免创建临时表。

示例:

EXPLAIN SELECT col1, col2 FROM my_table ORDER BY col3;

如果 EXPLAIN 输出的 Extra 列包含 "Using temporary; Using filesort"，则表示查询使用了临时表，并且需要对结果进行排序。

分析和优化:

通过分析 EXPLAIN 的输出，可以找出导致临时表创建的原因，并采取相应的优化措施，例如创建索引、优化 SQL 查询等。

案例分析：优化含有临时表的查询

假设我们有一个 orders 表和一个 customers 表，结构如下：

orders 表:

Column	Data Type
order_id	INT
customer_id	INT
order_date	DATE
amount	DECIMAL(10,2)

customers 表:

Column	Data Type
customer_id	INT
customer_name	VARCHAR(255)
city	VARCHAR(255)

现在，我们有一个查询，需要找出每个城市订单总金额最高的订单日期：

SELECT c.city, o.order_date, SUM(o.amount) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.city, o.order_date
HAVING SUM(o.amount) = (SELECT MAX(total_amount)
                          FROM (SELECT c.city, o.order_date, SUM(o.amount) AS total_amount
                                FROM orders o
                                JOIN customers c ON o.customer_id = c.customer_id
                                GROUP BY c.city, o.order_date) AS tmp
                          WHERE tmp.city = c.city)
ORDER BY c.city;

这个查询使用了子查询和 GROUP BY 子句，很可能会创建临时表。

优化步骤:

1. 使用 EXPLAIN 分析: 使用 EXPLAIN 语句分析查询的执行计划，发现确实使用了临时表。
2. 优化子查询: 可以使用窗口函数来避免子查询。
3. 创建索引: 在 orders 表的 customer_id 列和 customers 表的 customer_id 列上创建索引，加速连接操作。 同时在customers表的city列上创建索引。

优化后的查询:

SELECT city, order_date, total_amount
FROM (
    SELECT
        c.city,
        o.order_date,
        SUM(o.amount) AS total_amount,
        ROW_NUMBER() OVER (PARTITION BY c.city ORDER BY SUM(o.amount) DESC) AS rn
    FROM
        orders o
    JOIN
        customers c ON o.customer_id = c.customer_id
    GROUP BY
        c.city, o.order_date
) AS ranked_orders
WHERE rn = 1
ORDER BY city;

这个优化后的查询使用了窗口函数 ROW_NUMBER() 来计算每个城市订单总金额的排名，避免了子查询和临时表的创建。 通过 EXPLAIN 确认不再使用临时表。

内部临时表使用的限制与注意事项

虽然内部临时表是 MySQL 执行查询的重要辅助工具，但其使用也存在一些限制和需要注意的地方：

• 资源消耗： 临时表的创建和维护需要消耗大量的系统资源，包括 CPU、内存和磁盘 I/O。过度使用临时表会导致服务器性能下降。
• 存储引擎限制： 并非所有存储引擎都适合用于内部临时表。例如，MEMORY 存储引擎虽然速度快，但数据易失，不适合存储大量数据。
• 数据类型限制： 某些数据类型可能不适合存储在临时表中。例如，BLOB 和 TEXT 类型的数据可能会导致临时表变得非常大。
• 并发问题： 在高并发环境下，临时表的创建和销毁可能会导致锁冲突，影响数据库的并发性能。
• 版本差异： 不同版本的 MySQL 在内部临时表的实现上可能存在差异。因此，需要根据具体的 MySQL 版本进行优化。

最佳实践建议：

• 尽量避免创建不必要的临时表。
• 选择合适的存储引擎。
• 合理控制临时表的大小。
• 监控临时表的使用情况。
• 定期优化 SQL 查询。

小结

内部临时表是 MySQL 查询执行引擎的重要组成部分，理解其创建、使用和销毁机制对于优化 SQL 查询，提升数据库性能至关重要。合理利用索引、优化 SQL 查询、调整配置参数是减少临时表创建，提升查询效率的关键。 通过 EXPLAIN 分析查询计划，我们可以更好地理解 MySQL 的执行过程，并做出针对性的优化。

总而言之，深入理解内部临时表的工作原理，可以帮助我们编写出更高效的 SQL 查询，充分发挥 MySQL 数据库的性能。

希望今天的讲解能够帮助大家更好地理解 MySQL 内部临时表的机制。谢谢大家！