mysql - 更新慢，不知道为什么

Question

我的 UPDATE 语句很慢，无法找出原因

CREATE TEMPORARY TABLE ttShifts
(ShiftId int NOT NULL, ttdtAdded datetime not null, ttdtBookingStart DATETIME NOT NULL, ttHoursNeeded int not null, szHidden varchar(255),
 PRIMARY KEY (ShiftID))
 AS
 (select shiftId, dtAdded as ttdtAdded, dtBookingStart as ttdtBookingStart, HoursNeeded as ttHoursNeeded from shifts where shifts.lStatus=0);

 update ttShifts set szHidden='x' where szHidden is NULL and ShiftId in (select shiftid from shifts,practices where shifts.PracticeId=practices.PracticeId and shifts.iBranch = practices.iBranch and practices.Healthboard not in (select Locname from userlocationprefs where iUser=82 and Level=0 and fAcceptWork=true))

166 rows affected. (Query took 0.2899 seconds.)    

解释:

  1 PRIMARY ttShifts    index       PRIMARY 4       297 Using where 
  2 DEPENDENT SUBQUERY  shifts  eq_ref  PRIMARY PRIMARY 4   func    1       
  2 DEPENDENT SUBQUERY  practices   ALL PRIMARY             636 Using where; Using join buffer (flat, BNL join) 
  3 MATERIALIZED    userlocationprefs   ref PRIMARY PRIMARY 8   const,const 3   Using where 

好的，让我们尝试将其切换为使用联接来消除依赖子查询

update ttShifts join shifts on (ttShifts.ShiftID=shifts.shiftId) join practices on (shifts.practiceId=practices.PracticeId and shifts.iBranch=practices.iBranch) set szHidden='x' where szHidden is NULL and practices.Healthboard not in (select Locname from userlocationprefs where iUser=82 and Level=0 and fAcceptWork=true);

166 rows affected. (Query took 0.4009 seconds.)

对，所以需要更长的时间

解释:

1   PRIMARY ttShifts    ALL PRIMARY             297 Using where 
1   PRIMARY shifts  eq_ref  PRIMARY PRIMARY 4   ttShifts.shiftId    1       
1   PRIMARY practices   ALL                 636 Using where 
2   MATERIALIZED    userlocationprefs   ref PRIMARY PRIMARY 8   const,const 3   Using where 

好吧，所以它一定是物化位，由于某种原因它没有有效地执行，让我们尝试将其交换为直接相等检查，就像测试一样。

update ttShifts join shifts on (ttShifts.ShiftID=shifts.shiftId) join practices on (shifts.practiceId=practices.PracticeId and shifts.iBranch=practices.iBranch) set szHidden='x' where szHidden is NULL and practices.Healthboard!='X'

0.3493 秒。

好吧，不是那么回事。

如果我删除更新并将其设为选择...

select * from ttShifts join shifts on (ttShifts.ShiftID=shifts.shiftId) join practices on (shifts.practiceId=practices.PracticeId and shifts.iBranch=practices.iBranch) where szHidden is NULL and practices.Healthboard not in (select Locname from userlocationprefs where iUser=82 and Level=0 and fAcceptWork=true)

(166 rows, Query took 0.0159 seconds.)

那么为什么更新这么慢，我能做些什么来加快速度呢？

Answer 1

您的第一个 EXPLAIN 输出告诉我们它需要处理 297*1*636*3 行 = 566,676 行。所以是的，需要一些时间来处理。它类似于第二个 EXPLAIN 输出。

如果我是你，我会尝试关注标记为ALL的条目，因为它代表表扫描操作。

此外，IN 最适合用于常量值列表而不是子查询，因为它们会导致索引无用。如果可能，尝试删除具有常量值的子查询。

第二个EXPLAIN更糟糕，因为有两个表扫描没有索引可供使用。

第三次更新没有 EXPLAIN 输出，但我再次认为，因为要处理的行数仍然很高，因为您使用基数较低的列(not null)和 healthBoard !=' x' 作为过滤条件 WHERE 子句。

您的最后一个查询尝试比较 SELECT 与 UPDATE 的速度。嗯，UPDATE 更昂贵，因为它必须搜索匹配的行，写入值，写入索引。

据我所知，您的大部分问题是由于使用低基数列作为过滤条件。