业务背景

在微信商城开发过程中，运营团队提出了一个看似简单但技术实现颇具挑战的需求：

指定商品在商品列表中固定排位，其他商品按自然排序（如价格从低到高）展示。例如：

爆品补贴（固定位置1、2）

限时特惠（固定位置2、3、11、21、31、41）

这个需求背后是运营团队对核心营销资源位的强管控需求，但在技术实现上却面临排序逻辑冲突、商品重复展示、性能瓶颈等挑战。

技术方案演进

第一阶段：模块化方案（被否决）

最初我们提出了模块化专区的方案：

# 伪代码：模块化页面结构
page_structure = [
    {"type": "banner", "data": [...]},
    {"type": "爆品专区", "data": [商品A, 商品B]},  # 固定位置1-2
    {"type": "限时特惠", "data": [商品C, 商品D, ...]},  # 固定位置3-5
    {"type": "普通商品", "data": "按价格自然排序"}  # 剩余位置
]

否决原因：运营认为模块间商品重复难以监控，且灵活性过高增加了运营复杂度。

第二阶段：动态混合排序方案（性能瓶颈）

我们设计了实时混合排序算法：

# 伪代码：动态计算每个位置的商品
def get_mixed_sorted_products():
    fixed_positions = {1: 商品A, 2: 商品B, 11: 商品C}  # 固定位映射
    natural_sorted_products = get_natural_sorted_products()  # 按价格排序

    final_list = []
    current_position = 1

    while len(final_list) < page_size:
        if current_position in fixed_positions:
            # 固定位商品
            final_list.append(fixed_positions[current_position])
        else:
            # 自然排序商品（需排除已固定的商品）
            next_product = get_next_natural_product(exclude=fixed_positions.values())
            if next_product:
                final_list.append(next_product)
        current_position += 1

    return final_list

问题：商品数量大时，实时计算导致SQL查询复杂，性能瓶颈明显。

最终方案：双字段预排序 + 事务更新

数据库设计

-- 商品表增加排序字段
ALTER TABLE products ADD COLUMN (
    fixed_order INT DEFAULT NULL COMMENT '固定排序位置，NULL表示非固定位商品',
    app_order INT NOT NULL DEFAULT 0 COMMENT '最终展示排序值'
);

-- 创建索引优化排序查询
CREATE INDEX idx_app_order ON products(app_order);
CREATE INDEX idx_fixed_order ON products(fixed_order);

核心实现逻辑

1. 更新自然排序商品

-- 使用MySQL 8窗口函数高效生成自然排序
UPDATE products p
JOIN (
    SELECT 
        id, 
        ROW_NUMBER() OVER (ORDER BY price ASC, created_at DESC) as new_order
    FROM products 
    WHERE fixed_order IS NULL
) tmp ON p.id = tmp.id
SET p.app_order = tmp.new_order;

2. 插入固定位商品

-- 先循环把固定排序后面数据后移(我们是按数字越小排到越前，所以需要先把固定位置从小到大排序好再依次插入)
update products set app_order = app_order + 1 where app_order >=  #{fixed_order};

-- 再一次更新固定排序
update products set app_order = #{fixed_order} where fixed_order > 0

3. 保持事务一致性，隔离排序时对前端页面造成的影响

START TRANSACTION;

-- 重置所有排序值为初始状态
UPDATE products SET app_order = 0 WHERE app_order > 0;

-- 执行自然排序更新
UPDATE products p
JOIN (...) tmp ON p.id = tmp.id
SET p.app_order = tmp.new_order;

-- 执行固定位插入
UPDATE products ...;

COMMIT;

前端查询

-- 简单高效的查询（支持分页）
SELECT id, name, price, image 
FROM products 
ORDER BY app_order ASC 
LIMIT #{pageSize} OFFSET #{offset};

方案优势

性能极致优化

• 预计算排序结果，查询时直接使用ORDER BY app_order

• 避免实时混合排序的复杂计算

• 支持高效分页查询

数据一致性保障

• 事务更新确保排序操作的原子性

• 避免前端看到中间状态数据

• 商品重复问题从根本上解决

运营操作简单

• 后台只需设置商品的fixed_order值

• 系统自动处理混合排序逻辑

• 固定位冲突由系统自动检测

扩展性强

• 支持多种自然排序规则切换

• 固定位数量可灵活调整

• 易于监控和调试