MySQL运维与监控之：`MySQL`的`tmp_table_size`和`max_heap_table_size`：其在临时表中的作用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，下面是关于 MySQL 运维与监控中 tmp_table_size 和 max_heap_table_size 在临时表中的作用的技术讲座文章。

MySQL 临时表：内存与磁盘的权衡

大家好！今天我们来聊聊 MySQL 中临时表相关的重要配置参数：tmp_table_size 和 max_heap_table_size。理解这两个参数如何影响临时表的创建方式，对于优化查询性能和避免不必要的磁盘 I/O 至关重要。

什么是临时表？

在深入讨论配置参数之前，我们先明确一下什么是临时表。MySQL 在执行复杂查询时，可能需要创建临时表来存储中间结果。这些中间结果可能来自 ORDER BY、GROUP BY、UNION 等操作。临时表可以帮助 MySQL 更有效地处理数据，避免在原始表上进行复杂的操作。

临时表有两种主要类型：

内存临时表 (MEMORY engine)：存储在内存中，速度快，但受内存大小限制。
磁盘临时表 (MyISAM 或 InnoDB engine)：存储在磁盘上，可以处理更大的数据量，但速度相对较慢。

tmp_table_size 和 max_heap_table_size 的作用

tmp_table_size 和 max_heap_table_size 决定了 MySQL 何时使用内存临时表，以及何时将其转换为磁盘临时表。

tmp_table_size: 这个参数定义了内存临时表的最大大小。如果一个内存临时表的大小超过了 tmp_table_size，MySQL 会尝试将其转换为磁盘临时表。该参数是session级别的，可以被动态修改。
```
SHOW VARIABLES LIKE 'tmp_table_size';
SET SESSION tmp_table_size = 67108864; -- 64MB
```
max_heap_table_size: 这个参数定义了 MEMORY 存储引擎创建的表的最大大小。它影响所有使用 MEMORY 存储引擎的表，包括内存临时表。 max_heap_table_size 是全局参数，可以被动态修改。
```
SHOW VARIABLES LIKE 'max_heap_table_size';
SET GLOBAL max_heap_table_size = 67108864; -- 64MB
```

重要区别：

tmp_table_size 只影响临时表。
max_heap_table_size 影响所有 MEMORY 存储引擎的表。

临时表的创建流程

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

尝试创建内存临时表：MySQL 首先尝试使用 MEMORY 存储引擎创建一个内存临时表。
检查大小限制：如果临时表的大小超过了 tmp_table_size 或 max_heap_table_size (以较小者为准)，MySQL 会尝试将其转换为磁盘临时表。注意，是两者中较小的值。
转换为磁盘临时表：如果内存临时表超过大小限制，MySQL 会使用 internal_tmp_disk_storage_engine 系统变量指定的存储引擎（通常是 MyISAM 或 InnoDB）在磁盘上创建一个临时表。
使用磁盘临时表：后续操作将在磁盘临时表上进行。

示例演示

为了更好地理解这两个参数的作用，我们来看几个示例。

示例 1：内存临时表

假设 tmp_table_size 和 max_heap_table_size 都设置为 16MB。我们创建一个包含大量数据的表，并执行一个需要创建临时表的查询。

-- 创建一个测试表
CREATE TABLE large_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
);

-- 插入大量数据
INSERT INTO large_table (data) VALUES
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10'),
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10'),
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10'),
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10'),
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10'),
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10'),
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10'),
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10'),
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10'),
('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6'), ('data7'), ('data8'), ('data9'), ('data10');

-- 执行需要创建临时表的查询
EXPLAIN SELECT data, COUNT(*) FROM large_table GROUP BY data ORDER BY COUNT(*) DESC;

如果 EXPLAIN 输出显示 "Using temporary" 和 "Using filesort"，则表示 MySQL 创建了临时表，并且可能使用了磁盘排序。如果临时表的大小没有超过 16MB，则会使用内存临时表。

示例 2：磁盘临时表

现在，我们将 tmp_table_size 和 max_heap_table_size 都设置为 1MB。再次执行相同的查询。

-- 设置 tmp_table_size 和 max_heap_table_size
SET SESSION tmp_table_size = 1048576; -- 1MB
SET GLOBAL max_heap_table_size = 1048576; -- 1MB

-- 执行需要创建临时表的查询
EXPLAIN SELECT data, COUNT(*) FROM large_table GROUP BY data ORDER BY COUNT(*) DESC;

这次，由于临时表的大小很可能超过 1MB，MySQL 会创建一个磁盘临时表。EXPLAIN 输出仍然会显示 "Using temporary" 和 "Using filesort"，但这次临时表是在磁盘上创建的。

查看临时表的使用情况

可以通过 performance_schema 来监控临时表的使用情况。

SELECT
    event_name,
    COUNT(*) AS count,
    SUM(IF(temporary = 'YES', 1, 0)) AS num_temporary_tables,
    SUM(IF(temporary = 'NO', 1, 0)) AS num_non_temporary_tables
FROM performance_schema.events_statements_summary_by_digest
WHERE event_name LIKE 'statement/sql/%'
GROUP BY event_name
ORDER BY count DESC
LIMIT 10;

还可以使用 SHOW GLOBAL STATUS 命令来查看与临时表相关的状态变量：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

这些状态变量可以帮助你了解 MySQL 创建了多少临时表，以及其中有多少是磁盘临时表。

参数优化

优化 tmp_table_size 和 max_heap_table_size 需要根据你的具体应用场景和服务器配置进行调整。以下是一些建议：

足够大的内存：确保服务器有足够的内存来容纳较大的内存临时表。如果服务器内存不足，增加 tmp_table_size 和 max_heap_table_size 可能会导致性能下降。
监控临时表的使用情况：使用 performance_schema 或 SHOW GLOBAL STATUS 监控临时表的使用情况，了解是否频繁创建磁盘临时表。
逐步调整：逐步增加 tmp_table_size 和 max_heap_table_size，并监控性能变化。
考虑查询优化：在调整参数之前，首先尝试优化查询本身，例如添加索引、重写查询等。有时，通过优化查询可以避免创建临时表。

什么时候应该增加 tmp_table_size 和 max_heap_table_size？

当发现频繁创建磁盘临时表时，可以考虑增加这两个参数的值。
当服务器有足够的内存，并且查询需要大量的排序和分组操作时，增加这两个参数的值可以提高性能。

什么时候应该保持较小的 tmp_table_size 和 max_heap_table_size？

当服务器内存有限时，保持较小的参数值可以避免内存溢出。
当查询本身可以优化时，没有必要增加参数值。

不同存储引擎的影响

internal_tmp_disk_storage_engine 系统变量决定了磁盘临时表使用的存储引擎。MySQL 5.7 及更早版本默认使用 MyISAM，MySQL 8.0 及更高版本默认使用 InnoDB。

MyISAM: 速度快，但不支持事务和行级锁。
InnoDB: 支持事务和行级锁，但速度相对较慢。

根据你的需求选择合适的存储引擎。如果需要事务支持，建议使用 InnoDB。

tmp_table_create_size (已弃用)

在 MySQL 5.7 之前，还有一个参数 tmp_table_create_size，用于限制内部临时表的总大小。这个参数在 MySQL 5.7 中已被弃用，不再起作用。

总结:

tmp_table_size 和 max_heap_table_size 是 MySQL 中影响临时表创建方式的重要参数。通过合理配置这两个参数，可以在内存和磁盘之间取得平衡，优化查询性能。监控临时表的使用情况，并根据实际情况逐步调整参数，才能达到最佳效果。了解这两个参数的作用对于提升 MySQL 数据库的性能至关重要。

合理分配内存，优化查询性能

理解临时表的工作原理和参数配置，能帮助我们更好地优化 MySQL 数据库的性能。合理分配内存，避免频繁创建磁盘临时表，是提升查询速度的关键。

MySQL 临时表：内存与磁盘的权衡

发表回复 取消回复

发表回复取消回复