Warum kann diese Abfrageaktualisierung nicht erfolgreich ausgeführt werden?

Question

Ich habe zwei Tabellen: „customers“ (3.000 Zeilen) und „phone_call_log“ (350.000 Zeilen).

Ich muss den Zeitpunkt des letzten Anrufs bei jedem Kunden mithilfe von Anrufprotokollen implementieren (schneller für die Frontend-Suche).

Der Index lautet wie folgt:

• start_time (Zeitstempel)
• call(bigint(32) unsigned)
• Caller(bigint(32) unsigned)
• Telefonnummer (bigint(32) unsigned)
• last_call (Zeitstempel)

Beim Ausführen dieser Abfrage beträgt die Abschlusszeit für die Spalten „Anrufer/Angerufener“ ohne die OR-Anweisung weniger als 2 Sekunden, aber mit der OR-Anweisung wird sie nicht abgeschlossen (im Test habe ich sie nicht laufen lassen). länger als 30 Minuten).

UPDATE-Kunden
SET customer.last_call = (
     SELECT max(phone_call_log.start_time)
     VON phone_call_log
     WHERE phone_call_log.callee = customer.phonenumber
        ODER phone_call_log.caller = customer.phonenumber
)
WO kunden.telefonnummer NICHT NULL IST
  AND length(customers.phonenumber) >
  UND kunden.telefonnummer >

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值时存在问题。