在 IN 狀態下具有太多值的 UPDATE 需要太長時間

我有下一個查詢：

UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND branch_id IN
(23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,218,220,221,222,223,224,226,231,232)

這是我的plates_owners結構：

plate_id     | int(11) unsigned | PRI
branch_id    | int(11) unsigned | PRI
show_app     | int(1) unsigned  |
deleted      | int(1) unsigned  |
app_disabled | int(1) unsigned  |
modified     | timestamp        |

這個查詢需要超過 50 秒才能執行，有沒有優化它？

PS：我添加到 IN 語句中的值越少，查詢變得越快

您可以嘗試自己創建一個臨時表，將您的分支編號插入其中，然後使用JOIN執行UPDATE，如下所示：

CREATE TEMPORARY TABLE branch_numbers 
(
   id INT NOT NULL PRIMARY KEY
);
INSERT branch_numbers (id)
VALUES (23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36)
     ,(37),(38),(39),(40),(42),(43),(46),(47),(48),(64),(79),(82),(83),(102)
     ,(103),(130),(169),(209),(218),(220),(221),(222),(223),(224),(226),(231)
     ,(232);

UPDATE plates_owners 
INNER JOIN branch_numbers ON plates_owners.branch_id = branch_numbers.id
  SET deleted=1 
WHERE plate_id=4748;

DROP TABLE branch_numbers;

UPDATE plates_owners
SET deleted=1 
WHERE plate_id=4748
 AND FIND_IN_SET(
        branch_id,
        (23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,218,220,221,222,223,224,226,231,232)
     ) > 0

引用自：https://dba.stackexchange.com/questions/259677