如何在数据库中高效存储并维护支持行上下移动的行位置

你原来用连续整数row_num的方案在小数据量下没问题，但到了十亿级别的时候，大范围移动会触发海量更新，性能肯定顶不住。这里给几个更高效的思路：

1. 浮点型存储位置（轻量场景首选） #

把row_num改成FLOAT8（双精度浮点数），移动行时不需要更新其他行的位置，只需要给目标行设置一个介于目标位置前后两行的数值即可：

• 比如现有行位置是1.0、2.0，要把第二行移到第一行前面，直接把它的row_num设为0.5；
• 后续如果要在0.5和1.0之间插入新行，就用(0.5+1.0)/2=0.75作为新行的位置，以此类推。
  这种方式只需要更新被移动的那一行，完全避免批量更新，性能拉满。唯一需要注意的是浮点数精度问题，但双精度足够支撑几百万次操作，之后可以定期整理数值（比如重新分配连续整数）来重置精度。

2. 链表式结构（适合频繁移动场景） #

放弃连续位置的思路，给每行加两个字段：prev_row_id和next_row_id，分别存储上一行和下一行的主键ID。移动行时只需要修改被移动行本身以及它原来前后行、目标位置前后行的这四个字段：
比如把行B移到行A前面：

1. 先断开B原来的链接：修改B的前一行的next_row_id为B的next_row_id，修改B的后一行的prev_row_id为B的prev_row_id；
2. 再把B连到A前面：修改A的prev_row_id为B的ID，修改B的next_row_id为A的ID，修改原来A的前一行（如果存在）的next_row_id为B的ID，修改B的prev_row_id为原来A的前一行ID。
   这种方式不管数据量多大，移动操作的开销都是常数级。缺点是查询排序时需要遍历链表，或者用递归CTE生成有序列表，查询性能略逊于row_num直接排序，适合写操作远多于读操作的场景。

3. 分段式整数位置（平衡读写性能） #

把row_num拆成两部分：section_num（段号）和position_in_section（段内位置），每个段固定容纳N行（比如1000行）。移动行时：

• 同一段内移动，只需要更新段内相关行的position_in_section；
• 跨段移动，只需要修改被移动行的section_num和position_in_section，以及目标段的最多N行的位置，不会涉及其他段的行。
  这种方式把批量更新的范围限制在一个段内，避免全表更新，同时查询排序时可以用section_num, position_in_section直接排序，查询性能接近连续整数方案，适合读写都频繁的场景。

对比原方案 #

你原来用CASE语句批量更新的方式，语法上可行，但十亿行数据的更新会占用大量数据库资源，锁表时间长，甚至可能导致业务中断。上面的几种方案都能把更新操作的复杂度从O(n)降到O(1)或O(k)（k为固定小数值），彻底解决大数据量下的性能问题。

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