为什么这个MYSQL UPDATE查询需要2分钟才能运行？

Question

这个查询需要近2分钟才能执行（更改了9条记录）：

UPDATE table1 t1
SET t1.code_id = null, t1.code_group = null
WHERE t1.another_id IN (SELECT t2.another_id 
                        FROM table2 t2
                        WHERE ((t2.id_parent = 2658 AND t2.year = 2016) 
                               OR (t2.id = 2658 AND t2.year = 2016)))

仅执行此查询需要0.0030秒：

SELECT t2.another_id 
FROM table2 t2
WHERE ((t2.id_parent = 2658 AND t2.year = 2016) 
       OR (t2.id = 2658 AND t2.year = 2016))

并返回一个由整数形式组成的三行数据表。以下是这两个表的信息：

CREATE TABLE IF NOT EXISTS `table1` 
(
  `another_id` int(11) NOT NULL,
  `table1_id` int(11) NOT NULL,
  `code_group` varchar(1) DEFAULT NULL,
  `code_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`another_id`,`table1_id`),
  KEY `another_id` (`another_id`),
  KEY `code_group` (`code_group`,`code_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `table2` 
(
  `id_year` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `id_parent` int(11) DEFAULT NULL,
  `another_id` int(11) NOT NULL,
  `code_group` varchar(1) DEFAULT NULL,
  `code_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id_year`,`id`),
  KEY `id_parent` (`id_year`,`id_parent`)
  KEY `another_id` (`another_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_polish_ci;

有没有人能告诉我为什么执行这个查询需要2分钟？

- Jac Mar

---

为什么在OR语句中((t2.id_parent=2658 AND t2.year=2016)两次出现？ - Jens

使用“Explain”查看需要索引的位置 - Jens

根据我的经验，MySQL有时似乎存在优化无关子查询的问题，以至于它们似乎会为每一行再次执行。在您的情况下，我建议将子查询作为单独的查询运行，然后在应用程序中动态地创建仅包含结果的实际查询。 - Sirko

尝试使用索引进行优化。创建非聚集索引... - Kenzo_Gilead

"table2" 没有 "t2.another_id" 列。MySQL 将在其上进行连接，因此它是相关的列。 - Solarflare

我从原始代码中删除了太多内容。当然，还有t2.another_id列。感谢您发现这个错误。我已经更新了我的原始问题。 - Jac Mar

Answer 1

你可以使用INNER JOIN来进行如下更新：t2.year也不存在。

UPDATE table1 t1
INNER JOIN table2 t2 ON t2.another_id = t1.another_id
    AND ((t2.id_parent= 2658 AND t2.year= 2016) OR (t2.id= 2658 AND t2.year= 2016))
SET t1.code_id = NULL, t1.code_group = NULL 

- Shushil Bohara

---

嘿，我无法执行这个查询。MySQL 在第 2 行返回语法错误。 - Jac Mar

我已根据MYSQL更新了查询，顺便提一句，T2中缺少年份列。 - Shushil Bohara

Answer 2

IN有时会妨碍优化。我建议直接将子查询放在FROM子句中：

UPDATE table1 t1 JOIN
       (SELECT t2.another_id 
        FROM table2 t2
        WHERE ((t2.id_parent= 2658 AND t2.year= 2016) OR
               (t2.id= 2658 AND t2.year= 2016)
              )
       ) t2
       ON t1.another_id = t2.another_id
    SET t1.code_id = null,
        t1.code_group = null;

那么，看到这个查询，我建议在table1(another_id)上创建一个索引。实际上，这个索引可能已经足够满足你原来的查询。

- Gordon Linoff

---

虽然我还没有找到主问题的合理答案（两个表都已经有了“another_id”的索引），但你的解决方案非常好用。将2分钟的查询时间缩短为0.004秒，谢谢;) - Jac Mar

@JacMar...你的问题的答案是，IN有时会妨碍优化。 - Gordon Linoff