实习的时候遇到一个问题，运维反馈频繁的使用置顶功能导致数据库CPU使用率过高。

然后仔细了解后发现很有意思，就是一个后台帖子列表，要实现上移，下移，置顶的功能。

然后置顶的时候是怎么操作的呢？为了保持排序数字的有序且唯一，置顶的时候就是将最底部的记录和上条记录交换，就这样一直交换，直到交换到顶部。如果表里有10 w 条数据，就要交换 10w 次，性能自然是差，然后同事权哥最后的优化方法就是批量更新。

但是我仔细想想这个功能，感觉很有意思，所以对权哥说，我要研究出更好的方案。

先说总结

我把问题抽象成了一个具体的场景，就是实现列表的上移，下移，置顶，取消置顶功能，上移动和下移就是交换两条记录的排序值，置顶的话之前实现就是多次交换，如果10w条的表，假设排序值最低的那个记录置顶，就交换10w次。

后来优化只是用了批量更新。

所以我当时研究的时候发现核心问题是“全局唯一id，然后排序字段细化”

比如

原来是 0 ,1,2,3,4,5,将4置顶就需要四次交换，当时加一个合适偏移量，假设全表排序000,100,200,300,400,500，后面4个是一组，这个时候要把400置顶，把他的值变为100-1=99就可以，即实现了组内置顶，又实现了全局有序。

然后就只需要一次操作就可以完成置顶功能了

1. 全表实现列表上移，下移，置顶

1.1 原来的操作逻辑

CREATE TABLE Items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sort_order INT NOT NULL,
    INDEX idx_sort_order (sort_order)
);

上移操作

交换当前值和之前值的排序就可以了，下移操作同理

-- 获取当前条目的 sort_order
SELECT sort_order INTO @current_sort_order FROM Items WHERE id = [当前条目的ID];
-- 查找上一条记录
SELECT id, sort_order INTO @prev_id, @prev_sort_order
FROM Items
WHERE sort_order < @current_sort_order
ORDER BY sort_order DESC
LIMIT 1;
-- 交换 sort_order
UPDATE Items
SET sort_order = CASE
    WHEN id = [当前条目的ID] THEN @prev_sort_order
    WHEN id = @prev_id THEN @current_sort_order
END
WHERE id IN ([当前条目的ID], @prev_id);

原来的置顶操作：

通过不断与上一个记录交换位置，逐步将当前记录向上移动，直到到达顶部。

START TRANSACTION;
-- 获取当前条目的 sort_order
SELECT sort_order INTO @current_sort_order
FROM Items
WHERE id = [当前条目的ID];
-- 初始化控制变量
SET @continue := 1;
WHILE @continue DO
    -- 查找上一条记录
    SELECT id, sort_order INTO @prev_id, @prev_sort_order
    FROM Items
    WHERE sort_order < @current_sort_order
    ORDER BY sort_order DESC
    LIMIT 1;
    IF @prev_id IS NULL THEN
        -- 已经在顶部，退出循环
        SET @continue := 0;
    ELSE
        -- 交换 sort_order
        UPDATE Items
        SET sort_order = CASE
            WHEN id = [当前条目的ID] THEN @prev_sort_order
            WHEN id = @prev_id THEN @current_sort_order
        END
        WHERE id IN ([当前条目的ID], @prev_id);
        -- 更新当前 sort_order 为交换后的值
        SET @current_sort_order := @prev_sort_order;
    END IF;
END WHILE;
-- 提交事务
COMMIT;

批量更新优化后的操作：

将记录直接移动到正确的置顶的位置，同时批量更新受到影响的记录，保持其他记录的相对顺序

START TRANSACTION;
-- 获取当前条目的 sort_order
SELECT sort_order INTO @current_sort_order
FROM Items
WHERE id = [当前条目的ID];
-- 获取需要调整的最小 sort_order
SELECT MAX(sort_order) INTO @max_sort_order
FROM Items
WHERE sort_order < @current_sort_order;
-- 如果不存在比当前条目更小的 sort_order，说明已经在顶部
IF @max_sort_order IS NULL THEN
    -- 无需操作，直接提交事务
    COMMIT;
ELSE
    -- 批量更新受影响的条目，将它们的 sort_order 增加 1
    UPDATE Items
    SET sort_order = sort_order + 1
    WHERE sort_order > @max_sort_order AND sort_order < @current_sort_order;
    -- 将当前条目的 sort_order 设置为最大的小于当前 sort_order 的值
    UPDATE Items
    SET sort_order = @max_sort_order + 1
    WHERE id = [当前条目的ID];
    -- 提交事务
    COMMIT;
END IF;

但是这样做也会有问题，批量更新的耗时仍然会比较耗时，并且会重建索引，导致大量的行锁定等。所以我觉得可以有更优雅的解决方案：

1.2 新的思路

1.可以用时间戳做排序，然后置顶直接将时间戳变为当前时间就可以了

2.其实也可直接加一个是否置顶字段，然后排序的时候先按照置顶字段排序，然后再按照普通排序字段，如果取消置顶，让置顶字段失效就可以。思路大同小异。

但是这些都没法解决一个问题：分组置顶不影响全表有序

2. 全表分组实现列表上移，下移，置顶，取消置顶

上面的全表实现置顶功能其实还好，但是如何实现分组呢？每个组都有自己的置顶，然后在这个组内关键字搜索出来的结果也有置顶，这时候操作逻辑就不一样了。

首先是上移和下移，这个都是一样的，就是交换两条数据，只是 wherer 条件多了。

但是置顶功能呢？

我们要分组实现置顶，这个可以粗暴的在每一个组单独设置一个组号，然后再按照组号去排序，但是置顶的时候只应该在组内置顶，而不应该全局置顶。所以全局的排序字段应该是唯一的，这个时候貌似成了全局唯一有序 id 的问题，我在组内置顶，我比你本地组的 id+1，但是不会影响到整体的排序意图。

本质就是将排序id细化，防止重复，假设全表排序000,100,200,300,400,500，后面4个是一组，这个时候要把400置顶，把他的值变为100-1=99就可以，即实现了组内置顶，又实现了全局有序。

3.其他

其实这里想到还可以用链表，在Mysql记录里面加一个前后指针，同时在redis做一个hash的映射，实现这个一个linkedHashMap的思路，但是这样实现起来属实有点复杂还要修改表结构。

不知道有没有更优雅的设计，如果有大佬看到的话还请指教一下。

涛涛实习记录——实现置顶功能