為什麼這個查詢更新無法成功執行？

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值時有問題。