如何高效更新百万级网站排名表？SQL无锁批量更新方案探讨

问题背景 #

假设我们有一张存储互联网Top 100万访问量网站的表格，结构如下：

我们每周统计各网站访问量并更新排名。若某网站从900000名跃升至1000名，需将受影响的所有行排名+1，请问最高效的更新方式是什么？是否存在SQL技术支持此类批量更新且不锁表？

注：此处暂不考虑其他排名变动，仅做简化场景探讨。

高效更新方式 #

1. 范围批量更新 #

直接针对排名区间执行单条UPDATE语句，是最直接高效的方式，避免逐行更新的开销：

UPDATE website_rankings
SET Ranking = Ranking + 1
WHERE Ranking BETWEEN 1000 AND 899999;

之后再将目标网站的排名设为1000：

UPDATE website_rankings
SET Ranking = 1000
WHERE Address = '目标网站域名'; -- 建议用唯一标识字段（如ID）替代域名，更可靠

这种方式的核心优势：

• 仅触发两次SQL执行，而非数十万次逐行更新
• 数据库引擎会对范围更新做针对性优化，效率远高于循环更新

2. 确保索引优化 #

给Ranking字段建立普通索引，让WHERE Ranking BETWEEN ...的条件能快速定位到目标行，避免全表扫描，大幅提升更新速度。

无锁/低锁批量更新的SQL技术支持 #

大部分现代关系型数据库都支持低锁甚至无锁的批量更新，核心是利用引擎特性和更新策略：

1. 行级锁替代表锁 #

只要更新语句基于索引定位行（比如上面用Ranking索引），数据库只会对受影响的行加行级锁，不会锁整个表。MySQL InnoDB、PostgreSQL、SQL Server等主流引擎默认都采用这种策略，只有当更新条件无法利用索引导致全表扫描时，才会升级为表锁。

2. 乐观锁配合版本号 #

如果需要完全避免锁（高并发场景），可以给表新增version字段，更新时带上版本号校验：

UPDATE website_rankings
SET Ranking = Ranking + 1, version = version + 1
WHERE Ranking BETWEEN 1000 AND 899999 AND version = 当前记录版本号;

这种方式不依赖数据库锁，通过版本号冲突处理并发更新，适合对锁敏感的场景，但需要额外处理更新失败后的重试逻辑。

3. 分批更新缓解锁压力 #

若受影响行数极多（比如89万行），一次性更新可能持有锁时间过长，可拆分成多个小范围分批更新：

-- 示例：分10批更新，每批处理约9万行
UPDATE website_rankings
SET Ranking = Ranking + 1
WHERE Ranking BETWEEN 1000 + (n-1)*90000 AND 1000 + n*90000 - 1;

每批更新行数少，锁持有时间短，既能减少对其他业务的影响，又能保持整体更新效率。

内容的提问来源于stack exchange，提问作者hamslice5