mysql - UPDATE with 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;

这看起来相当简单，但是这个查询的 EXPLAIN 显示正在进行一些严重的行扫描:

# 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'

重要的似乎是行列，UPDATE 为 1198100，SELECT 子查询为 1200537。这两个数字都非常接近两个引用表中的总行数(均为 1207744)。所以它似乎正在为两者的行扫描做一整行，我不明白为什么。完全相同的查询在 MySQL 5.5 中运行良好。我希望 this解决方案会有所帮助，但将“derived_merge=off”传递给 optimizer_switch 并重新启动服务器没有帮助。

我当然不希望这个查询非常快。它不一定是。以前速度并不快(在 7200rpm 的旋转磁盘上几分钟)，但自从升级到 MySQL 5.7 以来，似乎它不会在宇宙热寂之前的任何时候完成，我宁愿不等待长的。有没有人有任何想法？是查询重写，还是 my.ini 设置或其他任何东西？

此外，如果我以任何方式违反了协议(protocol)，或者我是否可以改进我的问题，请告诉我。正如我上面所说，这是我在这里的第一篇文章。

感谢您的宝贵时间。

编辑:我想了一下 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