使用SELECT子查询更新在MySQL 5.7上运行得非常慢(但在5.5上运行得很好)

Question

先谢谢大家。我在将我的数据库从MySQL 5.5升级到5.7时遇到了一个问题，这让我完全不知所措。升级不是使用mysqldump或类似的方式完成的，而是使用几个非常长的SQL脚本从几个选项卡中分离的输入文件进行重新构建。一个看似无害的查询(尤其是存储过程中的查询)一直给我带来麻烦，我不知道为什么：

UPDATE liverpool.master_person mp 
SET Link_Count = ( SELECT count(*) FROM liverpool.person_record pr
WHERE mp.Master_Person_ID = pr.Master_Person_ID ) - 1;

这看起来相当简单，但是从这个查询中得到的解释显示，正在进行一些严重的行扫描：

# id | select_type          | table | partitions | type    | possible_keys | key                    | key_len | ref  | rows      | filtered | Extra
========================================================================================================================================================================
'1'  | 'UPDATE'             | 'mp'  | NULL       | 'index' | NULL          | 'PRIMARY'              | '4'     | NULL | '1198100' | '100.00' | NULL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'2'  | 'DEPENDENT SUBQUERY' | 'pr'  | NULL       | 'index' | NULL          | 'Master_Person_ID_IDX' | '17'    | NULL | '1200537' | '100.00' | 'Using where; Using index'

重要的似乎是行列，它是更新的1198100和SELECT子查询的1200537。这两个数字都非常接近两个引用表中的行总数(这两个表都是1207744行)。因此，它似乎要对两者进行一排一排的扫描，但我看不出原因。在MySQL 5.5中，完全相同的查询工作得很好。我希望this解决方案会有所帮助，但将“derived_merge=off”传递给optimizer_switch并重新启动服务器没有帮助。

我当然不认为这个查询会非常快。不一定非得这样。它以前并不太快(在7200 and旋转盘上几分钟)，但是自从升级到MySQL 5.7之后，它似乎不会在宇宙热死之前的任何时候完成，我也不想等那么久。外面有人有什么想法吗？是查询重写，还是my.ini设置，还是其他什么？

此外，请让我知道我是否以任何方式违反协议，或我是否可以改进我的问题。正如我上面所说的，这是我在这里的第一篇文章。

谢谢您抽时间见我。

编辑:我曾一度认为this解决方案看起来很有前途。显然，具有不同字符集/排序规则的表不能正确地读取彼此的索引。我很确定所有的东西都在latin1里，但我觉得这是值得确认的。因此，我将DEFAULT CHARSET=latin1显式地添加到所有CREATE TABLE语句中，并将CHARACTER SET latin1添加到LOAD DATA INFILE语句中。可悲的是，没有改变。

Answer 1

尝试将查询重写为：

UPDATE liverpool.master_person mp
  JOIN (SELECT Master_Person_ID, count(*) as cnt
          FROM liverpool.person_record
         GROUP BY Master_Person_ID)
       ) pr
    ON mp.Master_Person_ID = pr.Master_Person_ID
   SET mp.Link_Count = pr.cnt - 1