UPDATE 查詢比它的 SELECT 查詢慢 50 倍

UPDATE 查詢比它的 SELECT 查詢慢 50 倍。

我有一個名為的表sync_read，並且具有以下列

id
club_id
created_at
user_id
queue_id

該表具有以下索引：

Keyname            Column
club_id            club_id  
user_id            user_id
club-contact       (club_id,user_id)  

該表中有大約 69K 條記錄。我的目標是將queue_id具有相同**(club_id,user_id)**的行的列更新為相同的數字，例如

id | club_id | created_at | user_id | queue_id
1     99        2015-05-05   8994       59294
2     45        2015-05-05   9872       892191
3     99        2015-05-04   8994       59294

我正在使用以下查詢來更新數據

UPDATE sync_read,
      (SELECT GROUP_CONCAT(id) AS ids,(FLOOR(RAND() * POW(10,6))) AS rand 
      FROM sync_read 
      WHERE club_id = 15085 AND created_at = '2022-03-11 18:50:51'
      GROUP BY club_id, user_id)
       AS grouped
SET sync_read.queue_id = grouped.rand
WHERE sync_read.created_at = '2022-03-11 18:50:51'
 AND sync_read.club_id = 15085
 AND FIND_IN_SET(sync_read.id, grouped.ids) ;

76 rows affected. (Query took 22.2668 seconds.)

這似乎很慢。所以我檢查了那個查詢的解釋。結果是這樣的

然後我嘗試選擇相同的數據

SELECT * FROM sync_read, 
   (SELECT GROUP_CONCAT(id) AS ids,(FLOOR(RAND() * POW(10,6))) AS rand 
   FROM sync_read 
   WHERE club_id = 15085 AND created_at = '2022-03-11 18:50:51' 
   GROUP BY club_id, user_id) AS grouped 
   WHERE sync_read.created_at = '2022-03-11 18:50:51' AND sync_read.club_id = 15085 AND FIND_IN_SET(sync_read.id, grouped.ids)

Showing rows 0 - 24 (76 total, Query took 0.5225 seconds.)

解釋的結果是

所以你可以看到SELECT只需要 0.5 秒，但UPDATE在相同數量的行上需要 25 秒。為什麼 UPDATE 查詢比 SELECT 語句慢得多。看到 EXPLAIN 結果後，我懷疑 MySQL 是否正確考慮索引

這可能不是您正在尋找的答案，但為什麼不使用從 club_id 和 user_id 生成的 queue_id 的複合鍵呢？您甚至可以像這樣將它添加到您的表中：

alter table sync_read add column gen_queue_id bigint as (concat(club_id,user_id));

看起來這樣可以更快地完成這項工作：

UPDATE sync_read
   SET sync_read.queue_id = FLOOR(RAND() * POW(10,6))
   WHERE club_id = 15085
     AND created_at = '2022-03-11 18:50:51';

那需要

INDEX(club_id, created_at)   -- in this order

但是，queue_id屬於那張桌子嗎？

queue_id的冗餘

同時，“我的目標是將具有相同 (club_id,user_id) 的行的列 queue_id 更新為相同的數字”聽起來您已經queue_id在多行中重複。從該表中刪除該列，並將其放入單獨的表中，以免重複。該表將有 3 列：（club_id、user_id、queue_id），前 2 列是 PK。

這可能需要在其他一些查詢中加入。

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