Oracle中按排序添加position列的高效实现方案问询

首先先明确你的场景和需求：
你有一张包含3400万行数据的表table1，结构和示例数据如下：

-- 原表table1结构及示例数据
id | ele_id_1 | ele_val    | ele_id_2
---|----------|------------|---------
1  | 2        | 123        | 1
1  | 1        | abc        | 1
1  | 4        | xyz        | 2
4  | 1        | 4          | 456
1  | 2        | 5          | 22
1  | 2        | 4          | 344
1  | 2        | 3          | 6
1  | 2        | 2          | Test Name
1  | 2        | 1          | Hello 1

需求是：按id分组，每组内先按ele_id_1升序、再按ele_id_2升序排序，给每行添加position列标记组内的位次，预期输出如下：

-- 预期输出结果
id | ele_id_1 | ele_val    | ele_id_2   | position
---|----------|------------|------------|---------
1  | 2        | 123        | 1          | 2
1  | 1        | abc        | 1          | 1
1  | 4        | xyz        | 2          | 4
4  | 1        | 4          | 456        | 1
1  | 2        | 5          | 22         | 5
1  | 2        | 4          | 344        | 4
1  | 2        | 3          | 6          | 3
1  | 2        | 2          | Test Name  | 2
1  | 2        | 1          | Hello 1    | 1

针对3400万行的大数据量，必须用最高效的方案来实现，下面给你详细说明：

核心最优方案：窗口函数+索引优化 #

窗口函数是处理这类分组排序位次场景的首选，它不需要多次扫描表，单次扫描就能完成计算，性能远优于传统的子查询、关联查询方法。

1. 基础查询语句（直接生成结果） #

如果只是需要查询时展示position列，直接用以下SQL即可（支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库）：

SELECT 
    id,
    ele_id_1,
    ele_val,
    ele_id_2,
    -- 按id分组，组内按ele_id_1、ele_id_2升序排序，生成位次
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY ele_id_1 ASC, ele_id_2 ASC) AS position
FROM table1;

这里用ROW_NUMBER()是因为它会给每组内排序后的行分配连续的位次（即使排序键相同，也会按行的存储顺序分配不同位次）；如果需要相同排序键的行共享同一位次，可以换成RANK()或DENSE_RANK()，根据你的实际需求调整。

2. 性能优化关键：创建联合索引 #

3400万行数据下，排序操作很容易成为性能瓶颈，所以必须创建合适的联合索引来避免全表排序：

-- 创建联合索引，让数据库直接利用索引完成分组和排序
CREATE INDEX idx_table1_id_ele1_ele2 ON table1 (id, ele_id_1, ele_id_2);

这个索引可以让数据库跳过额外的排序步骤（避免Using filesort），直接按索引顺序读取数据并计算位次，性能会提升非常明显。

3. 给原表添加position列（高效方式） #

如果需要把position列永久添加到表中，绝对不要用全表UPDATE（3400万行的UPDATE会极慢，且长时间锁表），正确的做法是创建新表存储带位次的数据，然后替换原表：

-- 第一步：创建新表并插入带position的数据
CREATE TABLE table1_with_position 
AS
SELECT 
    id,
    ele_id_1,
    ele_val,
    ele_id_2,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY ele_id_1 ASC, ele_id_2 ASC) AS position
FROM table1;

-- 第二步：验证新表数据无误后，替换原表
RENAME TABLE table1 TO table1_old;
RENAME TABLE table1_with_position TO table1;

-- （可选）验证完成后删除旧表
-- DROP TABLE table1_old;

特殊情况：MySQL 5.x及以下版本 #

如果你的数据库是MySQL 5.x（不支持窗口函数），只能用用户变量来模拟，但这个方法在3400万行下性能会很差，仅作应急参考：

SELECT 
    id,
    ele_id_1,
    ele_val,
    ele_id_2,
    @rn := CASE WHEN @prev_id = id THEN @rn + 1 ELSE 1 END AS position,
    @prev_id := id
FROM (
    -- 先按排序规则预排序
    SELECT * FROM table1 ORDER BY id, ele_id_1, ele_id_2
) t,
-- 初始化变量
(SELECT @prev_id := NULL, @rn := 0) vars;

强烈建议升级到MySQL 8.0来使用窗口函数，否则大数据量下的性能会无法接受。

为什么这个方案高效？ #

• 窗口函数的时间复杂度是O(n log n)（带索引的话可以降到O(n)），而传统子查询的时间复杂度是O(n²)，3400万行下差距天差地别。
• 联合索引避免了全表排序，让数据库可以直接按索引顺序读取数据，大幅减少IO和CPU消耗。
• 创建新表的方式避免了全表更新的锁表问题，对业务影响极小。

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