为什么 MySQL 使用这么多临时表?

为什么 MySQL 使用这么多临时表?

任何配置错误都会导致 mysql 创建过多的临时表吗?mysql tuner 显示

Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were created on disk


table_open_cache = 125 tables
table_definition_cache = 256 tables

You have a total of 97 tables

You have 125 open tables.

Current table_cache hit rate is 3%

以前临时表是这样的,of the 23725 temp tables 38% were created on disk但我把它从 16m 改到max_heaptmp_table200m,它就降低了 30%...

engine myisam 
group_concat_max_len = 32768
key_buffer_size = 3.7 GB,
thread_stack = 256k,
table_cache = 125
query_cache_limit = 1M
query_cache_size = 16M
join_buffer_size = 2.00 M

max_connections = 800

另一个具有默认配置的系统正在显示 of 23725 temp tables, 1% were created on disk

但我尝试将此问题更改为机器上的默认设置,但仍然显示Of 580 temp tables, 16% were created on disk

我正在使用 Ubuntu 11.4 64 位,配备 48 GB 内存...有人可以提出解决方案吗?

使用“group by”将表上的数据库引擎从myisam更改为内存可以解决这个问题吗?

答案1

根据正在执行的查询,将根据需要创建和删除临时表。您看到的数字是自上次启动 MySQL 以来创建的临时表总数,而不是同时存在的临时表数量。

答案2

当查询无法一次性计算时,MySQL 会使用临时表。切换存储引擎不会改变这一点。问题在于询问,而不是配置。

增加该tmp_table_size值只能阻止部分表写入磁盘,它们仍将在内存中创建并填充数据。这些数据可能首先来自磁盘,尽管在 48GB RAM 的情况下,您可能已经缓存了相当多的数据。即使缓存了,由于这些临时表中有 30% 大于 200MB,因此在 RAM 中复制这么多数据仍然需要时间。

您可以使用语法在运行查询之前确定它是否使用临时表EXPLAIN。只需将EXPLAIN其放在查询之前,它就会输出一堆有关执行计划和查询效率的信息,而无需实际执行它。

您很可能找到导致这些临时表的查询,因为它们很可能是慢速查询,因此最终会出现在您的慢速查询日志中。

如果您需要帮助调整特定查询,数据库管理系统是一个值得去的好地方。

总结

调整您的查询。

相关内容