服务器特定的超级慢速SQL更新命令

Question

我在MySQL 5.5.31上有一个MYISAM数据库，正在运行以下更新

UPDATE tableA AS a
INNER JOIN tableB AS b
ON((a.id = b.dnum and a.account = b.account) 
   OR a.id = CONCAT(b.dnum, '\_', b.account, '\_', b.unique))
SET a.status = 0, a.apd = NULL WHERE a.status = 1;

我尝试使用相同的数据集和几乎相同的mysql设置在3台不同的测试机器上运行此查询，查询在1秒或更短的时间内完成，更新了大约25000行。

在生产服务器上运行这个查询，它永远不会完成，我在40+分钟之后就把它杀死了。

测试盒和产品之间配置上的主要差异是MySQL版本，为5.5.41，而生产系统有一个复制日志，所有缓存大小等等都是相同的。

我已经找到了一项工作，就是运行select和创建25000+ update语句，这个过程在生产过程中能够在2-3秒内运行。但是，我担心生产的SQL性能与测试环境有很大的不同，以至于性能问题可能会在意想不到的地方发生。

我在生产服务器上创建了一个新的测试数据库，并将数据导入到该数据库中，它具有相同的糟糕性能，因此它似乎是特定于该服务器的配置的。一般来说，SQL性能似乎不错，到目前为止，这是唯一一个在性能方面明显不同的查询。

我认为原因可能是复制，该复制设置为行级，因此它需要在bin日志中生成几千行更新，但我添加了set @@session.sql_log_bin=0；并设置了@@session.sql_log_bin=1；新测试数据库上的SQL的任何一方似乎都没有什么区别。此外，我还配置了一个测试框以写入bin日志(但没有设置从属程序)，这对性能没有很大影响。

有没有人知道我如何能确定这个数据库没有在合理的水平上执行这个查询的任何可能原因，或者任何诊断来找出正在发生的事情？

Answer 1

找出了性能差异的原因。

所有测试服务器的默认max_seeks_for_key设置为18446744073709551615，但生产服务器上出现问题的设置为500个。当我分析服务器之间的细微差异时，这种差异并不明显，因为我认为这种设置可能会提高性能，而不是减少性能。

Answer 2

or条件在join中是缓慢的。我建议将其分为两个更新：

UPDATE tableA a INNER JOIN
       tableB b
       ON a.id = b.dnum and a.account = b.account
    SET a.status = 0,
    a.apd = NULL
    WHERE a.status = 1;

UPDATE tableA a INNER JOIN
       tableB b
       ON a.id = CONCAT(b.dnum, '\_', b.account, '\_', b.unique))
    SET a.status = 0, a.apd = NULL
    WHERE a.status = 1;

然后，您可以使用适当的索引来加速这些操作。第一个是tableA(status, id, account)和tableB(dnum, account)。第二个是tableA(id, status)。