对许多行执行UPDATE和REPLACE

Question

我的表中有1700万行，其中一些行在[url]列中包含一个URL，我想替换所有行中URL的某些部分。

我的表中有800万行需要更新。

我已经对我的查询进行了一些优化，用4000个批次进行了替换，但这仍然需要很长时间：

declare @Rows INT,
@BatchSize INT;
SET @BatchSize = 4000;
SET @Rows = @BatchSize; -- initialize just to enter the loop
while (@Rows = @BatchSize)
BEGIN
UPDATE TOP (@BatchSize) [dbo].[table]
SET
[url] = replace([url], 
'?sv=2019-12-12&foo=bar',
'?sv=2020-02-10&bar=foo')
where [url] like '%foo=bar'
END;

你知道我该如何改进这项任务吗？

Answer 1

问题可能是由于WHERE子句的原因，每个update都需要扫描整个表。具有讽刺意味的是，一种解决方案是跳过批次。UPDATE将花费更长的时间，但它将只扫描表一次。

第二种备选方案是索引以优化WHERE子句。该条款有三种方法：

• 使用全文索引
• 创建一个"；基于表达式的"；索引，在SQL Server中需要添加一个计算列，然后对计算列进行索引
• 创建筛选的索引

第三种方法是最简单的方法。然而，遗憾的是，SQL Server不允许在筛选的索引中使用like，因此它在您的情况下不起作用。

所以，试试这个版本：

alter table t add url_suffix as (right(url, 7);
create index idx_table_url_suffix on table(url_suffix);

然后您可以将where逻辑表述为：

where url7 = 'foo=bar';

Answer 2

您可能需要了解MySQL字符串函数。它们可能允许您在SQL语句中完成整个操作(因为您只是在进行替换(。

类似于：

UPDATEtableSET some_text_row=replace(some_text_row，'some短语'，'其他短语'(WHERE some_text_row LIKE'%some短语"%"；