mysql - 单个事务中的多个更新与具有大 WHERE 子句的一个更新

Question

什么更有效率:

START TRANSACTION
UPDATE mytable SET foo = 'bar' WHERE (col1 = 813242) AND (col2 = 25343);
UPDATE mytable SET foo = 'bar' WHERE (col1 = 312643) AND (col2 = 8353);
UPDATE mytable SET foo = 'bar' WHERE (col1 = 843564) AND (col2 = 41233);
UPDATE mytable SET foo = 'bar' WHERE (col1 = 321312) AND (col2 = 5325);
UPDATE mytable SET foo = 'bar' WHERE (col1 = 554235) AND (col2 = 6321);
... x 10,000 times or more
COMMIT;

或

UPDATE mytable SET foo = 'bar' WHERE
((col1 = 16344) AND (col2 = 5456)) OR
((col1 = 42134) AND (col2 = 5436)) OR
((col1 = 84563) AND (col2 = 2321)) OR
((col1 = 43216) AND (col2 = 4267)) OR
((col1 = 53248) AND (col2 = 6234)) OR
... x 10,000 times or more

假设我在 (col1,col2) 上有 UNIQUE 索引

所以我的猜测是第一个选项很好，因为有索引，但它被分成多个查询，第二个选项很好，因为它只有一个查询，但另一方面它会进行全表扫描

当不使用OR时，这是EXPLAIN:

type: ref, possible_keys: myindex_UNIQUE, key: myindex_UNIQUE, ref: const

这是 EXPLAIN 当使用 OR 时:

type: ALL, possible_keys: myindex_UNIQUE, key: null, ref: null

查询WHERE子句有没有限制？

我的目标是最大速度

Answer 1

根据评论中的对话，可能的解决方案:

如果您有一个大表和一个值列表来标识要更新的行，您可以为值列表创建一个帮助表。

根据问题中的示例，表格可能是这样的:

CREATE TABLE mytable_operation (
    col1 INT
  , col2 INT
) ENGINE = MEMORY;

请注意，创建语句包含 ENGINE = MEMORY 提示，因此表将存储在内存中而不是磁盘中。

在更新之前将值加载到此表中。

将所有值加载到帮助表后，您可以使用以下查询更新生产表中的值。

UPDATE
  mytable
SET
  foo = 'bar'
WHERE
  EXISTS (SELECT 1 FROM mytable_operation MO WHERE mytable.col1 = MO.col1 AND mytable.col2 = MO.col2)

当然，您可以使用任何 DML 语句来操作生产数据。 (使用连接更新、DELETE、INSERT..ON DUPLICATE KEY 等)

完成数据操作后，您可以截断或删除帮助表。

如果生产表中的行数稍多，此解决方案可能比问题中的解决方案更快。