mysql - 40秒奇怪的sql性能谜题

Question

我正在运行一个查询来更新我的用户字段，如下所示:

UPDATE Members SET abc = abc + 1 where Members.id in (
SELECT DISTINCT(memberId) FROM Events WHERE Events.createdAt > "2017-08-06 13:10:00";

令人震惊的是，对于大约 500 个成员，这个查询运行了 40 秒......

所以分解:

SELECT DISTINCT(memberId) FROM Events WHERE Events.createdAt > "2017-08-06 13:10:00"

运行 0.1 秒，只有 39 行匹配。

成员(member)总数只有约 500 人。我不明白为什么这会花那么长时间……我是不是漏掉了什么？

我在 RDS 上运行 mysql 5.6

Answer 1

尝试用 exists 替换:

UPDATE Members m
    SET abc = abc + 1 
    WHERE EXISTS (SELECT 1
                  FROM events e
                  WHERE e.memberId = m.id AND
                        e.createdAt > '2017-08-06 13:10:00'
                 );

为了提高性能，您需要在 events(memberId, createdAt) 上建立索引。

我的猜测是 MySQL 对 Members 中的每一行运行一次子查询。这与您的时间一致 - ~0.1 秒 * ~500 行大约是 50 秒，离 40 秒不远。

对于 SELECT，这在几个版本前已修复。也许这个问题仍然存在于非 SELECT 查询中。

你也可以这样写:

UPDATE Members m JOIN
       (SELECT DISTINCT e.memberId
        FROM events e
        WHERE e.createdAt > '2017-08-06 13:10:00'
       ) e
       ON e.memberId = m.id
    SET abc = abc + 1 ;

这是否比 exists 版本更快，取决于您的数据的特征。如果没有建议的索引，这可能会更快。