我的 PHP 脚本中有一些疯狂的 SQL 查询，急需优化。然而问题不在于如何优化它们。
这些查询有很多“join”和一些奇怪的“order by”，并且针对包含数万条记录的表执行。lots_of_joins+crazy_order_by 使得它们需要一个“复制到临时表”步骤。
现在奇怪的是，同样的查询有时运行得很快（不到一秒），有时却要花很长时间（几十秒）。在这两种情况下，“解释”和配置文件都显示“复制到临时表”步骤。当查询需要很长时间时，99% 的时间都花在“复制到临时表”阶段。奇怪的是，在此期间，mysql 几乎消耗了 100% 的 CPU。
因此，我理解 tmp 表有时保存在内存中，有时写入磁盘（取决于当前内存可用性）。因此，这可以完美解释为什么同一查询有时很快，有时却需要很长时间。但是，有两件事我不明白。
如果瓶颈在于将临时表写入磁盘，那应该意味着在 I/O 上花费了大量时间，但这段时间内的平均 CPU 负载应该相对较低，肯定远非 100%。在进行如此多的 I/O 时，CPU 怎么会这么忙？
我在my.cnf中增加了：
max_heap_table_size = 1024M tmp_table_size = 1024M
（我认为默认值是 16M）
并且我真的不相信 tmp 表需要超过那么多的 RAM。
据我了解，临时表被写入磁盘而不是内存：a-如果查询和表需要它，因为它们不满足某些条件 b-如果它超过了 max_heap_table_size 和 tmp_table_size 之间的最小值
如果 (a) 是这种情况，那么就会发生总是，不是时不时。另一方面，我认为不太可能是 (b)，因为我已经大大增加了上述参数，但没有任何明显的变化。所需表的大小在相同查询的出现中不应该有太大变化（数据几乎相同）。因此，如果在增加内存大小之前它偶尔发生一次（这意味着所需临时表的大小刚好在最大值附近），那么在如此急剧增加之后，不稳定的行为应该已经完全消失。
所以我的问题基本上是：
创建磁盘表真的是导致复制到临时表步骤花费很长时间的唯一原因吗？（或者即使在内存中完成也会花费很长时间？如果是，为什么，为什么是随机的？）
如果是的话，那么
- 它怎么可能占用这么多的 CPU 呢？
- 为什么即使临时表不大于 min(tmp_table_size,max_heap_table_size) 也可以将其写入磁盘？
我真的不认为示例查询和配置文件是必要的，但如果需要的话我可以发布它们。
答案1
我认为您的问题不在于 tmp_table_size，而在于通过 query_cache 缓存“丑陋”的查询（结果集）。如果您的查询是 SELECT 类型，请使用 SQL_NO_CACHE。缓存后，它运行速度很快，但会使缓存过载，并且 mysql 已知内部重新组织缓存存在问题。此外，使用 EXPLAIN 检查您的查询，并在必要时对连接使用强制索引。
这作为评论更合适，但我目前的声誉太低了。