如何优化船舶引擎表数据拆分：替代多次Union All的高效方案

我有一张名为TblVesselEngineSummary的表，其中EngineConfiguration字段对应不同的引擎配置，需要按如下规则拆分记录：

• Single：单引擎，拆分为1条记录
• Twin：双引擎，拆分为2条记录
• Triple：三引擎，拆分为3条记录

目前我通过三次UNION ALL关联同表的方式实现拆分，但这种方式需要多次扫描表，性能不佳；为保证EngineId主键唯一，我给EngineSummaryID加上90000000或50000000。请问有没有更高效的实现方式？

当前使用的SQL代码 #

SELECT * FROM (

Select
    EngineSummaryID AS EngineId, VesselID_PK AS EngineBoatId,
    
    'Port' AS EngineLocation, EngineHoursPort AS 'Hours', 
    strEngineManufacturer AS 'EngineMake', strEngineModel AS 'EngineModel', 
    strEngineType AS 'EngineType', EngineYear, 
    EngineHP AS 'PowerHP', SerialForPort AS 'SerialNumber', 
    '1' AS 'Sort', UpdatedOn 

from TblVesselEngineSummary 
WHERE IsActiveVesselSummary = 'Y'

UNION ALL
--'Twin','Triple'
Select
    EngineSummaryID + 90000000 AS EngineId, VesselID_PK AS EngineBoatId,
    
    'Starboard' AS EngineLocation, EngineHoursStarboard AS 'Hours', 
    strEngineManufacturer AS 'EngineMake', strEngineModel AS 'EngineModel', 
    strEngineType AS 'EngineType', EngineYear, 
    EngineHP AS 'PowerHP', SerialForPort AS 'SerialNumber', 
    '2' AS 'Sort', UpdatedOn 

from TblVesselEngineSummary 
WHERE IsActiveVesselSummary = 'Y' AND EngineConfiguration IN ('Twin','Triple')

UNION ALL
--'Triple'
Select
    EngineSummaryID + 50000000 AS EngineId, VesselID_PK AS EngineBoatId,
    
    'Starboard' AS EngineLocation, EngineHoursStarboard AS 'Hours', 
    strEngineManufacturer AS 'EngineMake', strEngineModel AS 'EngineModel', 
    strEngineType AS 'EngineType', EngineYear, 
    EngineHP AS 'PowerHP', SerialForPort AS 'SerialNumber', 
    '3' AS 'Sort', UpdatedOn 

from TblVesselEngineSummary 
WHERE IsActiveVesselSummary = 'Y' AND EngineConfiguration = 'Triple'


) allEngines  ORDER BY EngineBoatId, Sort

样本数据 #

优化方案：使用数字生成器关联替代多次UNION ALL #

核心思路 #

通过生成一个包含1、2、3的临时数字集合，与原表进行关联，根据EngineConfiguration的值过滤出对应需要的行数。这种方式只需要扫描原表一次，避免了多次扫描带来的性能开销，同时通过CASE表达式统一处理EngineId、EngineLocation等字段的逻辑。

优化后的SQL代码 #

SELECT
    CASE 
        WHEN n.num = 1 THEN t.EngineSummaryID
        WHEN n.num = 2 THEN t.EngineSummaryID + 90000000
        WHEN n.num = 3 THEN t.EngineSummaryID + 50000000
    END AS EngineId,
    t.VesselID_PK AS EngineBoatId,
    CASE 
        WHEN n.num = 1 THEN 'Port'
        WHEN n.num = 2 THEN 'Starboard'
        WHEN n.num = 3 THEN 'Center' -- 匹配Triple的第三个引擎位置
    END AS EngineLocation,
    CASE 
        WHEN n.num = 1 THEN t.EngineHoursPort
        WHEN n.num = 2 THEN t.EngineHoursStarboard
        -- 针对Triple的Center引擎小时数，建议表结构单独存储，这里根据样本数据做适配
        WHEN n.num = 3 THEN CAST(SUBSTRING_INDEX(t.EngineHoursStarboard, ' ', 3) AS UNSIGNED)
    END AS Hours,
    t.strEngineManufacturer AS EngineMake,
    t.strEngineModel AS EngineModel,
    t.strEngineType AS EngineType,
    t.EngineYear,
    t.EngineHP AS PowerHP,
    t.SerialForPort AS SerialNumber, -- 若有对应端口的序列号字段，可在这里扩展CASE逻辑
    CAST(n.num AS VARCHAR(1)) AS Sort,
    t.UpdatedOn
FROM TblVesselEngineSummary t
JOIN (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3
) n ON (
    t.EngineConfiguration = 'Single' AND n.num = 1
    OR t.EngineConfiguration = 'Twin' AND n.num IN (1,2)
    OR t.EngineConfiguration = 'Triple' AND n.num IN (1,2,3)
)
WHERE t.IsActiveVesselSummary = 'Y'
ORDER BY t.VesselID_PK, n.num;

优化优势 #

1. 性能提升：原方案需要扫描表3次，优化后仅扫描1次，数据量越大，性能提升越明显。
2. 逻辑集中：所有拆分逻辑集中在JOIN条件和CASE表达式中，后续修改配置规则时更易维护。
3. 主键唯一性：保留原有的EngineSummaryID加固定值的逻辑，确保EngineId全局唯一。
4. 适配样本数据：针对Triple引擎的Center小时数，通过字符串处理提取对应数值（建议后续优化表结构，将Center引擎的小时数单独存储为字段，避免混存）。

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