mysql tmp_table_size和max_heap_table_size大小配置

MySQL数据库在处理复杂查询时，有时会创建内部临时表来帮助优化执行计划。`tmp_table_size` 和 `max_heap_table_size` 这两个系统变量就与这种内存中的临时表息息相关，它们对数据库性能有着显著的影响。 `tmp_table_size` 是一个重要的配置参数，它决定了在每个线程中创建的内存临时表的最大大小。当查询生成的临时表超过这个大小时，MySQL会自动将临时表转换为磁盘上的MyISAM表。默认情况下，MySQL将这些磁盘临时表存储在`tmpdir`变量所指定的目录下，通常是 `/tmp/` 目录。因此，合理设置 `tmp_table_size` 可以避免频繁的磁盘I/O操作，从而提高查询性能。 然而，`tmp_table_size` 并不是孤立的，它与 `max_heap_table_size` 配合工作。这两个变量的较小值决定了内存临时表的实际最大大小。也就是说，即使 `tmp_table_size` 设置得更大，如果 `max_heap_table_size` 较小，实际限制仍由 `max_heap_table_size` 决定。`max_heap_table_size` 专门用于控制用户创建的内存表（也称为Memory表）的最大大小。当创建或更改内存表时，这个变量的值会被用作计算表能容纳的最大行数的标准。 在调整这两个变量时，应根据服务器的内存大小和查询类型来设定。例如，如果你的服务器有16GB的内存，并且你的查询中包含大量`GROUP BY`或`JOIN`操作，增加 `tmp_table_size` 和 `max_heap_table_size` 的值可以有效地利用内存资源，减少磁盘I/O，提高查询速度。但是，过度设置可能会导致内存浪费，甚至可能导致服务器因内存不足而出现性能问题。 监控 `Created_tmp_disk_tables` 和 `Created_tmp_tables` 这两个状态变量可以帮助你了解数据库的临时表使用情况。理想的比例是 `Created_tmp_disk_tables` 与 `Created_tmp_tables` 的比值小于5%，这意味着大部分临时表都能保留在内存中。如果这个比例过高，可能需要考虑优化查询语句，或者适当增大 `tmp_table_size` 和 `max_heap_table_size` 的值。 此外，`max_heap_table_size` 支持在线动态调整，但只对新创建的内存表生效。已存在的内存表不会因为全局变量的改变而自动调整大小，除非进行 `CREATE TABLE`, `ALTER TABLE`, 或 `TRUNCATE TABLE` 操作。服务器重启后，所有内存表都会按照新的 `max_heap_table_size` 值进行初始化。 总结来说，`tmp_table_size` 和 `max_heap_table_size` 是MySQL数据库中关键的优化参数，它们影响到内存临时表的使用，进而影响查询效率。正确设置这两个参数，结合对查询语句的优化，可以显著提升数据库系统的整体性能。在调整时，务必根据实际的服务器资源和应用需求进行平衡，避免过度配置导致的问题。