SQL数据库中实现用户自定义顺序存储数据的方案有哪些？

问题1：新增辅助排序字段的方案是否正确？ #

这个方案是完全正确的，也是自定义排序需求下最基础、最普及的实现方案，逻辑简单直白，没有额外的复杂依赖，在数据量小、排序调整频率低的场景下非常好用，不存在原理性错误，只是在高频率调整、大数据量的场景下存在优化空间。

问题2：大数据量下批量修改排序值会不会很慢？ #

会，而且性能影响会非常明显：

• 如果order字段建了索引，批量更新除了修改数据本身，还要维护索引树的结构，数据量达到十万级以上时，更新操作会持有行锁甚至表锁，期间相关的读写请求都会被阻塞，直接影响线上业务可用性。
• 如果order字段没建索引，每次查询时的ORDER BY操作都会走全表排序，数据量上来后查询性能会先崩，反而得不偿失。

问题3：更优的实现方案有哪些？ #

针对插入不需要批量修改原有数据的需求，有几个成熟的优化方案可以选：

• 间隙排序法
  初始化排序值时不要用连续整数，把步长拉大为100、1000这类较大的数值，比如示例中的三个数据排序值可以设为0、100、200，需要在foo和hello之间插入数据时，直接给新数据赋值50即可，不需要修改任何原有数据。只有当两个值的间隙被插满时，才需要做一次全量重排，能把重排的频率降低几个数量级。
• 浮点型排序字段
  本质是间隙排序的变种，把order字段的类型从整型改为浮点型，两个数值之间可以无限插入中间值：比如0和1之间可以插0.5，0和0.5之间可以插0.25，理论上永远不会出现间隙用完的情况，几乎不需要做全量重排，只需要注意极少数数据库的浮点数排序精度问题，普通业务场景完全够用。
• 链表存储结构
  新增prev_id、next_id两个字段，存储当前记录前后相邻记录的主键，插入新数据时只需要修改原先后后两条记录的关联字段即可，完全不需要动其他数据。缺点是查询全量排序后的列表时需要递归拉取，适合排序调整频率极高、单组列表长度不超过数千条的场景，比如后台的拖拽排序功能。
• 固定规则直接查询排序
  如果你的自定义排序规则是固定不变的枚举值，不需要动态调整，甚至不需要加辅助字段，直接用数据库内置函数实现即可，比如MySQL可以写：

  SELECT name FROM 表名 ORDER BY FIELD(name, 'foo', 'hello', 'bar');
  

  就能直接返回你要的自定义顺序，适合排序规则不会动态修改的场景。

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