优化含MIN函数的SQL查询：关联最小编号对应字符串值的性能问题

首先纠正你原SQL里的两个明显笔误：

• 子查询里的group by deal_no应该是group by transaction_id，否则分组逻辑完全错误；
• 关联条件里的gd.step_id应该是gd.status，因为子查询是从status字段提取的最小编号，关联时要对应回原表的status字段。

原SQL的性能瓶颈在于：需要对global_data表做两次全表扫描（一次子查询分组，一次主查询关联），而且每次都要计算substr(status,2,3)，无法利用索引加速，导致查询效率极低。

优化方案1：使用窗口函数减少表扫描次数 #

窗口函数可以在单次表扫描中完成分组、排序和筛选，避免两次全表扫描的开销。具体写法如下：

WITH data AS (
    SELECT 
        transaction_id,
        status,
        string_value,
        -- 按transaction_id分组，取status编号最小的那条记录
        ROW_NUMBER() OVER (
            PARTITION BY transaction_id 
            ORDER BY CAST(SUBSTR(status, 2, 3) AS INT) ASC
        ) AS rn
    FROM global_data
)
SELECT transaction_id, status, string_value
FROM data
WHERE rn = 1;

如果status的后缀（比如A001里的001）本身是固定长度的数字字符串，也可以直接按字符串排序（因为'001' < '002'的字符串比较结果和数字一致），省去类型转换的开销：

WITH data AS (
    SELECT 
        transaction_id,
        status,
        string_value,
        ROW_NUMBER() OVER (
            PARTITION BY transaction_id 
            ORDER BY SUBSTR(status, 2, 3) ASC
        ) AS rn
    FROM global_data
)
SELECT transaction_id, status, string_value
FROM data
WHERE rn = 1;

优化方案2：添加索引加速窗口函数排序 #

如果这个查询是高频操作，建议创建复合索引，让窗口函数的排序过程直接利用索引，避免内存排序或磁盘排序的开销：

-- 创建transaction_id + status的复合索引
CREATE INDEX idx_global_data_trans_status ON global_data(transaction_id, status);

如果status的后缀数字经常被用来排序/筛选，可以进一步创建计算列并建立索引，彻底避免每次查询时的字符串截取和类型转换：

-- 添加计算列，存储status的后缀数字
ALTER TABLE global_data ADD COLUMN status_num INT GENERATED ALWAYS AS (CAST(SUBSTR(status, 2, 3) AS INT)) STORED;

-- 创建transaction_id + 计算列的复合索引
CREATE INDEX idx_global_data_trans_statusnum ON global_data(transaction_id, status_num);

对应的查询SQL可以简化为：

WITH data AS (
    SELECT 
        transaction_id,
        status,
        string_value,
        ROW_NUMBER() OVER (
            PARTITION BY transaction_id 
            ORDER BY status_num ASC
        ) AS rn
    FROM global_data
)
SELECT transaction_id, status, string_value
FROM data
WHERE rn = 1;

额外说明 #

如果你的数据库支持（比如MySQL 8.0+、PostgreSQL 10+、SQL Server 2012+），窗口函数是这类"取分组内Top1"场景的最优解，性能远优于子查询关联的写法。

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