我在我的PHP脚本中有一些疯狂的SQL查询,这很需要一些优化。 然而,问题不在于如何优化它们。
这些查询有很多“连接”和一些奇怪的“按顺序排列”,并且针对具有几万条logging的表进行。 lots_of_joins + crazy_order_by事物使他们需要“复制到tmp表”步骤。
现在奇怪的是,同样的查询有时会跑得快(不到一秒钟),有时需要几十年(几十秒)。 在这两种情况下,“解释”和configuration文件显示“复制到tmp表”步骤。 当查询花费时间时,99%的时间花在“复制到tmp表”阶段。 奇怪的是,在那个时候,mysql几乎消耗了100%的CPU。
所以,我知道tmp表有时会保存在内存中,有时会写入磁盘(取决于当前内存的可用性)。 因此,这完全可以解释为什么相同的查询有时可能很快,有时会花费很长时间。 但是,有两件事我不明白。
如果将临时表写入磁盘的瓶颈,这应该意味着在I / O上花费了大量的时间,但是在此期间的CPU负载平均值应该相对较低,肯定远远低于100%。 做这么多的I / O时,CPU如何忙?
我在my.cnf中增加了:
max_heap_table_size = 1024M tmp_table_size = 1024M
(我认为默认值是16M)
而且我真的不相信tmp表需要的是更多的内存。
据我所知,一个tmp表被写入到磁盘而不是内存:a-如果查询和表需要它,因为它们不满足某些标准b-如果它超过了max_heap_table_size和tmp_table_size之间的最小值
如果(a)是这种情况,那么它总会发生,而不是不时。 另一方面,(b)似乎不太可能,因为我已经增加了上述参数,没有任何明显的改变。 所需表格的大小在相同查询的出版物中应该不会有很大的变化(数据几乎相同)。 所以如果在增加内存大小之前偶尔会发生(这意味着所需的tmp表的大小只是在最大值附近),那么在这种剧烈增加之后,反常行为应该完全消失。
所以我的问题基本上是:
创build磁盘表真的是唯一的原因,可以使复制到tmp表的步骤需要很长时间? (或者,即使它是在记忆中完成,也可能需要很长时间?如果是这样,为什么?为什么随机?)
如果是,那么
我真的不认为示例查询和configuration文件是必要的,但我可以发布,如果他们是。
我认为你的问题与tmp_table_size没有太大关系,因为通过query_cachecaching你的“丑陋”查询(结果集)。 如果您的查询是键入SELECT,请使用SQL_NO_CACHE。 caching时,服务速度很快,但可能会使caching过载,而且mysql在内部重新组织caching方面存在已知问题。 另外,如果需要,请使用EXPLAIN检查您的查询并在连接上使用强制索引。
这将是更合适的评论,但我目前代表太低。