最佳MySQL临时表(内存表)配置？

Question

首先，我对优化mysql很陌生。事实上，我的web应用程序中有一个查询(大约每秒400个查询)，这个查询使用了一个我无法避免的GROUP BY，这就是创建临时表的原因。我的配置是：

max_heap_table_size = 16M  
tmp_table_size = 32M  

结果: temp表到磁盘百分比+- 12.5%

然后我改变了我的设置，根据这个职位

max_heap_table_size = 128M  
tmp_table_size = 128M

结果: temp表到磁盘百分比+- 18%

没有预期的结果，不明白为什么。

tmp_table_size = max_heap_table_size是错的吗？不应该增加尺寸吗？

查询

SELECT images, id  
FROM classifieds_ads   
WHERE  parent_category = '1' AND published='1' AND outdated='0'
GROUP BY aux_order  
ORDER BY date_lastmodified DESC  
LIMIT 0, 100;

解释

| 1 |SIMPLE|classifieds_ads | ref |parent_category, published, combined_parent_oudated_published, oudated | combined_parent_oudated_published | 7 | const,const,const | 67552 | Using where; Using temporary; Using filesort |

Answer 1

解释报告中的“使用临时”没有告诉我们临时表在磁盘上。它只告诉我们，查询希望创建一个临时表。

如果临时表的大小小于tmp_table_size而小于max_heap_table_size，则它将保留在内存中。

Max_heap_table_size是内存存储引擎中最大的表，无论该表是临时表还是非临时表。

Tmp_table_size是查询自动创建表时内存中最大的表。但无论如何，这不可能比max_heap_table_size大。因此，设置tmp_table_size大于max_heap_table_size是没有好处的。通常将这两个配置变量设置为相同的值。

可以监视创建了多少临时表，以及在磁盘上创建了多少个临时表，如下所示：

mysql> show global status like 'Created%'; 
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 20    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 43    |
+-------------------------+-------+

注在本例中，创建了43个临时表，但其中只有20个位于磁盘上。

当您增加tmp_table_size和max_heap_table_size的限制时，您允许内存中存在更大的临时表。

你可能会问，你需要多大才能造出来？您不一定需要使其足够大，以便每个临时表都能容纳在内存中。您可能希望将95%的临时表放入内存中，而只有剩余的稀有表才能进入磁盘。最后的5%可能非常大--比你想要使用的内存量要大得多。

所以我的做法是保守地增加tmp_table_size和max_heap_table_size。然后观察Created_tmp_disk_tables与Created_tmp_tables的比率，看看我是否达到了让95%的人留在内存中的目标(或者我想看到的任何比率)。

不幸的是，MySQL没有一个很好的方法告诉您临时表有多大。这将因查询的不同而有所不同，因此状态变量不能显示，它们只能向您显示它发生了多少次。而EXPLAIN实际上并没有执行查询，所以它无法准确地预测它将匹配多少数据。

另一种选择是Percona服务器，它是一个具有改进功能的MySQL发行版。其中之一就是在慢速查询日志中记录额外信息。。额外字段中包括由给定查询创建的任何临时表的大小。