mysql - SQL where 子句加速

Question

此查询

UPDATE table
SET stringcol = REPLACE(stringcol, 'tester1', 'tester2')
WHERE stringcol LIKE '%tester1%'

(在大 table 上)运行速度几乎是这个的两倍

UPDATE table
SET stringcol = REPLACE(stringcol, 'tester1', 'tester2')

其中仅省略了 WHERE 子句。

我认为这是一个非常奇怪的结果。

在第一个查询中，数据库必须首先在表中的每个 stringcol 中搜索 tester1 的出现。然后，表的缩减子集被传递给 REPLACE 函数，该函数需要再次查找 tester1 才能替换它。

在第二个查询中，数据库引擎必须在表中的每个 stringcol 中搜索 tester1 的出现，并进行相应的替换。

如果有的话，我希望第一个查询运行得更慢，因为 WHERE 子句和 REPLACE 函数都会检查 tester1.

我认为我对数据库如何处理查询的分析过于简单化。有人可以解释一下性能差异吗？

Answer 1

不带 WHERE 子句的查询将替换应用于表中的每一行。这意味着对于每条记录，您都将锁定该行存储 stringcol 的磁盘区域，并获取内容、执行替换并将其写回。根据数据库的不同，即使数据没有更改也可能会发生这种情况。这可能很聪明，因为验证是否存在更改可能比将其写入磁盘需要更长的时间。此外，由于单个语句正在更新每条记录，因此数据库将为每个更改创建事务日志。

添加 WHERE 子句将至少在进行更改之前过滤每一行。即使LIKE '%value%'不使用索引，数据库也会在获取锁并更新值之前检查字段的值。这可能会导致锁定的行数和应用的更改数量大幅减少。