分析 `MySQL` `临时表`的`内存`与`磁盘`转换`阈值`：`tmp_table_size` 和 `max_heap_table_size` 的作用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 临时表：内存与磁盘的平衡艺术

大家好，今天我们来深入探讨 MySQL 中临时表，特别是它们在内存和磁盘之间转换的关键参数：tmp_table_size 和 max_heap_table_size。理解这些参数对于优化查询性能，避免不必要的磁盘 I/O，以及有效地利用服务器资源至关重要。

什么是临时表？

在复杂的查询执行过程中，MySQL 可能会创建临时表来存储中间结果。这些临时表只在当前查询会话期间存在，查询结束后会自动删除。临时表的存在是为了分解复杂的查询逻辑，提高查询效率，避免不必要的全表扫描，或者处理排序、分组等操作。

临时表大致可以分为两种类型：

内存临时表 (MEMORY engine): 使用内存作为存储介质，速度非常快，但受内存大小限制。
磁盘临时表 (MyISAM/InnoDB engine): 使用磁盘作为存储介质，容量更大，但速度较慢。

MySQL 会尝试尽可能使用内存临时表，因为速度更快。然而，如果临时表的数据量超过了内存的限制，就会自动转换为磁盘临时表。

`tmp_table_size` 和 `max_heap_table_size`：关键参数

这两个参数控制着 MySQL 如何使用内存来创建临时表：

tmp_table_size: 这个参数控制着所有用户线程可创建的 MEMORY 临时表的最大大小。如果一个 MEMORY 临时表超过了这个大小，它将自动转换为磁盘上的 MyISAM 临时表。这个参数是全局设置，影响所有连接。
max_heap_table_size: 这个参数控制着用户可以创建的 MEMORY 表的最大大小，包括显式创建的 MEMORY 表和隐式创建的临时表。重要的是，max_heap_table_size 也适用于 tmp_table_size 限制下的临时表。也就是说，即使 tmp_table_size 设置得很大，如果 max_heap_table_size 设置得较小，那么内存临时表的大小仍然会受到 max_heap_table_size 的限制。这个参数也是全局设置，影响所有连接。

重要区别: 虽然两者都限制了内存表的大小，但 tmp_table_size 主要针对隐式创建的临时表，而 max_heap_table_size 影响所有 MEMORY 表，包括显式创建的表和临时表。内存临时表的大小实际上取 tmp_table_size 和 max_heap_table_size 中的较小值。

参数的影响和配置

设置过小： 如果 tmp_table_size 和 max_heap_table_size 设置得太小，MySQL 可能会频繁地将内存临时表转换为磁盘临时表，导致性能下降，增加磁盘 I/O。
设置过大： 如果 tmp_table_size 和 max_heap_table_size 设置得太大，可能会占用过多的服务器内存，导致其他查询的内存不足，甚至导致服务器崩溃。

配置建议：

评估服务器内存： 首先要了解服务器的总内存大小，以及 MySQL 可以使用的内存比例。通常，建议将 MySQL 的内存使用限制在总内存的 50%-80% 之间，留下足够的内存给操作系统和其他应用程序。
监控临时表使用情况： 使用 SHOW GLOBAL STATUS LIKE 'Created_tmp_%'; 命令来监控临时表的使用情况。关注以下几个指标：
- Created_tmp_disk_tables: 表示创建的磁盘临时表的数量。
- Created_tmp_tables: 表示创建的内存临时表的数量。
如果 Created_tmp_disk_tables 的数量很高，说明 MySQL 频繁地将内存临时表转换为磁盘临时表，需要考虑增加 tmp_table_size 和 max_heap_table_size 的值。
逐步调整参数： 不要一次性将 tmp_table_size 和 max_heap_table_size 设置得太大。应该逐步增加它们的值，每次增加后都监控服务器的性能，直到找到最佳的平衡点。
考虑查询优化： 在调整 tmp_table_size 和 max_heap_table_size 之前，应该先尝试优化查询语句。通过添加索引、重写查询逻辑等方式，可以减少临时表的使用，从而降低对内存的需求。
使用性能分析工具： 使用 EXPLAIN 命令分析查询语句的执行计划，可以了解 MySQL 是否使用了临时表，以及临时表的大小。还可以使用 MySQL Profiler 等工具来分析查询的性能瓶颈。

修改参数的方法：

可以通过以下方式修改 tmp_table_size 和 max_heap_table_size 的值：

修改 MySQL 配置文件 (my.cnf/my.ini): 这是永久修改参数的方法。在 [mysqld] 节中添加或修改以下行：
```
tmp_table_size = 64M
max_heap_table_size = 64M
```
修改后需要重启 MySQL 服务器才能生效。
使用 SET GLOBAL 命令： 这是临时修改参数的方法。使用以下命令修改参数：
```
SET GLOBAL tmp_table_size = 67108864;  -- 64MB
SET GLOBAL max_heap_table_size = 67108864; -- 64MB
```
这种方法修改的参数只在当前 MySQL 服务器会话期间有效，重启服务器后会恢复到配置文件中的值。

代码示例：

以下是一些代码示例，演示了如何监控临时表的使用情况，以及如何修改 tmp_table_size 和 max_heap_table_size 的值：

-- 查看临时表的使用情况
SHOW GLOBAL STATUS LIKE 'Created_tmp_%';

-- 查看 tmp_table_size 和 max_heap_table_size 的值
SHOW VARIABLES LIKE '%tmp_table_size%';
SHOW VARIABLES LIKE '%max_heap_table_size%';

-- 临时修改 tmp_table_size 和 max_heap_table_size 的值
SET GLOBAL tmp_table_size = 67108864; -- 64MB
SET GLOBAL max_heap_table_size = 67108864; -- 64MB

-- 模拟一个需要创建临时表的查询 (假设 order_date 字段没有索引)
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 10;

-- 使用 EXPLAIN 分析查询计划
EXPLAIN SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 10;

--  优化查询 (添加 order_date 索引)
CREATE INDEX idx_order_date ON orders (order_date);

-- 再次使用 EXPLAIN 分析查询计划，观察是否还使用了临时表
EXPLAIN SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 10;

--  删除索引 (测试完毕后删除)
DROP INDEX idx_order_date ON orders;

临时表转换的详细流程

当 MySQL 需要创建一个临时表来处理查询时，它会遵循以下流程：

评估需求： MySQL 会评估查询的复杂性，以及需要存储的中间结果的数据量。
尝试内存临时表： MySQL 首先尝试创建一个 MEMORY 临时表。它会检查 tmp_table_size 和 max_heap_table_size 的限制，并取两者中的较小值作为内存临时表的最大大小。
监控内存使用： 在将数据写入内存临时表的过程中，MySQL 会持续监控内存的使用情况。
转换到磁盘临时表： 如果内存临时表的大小超过了 tmp_table_size 和 max_heap_table_size 中的较小值，或者 MEMORY 引擎的其他限制 (例如，MEMORY 引擎不支持 BLOB 或 TEXT 类型)，MySQL 会自动将临时表转换为磁盘上的 MyISAM 临时表 (在 MySQL 5.7 及更早版本) 或 InnoDB 临时表 (在 MySQL 8.0 及更高版本)。
使用磁盘临时表： 转换完成后，MySQL 会继续使用磁盘临时表来存储中间结果。

一些会导致临时表上磁盘的常见原因:

超出内存限制: 临时表的数据量超过了 tmp_table_size 和 max_heap_table_size 中的较小值。
使用了 BLOB 或 TEXT 类型: MEMORY 引擎不支持 BLOB 或 TEXT 类型，因此如果临时表中包含这些类型的字段，则必须使用磁盘临时表。
使用了某些函数: 某些函数 (例如 GROUP_CONCAT) 可能会生成很大的中间结果，导致临时表超出内存限制。
复杂的 GROUP BY 或 ORDER BY 操作: 复杂的 GROUP BY 或 ORDER BY 操作可能需要大量的内存来排序和分组数据，从而导致临时表超出内存限制。
缺少索引: 缺少索引会导致 MySQL 进行全表扫描，从而生成大量的中间结果，增加临时表的大小。

如何避免磁盘临时表？

避免磁盘临时表的关键在于优化查询，减少需要存储的中间结果的数据量。以下是一些常用的技巧：

添加索引： 添加适当的索引可以加快查询速度，减少全表扫描，从而减少临时表的使用。
优化查询逻辑： 重写查询逻辑，避免不必要的排序、分组等操作。
减少返回的列： 只选择需要的列，避免返回不必要的列，从而减少临时表的大小。
使用 WHERE 子句过滤数据： 使用 WHERE 子句过滤掉不需要的数据，减少需要处理的数据量。
避免使用 DISTINCT 关键字： DISTINCT 关键字可能会导致 MySQL 创建临时表来去重数据。如果可能，可以使用其他方法来去重数据。
避免使用 UNION 关键字： UNION 关键字可能会导致 MySQL 创建临时表来合并结果集。如果可能，可以使用其他方法来合并结果集。
增加 tmp_table_size 和 max_heap_table_size 的值： 如果以上方法都无法避免磁盘临时表，可以考虑增加 tmp_table_size 和 max_heap_table_size 的值，但要注意不要设置得太大，以免占用过多的服务器内存。

不同 MySQL 版本的影响

MySQL 5.7 及更早版本使用 MyISAM 引擎作为磁盘临时表的默认引擎。 MyISAM 引擎不支持事务，因此临时表上的操作不是事务安全的。

MySQL 8.0 及更高版本使用 InnoDB 引擎作为磁盘临时表的默认引擎。 InnoDB 引擎支持事务，因此临时表上的操作是事务安全的。这提高了数据一致性和可靠性。

此外，MySQL 8.0 还引入了许多性能优化，包括对临时表的优化。例如，MySQL 8.0 引入了更好的排序算法，可以减少临时表的使用。

表格总结

参数名称	作用	影响范围	默认值（可能因版本和配置而异）	修改后是否需要重启
`tmp_table_size`	控制所有用户线程可创建的 MEMORY 临时表的最大大小。如果超过此大小，临时表将转换为磁盘 MyISAM/InnoDB 表。	全局	16M	是（配置文件）/否（SET GLOBAL）
`max_heap_table_size`	控制用户可以创建的 MEMORY 表的最大大小，包括显式创建的 MEMORY 表和隐式创建的临时表。也限制了 `tmp_table_size` 限制下的临时表。内存临时表的实际大小取 `tmp_table_size` 和 `max_heap_table_size` 中的较小值。	全局	16M	是（配置文件）/否（SET GLOBAL）
`internal_tmp_disk_storage_engine`	决定 MySQL 8.0 及更高版本中使用的内部磁盘临时表的存储引擎。可以设置为 `MyISAM` 或 `InnoDB`。默认值为 `InnoDB`。	全局	InnoDB	是（配置文件）/否（SET GLOBAL）

理解和配置临时表对性能至关重要

通过深入了解 tmp_table_size 和 max_heap_table_size 的作用，以及如何监控和优化临时表的使用情况，我们可以更好地利用 MySQL 的性能，避免不必要的磁盘 I/O，提高查询效率。记住，优化查询语句是减少临时表使用的关键。

监控和优化，让数据库更高效

监控临时表的使用情况，结合查询优化，并合理配置 tmp_table_size 和 max_heap_table_size，可以有效地提升 MySQL 数据库的性能。要根据实际的业务需求和服务器资源，找到最佳的平衡点。