Question

更新查询出现问题，耗时超过20分钟（之后我会终止它）。

场景：

表一有大约30万条记录。

表二包含相同的记录集（复制），但有一个额外的字段，该字段需要包含与多个字段匹配的记录的id，并且具有另一个字段中的最高值（分数）。为了澄清，最终结果应该是包含300K条记录的表二，每条记录都具有具有相同基本属性集的另一条记录的id，以及具有这些属性的记录集中的最高分数。

当我只将2K条记录而不是完整的300k条记录复制到表2中时，以下操作将在大约5秒内完成。

UPDATE vtable2 v1 SET v1.buddy = (
    SELECT v2.id FROM vtable1 v2
    WHERE
    v2.group_id = v1.group_id AND
    // 6 more basic comparisons
    ORDER BY score DESC LIMIT 1
)

我需要为全部30万张唱片找到伙伴。参与联接和排序的所有字段都有索引。

非常感谢您的帮助。

Answer 1

MySQL子查询往往会慢一点。在这种情况下，我更喜欢使用联接。我不太清楚你的模式设计，但你可以试试这样的-

UPDATE vtable2 v1
[INNER] JOIN vtable1 v2 
ON v2.group_id = v1.group_id
AND //OTHER JOIN CONDITIONS IF ANY
WHERE
//any other conditions
SET
v1.buddy = v2.id

PS-当然你需要确保你的列上有正确的索引。如果你需要帮助，你可以发布整个查询和解释计划。

Answer 2

您可以使用数字变量进行测试

 SELECT v2.id FROM vtable1 v2
WHERE
v2.group_id = 1 AND
// 6 more basic comparisons
ORDER BY score DESC LIMIT 1

Anyway I think use Join it s better but I don t have schema DB.
Maybe you have a trouble about index on your sql DB.

Answer 3

您可以使用排除联接在vtable1中查找该行，这样就无法在vtable1中找到得分更高的其他行。

UPDATE vtable2 AS v1
INNER JOIN vtable1 AS v2a ON v1.group_id = v2a.group_id AND (...conditions...)
LEFT OUTER JOIN vtable1 AS v2b ON v1.group_id = v2b.group_id
  AND v2a.score < v2b.score AND (...conditions...)
SET v1.buddy = v2.id
WHERE v2b.group_id IS NULL;

您必须为外部联接复制表达式中的所有其他条件；不能将它们放入WHERE子句中。

友情链接