为什么这个查询更新无法成功执行？

Question

我有2个表，customers（3000行）和phone_call_log（350,000行）。

我需要使用通话记录（对于前端搜索更快）将最后一次通话的时间实现到每个客户。

索引如下：

• start_time（时间戳）
• 调用（bigint（32）无符号）
• 调用者（bigint(32) 无符号）
• 电话号码（bigint(32) 无符号）
• last_call（时间戳）

运行此查询时，没有OR语句的情况下，对于caller / callee列，完成时间小于2秒，但是有了OR语句，将无法完成（在测试中，我没有让它运行超过30分钟）。

UPDATE customers
SET customers.last_call = (
     SELECT max(phone_call_log.start_time)
     FROM phone_call_log
     WHERE phone_call_log.callee = customers.phonenumber
        OR phone_call_log.caller = customers.phonenumber
)
WHERE customers.phonenumber IS NOT NULL
  AND length(customers.phonenumber) > 6
  AND customers.phonenumber > 1000000;

Answer 1

最快

当有电话呼入时，改变数据流以更新customers.last_call。

更新连接

UPDATE与JOIN相比，IN ( SELECT ... )效果更好。

或者

OR会降低性能。查询很可能会为每个客户扫描整个phone_call_log。

一种解决方法是进行两个UPDATE，并使用适当的索引：

UPDATE 
    SET customers.last_call = GREATEST( customers.last_call,
                 ( select max(phone_call_log.start_time)
                      FROM  phone_call_log
                     WHERE  phone_call_log.callee = customers.phonenumber 
                 )
    WHERE ...
UPDATE 
    SET customers.last_call = GREATEST( customers.last_call,
                 ( ... caller ... )
                 )
    WHERE ...

这需要在phone_call_log上创建以下索引：

INDEX(callee, start_time)
    INDEX(caller, start_time)

并且删除当前的单列索引caller和callee。

数据类型

对于电话号码来说，使用BIGINT可能是错误的，特别是考虑到LENGTH(customers.phonenumber) > 6。

实际上，所有这些都可以归结为一个简单的测试：

where customers.phonenumber is not null
  AND LENGTH(customers.phonenumber) > 6
  AND customers.phonenumber > 1000000;

每个>检查都会检查NOT NULL；根据数据类型只使用其中一个，并对其进行索引。

（请提供SHOW CREATE TABLE；'英文'不够准确。）

Answer 2

使用OR的查询无法有效地使用索引。我建议你尝试以下方法：

UPDATE customers
SET last_call = GREATEST(
    (SELECT MAX(start_time) FROM phone_call_log WHERE callee = customers.phonenumber),
    (SELECT MAX(start_time) FROM phone_call_log WHERE caller = customers.phonenumber)
)

请注意，GREATEST在处理NULL值时存在问题。